Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Cohort Tables with BigQuery and Grafana
#standardSQL
WITH
pages AS (
SELECT
anonymous_id,
SAFE_CAST(sent_at AS timestamp) AS sent_at,
context_page_path,
ROW_NUMBER() OVER (PARTITION BY anonymous_id ORDER BY sent_at ASC) AS row_num
FROM
`table-name-redacted`
WHERE
context_page_path LIKE '/blog/%'
AND user_id != 'undefined' ),
cohort AS (
SELECT
pages.anonymous_id,
TIMESTAMP_TRUNC(sent_at, week) AS weekly_cohort
FROM
pages
WHERE
pages.row_num = 1 ),
absolute_counts AS (
SELECT
cohort.weekly_cohort AS weekly_cohort,
COUNT(DISTINCT
CASE
WHEN row_num = 1 THEN pages.anonymous_id
END
) AS total_unique_visitors,
COUNT(DISTINCT
CASE
WHEN row_num = 2 THEN pages.anonymous_id
END
) AS at_least_2_pageviews,
COUNT(DISTINCT
CASE
WHEN row_num = 3 THEN pages.anonymous_id
END
) AS at_least_3_pageviews,
COUNT(DISTINCT
CASE
WHEN row_num = 4 THEN pages.anonymous_id
END
) AS at_least_4_pageviews,
COUNT(DISTINCT
CASE
WHEN row_num = 5 THEN pages.anonymous_id
END
) AS at_least_5_pageviews,
COUNT(DISTINCT
CASE
WHEN row_num > 5 THEN pages.anonymous_id
END
) AS more_than_5_pageviews
FROM
cohort
INNER JOIN
pages
ON
cohort.anonymous_id = pages.anonymous_id
GROUP BY
1
ORDER BY
1 )
SELECT
weekly_cohort,
total_unique_visitors,
ROUND(at_least_2_pageviews/total_unique_visitors, 3) AS at_least_2_pageviews,
ROUND(at_least_3_pageviews/total_unique_visitors, 3) AS at_least_3_pageviews,
ROUND(at_least_4_pageviews/total_unique_visitors, 3) AS at_least_4_pageviews,
ROUND(at_least_5_pageviews/total_unique_visitors, 3) AS at_least_5_pageviews,
ROUND(more_than_5_pageviews/total_unique_visitors, 3) AS more_than_5_pageviews
FROM
absolute_counts
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment