Skip to main content

Hi

I am having problems finding a way to count active days the last 28 days based on an activity. I can only count the sum of activities within a periode, but i need the result to be a count of active days. It does not matter if the did the event 1 or 20 times pr. day. 

Can anyone help with this trait?

Anne :-)

Hi Anne,

Do you happen to have Profile Sync activated and/or a data warehouse connected to your source? And are you familiar with rETL by any chance?

In that case the easiest way that I think you could get this is by doing a rETL query on the table that has this the events for this activity. It would look something like:

SELECT 
COUNT(DISTINCT FROM_UNIXTIME(your_timestamp_column, '%Y-%m-%d')) AS observed_days,
user_id
FROM events_table
WHERE your_timestamp_column >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 28 DAY));

This would give a count for each distinct day that was observed in the last 28 days.

You could send this as an identify call into Unify or any other destination that would carry the amount of days of activity over the last 28 days.

Best,
Wouter

Hi Wouter

Thank you.

I was hoping that I could do this by using the functions in Unify and Engage, as this must be a calculation relevant for many companies. But it seems that it’s not possible. I guess I need to involve my developer. 

Best, Anne

Reply


Terms & Conditions