allanninal.dev / woo

Field notes / WooCommerce core: products and catalog

Repair WooCommerce core: products and catalog

Popularity sort uses stale total sales

Sort a WooCommerce shop by "Popularity" and it should show what actually sells first. Instead it often shows products that stopped selling months ago, while a genuine bestseller sits on page three. The sort is not broken. The number it sorts by, total_sales, is wrong. Here is why that number drifts and a small script that recounts it from real orders.

Python and Node.js Runs on a schedule Safe by default (dry run)
Man in green jacket walking on sidewalk during daytime
Photo by Markus Spiske on Unsplash
The short answer

The Popularity sort orders products by the total_sales number saved on each product, and WooCommerce only updates that number through its own order status hooks. Imported orders, statuses changed outside the normal checkout flow, and refunds that never decrement the count all leave it stale. Run a small Python or Node.js script on a schedule that sums real quantities from paid orders and refunds through the WooCommerce REST API, compares that to the stored number, and corrects any product that disagrees. Full code, tests, and a dry run guard are below.

The problem in plain words

Every WooCommerce product carries a hidden number called total_sales. It is not shown on the product page. It exists for one job: sorting the catalog by "Popularity" in the shop, in a widget, or in a shortcode. When a shopper picks that sort, WooCommerce simply orders products by this number, largest first.

WooCommerce keeps that number current by listening for an order to reach Processing or Completed, then adding the quantity from each line item. That works fine for orders that go through a normal checkout. It falls apart the moment an order gets into a paid state some other way, or a paid order is later refunded and the count is never walked back down. The catalog then ranks by a number that describes last quarter, or a bulk import, instead of what customers are buying today.

Order imported or status set by SQL Order refunded or cancelled later ✕ hook never runs total_sales wrong too high or too low Popularity sort ranked wrong Real bestseller buried
The count only moves through the normal checkout hooks. Anything that bypasses them, or reverses a sale afterward, leaves the stored number disagreeing with reality.

Why it happens

The WooCommerce core code that updates total_sales runs inside the order status transition hooks, specifically when an order moves into a status that counts as a completed sale. That is a clean design for the common case, but several everyday situations skip it entirely:

None of this shows up as an error. The store just quietly ranks its catalog by a number that stopped being true a while ago, and nobody notices until a merchant asks why their newest bestseller is not on the first page of the Popularity sort.

The key insight

The orders themselves are the source of truth for what sold, not the cached total_sales field. If you sum the real quantities from paid orders, then subtract whatever was later refunded, you get the number the Popularity sort should have been using all along. A recount script is a safety net that runs on a schedule, computes that real number, and repairs the products whose cached count drifted.

The fix, as a flow

We do not touch checkout or the order status hooks. We add a job that runs on a schedule, walks paid orders in a lookback window, and adds up the real quantity sold per product, refunds included as negative units. It then compares that real count to what is stored on the product and, only when the two disagree, writes the corrected number back through the WooCommerce REST API.

Scheduled job once a day List paid orders and their refunds Sum real qty per product_id Stored count disagrees? yes no, skip Write real count to total_sales
The recount reads the truth from real orders and refunds, and only writes the products whose cached count no longer matches it. Everything else is left alone.

Build it step by step

1

Get access to the store

You need a WooCommerce REST API key pair, a consumer key and a consumer secret, with read and write access to products and orders. Create the key under WooCommerce, Settings, Advanced, REST API. Keep every value in environment variables, never in the file.

setup (shell)
pip install requests

export WOO_STORE_URL="https://yourstore.com"
export WOO_CONSUMER_KEY="ck_..."
export WOO_CONSUMER_SECRET="cs_..."
export LOOKBACK_DAYS="365"
export DRY_RUN="true"   # start safe, change to false to write
setup (shell)
npm install

export WOO_STORE_URL="https://yourstore.com"
export WOO_CONSUMER_KEY="ck_..."
export WOO_CONSUMER_SECRET="cs_..."
export LOOKBACK_DAYS="365"
export DRY_RUN="true"   // start safe, change to false to write
2

Walk every paid order and its refunds

Ask the WooCommerce REST API for orders in Processing or Completed within the lookback window, and page through all of them. For each order, also fetch its refunds, since a refund line item carries the quantity that was returned. Going through the REST API means the code works the same whether the store uses High Performance Order Storage (HPOS) or the legacy post based orders, because WooCommerce handles the storage for you.

step2.py
import os, requests
from requests.auth import HTTPBasicAuth

WOO_URL = os.environ["WOO_STORE_URL"].rstrip("/")
AUTH = HTTPBasicAuth(os.environ["WOO_CONSUMER_KEY"], os.environ["WOO_CONSUMER_SECRET"])

def paid_orders(after_iso):
    page = 1
    while True:
        r = requests.get(
            f"{WOO_URL}/wp-json/wc/v3/orders",
            params={"status": "processing,completed", "after": after_iso, "per_page": 100, "page": page},
            auth=AUTH, timeout=30,
        )
        r.raise_for_status()
        batch = r.json()
        if not batch:
            return
        yield from batch
        page += 1

def refund_line_items(order_id):
    r = requests.get(f"{WOO_URL}/wp-json/wc/v3/orders/{order_id}/refunds", auth=AUTH, timeout=30)
    r.raise_for_status()
    for refund in r.json():
        yield from refund.get("line_items", [])
step2.js
const WOO_URL = process.env.WOO_STORE_URL.replace(/\/$/, "");
const AUTH = "Basic " + Buffer.from(
  `${process.env.WOO_CONSUMER_KEY}:${process.env.WOO_CONSUMER_SECRET}`
).toString("base64");

async function woo(path) {
  const res = await fetch(`${WOO_URL}/wp-json/wc/v3${path}`, { headers: { Authorization: AUTH } });
  if (!res.ok) throw new Error(`Woo ${path} returned ${res.status}`);
  return res.json();
}

async function* paidOrders(afterIso) {
  let page = 1;
  while (true) {
    const batch = await woo(`/orders?status=processing,completed&after=${afterIso}&per_page=100&page=${page}`);
    if (!batch.length) return;
    for (const order of batch) yield order;
    page++;
  }
}

async function refundLineItems(orderId) {
  const refunds = await woo(`/orders/${orderId}/refunds`);
  return refunds.flatMap((refund) => refund.line_items || []);
}
3

Sum real quantity per product

Add up the quantity from every order line item, keyed by product_id. Refund line items already carry a negative quantity in WooCommerce, so adding them subtracts the returned units automatically. The result is the true number of units sold for each product, right now, in the window you chose.

step3.py
from collections import defaultdict

def net_quantity(line_item):
    try:
        return int(line_item.get("quantity") or 0)
    except (TypeError, ValueError):
        return 0

def real_sales_by_product(orders_with_refunds):
    totals = defaultdict(int)
    for order, refund_items in orders_with_refunds:
        for item in order.get("line_items", []):
            if item.get("product_id"):
                totals[item["product_id"]] += net_quantity(item)
        for item in refund_items:
            if item.get("product_id"):
                totals[item["product_id"]] += net_quantity(item)
    return totals
step3.js
export function netQuantity(lineItem) {
  const qty = Number(lineItem && lineItem.quantity);
  return Number.isFinite(qty) ? Math.trunc(qty) : 0;
}

function addRealSales(totals, order, refundItems) {
  for (const item of order.line_items || []) {
    if (item.product_id) totals.set(item.product_id, (totals.get(item.product_id) || 0) + netQuantity(item));
  }
  for (const item of refundItems) {
    if (item.product_id) totals.set(item.product_id, (totals.get(item.product_id) || 0) + netQuantity(item));
  }
}
4

Decide, with one pure function

Keep the decision in its own function that takes the stored count and the real count and returns an action. A pure function like this is easy to read and easy to test, which we do later. The rule is simple. If the two numbers already agree, skip it. Otherwise, fix it, and never let the corrected number go negative.

decide.py
def decide(stored_total_sales, real_total_sales):
    try:
        stored = int(stored_total_sales)
    except (TypeError, ValueError):
        stored = 0
    real = max(0, int(real_total_sales))
    if stored == real:
        return ("skip", "total_sales already correct")
    return ("fix", f"stored {stored}, real {real}")
decide.js
export function decide(storedTotalSales, realTotalSales) {
  const stored = Number.isFinite(Number(storedTotalSales)) ? Math.trunc(Number(storedTotalSales)) : 0;
  const real = Math.max(0, Math.trunc(realTotalSales || 0));
  if (stored === real) return ["skip", "total_sales already correct"];
  return ["fix", `stored ${stored}, real ${real}`];
}
5

Write the corrected number back

When the action is fix, update the product's total_sales field through the WooCommerce REST API. This is the same field the Popularity sort reads, so the correction takes effect the moment it is saved, no cache to clear and no reindex to wait for.

apply.py
def write_total_sales(product_id, real_total_sales):
    requests.put(
        f"{WOO_URL}/wp-json/wc/v3/products/{product_id}",
        json={"total_sales": str(real_total_sales)},
        auth=AUTH, timeout=30,
    ).raise_for_status()
apply.js
async function writeTotalSales(productId, realTotalSales) {
  await woo(`/products/${productId}`, {
    method: "PUT",
    body: JSON.stringify({ total_sales: String(realTotalSales) }),
  });
}
6

Wire it together with a dry run guard

The loop ties every piece together. Notice the dry run guard. On the first few runs, leave DRY_RUN on so the script only reports which products it would correct and by how much. Read the output, trust it, then switch it off to let it write. Run it on a schedule with cron once a day.

Run it safe

Always start with DRY_RUN=true. A recount rewrites real product data, so you want to see its plan before it acts. Once the report looks right for a day, turn it off.

The full code

Here is the complete recount script in one file for each language. It reads settings from the environment, logs what it does, respects the dry run flag, and is safe to run again and again because it only ever writes a product whose stored count still disagrees with the real one.

View this code on GitHub Full runnable folder with tests in the woocommerce-fixes repo.

recount_total_sales.py
"""Recount WooCommerce's total_sales product meta from real, paid orders.

The "Popularity" catalog sort orders products by the total_sales number
stored on each product. WooCommerce core only bumps that number through its
own order status hooks, so it drifts from reality whenever orders are
imported straight into the database, a status is changed by another plugin
or a direct SQL update, or a refund and cancellation never decrements it
back down. This walks paid orders in a lookback window, sums real
quantities per product (minus refunded quantities), compares that to the
stored total_sales, and corrects any product whose number is wrong. Read
only by default. Run on a schedule.
"""
import os
import logging
import requests
from collections import defaultdict
from requests.auth import HTTPBasicAuth

logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")
log = logging.getLogger("recount_total_sales")

WOO_URL = os.environ["WOO_STORE_URL"].rstrip("/")
AUTH = HTTPBasicAuth(os.environ["WOO_CONSUMER_KEY"], os.environ["WOO_CONSUMER_SECRET"])
LOOKBACK_DAYS = int(os.environ.get("LOOKBACK_DAYS", "365"))
DRY_RUN = os.environ.get("DRY_RUN", "true").lower() == "true"

SALE_COUNTED_STATUSES = {"processing", "completed"}


def decide(stored_total_sales, real_total_sales):
    try:
        stored = int(stored_total_sales)
    except (TypeError, ValueError):
        stored = 0
    real = max(0, int(real_total_sales))
    if stored == real:
        return ("skip", "total_sales already correct")
    return ("fix", f"stored {stored}, real {real}")


def net_quantity(line_item):
    try:
        return int(line_item.get("quantity") or 0)
    except (TypeError, ValueError):
        return 0


def paid_orders(after_iso):
    page = 1
    while True:
        r = requests.get(
            f"{WOO_URL}/wp-json/wc/v3/orders",
            params={"status": "processing,completed", "after": after_iso, "per_page": 100, "page": page},
            auth=AUTH, timeout=30,
        )
        r.raise_for_status()
        batch = r.json()
        if not batch:
            return
        for order in batch:
            yield order
        page += 1


def refund_line_items(order_id):
    r = requests.get(f"{WOO_URL}/wp-json/wc/v3/orders/{order_id}/refunds", auth=AUTH, timeout=30)
    r.raise_for_status()
    for refund in r.json():
        for item in refund.get("line_items", []):
            yield item


def real_sales_by_product(lookback_days):
    import datetime

    after = (datetime.date.today() - datetime.timedelta(days=lookback_days)).isoformat() + "T00:00:00"
    totals = defaultdict(int)
    for order in paid_orders(after):
        for item in order.get("line_items", []):
            product_id = item.get("product_id")
            if not product_id:
                continue
            totals[product_id] += net_quantity(item)
        for item in refund_line_items(order["id"]):
            product_id = item.get("product_id")
            if not product_id:
                continue
            totals[product_id] += net_quantity(item)
    return totals


def get_product(product_id):
    r = requests.get(f"{WOO_URL}/wp-json/wc/v3/products/{product_id}", auth=AUTH, timeout=30)
    if r.status_code == 404:
        return None
    r.raise_for_status()
    return r.json()


def write_total_sales(product_id, real_total_sales):
    requests.put(
        f"{WOO_URL}/wp-json/wc/v3/products/{product_id}",
        json={"total_sales": str(real_total_sales)},
        auth=AUTH, timeout=30,
    ).raise_for_status()


def run():
    fixed = 0
    totals = real_sales_by_product(LOOKBACK_DAYS)
    for product_id, real_total_sales in totals.items():
        product = get_product(product_id)
        if product is None:
            log.warning("Product %s has sales but no longer exists, skipping", product_id)
            continue
        action, reason = decide(product.get("total_sales"), real_total_sales)
        if action == "skip":
            continue
        log.info("Product %s: %s. %s", product_id, reason, "would fix" if DRY_RUN else "fixing")
        if not DRY_RUN:
            write_total_sales(product_id, real_total_sales)
        fixed += 1
    log.info("Done. %d product(s) %s.", fixed, "to fix" if DRY_RUN else "fixed")


if __name__ == "__main__":
    run()
recount-total-sales.js
/**
 * Recount WooCommerce's total_sales product meta from real, paid orders.
 *
 * The "Popularity" catalog sort orders products by the total_sales number
 * stored on each product. WooCommerce core only bumps that number through
 * its own order status hooks, so it drifts from reality whenever orders are
 * imported straight into the database, a status is changed by another
 * plugin or a direct SQL update, or a refund and cancellation never
 * decrements it back down. This walks paid orders in a lookback window,
 * sums real quantities per product (minus refunded quantities), compares
 * that to the stored total_sales, and corrects any product whose number is
 * wrong. Read only by default. Run on a schedule.
 */
import { pathToFileURL } from "node:url";

const WOO_URL = (process.env.WOO_STORE_URL || "https://example.com").replace(/\/$/, "");
const AUTH = "Basic " + Buffer.from(
  `${process.env.WOO_CONSUMER_KEY || "ck_dummy"}:${process.env.WOO_CONSUMER_SECRET || "cs_dummy"}`
).toString("base64");
const LOOKBACK_DAYS = Number(process.env.LOOKBACK_DAYS || 365);
const DRY_RUN = (process.env.DRY_RUN || "true").toLowerCase() === "true";

export function decide(storedTotalSales, realTotalSales) {
  const stored = Number.isFinite(Number(storedTotalSales)) ? Math.trunc(Number(storedTotalSales)) : 0;
  const real = Math.max(0, Math.trunc(realTotalSales || 0));
  if (stored === real) return ["skip", "total_sales already correct"];
  return ["fix", `stored ${stored}, real ${real}`];
}

export function netQuantity(lineItem) {
  const qty = Number(lineItem && lineItem.quantity);
  return Number.isFinite(qty) ? Math.trunc(qty) : 0;
}

async function woo(path, options = {}) {
  const res = await fetch(`${WOO_URL}/wp-json/wc/v3${path}`, {
    ...options,
    headers: { "Content-Type": "application/json", Authorization: AUTH, ...(options.headers || {}) },
  });
  if (res.status === 404) return null;
  if (!res.ok) throw new Error(`Woo ${path} returned ${res.status}`);
  return res.json();
}

async function* paidOrders(afterIso) {
  let page = 1;
  while (true) {
    const batch = await woo(`/orders?status=processing,completed&after=${afterIso}&per_page=100&page=${page}`);
    if (!batch || !batch.length) return;
    for (const order of batch) yield order;
    page++;
  }
}

async function refundLineItems(orderId) {
  const refunds = (await woo(`/orders/${orderId}/refunds`)) || [];
  const items = [];
  for (const refund of refunds) {
    for (const item of refund.line_items || []) items.push(item);
  }
  return items;
}

async function realSalesByProduct(lookbackDays) {
  const after = new Date(Date.now() - lookbackDays * 86400000).toISOString();
  const totals = new Map();
  for await (const order of paidOrders(after)) {
    for (const item of order.line_items || []) {
      if (!item.product_id) continue;
      totals.set(item.product_id, (totals.get(item.product_id) || 0) + netQuantity(item));
    }
    for (const item of await refundLineItems(order.id)) {
      if (!item.product_id) continue;
      totals.set(item.product_id, (totals.get(item.product_id) || 0) + netQuantity(item));
    }
  }
  return totals;
}

async function writeTotalSales(productId, realTotalSales) {
  await woo(`/products/${productId}`, {
    method: "PUT",
    body: JSON.stringify({ total_sales: String(realTotalSales) }),
  });
}

export async function run() {
  let fixed = 0;
  const totals = await realSalesByProduct(LOOKBACK_DAYS);
  for (const [productId, realTotalSales] of totals) {
    const product = await woo(`/products/${productId}`);
    if (!product) {
      console.warn(`Product ${productId} has sales but no longer exists, skipping`);
      continue;
    }
    const [action, reason] = decide(product.total_sales, realTotalSales);
    if (action === "skip") continue;
    console.log(`Product ${productId}: ${reason}. ${DRY_RUN ? "would fix" : "fixing"}`);
    if (!DRY_RUN) await writeTotalSales(productId, realTotalSales);
    fixed++;
  }
  console.log(`Done. ${fixed} product(s) ${DRY_RUN ? "to fix" : "fixed"}.`);
}

if (import.meta.url === pathToFileURL(process.argv[1]).href) {
  run().catch((err) => { console.error(err); process.exit(1); });
}

Add a test

The decision rule is the part most worth testing, because it decides which products get their sales data rewritten. Because we kept decide pure, the test needs no network and no store credentials. It just feeds in plain numbers and checks the action.

test_popularity_decide.py
from recount_total_sales import decide, net_quantity


def test_skip_when_totals_match():
    assert decide(12, 12)[0] == "skip"


def test_fix_when_stored_is_lower_than_real():
    assert decide(3, 40)[0] == "fix"


def test_fix_when_stored_is_higher_than_real():
    assert decide(40, 3)[0] == "fix"


def test_fix_when_stored_is_missing():
    assert decide(None, 5)[0] == "fix"


def test_skip_when_stored_is_missing_and_real_is_zero():
    assert decide(None, 0)[0] == "skip"


def test_negative_real_total_is_floored_at_zero():
    action, reason = decide(0, -4)
    assert action == "skip"
    assert reason == "total_sales already correct"


def test_net_quantity_reads_order_line_item():
    assert net_quantity({"quantity": 3}) == 3


def test_net_quantity_reads_negative_refund_line_item():
    assert net_quantity({"quantity": -2}) == -2


def test_net_quantity_defaults_to_zero_when_missing():
    assert net_quantity({}) == 0
recount-total-sales.test.js
import { test } from "node:test";
import assert from "node:assert/strict";
import { decide, netQuantity } from "./recount-total-sales.js";

test("skip when totals match", () => {
  assert.equal(decide(12, 12)[0], "skip");
});

test("fix when stored is lower than real", () => {
  assert.equal(decide(3, 40)[0], "fix");
});

test("fix when stored is higher than real", () => {
  assert.equal(decide(40, 3)[0], "fix");
});

test("fix when stored is missing", () => {
  assert.equal(decide(null, 5)[0], "fix");
});

test("skip when stored is missing and real is zero", () => {
  assert.equal(decide(null, 0)[0], "skip");
});

test("negative real total is floored at zero", () => {
  const [action, reason] = decide(0, -4);
  assert.equal(action, "skip");
  assert.equal(reason, "total_sales already correct");
});

test("netQuantity reads order line item", () => {
  assert.equal(netQuantity({ quantity: 3 }), 3);
});

test("netQuantity reads negative refund line item", () => {
  assert.equal(netQuantity({ quantity: -2 }), -2);
});

test("netQuantity defaults to zero when missing", () => {
  assert.equal(netQuantity({}), 0);
});

Case studies

Store migration

The migrated shop where nothing was ever popular

A merchant moved three years of order history into a fresh WooCommerce install using a database import. The orders looked right, the revenue reports matched, but every product showed zero on the Popularity sort, because the import never fired the order status hooks that increment total_sales.

Running the recount script in dry run listed every product with real sales and the exact count it should have. Turning off dry run wrote the correct numbers in one pass, and the Popularity sort matched the revenue report from that day forward.

Heavy returns season

The seasonal item that never dropped in rank

A holiday product had a high return rate once the season ended, but total_sales kept counting every original sale and never subtracted the refunds. Months later it still ranked near the top of Popularity, pushing out items that were actually still selling.

The recount job, run nightly, picked up the accumulated refunds and adjusted the count down to the real net units sold. The seasonal item settled to its true rank within a day and stayed accurate as new refunds came in.

What good looks like

After this runs on a schedule, an import, a manual status change, or a wave of refunds is no longer a permanent dent in the catalog's ranking. The worst case becomes a short delay of up to a day before the next run corrects it. Keep it running even after you clean up the immediate cause, since imports and refunds will always happen again.

FAQ

Why does the Popularity sort show the wrong products first?

Popularity sorts by the total_sales number stored on each product, and that number only updates through WooCommerce's own order status hooks. Imported orders, statuses changed by another plugin or direct SQL, and refunds that never decrement the count all leave it wrong. A script that recounts total_sales from real paid orders and refunds fixes it.

Is it safe to rewrite total_sales with a script?

Yes, when the script computes the real count from actual order line items and refunds and only changes products whose stored number disagrees with that count. Start in dry run mode to review the list before it writes.

How often should the recount run?

Once a day is enough for most stores, since total_sales drift builds up slowly from imports, manual edits, and refunds rather than from normal checkout traffic. A nightly cron job keeps the Popularity sort trustworthy without extra load.

Related field notes

Orders stuck on Pending after a successful Stripe payment Order marked paid with no matching Stripe charge

Citations

On the problem:

  1. WooCommerce core source: total_sales is updated inside order status change handling, tied to specific order events. github.com/woocommerce/woocommerce
  2. WooCommerce docs: how the store catalog Popularity ordering works and what it sorts by. woocommerce.com/document
  3. WooCommerce support forum: total_sales does not match real orders after an import or migration. wordpress.org/support/plugin/woocommerce

On the solution:

  1. WooCommerce REST API: list orders with status and date filters, and read refunds for an order. woocommerce.github.io/woocommerce-rest-api-docs
  2. WooCommerce REST API: update a product, including writing the total_sales field. woocommerce.github.io/woocommerce-rest-api-docs
  3. WooCommerce REST API: refund line items and their quantity and product fields. woocommerce.github.io/woocommerce-rest-api-docs

Stuck on a tricky one?

If you have a bug in WooCommerce, WooCommerce Subscriptions, or the WooCommerce Stripe gateway that you would rather hand off, this is the kind of work I do. Message me and we can work through it together.

Contact me on LinkedIn

Did this fix your catalog sort?

If this saved you from a Popularity sort that misled shoppers, you can buy me a coffee. It is the best way to keep these field notes free and growing.

Buy me a coffee on Ko-fi

Back to all WooCommerce and Stripe field notes