Как посчитать Push Open Rate в SQL

Зачем Push Open Rate

Push — главный канал re-engagement в mobile. Если Open Rate 1-2% — push spam. Если 10%+ — relevant. Optimization push timing/copy = больше DAU без acquisition costs.

Формула

Push Open Rate = pushes_opened / pushes_delivered × 100%

Delivered (не sent): юзер должен иметь push permissions ON.

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

WITH stats AS (
    SELECT
        DATE_TRUNC('week', sent_at) AS week,
        COUNT(*) FILTER (WHERE delivered = TRUE) AS delivered,
        COUNT(*) FILTER (WHERE opened = TRUE) AS opened
    FROM push_events
    WHERE sent_at >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY 1
)
SELECT
    week,
    delivered,
    opened,
    opened::NUMERIC * 100 / NULLIF(delivered, 0) AS open_rate_pct
FROM stats
ORDER BY week;

По типам / time-of-day

SELECT
    push_type,
    EXTRACT(HOUR FROM sent_at) AS hour_of_day,
    COUNT(*) AS delivered,
    COUNT(*) FILTER (WHERE opened) AS opened,
    COUNT(*) FILTER (WHERE opened)::NUMERIC * 100 / NULLIF(COUNT(*), 0) AS open_rate
FROM push_events
WHERE sent_at >= CURRENT_DATE - INTERVAL '30 days'
  AND delivered = TRUE
GROUP BY push_type, hour_of_day
HAVING COUNT(*) >= 100
ORDER BY open_rate DESC;

Transactional push (order delivered, message received) обычно 30-50% open. Marketing push: 2-8%.

Push fatigue

WITH user_push_count AS (
    SELECT
        user_id,
        COUNT(*) AS pushes_received_30d,
        AVG(CASE WHEN opened THEN 1 ELSE 0 END) AS personal_open_rate
    FROM push_events
    WHERE sent_at >= CURRENT_DATE - INTERVAL '30 days'
      AND delivered = TRUE
    GROUP BY user_id
)
SELECT
    CASE
        WHEN pushes_received_30d <= 5 THEN '1-5'
        WHEN pushes_received_30d <= 15 THEN '6-15'
        WHEN pushes_received_30d <= 30 THEN '16-30'
        ELSE '30+'
    END AS volume_bucket,
    COUNT(*) AS users,
    AVG(personal_open_rate) * 100 AS avg_open_rate
FROM user_push_count
GROUP BY 1
ORDER BY 1;

Юзеры с 30+ push в месяц — open rate низкий. Push fatigue effect.

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

Ошибка 1. Sent vs Delivered. Юзер с permissions OFF — sent ≠ delivered. Делите только на delivered.

Ошибка 2. Multiple opens. Юзер дважды нажал push. Use COUNT(*) FILTER (WHERE opened) per push_id или per push event.

Ошибка 3. Включать silent push. Background updates — не user-facing. Filter out.

Ошибка 4. Permissions opt-out. Если 60% users opt out — общий reach low. Watch permission rate отдельно.

Ошибка 5. iOS vs Android. iOS opt-in required. Android opt-out (по умолчанию). Разные base rates.

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

• Как посчитать email open rate в SQL
• Как посчитать CTR в SQL
• Как посчитать DAU в SQL
• Как посчитать reactivation в SQL

FAQ

Какой Push Open Rate ok?

Transactional: 30-50%. Marketing/promo: 2-8%. Reminders: 5-15%.

Push vs Email — какое лучше?

Push — immediate, requires permissions. Email — reach all subscribers, lower urgency.

Push fatigue threshold?

15+ pushes в месяц снижает Open Rate на 30-50%. Cap volume.

iOS vs Android Open Rate?

iOS обычно ниже (opt-in friction). Android higher (default ON).

Best time для push?

Зависит от категории. Доставка еды: 18-21. E-com: 19-22. News: 7-9.