Home›Learn›Analytics

How to calculate revenue by region

Geographic segmentation with quarter-over-quarter comparison using SQL

Definition

What it is

Revenue by region is a geographic segmentation of sales data — typically by country, state/province, or city — that shows how revenue is distributed across locations and how that distribution is changing over time. It is essential for businesses that sell across multiple geographies, helping identify high-growth markets, underperforming regions, and the impact of regional campaigns or expansions.

Formula

Revenue by Region Formula

Formula

Regional Revenue = SUM(order_total) GROUP BY shipping_country (or state, city) QoQ Growth = ((Current Quarter − Prior Quarter) ÷ Prior Quarter) × 100

Use the shipping address fields for geographic segmentation (shipping_country, shipping_province, shipping_city). For Amazon, use the ship-to address from order items. For Shopify, use the shipping_address fields on the orders table. Normalise country codes to ISO 3166-1 alpha-2 for consistent grouping across platforms.

Business impact

Why it matters

Regional revenue analysis surfaces opportunities and risks invisible in aggregate numbers. A business with flat overall revenue may have one region growing 30% offsetting another declining 25% — two very different strategic situations that look identical at the top level. Quarter-over-quarter comparison by region helps evaluate the impact of regional campaigns, shipping changes, or market expansion investments.

The challenge

How most teams track this today

Shopify Analytics has basic geographic reports but limited date range flexibility. Amazon Seller Central shows geographic sales data in the Business Reports section but only for certain marketplace regions. Combining both platforms' geographic data requires exporting and manually joining two separate reports.

Run it on your data

Calculate this automatically with Taptic Data

Connect your SQL Server account and Taptic generates this calculation from plain English against your actual data — no Excel exports, no manual joins. The SQL runs against your real schema, your real tables, your real numbers.

SQL query for revenue by region with quarter-over-quarter comparison →Connect SQL Server →Try Free — $29.99/mo

FAQ

Common questions

Should I segment by shipping address or billing address?

Shipping address is the standard for eCommerce geographic segmentation — it represents where the product is consumed, which is more meaningful for market analysis. Billing address is relevant for financial and tax reporting. For most regional revenue analysis, use shipping address.

How do I handle orders with no geographic data?

Some orders may have null or incomplete address fields. Best practice is to group these as "Unknown" or "Other" rather than excluding them — excluding them distorts regional totals. If null geographic data is common, it may indicate a checkout flow issue worth fixing.

Can I do this analysis across Amazon and Shopify combined in Taptic?

Yes. Both Amazon and Shopify import shipping address fields. A UNION ALL query combining both sources with a channel column gives you a unified geographic revenue view. You can then GROUP BY country and channel to see both total regional revenue and channel mix by region.

What is the difference between QoQ and MoM for regional analysis?

Quarter-over-quarter (QoQ) is better for regional analysis because it smooths out monthly noise and aligns with business planning cycles. Month-over-month (MoM) is more volatile for regional data — a single large order from one country can distort the MoM comparison significantly. QoQ gives a more reliable trend signal.

Analytics hub

All Analytics queries, use cases, and SQL examples in one place.

SQL Query Examples hub — queries for databases, warehouses, and eCommerce data →

Stop calculating this in spreadsheets

Connect SQL Server to Taptic Data and run this calculation automatically from plain English — against your real data, on a schedule, delivered to your team.

Start Free — $29.99/mo Try the Live Demo