Daily marketing efficiency

Spend vs revenue vs sessions, every day for two years — heavy on purpose. This one is served from a jellyrows rollup; hit Verify to see the parity diff.

Running…

View SQL

SELECT s.day,
       s.spend,
       sess.sessions,
       sess.conversions,
       o.revenue,
       round(o.revenue / NULLIF(s.spend, 0), 2) AS roas,
       round(avg(o.revenue / NULLIF(s.spend, 0)) OVER (ORDER BY s.day ROWS 6 PRECEDING), 2) AS roas_7d
  FROM (SELECT day, sum(spend) AS spend FROM sample.ad_spend GROUP BY day) s
  LEFT JOIN (SELECT started_at::date AS day, count(*) AS sessions,
                    count(*) FILTER (WHERE converted) AS conversions
               FROM sample.web_sessions GROUP BY 1) sess USING (day)
  LEFT JOIN (SELECT created_at::date AS day, round(sum(total)) AS revenue
               FROM sample.orders WHERE status = 'completed' GROUP BY 1) o USING (day)
 ORDER BY s.day