What sells best, and who spends most, across all our stores?

The data

Top customers

SELECT customer, SUM(amount) AS spend
FROM orders
GROUP BY customer
ORDER BY spend DESC
LIMIT 3;

=TAKE(SORT(GROUPBY(Orders[Customer], Orders[Amount], SUM, 0, 0), 2, -1), 3)

GROUPBY is available in recent Microsoft 365 builds; elsewhere use a pivot table sorted by Sum of Amount.

Customers in more than one store

SELECT customer
FROM orders
GROUP BY customer
HAVING COUNT(DISTINCT store) = 2;

Multi-store customers are often the most loyal; treating them as two customers understates their value.

Making the stores comparable

• Product keys: map each store's product IDs (ASIN, Shopify variant ID) to your own SKU before ranking products.
• Customer keys: marketplaces may hide buyer emails; rank customers only within stores where you have them.
• Currency and tax: convert to one currency and compare net of VAT.
• Returns: rank on net sales — see products with high return rates.

Ask it in Analistable: “Top 10 products by revenue across all stores this quarter, and which customers bought in more than one store.”

Top products

SELECT sku, SUM(amount) AS revenue
FROM orders_with_sku
GROUP BY sku
ORDER BY revenue DESC
LIMIT 10;

The same query on the product column ranks products; run it per store as well (GROUP BY store, sku) to see whether a product sells everywhere or only in one channel.

How concentrated is revenue?

Two customers bring 86% of revenue. A cumulative share column shows concentration risk better than a ranked list. By store, the web shop took 550 (73.3%) and Amazon 200 (26.7%).

SELECT customer, SUM(amount) AS spend,
       ROUND(100.0 * SUM(amount) / SUM(SUM(amount)) OVER (), 1) AS share_pct
FROM orders
GROUP BY customer
ORDER BY spend DESC;

Older Excel and Google Sheets

• Excel 2019 / 2016: insert a pivot table from the stacked table, Customer (or SKU) in Rows, Sum of Amount in Values, then sort descending and use Value Filters › Top 10 to keep the top N.
• Google Sheets: =QUERY(Orders!A2:E, "select A, sum(D) where A is not null group by A order by sum(D) desc limit 3", 0) with customer in A and amount in D.
• Ties: RANK.EQ gives equal values the same rank; decide whether a “top 10” can include an eleventh tied product.

Troubleshooting

Run the ranking for the same period each month and keep the previous month's list next to it — products moving into or out of the top 10 are often more useful than the list itself. To build the stacked table in the first place, see combining data from multiple sources.

Frequently asked questions

How do I combine sales from several stores?

Stack each store's export with a Store column, using the same product and customer keys, then group and rank.

How do I find customers who buy in more than one store?

Group orders by customer and keep those with COUNT(DISTINCT store) greater than 1.

What if each store uses different product IDs?

Create a mapping table from each store's ID to your SKU and join through it before ranking.

How do I see the share of revenue from top customers?

Divide each customer's spend by total revenue and add a running total; the cumulative share shows concentration.

Should I rank on gross or net sales?

Net of refunds and returns where you can, so heavily returned products don't top the list.

Can I rank products per store as well?

Yes: group by store and product, then rank within each store with RANK() OVER (PARTITION BY store ...).

Related guides

• Which invoices are more than 30 days overdue?
• Have any suppliers billed us twice?
• How do sales reps rank across all regions?
• How much has each customer spent, net of refunds?
• How fast is each product's stock turning over?