Geographic and Segmentation Analysis

Geographic analysis reveals where your ec_customers are, which markets are strongest, and where growth opportunities exist. Combined with demographic segmentation (age group, acquisition channel), it paints a complete picture of your customer base's composition and behaviour across different groups.

For e-commerce businesses, geographic analysis informs decisions about: where to open physical locations, which countries to localise for, where to focus digital marketing spend, and which markets have the highest LTV ec_customers.

Why Geography Matters in Analytics

A global average hides enormous regional variation:

• Customers in one country might convert at 8%, while another at 1.5%
• Average order value might be 3× higher in one market
• Churn rates vary significantly by region (often correlated with competitive intensity)

Geographic segmentation makes these hidden patterns visible.

Revenue by Country

The foundation: aggregate revenue by country. This shows market size and can be compared to population or market size data to identify which countries are under-penetrated.

The RANK() OVER (ORDER BY revenue DESC) provides overall ranking. pct_of_total_revenue using SUM(...) OVER () (window over all countries) shows each country's share without a separate subquery for the total.

SELECT
    c.country,
    COUNT(DISTINCT c.customer_id) AS ec_customers,
    COUNT(DISTINCT o.order_id) AS ec_orders,
    ROUND(SUM(o.total_amount), 2) AS revenue,
    ROUND(AVG(o.total_amount), 2) AS avg_order_value,
    ROUND(SUM(o.total_amount) / COUNT(DISTINCT c.customer_id), 2) AS revenue_per_customer,
    ROUND(SUM(o.total_amount) / SUM(SUM(o.total_amount)) OVER () * 100, 1) AS pct_of_total_revenue,
    RANK() OVER (ORDER BY SUM(o.total_amount) DESC) AS revenue_rank
FROM ec_customers c
JOIN ec_orders o ON o.customer_id = c.customer_id
WHERE o.status NOT IN ('cancelled', 'refunded')
GROUP BY c.country
ORDER BY revenue DESC;

What This Returns

Each row is one country's full commercial profile. revenue_per_customer is the most useful normalised metric — it removes the effect of market size and shows which countries have the highest-value ec_customers. A country with 50 ec_customers but £500 revenue_per_customer is more commercially attractive per marketing pound than one with 500 ec_customers at £80 each.