План и факт выручки по месяцам и группам каналов
| Месяц | Отклонение, руб. | Выполнение плана | Факт выручки, руб. | План выручки, руб. |
|---|---|---|---|---|
| 2025-01-01 | 236 715 ₽ | 101,6 % | 15 197 715 ₽ | 14 961 000 ₽ |
| 2025-01-01 | -505 921 ₽ | 95 % | 9 641 079 ₽ | 10 147 000 ₽ |
| 2025-02-01 | -1 400 776 ₽ | 90,6 % | 13 574 224 ₽ | 14 975 000 ₽ |
| 2025-02-01 | -901 900 ₽ | 89,8 % | 7 982 100 ₽ | 8 884 000 ₽ |
| 2025-03-01 | -2 226 563 ₽ | 88,8 % | 17 682 437 ₽ | 19 909 000 ₽ |
| 2025-03-01 | -1 158 870 ₽ | 90,1 % | 10 513 130 ₽ | 11 672 000 ₽ |
| 2025-04-01 | -2 044 357 ₽ | 89 % | 16 542 643 ₽ | 18 587 000 ₽ |
| 2025-04-01 | -1 176 596 ₽ | 89,8 % | 10 312 404 ₽ | 11 489 000 ₽ |
| 2025-05-01 | -998 566 ₽ | 94,2 % | 16 224 434 ₽ | 17 223 000 ₽ |
| 2025-05-01 | -440 347 ₽ | 96 % | 10 613 653 ₽ | 11 054 000 ₽ |
| 2025-06-01 | -769 464 ₽ | 94,9 % | 14 353 536 ₽ | 15 123 000 ₽ |
| 2025-06-01 | 365 537 ₽ | 103,8 % | 10 051 537 ₽ | 9 686 000 ₽ |
| 2025-07-01 | -1 077 122 ₽ | 92,8 % | 13 833 878 ₽ | 14 911 000 ₽ |
| 2025-07-01 | 1 006 796 ₽ | 111,7 % | 9 602 796 ₽ | 8 596 000 ₽ |
| 2025-08-01 | -149 291 ₽ | 99,1 % | 16 510 709 ₽ | 16 660 000 ₽ |
| 2025-08-01 | -674 634 ₽ | 93,9 % | 10 473 366 ₽ | 11 148 000 ₽ |
| 2025-09-01 | -2 394 477 ₽ | 88,3 % | 18 096 523 ₽ | 20 491 000 ₽ |
| 2025-09-01 | -1 316 732 ₽ | 89,7 % | 11 512 268 ₽ | 12 829 000 ₽ |
| 2025-10-01 | -2 272 293 ₽ | 90,2 % | 20 990 707 ₽ | 23 263 000 ₽ |
| 2025-10-01 | -85 759 ₽ | 99,4 % | 13 226 241 ₽ | 13 312 000 ₽ |
| 2025-11-01 | -4 930 040 ₽ | 80 % | 19 663 960 ₽ | 24 594 000 ₽ |
| 2025-11-01 | -2 364 564 ₽ | 84,6 % | 12 940 436 ₽ | 15 305 000 ₽ |
| 2025-12-01 | -3 922 828 ₽ | 84,9 % | 21 984 172 ₽ | 25 907 000 ₽ |
| 2025-12-01 | -1 118 958 ₽ | 93,5 % | 16 132 042 ₽ | 17 251 000 ₽ |
SQL, по которому посчитано
WITH fact AS (
SELECT CAST(date_trunc('month', date) AS DATE) AS month,
CASE WHEN channel = 'Сайт' THEN 'Сайт' ELSE 'Маркетплейсы' END AS channel_group,
SUM(revenue - returned_revenue) AS fact_revenue
FROM sales
WHERE date > (SELECT max(date) FROM sales) - INTERVAL 12 MONTH
GROUP BY 1, 2
),
target AS (
SELECT month,
CASE WHEN channel = 'Сайт' THEN 'Сайт' ELSE 'Маркетплейсы' END AS channel_group,
SUM(revenue) AS plan_revenue
FROM plan
WHERE month > (SELECT max(date) FROM sales) - INTERVAL 12 MONTH
GROUP BY 1, 2
)
SELECT t.month AS month,
t.channel_group AS channel_group,
round(t.plan_revenue) AS plan_revenue,
round(coalesce(f.fact_revenue, 0)) AS fact_revenue,
round(coalesce(f.fact_revenue, 0) - t.plan_revenue) AS deviation,
round(coalesce(f.fact_revenue, 0) / nullif(t.plan_revenue, 0), 3) AS completion
FROM target t
LEFT JOIN fact f ON f.month = t.month AND f.channel_group = t.channel_group
ORDER BY t.month, t.channel_group