ROI продаж по каналам за последний месяц
SQL, по которому посчитано
WITH last_month AS (
SELECT date_trunc('month', max(date)) AS month_start
FROM sales
),
sales_channel AS (
SELECT
s.channel,
sum(s.revenue - s.returned_revenue) AS net_revenue,
sum(s.cost_of_goods) AS cogs,
sum(s.commission) AS commission,
sum(s.logistics) AS logistics
FROM sales s
CROSS JOIN last_month lm
WHERE s.date >= lm.month_start
GROUP BY s.channel
),
ads_channel AS (
SELECT
CASE a.source
WHEN 'Продвижение WB' THEN 'Wildberries'
WHEN 'Продвижение Ozon' THEN 'Ozon'
WHEN 'Яндекс Директ' THEN 'Сайт'
WHEN 'VK Реклама' THEN 'Сайт'
END AS channel,
sum(a.cost) AS ad_cost
FROM ads a
CROSS JOIN last_month lm
WHERE a.date >= lm.month_start
GROUP BY 1
)
SELECT
sc.channel,
round(sc.net_revenue, 2) AS net_revenue,
round(sc.net_revenue - sc.cogs - sc.commission - sc.logistics, 2) AS gross_profit,
round(ac.ad_cost, 2) AS ad_cost,
round(
(sc.net_revenue - sc.cogs - sc.commission - sc.logistics - ac.ad_cost) / nullif(ac.ad_cost, 0),
4
) AS roi
FROM sales_channel sc
JOIN ads_channel ac ON ac.channel = sc.channel
WHERE ac.ad_cost > 0
ORDER BY roi DESC
LIMIT 30