DEV Community

Emir Mrkaljević
Emir Mrkaljević

Posted on

How I export WooCommerce sale products and keep only the 20%+ discounts

I sometimes need a spreadsheet of what a WooCommerce store actually has on sale, not the full catalog. A "sale" badge is not enough: I want a minimum discount, variation prices and stock in the same table, and I want products that miss the cut to cost me nothing.

WooCommerce exposes a public Store API on most shops (no admin login), so you can do the basic version yourself in about 40 lines of Python. Further down I show the hosted version I use for bigger jobs.

Doing it yourself with the Store API

The endpoint is /wp-json/wc/store/v1/products. It is paginated (X-WP-TotalPages header), accepts on_sale=true, and returns prices as integer strings in minor units (so 1699 with currency_minor_unit: 2 means 16.99). This script pulls every on-sale product, computes the discount from regular vs sale price, keeps 20%+ and writes a CSV:

import csv, sys, requests

STORE = sys.argv[1] if len(sys.argv) > 1 else "https://nalgene.com"
MIN_DISCOUNT = 20  # percent

def products(store, per_page=100):
    page = 1
    while True:
        r = requests.get(f"{store}/wp-json/wc/store/v1/products",
                         params={"per_page": per_page, "page": page, "on_sale": "true"},
                         headers={"User-Agent": "sale-filter-demo/1.0"}, timeout=30)
        r.raise_for_status()
        batch = r.json()
        if not batch:
            return
        yield from batch
        if page >= int(r.headers.get("X-WP-TotalPages", page)):
            return
        page += 1

rows = []
for p in products(STORE):
    pr = p["prices"]
    unit = 10 ** pr.get("currency_minor_unit", 2)
    regular, sale = int(pr["regular_price"]) / unit, int(pr["sale_price"]) / unit
    if not regular or sale >= regular:
        continue
    discount = round((regular - sale) / regular * 100)
    if discount >= MIN_DISCOUNT:
        rows.append({"title": p["name"], "regular": regular, "sale": sale,
                     "discount_pct": discount, "currency": pr["currency_code"],
                     "in_stock": p["is_in_stock"], "url": p["permalink"]})

rows.sort(key=lambda r: -r["discount_pct"])
with open("sale.csv", "w", newline="") as f:
    w = csv.DictWriter(f, fieldnames=["title", "regular", "sale", "discount_pct", "currency", "in_stock", "url"])
    w.writeheader(); w.writerows(rows)
print(f"{len(rows)} products at {MIN_DISCOUNT}%+ off -> sale.csv")
for r in rows[:5]:
    print(f'{r["discount_pct"]:>3}%  {r["sale"]:>7.2f} (was {r["regular"]:.2f})  {r["title"]}')
Enter fullscreen mode Exit fullscreen mode

Run it with pip install requests and python sale_filter.py https://nalgene.com. When I ran it, it printed:

9 products at 20%+ off -> sale.csv
 64%     5.00 (was 13.99)  24oz Neoprene Sleeve
 62%     5.00 (was 13.26)  24oz Gray Neoprene Sleeve
 35%    10.99 (was 16.99)  12oz Kids On-The-Fly Lock-Top Bottle
 35%    10.99 (was 16.99)  12oz Kids Grip-N-Gulp Sustain Water Bottle
 35%    10.99 (was 16.99)  12oz On-The-Fly Kids Lock-Top Bottle with Graphics
Enter fullscreen mode Exit fullscreen mode

Caveats: variable products report the cheapest variation in prices, so per-size or per-color discounts need a request per variation. Some stores disable the Store API or rate-limit it (HTTP 429), and a few put it behind a different path. Be polite: keep per_page reasonable, do not hammer the shop, and check robots.txt.

The hosted version

I got tired of stitching pages, sale flags and variation requests by hand, so I use an Apify Actor I maintain (TinyRex). It reads that same Store API, respects robots.txt, and writes one row per product (or one row per variation) that you can download as CSV, Excel or JSON.

The input I use

A single store, only products whose discount is at least 20%, cap at 25 rows so the run stays short, no variation details on the first pass:

{
  "stores": ["https://nalgene.com"],
  "maxProductsPerStore": 25,
  "outputMode": "products",
  "minDiscountPercent": 20,
  "includeVariations": false,
  "descriptionFormat": "none"
}
Enter fullscreen mode Exit fullscreen mode

minDiscountPercent uses regular price vs sale price. Set it to 0 (or omit it) to turn the cut off. If I only care that something is on sale at all, I use "onlyOnSale": true instead. Products below the cut, products with no discount, and sites that are not WooCommerce or that block the API are not charged.

Other filters I reach for on bigger catalogs: search (the store's own product search), keywords / excludeKeywords, categories, brands, minPrice / maxPrice, and onlyInStock.

What a row looks like

A saved product looks like this (fields trimmed). This one is a real row from a short test on nalgene.com:

{
  "storeUrl": "https://nalgene.com",
  "storeName": "Nalgene",
  "title": "12oz Kids On-The-Fly Lock-Top Bottle",
  "price": 10.99,
  "regularPrice": 16.99,
  "salePrice": 10.99,
  "onSale": true,
  "discountPercent": 35,
  "currency": "USD",
  "inStock": true,
  "url": "https://nalgene.com/product/12oz-kids-on-the-fly-lock-top-bottle/"
}
Enter fullscreen mode Exit fullscreen mode

After a run I download the dataset as CSV or JSON, or send it to Sheets, n8n or a webhook. A STORES report and a SUMMARY record tell me how many products were found, how many passed the filters, and how many were saved.

On that same short test I set the cut at 10% and the cap at 5. The run looked at 46 products from nalgene.com, saved 5 sale items (discounts from 24% to 62%), finished in about 20 seconds and cost a fraction of a cent. The cap stopped the run after five matches, so 5 of 46 is not "only five products were on sale."

Filters and monitoring I actually use

  • Minimum discount %: keep 20% or 30% off, drop the 5% "sale" stickers. Below the cut is free.
  • Only in stock: useful when I am building a buy list, not a historical catalog.
  • One row per variation: set "outputMode": "variants" when I need size or color prices in a spreadsheet. Variation details do not add a second charge in product mode.
  • Monitoring: "monitorMode": "changes" on a daily schedule returns new products plus price or stock changes, with the previous values. The first run is the baseline.
  • Several stores: paste a list of shop domains in stores. Non-WooCommerce sites are reported as not_woocommerce and are free.

Pricing is $0.60 per 1,000 products saved (per variation row in variation mode). Filtered, blocked and non-WooCommerce inputs are free. If a store rate-limits you (HTTP 429), turn on a proxy or lower maxConcurrency and try again; a blocked run is not charged for products it did not save.

If you want the longer walkthrough (category URLs, variation mode, and how the Store API is discovered), I wrote it here: How to export products from any WooCommerce store to CSV or JSON.

The actor is here: WooCommerce Products Scraper. If something breaks on a public store, open an issue on the Store page with the URL.

Top comments (0)