Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Monthly total user retention numbers in BigQuery with google analytics
SELECT
last as last_month,
DATETIME_DIFF(last, first, MONTH) as retained_months,
COUNT(*) as users
FROM (
SELECT
DATE(DATE_TRUNC(TIMESTAMP_MICROS(min(event_timestamp)), MONTH)) as first,
DATE(DATE_TRUNC(TIMESTAMP_MICROS(max(event_timestamp)), MONTH)) as last
FROM `<your-project>.events_*`
WHERE event_name = 'view_item'
GROUP BY user_pseudo_id
)
WHERE DATETIME_DIFF(last, first, MONTH) > 0
GROUP BY last_month, retained_months
ORDER BY last_month, retained_months
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment