A store’s “Sale” collection is whatever the merchant chose to put in it. The real list of discounted products is in the prices themselves. In Shopify, a product on sale has two prices: the price you pay, and a higher compare-at price that the store shows crossed out. Both are in the store’s public product data, for every variant, so a spreadsheet can find every item on sale whether or not it made it into the Sale collection.

Export the prices

Export the store with the Variant rows format. Each variant gets its own row, and the two columns you need sit next to each other:

  • price (column Q): what the variant sells for right now.
  • compare_at_price (column R): the “was” price. Empty if the store hasn’t set one.

The same row has title, variant_title, sku, vendor, product_type, available and product_url, so you don’t need a lookup to know what you are looking at. The column letters above hold for the standard 26 columns; the optional description columns are added at the end, so they don’t move these two.

Filter to the items on sale

A variant is on sale when its compare-at price is filled in and higher than its price. Add a helper column in the first empty column (AA, unless you added the description columns) with:

=IF(AND(R2<>"", R2>Q2), 1-Q2/R2, "")

Fill it down and format it as a percentage. It shows the discount for every variant on sale and stays empty for everything else. Then:

  1. Filter column AA to hide blanks. What is left is everything on sale.
  2. Sort AA largest to smallest to put the deepest discounts first.
  3. Filter available to true if you only care about sale items that can still be bought.

If your Excel uses semicolons as the list separator (common in Europe), write the formula with semicolons instead of commas, and pick Semicolon (European Excel) in the popup before you export.

Questions the sheet can answer

  • How much of the store is discounted? Count the non-empty cells in AA and divide by the number of variant rows. Do the same per product_type or vendor with a pivot table to see where the discounts are concentrated.
  • How deep are the discounts? The average and maximum of AA, per category. A store with a handful of items at 70% off is clearing stock; one with everything at 20% off is running a promotion.
  • Are only some sizes reduced? Shopify sets prices per variant, so a product can be on sale in one colour and full price in another. The variant rows make that visible.
  • Has the sale changed? Export again next week and compare the two files on variant_id. See tracking prices with two CSV exports.

What the export can’t see

The compare-at price is the only discount stored on the product itself. Several kinds of discount live elsewhere and won’t show up:

  • Discount codes and automatic discounts applied in the cart or at checkout. A store can run “20% off everything” this way without changing a single product price.
  • Prices for other markets or customer groups. The export shows the prices in the store’s public product data. A store selling in several countries can charge different prices to shoppers elsewhere.
  • Whether the “was” price was ever real. The compare-at price is whatever the merchant typed in. It is a claim, not a price history. Some stores also leave an old compare-at price that is equal to or lower than the current price; the formula above ignores those, because they aren’t a discount.
  • How well the sale is selling. CatalogCSV has no sales data and doesn’t estimate it.

And the usual limits apply: a store running a bot check can’t be read, and a password-protected store needs its password entered on the page first.

Free for the first 100 products

Every export includes the first 100 products for free, in every format. The free file contains the first 100 products in the order the store lists them, which means the sale items further down a big catalog may be cut off; the popup says how many products the store has in total when it finishes. A Pro license, $9 a month or $79 a year, removes the limit. Everything runs in your browser: no account, and nothing installed on the store.