retention_by_week_by_medium.sql

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