Как посчитать deeplink conversion в SQL

Зачем deeplink conversion

Deeplink (Universal Links, App Links) перенаправляет юзера из браузера/email/push в конкретный экран приложения. Conversion rate = % юзеров, дошедших до target screen. Низкая conversion = broken link, missing app, OS-фейл. Email-кампания с broken deeplinks теряет 30-50% click-through.

Структура события

Стандартный лог:

• deeplink_clicked (user clicked link)
• app_opened_via_deeplink (app launched from link)
• target_screen_reached (правильный экран открыт)

Conversion rate в SQL

WITH clicks AS (
    SELECT
        deeplink_id,
        user_id,
        click_timestamp,
        deeplink_url,
        target_screen
    FROM deeplink_clicks
    WHERE click_timestamp >= CURRENT_DATE - INTERVAL '7 days'
),
opens AS (
    SELECT
        c.deeplink_id,
        c.user_id,
        MIN(e.event_timestamp) AS opened_at
    FROM clicks c
    JOIN events e ON e.user_id = c.user_id
       AND e.event_name = 'app_opened_via_deeplink'
       AND e.event_timestamp BETWEEN c.click_timestamp AND c.click_timestamp + INTERVAL '5 minutes'
    GROUP BY c.deeplink_id, c.user_id
),
screens AS (
    SELECT
        c.deeplink_id,
        c.user_id,
        MIN(e.event_timestamp) AS reached_at
    FROM clicks c
    JOIN events e ON e.user_id = c.user_id
       AND e.event_name = c.target_screen
       AND e.event_timestamp BETWEEN c.click_timestamp AND c.click_timestamp + INTERVAL '5 minutes'
    GROUP BY c.deeplink_id, c.user_id
)
SELECT
    (SELECT COUNT(DISTINCT user_id) FROM clicks) AS clicks,
    (SELECT COUNT(DISTINCT user_id) FROM opens) AS opened,
    (SELECT COUNT(DISTINCT user_id) FROM screens) AS reached,
    (SELECT COUNT(DISTINCT user_id) FROM opens)::NUMERIC * 100
    / NULLIF((SELECT COUNT(DISTINCT user_id) FROM clicks), 0) AS app_open_rate_pct,
    (SELECT COUNT(DISTINCT user_id) FROM screens)::NUMERIC * 100
    / NULLIF((SELECT COUNT(DISTINCT user_id) FROM clicks), 0) AS screen_reach_pct;

Открытие app vs reach screen — разные функции. Reach screen — final conversion.

iOS Universal vs Android App Links

SELECT
    platform,
    COUNT(*) AS clicks,
    COUNT(*) FILTER (WHERE opened_via_deeplink) AS opened,
    COUNT(*) FILTER (WHERE reached_target_screen) AS reached,
    COUNT(*) FILTER (WHERE reached_target_screen)::NUMERIC
    / NULLIF(COUNT(*), 0) * 100 AS conversion_pct
FROM deeplink_attempts
WHERE click_timestamp >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY platform;

iOS Universal Links и Android App Links имеют разную надёжность. Universal Links — strict apple_app_site_association config. Если broken — open в Safari.

Failure reasons

SELECT
    failure_reason,
    COUNT(*) AS occurrences
FROM deeplink_attempts
WHERE reached_target_screen = FALSE
  AND click_timestamp >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY failure_reason
ORDER BY occurrences DESC;

Топ-причины:

• app not installed → fall through to App Store
• URL routing fail (старая версия приложения)
• OS deprecated old deeplink scheme
• ad blocker / content blocker

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

Ошибка 1. Считать только app_open. App открылся, но не на target screen → пользователь видит main screen, разочарован.

Ошибка 2. Window слишком длинное. Deeplink → app open в течение 30 секунд. Не дни.

Ошибка 3. Игнорировать не-installed. Если app не установлено, deeplink ведёт в App Store. Это install funnel, не conversion.

Ошибка 4. Считать на total clicks без unique users. Один user может кликнуть много раз на один deeplink. DISTINCT user_id.

Ошибка 5. Не различать sources. Email vs push vs SMS вышибают разную поведение OS. Сегментируйте.

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

• Как посчитать app install attribution в SQL
• Как посчитать tutorial drop-off в SQL
• Как посчитать push notification open rate в SQL
• Как посчитать conversion-window в SQL

FAQ

Universal Links vs deep links?

Universal Links — Apple, https-based. Deep links — proprietary scheme.

Какой conversion rate?

iOS Universal: 80-95%. Android App Links: 70-90%. Сломанные deeplinks падают до 30-50%.

Fallback strategy?

Web URL → if app installed, open app. Else open mobile web.

Какие events нужны?

deeplink_clicked, app_opened_with_intent, target_screen_view. Plus failure events.

Cold start vs warm?

Cold start (app не running) занимает 1-3 секунды. Warm (background) — мгновенно.