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

Зачем Geo Distribution

Geo breakdown = где users / revenue. Drives localization, GTM strategy, regional pricing. CAC vs LTV varies sharply by country.

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

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;

Top countries

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;

Top 5 countries часто 70%+ revenue (Pareto).

Penetration rate

Penetration = % addressable population using product:

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;

Low penetration in big markets — growth opportunity.

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;

Premium-heavy countries (Western Europe) vs free-heavy (developing) — different tiers mix.

City-level

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;

Moscow + Saint Petersburg могут составлять 60%+ of RU users.

ARPU heatmap by country

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,
    CASE
        WHEN AVG(monthly_revenue) > 50 THEN 'high-ARPU'
        WHEN AVG(monthly_revenue) > 20 THEN 'mid-ARPU'
        ELSE 'low-ARPU'
    END AS tier
FROM user_revenue
GROUP BY country
HAVING COUNT(DISTINCT user_id) >= 100
ORDER BY arpu DESC;

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

Ошибка 1. IP-based geo wrong. VPN users показывают «their» country. Self-declared more reliable when available.

Ошибка 2. Aggregating «Other». Long tail countries grouped together. Hides important markets.

Ошибка 3. Currency conversion. Revenue в local currency vs USD. Standardize.

Ошибка 4. Time-shifted markets. Hourly trends ≠ for different timezones aggregate.

Ошибка 5. Country codes inconsistent. "US" / "USA" / "United States" — same. Standardize ISO 3166.

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

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

FAQ

Country source?

IP geo (less accurate, VPN) / Phone number / Billing address / Self-declared.

How many countries track?

Top 20 — 90%+ users typically. Long tail aggregated.

City vs country?

Country — strategic. City — operational (geo-targeted campaigns).

Multi-country users?

Travelers / digital nomads. Primary country = billing.

Local currency vs USD?

Reporting в USD для compare. Operations в local.