Created
May 11, 2016 19:45
-
-
Save phpmypython/80e9c100889b03f858f646bf28ca52e8 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH cohort_dfn_by_refr_channel_acquired_by_week AS ( | |
SELECT | |
domain_userid, | |
refr_acquired_medium, | |
week_start | |
FROM ( | |
SELECT | |
domain_userid, | |
refr_medium AS refr_acquired_medium, | |
DATE_TRUNC('week',collector_tstamp) as week_start, | |
rank() | |
OVER (PARTITION BY domain_userid, refr_medium | |
ORDER BY collector_tstamp) AS visit_number | |
FROM atomic.events | |
WHERE refr_medium != 'internal' | |
AND refr_source != '' | |
AND event = 'page_ping' | |
AND useragent != 'Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)' | |
AND app_id = 'barkpost-web' | |
AND pp_yoffset_min = 0 | |
) t | |
WHERE visit_number = 1 | |
), | |
retention_by_week_by_user AS ( | |
SELECT | |
domain_userid, | |
weeks_active AS week_actual, | |
rank() | |
OVER (PARTITION BY t.domain_userid | |
ORDER BY t.weeks_active ASC) AS weeks_active | |
FROM ( | |
SELECT | |
domain_userid, | |
DATE_TRUNC('week', collector_tstamp) AS weeks_active | |
FROM atomic.events | |
WHERE app_id = 'barkpost-web' | |
AND event = 'page_ping' | |
AND useragent != 'Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)' | |
AND refr_medium != 'internal' | |
AND pp_yoffset_min = 0 | |
GROUP BY 1, 2 | |
) t | |
), | |
retention_totals AS ( | |
SELECT | |
first.refr_acquired_medium AS refr_acquired_medium, | |
total.week_actual AS week_actual, | |
total.weeks_active AS week_rank, | |
count(DISTINCT total.domain_userid) AS uniques | |
FROM retention_by_week_by_user AS total | |
JOIN cohort_dfn_by_refr_channel_acquired_by_week AS first | |
ON first.domain_userid = total.domain_userid | |
GROUP BY 1, 2, 3 | |
ORDER BY 1, 2, 3 | |
), | |
retention_starting_totals AS ( | |
SELECT | |
refr_acquired_medium, | |
week_start, | |
sum(count(DISTINCT domain_userid)) | |
OVER (PARTITION BY refr_acquired_medium,week_start) AS starting_total | |
FROM cohort_dfn_by_refr_channel_acquired_by_week | |
GROUP BY 1,2 | |
), | |
retention_normalize AS ( | |
SELECT | |
total.refr_acquired_medium, | |
total.week_actual, | |
total.week_rank, | |
sum(uniques) AS uniques, | |
start.starting_total | |
FROM retention_totals AS total INNER JOIN retention_starting_totals AS start | |
ON total.refr_acquired_medium = start.refr_acquired_medium AND total.week_actual=start.week_start | |
GROUP BY total.refr_acquired_medium, total.week_actual, total.week_rank, start.starting_total | |
) | |
SELECT * | |
FROM retention_normalize |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment