Как посчитать Conversion Window в SQL

Зачем Conversion Window

Реклама в Google: 7-day window. Реклама в Facebook: 28-day. Если ваш sales cycle 3 месяца, 7-day window недосчитывает половину conversions. Знание actual conversion window помогает выбрать правильную атрибуцию.

Определение

Conversion Window — время от touchpoint (клика по рекламе) до conversion (покупки).

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

WITH touchpoint_conversion AS (
    SELECT
        t.user_id,
        t.touched_at,
        t.channel,
        MIN(c.converted_at) AS first_conversion
    FROM touchpoints t
    LEFT JOIN conversions c ON c.user_id = t.user_id AND c.converted_at >= t.touched_at
    GROUP BY t.user_id, t.touched_at, t.channel
)
SELECT
    AVG(EXTRACT(EPOCH FROM (first_conversion - touched_at)) / 86400) AS avg_days_to_convert,
    PERCENTILE_CONT(0.5) WITHIN GROUP (
        ORDER BY EXTRACT(EPOCH FROM (first_conversion - touched_at)) / 86400
    ) AS median_days
FROM touchpoint_conversion
WHERE first_conversion IS NOT NULL;

Distribution

WITH days AS (
    SELECT
        EXTRACT(EPOCH FROM (MIN(c.converted_at) - t.touched_at)) / 86400 AS days_to_convert
    FROM touchpoints t
    JOIN conversions c ON c.user_id = t.user_id AND c.converted_at >= t.touched_at
    GROUP BY t.touched_at, t.user_id
)
SELECT
    CASE
        WHEN days_to_convert <= 1 THEN '< 1 day'
        WHEN days_to_convert <= 7 THEN '1-7 days'
        WHEN days_to_convert <= 30 THEN '8-30 days'
        WHEN days_to_convert <= 90 THEN '31-90 days'
        ELSE '90+ days'
    END AS bucket,
    COUNT(*) AS conversions
FROM days
GROUP BY 1
ORDER BY 1;

Если distribution skewed к >30 days — стандартный 7-day window недостаточен.

По каналам

WITH ttc AS (
    SELECT
        t.channel,
        EXTRACT(EPOCH FROM (MIN(c.converted_at) - t.touched_at)) / 86400 AS days_to_convert
    FROM touchpoints t
    JOIN conversions c ON c.user_id = t.user_id AND c.converted_at >= t.touched_at
    GROUP BY t.touched_at, t.user_id, t.channel
)
SELECT
    channel,
    COUNT(*) AS conversions,
    AVG(days_to_convert) AS avg_days,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_to_convert) AS median_days,
    PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY days_to_convert) AS p90_days
FROM ttc
GROUP BY channel
ORDER BY median_days;

Брендовый search обычно close to conversion (impulse). Display — long window (awareness).

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

Ошибка 1. Сравнивать lifetime CR. Если window 30 days, юзер converted через 60 days — не считается. Это distort lifetime CR.

Ошибка 2. Multi-touch. Юзер touched 5 раз. Conversion window к какому? Чаще к last touch.

Ошибка 3. Outlier conversions. Юзер кликнул 2 года назад, теперь купил. Cap window 90-180 days.

Ошибка 4. Сравнить с platform default windows. Google Ads default 30-day click. Facebook 7-day click + 1-day view. Compare на same basis.

Ошибка 5. Time zone confusion. Touch в UTC, conversion в local time. Convert to one zone.

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

• Как посчитать First-Touch Attribution в SQL
• Как посчитать Last-Touch Attribution в SQL
• Как посчитать ROAS в SQL
• Как посчитать time to first purchase в SQL

FAQ

Какой window выбрать?

Анализируйте distribution. Если 90% conversions в первые 30 days — берите 30-day window.

Google Ads vs Facebook windows?

Google Ads default 30-day click. Facebook 7-day click + 1-day view (post-iOS 14).

Window и attribution model?

Связаны: short window favors last-touch. Long window — first-touch attribution.

B2B vs B2C window?

B2C — 7-30 days. B2B — 30-180 days (long sales cycles).

Conversion window и CR?

Длинный window = больше conversions учитывается = выше CR. Compare apples-to-apples.