Question about last active

[Kalmer]

Hello since right now our database dosen't have "last active" then i'm trying to create this graph which will give me clients who have not been active in the last 30 days.
So we are an emailing company and have we defined values of client actions in emails. The actions are "click", "view", "broken", "sent" and some more which aren't really necessary right now. The way i'm sure they are not active is done by this script:

Count({$<action_type={'Sent'}>} action_timestamp) - Count({$<action_type={'View'}>} action_timestamp)

So all the clients who got the email (SENT) i delete the clients who used (VIEW) and the result is "NoAction" clients.

Now how do i add a checker if the same client_id has only gotten sent email and he hasn't done anything else than get the Sent for 30 days.

Best regards!

Kalmer

[Gysbert_Wassenaar · Accepted solution]

Perhaps like this:

count({<action_timestamp={'>$(=Today()-30)<=$(=Today())'},action_type={'Sent'},

           ClientID=E({<action_timestamp={'>$(=Today()-30)<=$(=Today())'},action_type-={'Sent'}>}ClientID)>}ClientID)

But without example data it's hard to make a good guess. If this doesn't work (which wouldn't suprise me) then it would help if you can post a small sample of the source data.

Anyway, don't forget to take a look at this blog post: Excluding values in Set Analysis‌.

[Kalmer]

Thank you Gysbert!

IT acctually worked (had to change a few table names but got the trick done). 😃 cheers!