Sample Google Analytics data
-- Sample data: visit dates for a subset of users on Google Analytics public data
SELECT *
FROM `training.ga_users`
ORDER BY user_id, date
LIMIT 10| user_id | date |
|---------------------|------------|
| 0001066168651197929 | 2017-06-27 |
| 0001066168651197929 | 2017-06-28 |
| 0001285462512259769 | 2017-06-30 |
| 0001436786657417059 | 2017-05-15 |
| 0002113059720701099 | 2017-01-13 |
| 0003816501532259309 | 2017-01-22 |
| 0004359630748414109 | 2017-01-11 |
| 0004615806807819649 | 2017-03-29 |
| 0004964943560913819 | 2017-02-08 |
| 000614761893770079 | 2017-02-10 |Number of previous visits per user, over rolling time window
With BigQuery, this could actually be done with RANGE, but not in other systems such as PostgreSQL or Redshift, so let’s use a self join.
- With a unique row per user and visit, self-join on the following conditions:
- user id
- previous date strictly less than current date
- previous date greater than a specified time interval
- Group by user and visit, and count previous visits
-- Step 1: get previous visits dates over 90 days rolling window, for each user
-- Left join will create multiple rows per user in case of multiple previous visits
WITH sub1 AS (
SELECT
curr.user_id,
curr.date,
prev.date AS date_prev
FROM `training.ga_users` AS curr
LEFT JOIN `training.ga_users` AS prev
ON curr.user_id = prev.user_id
AND prev.date < curr.date
AND prev.date >= DATE_ADD(curr.date, INTERVAL -90 DAY)
)
-- Step 2: group by user_id and visit, and count non-Null previous dates
SELECT
user_id,
date,
COUNT(date_prev) AS visits_last_90d
FROM sub1
GROUP BY 1,2
ORDER BY user_id, date| user_id | date | visits_last_90d |
|---------------------|------------|-----------------|
| 0001066168651197929 | 2017-06-27 | 0 |
| 0001066168651197929 | 2017-06-28 | 1 |
| 0001285462512259769 | 2017-06-30 | 0 |
| 0001436786657417059 | 2017-05-15 | 0 |
| 0002113059720701099 | 2017-01-13 | 0 |
| ... | ... | ... |
| 9998297178122818269 | 2017-01-30 | 0 |
| 9998527765006831729 | 2017-05-07 | 0 |
| 9999350616287088509 | 2017-05-28 | 0 |
| 9999803502828528029 | 2017-03-31 | 0 |
| 9999963186378918199 | 2017-03-13 | 0 |