Question

Last 14 days user population

  • 12 April 2021
  • 3 replies
  • 53 views

Hello, I have a dimension called activity_day which is just a column of daily dates from 2020-01-01 to 2021-12-31, etc. I also have a measure active_users that counts unique active users on that activity day (count_distinct).

 

How can I calculate unique users in the last 14 days for each activity day including that activity day? Preferably with another measure but a table calculation would also work. Something like this:

activity_day active_users users_active_last_14d
2020-12-31 100 210
2020-12-30 159 170
... ... ...
2020-12-18 99 110

 

So in the first row, I would have unique active users that were active between 2021-12-18 and 2021-12-31 (210). In the second row I would have unique active users that were active between 2021-12-17 and 2021-12-30 (170) and so on.

 

Thanks!


3 replies

Since you’re using overlapping periods, I think you’d need to create a snapshot of the data for each period of 14 days. I’ve been using this flexible Period-over-period code, and it should help you out, as well. You’d need to set your ‘Created Date’ filter to ‘in the past 14 days’ when you’re in the Explore, and then you should get the results you want.

I ran into one issue when implementing the code:

  • Under Step 2, in the refinement template code: if your database uses double quotes around table names, you’ll need to manually enter the table name where the code currently says “${EXTENDED}”. I had to change this to 'order_items.CREATED_AT' for the code to work.

The huge benefit to this is that you can apply it to any measure you want!

I’m not sure how to get the data back into a format like what you’re looking for in your example, but I hope the Period over Period code gets you moving in the right direction.

@eric_h, thanks for the suggestion. Your suggested solution looks super complicated for something so trivial imo. In a way, that it makes me consider whether the benefit of having this particular KPI outweighs the time spent getting this done.

I have looked into this relatively easy solution: 

It does the trick but only for a single number (that is, only if the measure is selected). If I include dates, then I just get daily active users for each day. I also got it semi working by creating a derived table in SQL: 

view: my_view {
derived_table: {
sql:
WITH
temp1 AS (
SELECT
date,
STRING_AGG(DISTINCT users) AS users
FROM
my_table
GROUP BY
date),

temp2 AS (
SELECT
date,
STRING_AGG(users) OVER(ORDER BY UNIX_DATE(date) RANGE BETWEEN 13 PRECEDING
AND CURRENT ROW) AS users
FROM
temp1)

SELECT
date,
(
SELECT
COUNT(DISTINCT id)
FROM
UNNEST(SPLIT(users)) AS id) AS unique_14_days
FROM
temp2;;
}
dimension: unique_14_days {
type: number
sql: ${TABLE}.unique_14_days;;
}

dimension_group: date_session {
type: time
timeframes: [
raw,
date,
week,
month,
quarter,
year
]
convert_tz: no
datatype: date
sql: ${TABLE}.date_session ;;
}

measure: unique_14_days_measure {
# hack for visualisation -> still requires both dimensions and this measure to be selected
type: number
sql: ${TABLE}.unique_14_days;;
}
}

However, a problem with this approach is that filters do not really work as expected in my explore. So yeah...

I know you can get the filters to carry over with native derived tables using ‘bind_all_filters: yes’ in your ‘explore_source:’ definition. As far as I know you can’t carry them over in a SQL derived table in the same way.

 

Clever SQL there using STRING_AGG :)

Reply