Weekly Average by Monthly Average Ratio

[Karan_Veer_Sing]

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

[zckymc]

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:

Looker Help Center

[Analytic Block] Daily, Weekly, Monthly Active Users

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...

[Karan_Veer_Sing]

@zckymc Thank you for the solution. I am following the guide and will let you know how it goes.

[Karan_Veer_Sing]

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

[zckymc]

Hi @Karan_Veer_Sing

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.

[Karan_Veer_Sing]

That makes sense. But would it also be better to get the users with their activity date from the last 90 days?