ROMI по источникам рекламы: как считать окупаемость каналов

SQL, по которому посчитано

WITH
period AS (
    SELECT date_trunc('month', max(date))::DATE AS month_start
    FROM ads
),
source_channel(source, channel) AS (
    VALUES
        ('Яндекс Директ', 'Сайт'),
        ('VK Реклама', 'Сайт'),
        ('Продвижение WB', 'Wildberries'),
        ('Продвижение Ozon', 'Ozon')
),
sales_cat AS (
    SELECT
        s.channel,
        s.category,
        SUM(s.revenue - COALESCE(s.returned_revenue, 0)) AS net_revenue,
        SUM(s.cost_of_goods) AS cogs
    FROM sales s
    CROSS JOIN period p
    WHERE s.date >= p.month_start
      AND s.date < (p.month_start + INTERVAL 1 MONTH)::DATE
    GROUP BY s.channel, s.category
),
sales_channel AS (
    SELECT
        channel,
        SUM(net_revenue) AS net_revenue,
        SUM(cogs) AS cogs
    FROM sales_cat
    GROUP BY channel
),
margin_cat AS (
    SELECT
        channel,
        category,
        CASE WHEN net_revenue > 0 THEN cogs / net_revenue ELSE 0 END AS cogs_share
    FROM sales_cat
),
margin_channel AS (
    SELECT
        channel,
        CASE WHEN net_revenue > 0 THEN cogs / net_revenue ELSE 0 END AS cogs_share
    FROM sales_channel
),
ads_agg AS (
    SELECT
        a.source,
        a.category,
        SUM(a.cost) AS cost,
        SUM(a.revenue) AS revenue
    FROM ads a
    CROSS JOIN period p
    WHERE a.date >= p.month_start
      AND a.date < (p.month_start + INTERVAL 1 MONTH)::DATE
    GROUP BY a.source, a.category
),
ads_profit AS (
    SELECT
        a.source,
        SUM(a.cost) AS cost,
        SUM(a.revenue) AS revenue,
        SUM(a.revenue * (1 - COALESCE(mc.cogs_share, mch.cogs_share, 0))) AS gross_profit
    FROM ads_agg a
    JOIN source_channel sc ON sc.source = a.source
    LEFT JOIN margin_cat mc ON mc.channel = sc.channel AND mc.category = a.category
    LEFT JOIN margin_channel mch ON mch.channel = sc.channel
    GROUP BY a.source
)
SELECT
    source,
    round(cost, 0) AS cost_rub,
    round(revenue, 0) AS revenue_rub,
    round(gross_profit, 0) AS gross_profit_rub,
    CASE WHEN cost > 0 THEN round((gross_profit - cost) / cost, 4) ELSE NULL END AS romi
FROM ads_profit
ORDER BY romi DESC NULLS LAST