Как посчитать install attribution в SQL

Зачем attribution

В мобильном маркетинге нужно знать, откуда пришёл install: paid Google Ads, organic search в App Store, или referral. iOS 14.5+ ATT-tracking ограничивает device IDs, поэтому attribution стал сложнее. Стандарт — last-touch click + fingerprint matching внутри 24-48 часов после клика.

Last-touch модель

Install приписывается последнему click перед install. Простая, но скрывает upper-funnel влияние:

last_click_before_install = MAX(click_at) FILTER (click_at <= install_at)

Attribution в SQL

WITH installs AS (
    SELECT user_id, install_at, device_id
    FROM app_installs
    WHERE install_at >= CURRENT_DATE - INTERVAL '30 days'
),
clicks AS (
    SELECT
        device_id,
        click_id,
        click_at,
        campaign,
        utm_source
    FROM ad_clicks
    WHERE click_at >= CURRENT_DATE - INTERVAL '37 days'
),
last_click AS (
    SELECT
        i.user_id,
        i.install_at,
        c.campaign,
        c.utm_source,
        c.click_at,
        ROW_NUMBER() OVER (PARTITION BY i.user_id ORDER BY c.click_at DESC) AS rn
    FROM installs i
    LEFT JOIN clicks c
      ON c.device_id = i.device_id
     AND c.click_at <= i.install_at
     AND c.click_at >= i.install_at - INTERVAL '7 days'
)
SELECT
    user_id,
    install_at,
    COALESCE(campaign, 'organic') AS attributed_campaign,
    COALESCE(utm_source, 'organic') AS attributed_source
FROM last_click
WHERE rn = 1 OR rn IS NULL;

LEFT JOIN + ROW_NUMBER отдаёт last click. Если нет — organic (или App Store search).

Click-to-install window

WITH attributed AS (
    SELECT
        attributed_source,
        EXTRACT(EPOCH FROM (install_at - click_at)) / 3600 AS hours_to_install
    FROM attribution_table
)
SELECT
    attributed_source,
    AVG(hours_to_install) AS avg_hours,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY hours_to_install) AS median_hours,
    COUNT(*) FILTER (WHERE hours_to_install <= 1)  AS installs_within_1h,
    COUNT(*) FILTER (WHERE hours_to_install <= 24) AS installs_within_24h
FROM attributed
WHERE hours_to_install IS NOT NULL
GROUP BY attributed_source;

70-80% installs происходят в первые 60 минут после клика. Поэтому attribution window обычно 24h максимум.

Fingerprint match

Без device_id (iOS) используют probabilistic match: IP + User-Agent + время:

WITH probable AS (
    SELECT
        i.user_id,
        c.click_id,
        c.campaign,
        ABS(EXTRACT(EPOCH FROM (i.install_at - c.click_at))) AS time_diff_sec
    FROM installs i
    LEFT JOIN ad_clicks c
      ON c.ip = i.ip
     AND c.user_agent = i.user_agent
     AND c.click_at BETWEEN i.install_at - INTERVAL '24 hours' AND i.install_at
)
SELECT
    user_id,
    campaign,
    time_diff_sec
FROM probable
QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY time_diff_sec) = 1;

QUALIFY в Snowflake/BigQuery. В Postgres — через CTE.

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

Ошибка 1. Слишком широкое window. 30-дневное window → клик 3 недели назад приписан к install. Стандарт — 1-7 дней.

Ошибка 2. View-through attribution. Impression без click → не all platforms credit. Stick to click-based.

Ошибка 3. Не различать last-paid vs last-click. Last click может быть branded search, который не считается «paid acquisition».

Ошибка 4. Игнорировать App Store Search. Apple Search Ads и organic App Store search — отдельная категория. Не organic.

Ошибка 5. Игнорировать SKAdNetwork. iOS 14.5+ обязывает использовать SKAN. Direct attribution = устаревшая.

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

• Как посчитать multi-touch attribution в SQL
• Как посчитать last-touch attribution в SQL
• Как посчитать device fingerprinting в SQL
• Как посчитать CAC по каналам в SQL

FAQ

iOS vs Android attribution?

iOS — SKAN или fingerprint. Android — Google Play Referrer + GPS_ADID.

Какое window?

24h для click attribution. 1-7 дней для view-through (если поддерживается).

Branded vs non-branded?

Branded — юзер уже знал. Non-branded — open discovery. Сегментируйте.

Multi-touch для install?

Researches (Branch, AppsFlyer) дают MTA для mobile. SQL — first-touch + last-touch минимум.

Что считать organic?

Direct app store search, не attributed click. iOS App Store Search Ads — отдельно.