Целевой и фактический ДРР на Ozon по категориям за 12 месяцев
SQL, по которому посчитано
WITH ozon_sales AS (
SELECT category,
sum(revenue) AS revenue,
sum(cost_of_goods) AS cogs,
sum(commission) AS commission,
sum(logistics) AS logistics
FROM sales
WHERE channel = 'Ozon'
AND date > (SELECT max(date) FROM sales) - INTERVAL 12 MONTH
GROUP BY category
),
ozon_ads AS (
SELECT category,
sum(cost) AS ad_cost,
sum(revenue) AS ad_revenue
FROM ads
WHERE source = 'Продвижение Ozon'
AND category <> 'Все'
AND date > (SELECT max(date) FROM sales) - INTERVAL 12 MONTH
GROUP BY category
),
base AS (
SELECT s.category,
s.revenue,
s.revenue - s.cogs - s.commission - s.logistics AS margin,
coalesce(a.ad_cost, 0) AS ad_cost,
coalesce(a.ad_revenue, 0) AS ad_revenue
FROM ozon_sales s
LEFT JOIN ozon_ads a USING (category)
)
SELECT category,
round(revenue) AS revenue,
round(margin) AS margin,
round(margin / revenue, 3) AS target_drr,
round(ad_cost) AS ad_cost,
round(ad_revenue) AS ad_revenue,
round(ad_cost / nullif(ad_revenue, 0), 3) AS fact_drr,
round(ad_cost / nullif(ad_revenue, 0) - margin / revenue, 3) AS drr_gap
FROM base
ORDER BY revenue DESC
LIMIT 30