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

Зачем CPI

CPI = Cost Per Install. Mobile-specific. Apple App Store / Google Play. Base метрика mobile UA (User Acquisition).

Формула

CPI = total_spend / installs

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

SELECT
    DATE_TRUNC('day', DATE) AS day,
    SUM(spend) AS spend,
    SUM(installs) AS installs,
    SUM(spend)::NUMERIC / NULLIF(SUM(installs), 0) AS cpi
FROM mobile_ua_campaigns
WHERE DATE >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;

iOS vs Android

SELECT
    platform,
    SUM(spend) AS spend,
    SUM(installs) AS installs,
    SUM(spend)::NUMERIC / NULLIF(SUM(installs), 0) AS cpi,
    -- Quality proxy
    SUM(d7_retained_users)::NUMERIC * 100 / NULLIF(SUM(installs), 0) AS d7_retention_pct
FROM mobile_ua_campaigns
WHERE DATE >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY platform
ORDER BY platform;

iOS CPI часто 1.5-3x Android (premium audience).

Blended CPI

Включает organic installs:

WITH paid AS (
    SELECT SUM(spend) AS spend, SUM(installs) AS installs
    FROM mobile_ua_campaigns
    WHERE DATE >= CURRENT_DATE - INTERVAL '30 days'
),
organic AS (
    SELECT COUNT(*) AS organic_installs
    FROM app_installs
    WHERE installed_at >= CURRENT_DATE - INTERVAL '30 days'
      AND source = 'organic'
)
SELECT
    paid.spend AS marketing_spend,
    paid.installs AS paid_installs,
    organic.organic_installs,
    paid.installs + organic.organic_installs AS total_installs,
    paid.spend::NUMERIC / NULLIF(paid.installs, 0) AS paid_cpi,
    paid.spend::NUMERIC / NULLIF(paid.installs + organic.organic_installs, 0) AS blended_cpi
FROM paid, organic;

По campaign / publisher

SELECT
    network,
    campaign_id,
    SUM(spend) AS spend,
    SUM(installs) AS installs,
    SUM(spend)::NUMERIC / NULLIF(SUM(installs), 0) AS cpi,
    -- ROAS / CPI ratio
    SUM(revenue_d7) AS revenue_d7,
    SUM(revenue_d7)::NUMERIC / NULLIF(SUM(spend), 0) AS roas_d7
FROM mobile_ua_campaigns
WHERE DATE >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY network, campaign_id
HAVING SUM(installs) >= 100
ORDER BY roas_d7 DESC;

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

Ошибка 1. Install fraud. Some networks fake installs (SDK spoofing, click flooding). Use anti-fraud (Adjust, Appsflyer).

Ошибка 2. Attribution window. 7-day click, 1-day view — standard. Longer → more installs attributed → lower CPI.

Ошибка 3. SKAdNetwork (iOS). iOS 14+ — privacy. CPI accuracy lower. SKAd v4 — improved but still aggregated.

Ошибка 4. Re-install как install. Reinstall (после uninstall) counted? Network-specific.

Ошибка 5. Quality varies. Cheap CPI from incentivized (paid-to-install) networks — bad quality. ROAS matters.

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

• Как посчитать CAC в SQL
• Как посчитать ROAS в SQL
• Как посчитать CPA в SQL
• Как посчитать day-7 retention в SQL

FAQ

Какой CPI ok?

iOS: $3-8. Android: $1-4. Premium markets (US/UK/JP) higher.

Blended vs Paid CPI?

Paid — UA team metric. Blended — true marginal cost (помогает organic).

iOS CPI выше — почему?

Premium device users, LTV выше. Worth higher CPI если LTV/CPI ratio ok.

CPI растёт — что делать?

1. Test new creatives. 2) Refine targeting. 3) Switch networks. 4) Optimize for quality, not just installs.

Incentivized installs?

Users get reward for install. Cheap, но retention horrible. Avoid для quality.