Hi Guys,
I am new to Looker and have come across a requirement, where I need to display the weekly average users by monthly average ratio (on weekly basis). As the grain of the data is different, not sure how to calculate this ratio using table calculations.
I trying to achieve this by using the Activity date and active user count. Any help on this would be appreciated.
Thanks,
Karan
Hi @Karan_Veer_Sing,
If you need a quick and easy but less accurate solution, you can do so with 3 table calculations:
7DAU
mean(offset_list(${page_views.user_count},7,7))
30DAU
mean(offset_list(${page_views.user_count},30,30))
Ratio
${7dau}/${30dau}
This is not entirely accurate, as Looker Calcs won’t dedupe against users who were active on 2 or more days during the 7 or 30 days periods, it will count them twice.
If you want a more accurate method you can follow this guide:
About This Block Active Users is a common metric you can use to measure how customers are engaging with your product over different timeframes. In the Daily, Weekly, Monthly Active Users Analytical...
@zckymc Thank you for the solution. I am following the guide and will let you know how it goes.
Hi @zckymc, I followed the guide (had to modify it a bit to get it to work). Just one question, under Redshift code, in SQL for active_users view the following snippet is given:
WITH daily_use AS (
-- ## 2 ) Create a table of days and activity by user_id
SELECT
user_id
, DATE_TRUNC('day', created_at) as activity_date
FROM orders
GROUP BY 1, 2
)
-- ## 3) Cross join activity and dates to build a row for each user/date combo with
-- days since last activity
SELECT
daily_use.user_id
, wd.date as date
, MIN(wd.date::date - daily_use.activity_date::date) as days_since_last_action
FROM ${dates.SQL_TABLE_NAME} AS wd
LEFT JOIN daily_use
ON wd.date >= daily_use.activity_date
AND wd.date < daily_use.activity_date + interval '30 day'
GROUP BY 1,2
My question is, what is the use of including user_id while creating daily_use, if it is not being used to join to the dates table?
Also, this solution worked fine in development mode with shorted duration. But, in production it takes forever to build the PDT. Any pointer on this will be helpful.
Regards,
Karan
You’re selecting user_id
in the daily_use
table as you want to know if a user_id
was active on a certain date. Without selecting user_id
you’d just end up with a list of dates, not knowing if a certain user_id
was active on that day or not.
Regarding performance, the resulting table can end up being huge if you don’t limit the window_dates used. We choose to limit the dates
table to only the past 60 days from today.
That makes sense. But would it also be better to get the users with their activity date from the last 90 days?