The first surprise in most Shopify exports is the row count. A store that lists 300 products produces a file with 3,000 rows. Nothing has been duplicated: each row is a variant, and understanding variants is the key to reading any Shopify catalogue correctly.
Products, options and variants
Shopify models a catalogue in three layers:
- A product is what the shopper sees as one listing: “Wool Runner”, with its title, description, photos, vendor and tags.
- Options are the choices a shopper makes on that listing, such as Colour and Size. A product can have up to three option axes.
- A variant is one specific combination of option values, such as Navy, size 9. It is the thing that is actually bought, so it has its own price, compare-at price, SKU, barcode, weight and stock status.
A shoe in 4 colours and 10 sizes therefore has 40 variants, and 40 rows in the export. A product with no options still has exactly one variant, which Shopify names “Default Title”.
Why one row per variant is the right shape
It is tempting to want one row per product, but that hides the information people usually need. Prices often differ by size. Stock nearly always does: a product page may say “in stock” while every popular size is sold out. Sale prices are frequently applied to some colours and not others. And the SKU, the code that links a listing to a warehouse, exists only on the variant.
One row per variant keeps all of that, and rolling it up to products later is easy. Going the other way is impossible.
Reading the option columns
The export has option1, option2 and option3 for the values, and an options column with the axis names in the same order. If options says Color / Size, then option1 is the colour and option2 the size. Different products in the same store can use different axes, so always check options before treating option1 as colour across the whole file.
Getting one line per product
In the tool
Switch the results table to Grouped by product. Each line shows the price range across variants, how many variants are in stock and the best discount.
In a spreadsheet
Insert a pivot table on the exported data and use product_id (or title) as rows. Useful values to add:
- COUNTA of variant_id: how many variants each product has.
- MIN and MAX of price: the price range.
- COUNTIF on available = TRUE: how many variants are in stock. In Google Sheets a helper column with
=IF(available_cell=TRUE,1,0)summed in the pivot does the same job. - MAX of discount_pct: the deepest markdown on any variant.
Measuring size and colour availability
Because each variant is its own row, the export answers questions a product page cannot. Filter to one product type, pivot by the size option, and count available versus unavailable variants: you can see at a glance which sizes sell out first across a whole range. Pivot by colour instead to see which colourways a store keeps deepest.
SKUs: useful, but check them
SKUs are entered by the merchant, so their quality varies. Some stores leave them blank, some reuse the same code on several variants, and some encode meaning in them (style, colour and size in a code like TR-M-NVY-09). For joining datasets, variant_id is the safer key. For matching against a supplier or warehouse list, the SKU is usually what you need. Our catalogue audit guide shows how to find blank and duplicate SKUs quickly.
Next, look up any unfamiliar field in the column reference.