How to Analyze a Shopify Orders Export (CSV)

Shopify's analytics dashboard answers the common questions. The orders export answers the uncommon ones — the ones specific to your store — and it's the only way to get raw data into a spreadsheet. But the export has a structure that catches almost everyone the first time, and getting it wrong inflates every number. This guide covers what's in the file, the one thing you must know about it, and the questions worth asking once it's loaded.

Getting the export

Orders → Export → All orders (or a date range) → CSV for Excel, Numbers, or other spreadsheet programs. Large stores get the file by email. Open it in a text editor first, not Excel — Excel will reformat dates and strip leading zeros from ZIP codes on open.

What's in the file

Around 70 columns. The ones that matter for analysis:

The trap: one row per line item, not per order

An order with three products is three rows. The order-level columns — Total, Email, Created at, Shipping Country — are filled in on the first row of the order and blank on the rest. Line-item columns are filled on every row.

Consequences:

• =SUM(Total) is correct (blanks add nothing) — but =COUNTA(Name) counts orders while =COUNTA(Lineitem name) counts line items.
• =AVERAGE(Total) is correct only because blanks are ignored. =AVERAGEIF with a criteria on a line-item column will be wrong.
• A pivot with Shipping Country in Rows shows a large (blank) group: every non-first row.
• Sorting the sheet by any column destroys the link between line items and their order, permanently.

The fix in Excel: before doing anything else, fill the order-level columns down. Select the column, Find & Select → Go To Special → Blanks, type = and click the cell above, press Ctrl+Enter, then copy and paste as values. Do this for Name, Email, Created at, Financial Status, and Shipping Country. Now every row carries its order's identity and grouping works.

If you upload the export to ChatExcel, tell the assistant the structure once — "each row is a line item; order-level columns are only filled on the first row of each order" — and ask for order-level metrics "using only rows where Total is not blank". It will carry that through the conversation.

The twelve questions

Order-level (rows where Total is filled):

1. Orders and revenue by month. The trend. Chart it as a line.
2. Average order value = Total revenue ÷ order count. Track it monthly; a falling AOV with rising orders usually means a discount code is working too well.
3. Repeat customer rate = customers with ≥ 2 orders ÷ all customers. Group by Email, count orders, then count how many emails have 2+.
4. Time between first and second order — the median tells you when to send the win-back email.
5. Revenue by country / province. Where to advertise, where shipping is eating margin.
6. Discount code performance — orders and revenue by code, and AOV with vs. without a code.
7. Refund rate — refunded + partially_refunded orders ÷ all orders, and refunded amount ÷ revenue. Segment by product if it's high.
8. Unfulfilled orders older than N days — an operations list, not a metric.

Line-item-level (all rows):

9. Top products by units and by revenue — they're rarely the same list.
10. Products most often bought together — for orders with 2+ line items, pair up the products. This is a hard formula and an easy question to ask.
11. Units per order distribution — how many orders are single-item?
12. SKU-level margin, if you add a cost column from your inventory sheet (an XLOOKUP on SKU).

Money columns and currency

Shopify exports money as plain numbers in the store currency (2400.5, no symbol). Multi-currency stores get a Currency column; sum within currency or convert first. Total includes shipping and tax; Subtotal is merchandise only — pick the one your finance team means by "revenue" and use it consistently.

Dates and time zones

Created at includes a UTC offset (-0400). For monthly grouping the offset doesn't matter; for "orders by hour of day" it does — strip the offset or convert to your local zone before grouping.

Two sanity checks

Before sharing any number:

• Order count in the sheet (distinct Name) should match the count Shopify shows for the same date range.
• Revenue for one month should match the Shopify dashboard's Total sales for that month within rounding. If it doesn't, you're probably summing line-item prices instead of order totals, or counting refunded orders as sales.

Ask both of these first in ChatExcel — "how many distinct orders, and what's the total of Total for March?" — and reconcile against the dashboard before going further.