Vizor Games — когортный отчёт по маркетинговым метрикам (CPI, ROAS, C2B)

WITH params AS (
  SELECT DATE '2024-01-01' AS d_from, DATE '2024-12-31' AS d_to,
         'week' AS gran  -- 'day' | 'week'
),
 
-- 1) Когортный ключ
cohort_key AS (
  SELECT install_date,
         CASE WHEN (SELECT gran FROM params) = 'day'
              THEN install_date
              -- неделя с пятницы: смещение PG: extract(dow) — 0=Sunday, 5=Friday
              ELSE install_date - ((EXTRACT(DOW FROM install_date)::int - 5 + 7) % 7)
         END AS cohort_dt
  FROM (SELECT DISTINCT install_date FROM cost_data
        UNION
        SELECT DISTINCT DATE(install_time) FROM mmp_data) d
),
 
-- 2) Cost-агрегаты по когорте
cost_agg AS (
  SELECT ck.cohort_dt,
         c.platform,
         SUM(c.spend)        AS cost,
         SUM(c.impressions)  AS impressions,
         SUM(c.clicks)        AS clicks
  FROM cost_data c
  JOIN cohort_key ck ON ck.install_date = c.install_date
  GROUP BY ck.cohort_dt, c.platform
),
 
-- 3) Installs из MMP (по install_time)
installs_agg AS (
  SELECT ck.cohort_dt, m.platform,
         COUNT(DISTINCT m.user_id) AS installs
  FROM mmp_data m
  JOIN cohort_key ck ON ck.install_date = DATE(m.install_time)
  WHERE m.event_name = 'install'        -- если такая семантика
  GROUP BY ck.cohort_dt, m.platform
),
 
-- 4) Платящие на день D (cohort_day <= D)
paying AS (
  SELECT ck.cohort_dt, m.platform, m.user_id,
         MIN(m.cohort_day) AS first_pay_day,
         SUM(m.event_revenue_usd) FILTER (WHERE m.cohort_day = 0) AS rev_d0,
         SUM(m.event_revenue_usd) FILTER (WHERE m.cohort_day <= 1) AS rev_d1,
         SUM(m.event_revenue_usd) FILTER (WHERE m.cohort_day <= 3) AS rev_d3,
         SUM(m.event_revenue_usd) FILTER (WHERE m.cohort_day <= 5) AS rev_d5
  FROM mmp_data m
  JOIN cohort_key ck ON ck.install_date = DATE(m.install_time)
  WHERE m.event_name = 'purchase'
  GROUP BY ck.cohort_dt, m.platform, m.user_id
),
 
cohort_pay AS (
  SELECT cohort_dt, platform,
         COUNT(*)                                                   AS buyers,
         SUM(CASE WHEN first_pay_day = 0 THEN 1 ELSE 0 END)          AS b0,
         SUM(CASE WHEN first_pay_day <= 1 THEN 1 ELSE 0 END)         AS b1,
         SUM(CASE WHEN first_pay_day <= 3 THEN 1 ELSE 0 END)         AS b3,
         SUM(CASE WHEN first_pay_day <= 5 THEN 1 ELSE 0 END)         AS b5,
         SUM(rev_d0) AS rev_d0,
         SUM(rev_d1) AS rev_d1,
         SUM(rev_d3) AS rev_d3,
         SUM(rev_d5) AS rev_d5,
         SUM(rev_d0 + COALESCE(rev_d1,0) + COALESCE(rev_d3,0) + COALESCE(rev_d5,0)) AS total_rev_proxy
  FROM paying
  GROUP BY cohort_dt, platform
)
 
-- 5) Финальный вывод
SELECT c.cohort_dt, c.platform,
       c.cost,
       i.installs,
       p.buyers,
       c.cost  / NULLIF(i.installs, 0)                  AS CPI,
       c.cost  * 1000.0 / NULLIF(c.impressions, 0)      AS CPM,
       c.clicks * 1.0   / NULLIF(c.impressions, 0)      AS CTR,
       i.installs * 1000.0 / NULLIF(c.impressions, 0)   AS IPM,
 
       p.b0   * 1.0 / NULLIF(i.installs, 0)             AS C2B_0,
       p.b1   * 1.0 / NULLIF(i.installs, 0)             AS C2B_1,
       p.b3   * 1.0 / NULLIF(i.installs, 0)             AS C2B_3,
       p.b5   * 1.0 / NULLIF(i.installs, 0)             AS C2B_5,
 
       c.cost / NULLIF(p.b0, 0)                         AS CPB_0,
       c.cost / NULLIF(p.b1, 0)                         AS CPB_1,
       c.cost / NULLIF(p.b3, 0)                         AS CPB_3,
       c.cost / NULLIF(p.b5, 0)                         AS CPB_5,
 
       p.rev_d0 / NULLIF(c.cost, 0)                     AS ROAS_0,
       p.rev_d1 / NULLIF(c.cost, 0)                     AS ROAS_1,
       p.rev_d3 / NULLIF(c.cost, 0)                     AS ROAS_3,
       p.rev_d5 / NULLIF(c.cost, 0)                     AS ROAS_5
FROM cost_agg c
LEFT JOIN installs_agg  i ON c.cohort_dt = i.cohort_dt AND c.platform = i.platform
LEFT JOIN cohort_pay    p ON c.cohort_dt = p.cohort_dt AND c.platform = p.platform
ORDER BY c.cohort_dt;