Monthly revenue by channel

Which acquisition channels the revenue actually comes from.

Running…

View SQL

SELECT date_trunc('month', o.created_at)::date AS month,
       round(sum(o.total) FILTER (WHERE c.channel = 'organic'))    AS organic,
       round(sum(o.total) FILTER (WHERE c.channel = 'google_ads')) AS google_ads,
       round(sum(o.total) FILTER (WHERE c.channel = 'meta_ads'))   AS meta_ads,
       round(sum(o.total) FILTER (WHERE c.channel = 'email'))      AS email,
       round(sum(o.total) FILTER (WHERE c.channel IN ('referral','tiktok_ads'))) AS other
  FROM sample.orders o
  JOIN sample.customers c ON c.id = o.customer_id
 WHERE o.status = 'completed'
 GROUP BY 1
 ORDER BY 1