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

Зачем нужен Geo Distribution

Geo distribution — это разрез пользователей и выручки по странам и городам: где сидит ваша аудитория и откуда приходят деньги. От этого зависят решения о локализации, стратегии выхода на рынки (GTM) и региональном ценообразовании. Ключевой нюанс — соотношение CAC и LTV резко меняется от страны к стране: пользователь из одного рынка может окупаться за месяц, а из другого не окупиться никогда. Поэтому «где наши пользователи» и «где наша выручка» — это, как правило, разные карты, и аналитик должен показывать обе.

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

Стартовый запрос считает по каждой стране число пользователей, выручку, ARPU и доли в общем объёме:

SELECT
    country,
    COUNT(DISTINCT user_id) AS users,
    SUM(revenue)            AS revenue,
    AVG(revenue)            AS arpu,
    COUNT(DISTINCT user_id)::NUMERIC * 100 / SUM(COUNT(DISTINCT user_id)) OVER () AS user_share_pct,
    SUM(revenue)::NUMERIC * 100 / SUM(SUM(revenue)) OVER () AS revenue_share_pct
FROM users u
LEFT JOIN transactions t ON t.user_id = u.user_id AND t.status = 'paid'
WHERE u.created_at >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY country
ORDER BY revenue DESC;

Обратите внимание на две детали. Оконные функции SUM(...) OVER () считают долю каждой страны от общего итога прямо в одном запросе — не нужен отдельный подзапрос с тотальной суммой. А условие t.status = 'paid' стоит в ON, а не в WHERE: так LEFT JOIN сохраняет страны вообще без платежей (у них выручка станет NULL/0), а не выкидывает их из выборки.

Топ стран

Чтобы выделить главные рынки, ранжируем страны по выручке:

SELECT
    country,
    COUNT(DISTINCT user_id) AS users,
    SUM(revenue)            AS revenue,
    RANK() OVER (ORDER BY SUM(revenue) DESC) AS revenue_rank
FROM users u
LEFT JOIN transactions t USING (user_id)
WHERE t.status = 'paid'
GROUP BY country
ORDER BY revenue DESC
LIMIT 20;

Почти в любом продукте работает принцип Парето: топ-5 стран часто дают 70%+ всей выручки. Это нормально, но важно смотреть не только на абсолют, но и на потенциал роста — большой рынок с маленькой долей может быть интереснее насыщенного лидера.

Penetration rate

Penetration rate — доля потенциальной аудитории рынка, которая уже пользуется продуктом. Она показывает, где есть запас для роста:

WITH country_users AS (
    SELECT country, COUNT(*) AS users
    FROM users
    WHERE active = TRUE
    GROUP BY country
),
country_population AS (
    SELECT * FROM (VALUES
        ('US', 330000000),
        ('UK', 67000000),
        ('DE', 83000000),
        ('RU', 144000000)
    ) AS p(country, population)
)
SELECT
    cu.country,
    cu.users,
    cp.population,
    cu.users::NUMERIC * 100 / cp.population AS penetration_pct
FROM country_users cu
JOIN country_population cp ON cp.country = cu.country
ORDER BY penetration_pct DESC;

Низкое проникновение на большом рынке — сигнал точки роста: аудитория есть, а вы её ещё не забрали. Высокое проникновение на маленьком рынке, наоборот, говорит, что расти там почти некуда. В реальной задаче вместо всего населения обычно берут addressable-аудиторию (например, только людей с подходящим возрастом и доступом в интернет).

Geo × tier

Пересечение страны и тарифа показывает, как отличается структура монетизации по рынкам:

SELECT
    u.country,
    u.subscription_tier,
    COUNT(DISTINCT u.user_id) AS users,
    SUM(t.amount)             AS revenue,
    AVG(t.amount)             AS arpu
FROM users u
LEFT JOIN transactions t ON t.user_id = u.user_id AND t.status = 'paid'
WHERE u.created_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY u.country, u.subscription_tier
ORDER BY u.country, revenue DESC;

Такой разрез вскрывает разную структуру тарифов: в странах Западной Европы обычно перевес в сторону платных планов, а на развивающихся рынках — в сторону бесплатного. Это напрямую влияет на то, где имеет смысл продвигать премиум, а где — работать над конверсией из free.

City-level и ARPU по странам

Спуститься с уровня страны на уровень города полезно для операционных задач — например, гео-таргетинга кампаний:

SELECT
    country,
    city,
    COUNT(DISTINCT user_id) AS users,
    SUM(revenue)            AS revenue
FROM users u
LEFT JOIN transactions t USING (user_id)
WHERE country = 'RU'
GROUP BY country, city
ORDER BY users DESC
LIMIT 30;

В России Москва и Санкт-Петербург вместе нередко дают 60%+ всех пользователей — концентрация в столицах характерна для многих продуктов. Отдельно полезно смотреть ARPU по странам, добавив медиану, чтобы среднее не перекашивали несколько крупных плательщиков:

SELECT
    country,
    COUNT(DISTINCT user_id) AS users,
    AVG(monthly_revenue)    AS arpu,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY monthly_revenue) AS median_arpu,  -- медианный ARPU
    CASE
        WHEN AVG(monthly_revenue) > 50 THEN 'высокий ARPU'
        WHEN AVG(monthly_revenue) > 20 THEN 'средний ARPU'
        ELSE 'низкий ARPU'
    END AS tier
FROM user_revenue
GROUP BY country
HAVING COUNT(DISTINCT user_id) >= 100  -- отсекаем страны с маленькой выборкой
ORDER BY arpu DESC;

Условие HAVING COUNT(...) >= 100 отсекает страны, где пользователей слишком мало: на выборке из пяти человек ARPU ничего не значит и только зашумит картину.

На собесе

Типичная формулировка: «Посчитай распределение пользователей и выручки по странам». Что хочет увидеть интервьюер:

• Две метрики, а не одну. Доля пользователей и доля выручки — разные вещи; сильный ответ показывает обе через оконные функции SUM() OVER ().
• Корректный JOIN. Условие статуса платежа в ON, а не в WHERE, чтобы LEFT JOIN не терял страны без платежей. Это любимая ловушка.
• Дедупликацию. COUNT(DISTINCT user_id), а не COUNT(*) — иначе пользователь с несколькими транзакциями посчитается многократно.
• Оговорку про качество данных. Упомяните, что гео по IP ненадёжно из-за VPN и что коды стран надо приводить к единому стандарту — это выделяет кандидата.

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

• Гео по IP ненадёжно. Пользователи с VPN отображаются в «чужой» стране. Когда есть self-declared страна или billing-адрес, они точнее, чем IP.
• Схлопывание в «Other». Если длинный хвост стран сваливать в одну группу «прочие», можно спрятать важный растущий рынок. Смотрите хвост отдельно.
• Игнорирование валют. Выручка в локальных валютах несопоставима напрямую. Перед сравнением приводите всё к единой валюте (обычно USD).
• Разные часовые пояса. Почасовые тренды по странам с разными таймзонами нельзя складывать в один ряд — пики просто не совпадут по времени.
• Несогласованные коды стран. «US», «USA» и «United States» — одна и та же страна, но SQL посчитает их как три. Приводите к единому стандарту ISO 3166.

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

• Как посчитать revenue в SQL
• Как посчитать ARPU в SQL
• Как посчитать ARPPU в SQL
• Как посчитать LTV в SQL

FAQ

Откуда брать страну пользователя?

Основные источники: гео по IP (наименее точный, ломается на VPN), код телефона, billing-адрес и self-declared страна из профиля. Billing и self-declared обычно надёжнее IP. На практике выбирают источник исходя из того, что важнее — покрытие (IP есть почти всегда) или точность (billing есть только у плательщиков).

Сколько стран отслеживать?

Как правило, топ-20 стран покрывают 90%+ пользователей, а длинный хвост агрегируют. Но перед тем как схлопнуть хвост, стоит убедиться, что в нём не прячется быстрорастущий рынок — иначе можно проглядеть новую точку роста.

Разрез по городу или по стране?

Страна — это стратегический уровень: локализация, выход на рынок, ценообразование. Город — операционный: гео-таргетинг кампаний, логистика, офлайн-мероприятия. Обычно начинают со страны и спускаются до города там, где нужно принимать точечные решения.

Как считать пользователей, которые бывают в нескольких странах?

Путешественники и цифровые кочевники «мелькают» в разных гео. Чтобы не задваивать их, фиксируют одну основную страну — чаще всего по billing-адресу или по стране с наибольшим числом сессий. Главное — выбрать одно правило и применять его последовательно во всех отчётах.

В какой валюте показывать выручку?

Для сравнения стран между собой отчётность ведут в единой валюте (обычно USD), приводя суммы по курсу. Для операционных решений внутри рынка — в локальной валюте, потому что именно её видят пользователи и в ней устроено ценообразование. Идеально хранить и исходную сумму в локальной валюте, и приведённую.

Тренируйте SQL — откройте тренажёр с 1500+ вопросами для собесов.