Как посчитать Promo Effectiveness в SQL

Зачем Promo Effectiveness

«Промо +50% sales!» Радостно. Аналитик: «А без промо было бы +30% (organic growth + сезон)». Real incremental = 20%. Cost of promo (discount + advertising) > incremental → loss.

Формула

Promo Effectiveness ROI = (Incremental Revenue - Promo Cost) / Promo Cost × 100%
Incremental Sales = Sales_during_promo - Baseline_sales

Базовый расчёт

Naive (без baseline):

SELECT
    SUM(total) AS revenue_during_promo,
    COUNT(*) AS orders_during_promo
FROM orders
WHERE status = 'paid'
  AND created_at BETWEEN '2026-03-01' AND '2026-03-07'
  AND promo_code = 'SPRING20';

Incremental Sales

Сравнить с pre-promo / control period:

WITH pre_promo AS (
    SELECT
        AVG(daily_revenue) AS baseline_daily_revenue
    FROM (
        SELECT DATE_TRUNC('day', created_at) AS day, SUM(total) AS daily_revenue
        FROM orders
        WHERE status = 'paid'
          AND created_at BETWEEN '2026-02-08' AND '2026-02-28'  -- pre-promo 3 weeks
        GROUP BY 1
    ) x
),
during_promo AS (
    SELECT
        SUM(total) AS total_during_promo,
        COUNT(DISTINCT DATE_TRUNC('day', created_at)) AS days
    FROM orders
    WHERE status = 'paid'
      AND created_at BETWEEN '2026-03-01' AND '2026-03-07'
)
SELECT
    d.total_during_promo,
    d.days,
    p.baseline_daily_revenue * d.days AS baseline_expected,
    d.total_during_promo - (p.baseline_daily_revenue * d.days) AS incremental_revenue
FROM pre_promo p
CROSS JOIN during_promo d;

По кампаниям

WITH promo_stats AS (
    SELECT
        promo_code,
        COUNT(*) AS orders,
        SUM(total) AS revenue,
        SUM(discount_amount) AS total_discount_given
    FROM orders
    WHERE status = 'paid'
      AND promo_code IS NOT NULL
      AND created_at >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY promo_code
)
SELECT
    promo_code,
    orders,
    revenue,
    total_discount_given,
    revenue - total_discount_given AS revenue_after_discount
FROM promo_stats
ORDER BY revenue DESC;

True effectiveness — нужен baseline. Sample-based attribution complex.

Частые ошибки

Ошибка 1. Naive «sales during promo = effectiveness». Без baseline вы видите только activity, не incremental.

Ошибка 2. Включать в incremental existing customers, которые купили бы и так. «Самообман» — promo cannibalize organic sales.

Ошибка 3. Не учитывать halo effect. Promo привлекает новых, кто покупает позже organic. Window analysis.

Ошибка 4. Promo cost = только discount. Cost = discount + advertising + operations.

Ошибка 5. Sample size. Один promo неделя vs other — high variance. Average over multiple promos.

Связанные темы

• Как посчитать coupon redemption rate в SQL
• Как посчитать discount rate в SQL
• Как посчитать ROAS в SQL
• Как посчитать AOV в SQL

FAQ

Какой Promo ROI ok?

20-50%+ — норма для promo. <0 — loss. 100%+ — exceptionally effective.

Promo effectiveness vs ROAS?

ROAS — для ad spend. Promo effectiveness — для discount + ad spend combined.

Baseline — как определять?

Pre-promo 2-4 weeks of normal sales. Adjust for sezonalitet (YoY same period).

Halo effect — как мерить?

Track new customers acquired during promo. Их LTV — long-term halo value.

Cannibalization?

Юзеры, которые купили бы и без promo, теперь получают скидку = меньше margin.