View the original community article here

Last tested: Nov 28, 2019
 

The Problem

To look at three KPIs,
1. Active Users (Activity within last 30 days)
2. Retained Active Users
3. Re-Activated Users (Activity within last 30 days, but no activity from 30 - 60 days, but activity from 90 days +)

A Solution

In order to do this, we will need some date manipulation and some windows functions, meaning we will use a derived table.

1. Here is an idea of how to create this, using Standard SQL (Bigquery)
2. Required data is User ID connected to an Activity Date (basically, what date they performed an activity on.)
3. We will create a derived table like below, only needing to change the parts in [] with your data set.

WITH daily_use AS (

SELECT

[MEMBER_ACCOUNT_ID] as user_id

, cast(TIMESTAMP_TRUNC(timestamp_millis(TIMESTAMP_IN_HOUR),day) as date) as activity_date

FROM [DATABASE.TABLE]

GROUP BY 1, 2

)

SELECT

daily_use.user_id

, wd.date as date

, CASE WHEN

MIN( DATE_DIFF(wd.date, daily_use.activity_date, day) ) < 30 THEN "yes"

ELSE "no" END as within_30

, CASE WHEN

MIN(DATE_DIFF(wd.date, daily_use.activity_date, day) ) BETWEEN 30 AND 60 THEN "yes"

ELSE "no" END as from_30_60

, CASE WHEN

min(DATE_DIFF(wd.date, daily_use.activity_date, day) ) > 60 THEN "yes"

ELSE "no" END as over_60

FROM (SELECT date

FROM UNNEST(GENERATE_DATE_ARRAY(DATE_SUB(CURRENT_DATE, INTERVAL 5 YEAR), CURRENT_DATE)) date) AS wd

CROSS JOIN daily_use

WHERE wd.date BETWEEN daily_use.activity_date AND DATE_ADD(daily_use.activity_date, INTERVAL 5 YEAR)

GROUP BY 1,2