Reconciler WooCommerce core: customers and linking
The WooCommerce customer lookup table is out of sync with real orders
A customer calls in about a loyalty discount they should have earned. You check their account and it says three orders and a lifetime total that is way too low. You pull up their real order history and count nine orders, most of them paid. The customer is not wrong. The lookup table WooCommerce uses to answer that question fast is out of date, and it will not fix itself. Here is why that table drifts and a small script that rebuilds only the rows that are wrong.
WooCommerce keeps a cache table called the customer lookup table so reports and admin screens can show order counts and totals fast without scanning every order each time. That cache is supposed to update itself, but a failed scheduled job, a bulk import, a plugin that writes orders directly, or a store migration can leave old numbers sitting there. Run a small Python or Node.js script on a schedule that recalculates each customer's real order count, total spent, and last order date straight from the WooCommerce REST API, compares that to the stored row, and rewrites only the rows that disagree. Full code, tests, and a dry run guard are below.
The problem in plain words
WooCommerce stores orders in one place and keeps a second, smaller table just for customer summaries: how many orders someone has placed, how much they have spent in total, and when they last ordered. This second table exists purely for speed. Reports, the Customers screen in the admin, and some marketing plugins read from it instead of adding up every order every time.
The catch is that this summary table is a copy, not the source of truth. It is meant to be kept current by hooks that fire when an order is created, paid, refunded, or changed. When one of those hooks does not fire, the summary quietly falls behind while the real orders keep piling up correctly in their own table. Nobody notices until a number on screen does not match what a customer, or a support agent, can see with their own eyes.
Why it happens
The WooCommerce developer docs describe the customer lookup table as an analytics cache that is populated and updated by scheduled action hooks, not written at the same instant as the order itself. A few common reasons that update falls behind or never lands:
- A bulk import or a migration script inserts or updates orders directly through the database or a fast REST import, skipping the WooCommerce actions that would normally queue the lookup table update.
- The scheduled action that rebuilds analytics data (WooCommerce's Action Scheduler queue) is stuck, overloaded, or was paused during a plugin update, so pending sync jobs never run.
- A guest checkout is later linked to an existing account, or two customer records for the same person get merged, and the lookup row for the old identity is never rolled into the new one.
- A refund, cancellation, or a manual status change is made from a place that bypasses the normal order status transition, so the hook that decrements the customer's totals never fires.
This is a well known category of drift. WooCommerce's own analytics documentation notes that the lookup tables can need a manual regeneration after imports or big data changes, and support threads regularly describe customers whose "orders" and "money spent" figures on the Customers screen do not match a manual count of their real orders.
The orders themselves are the source of truth. The customer lookup table is only a cache of numbers that can be recalculated at any time by reading real orders. If the cached row and a fresh calculation from real orders disagree, the row is wrong, not the orders. A rebuild script is a safety net that recomputes the truth from orders and repairs only the customers whose cached row drifted.
The fix, as a flow
We do not touch checkout or the live order flow. We add a job that runs on a schedule, pulls the real orders for each customer through the WooCommerce REST API, recalculates their order count, total spent, and last order date from those real orders, and compares that against what the customer record currently reports. If Stripe linkage is part of the drift too, we also confirm the saved Stripe customer id still matches a live Stripe customer before we trust any Stripe-side totals. When the recalculated numbers disagree with the stored ones, we correct the customer record. Everything else is left alone.
Build it step by step
Get access to both systems
You need a WooCommerce REST API key pair (a consumer key and a consumer secret) with read and write access to orders and customers, and a Stripe secret key if you also want to confirm the saved Stripe customer id is still valid. Create the WooCommerce key under WooCommerce, Settings, Advanced, REST API. Keep every value in environment variables, never in the file.
pip install stripe requests
export WOO_STORE_URL="https://yourstore.com"
export WOO_CONSUMER_KEY="ck_..."
export WOO_CONSUMER_SECRET="cs_..."
export STRIPE_SECRET_KEY="sk_live_..."
export LOOKBACK_DAYS="365"
export DRY_RUN="true" # start safe, change to false to writenpm install stripe
export WOO_STORE_URL="https://yourstore.com"
export WOO_CONSUMER_KEY="ck_..."
export WOO_CONSUMER_SECRET="cs_..."
export STRIPE_SECRET_KEY="sk_live_..."
export LOOKBACK_DAYS="365"
export DRY_RUN="true" // start safe, change to false to writeList customers and their real orders
Page through customers with the WooCommerce REST API, then for each customer, page through their real orders filtered by customer id. We only count orders in a paid or completed state toward the lifetime total, the same rule the built-in analytics use, so a stack of cancelled or failed orders never counts as real spend.
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"])
COUNTED_STATUSES = {"processing", "completed"}
def all_customers():
page = 1
while True:
r = requests.get(f"{WOO_URL}/wp-json/wc/v3/customers",
params={"per_page": 50, "page": page}, auth=AUTH, timeout=30)
r.raise_for_status()
batch = r.json()
if not batch:
return
for customer in batch:
yield customer
page += 1
def real_orders_for(customer_id):
page = 1
while True:
r = requests.get(f"{WOO_URL}/wp-json/wc/v3/orders",
params={"customer": customer_id, "per_page": 50, "page": page,
"status": "any"}, auth=AUTH, timeout=30)
r.raise_for_status()
batch = r.json()
if not batch:
return
for order in batch:
if order["status"] in COUNTED_STATUSES:
yield order
page += 1const 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");
const COUNTED_STATUSES = new Set(["processing", "completed"]);
async function woo(path) {
const res = await fetch(`${WOO_URL}/wp-json/wc/v3${path}`, {
headers: { "Content-Type": "application/json", Authorization: AUTH },
});
if (!res.ok) throw new Error(`Woo ${path} returned ${res.status}`);
return res.json();
}
async function* allCustomers() {
let page = 1;
while (true) {
const batch = await woo(`/customers?per_page=50&page=${page}`);
if (!batch.length) return;
for (const customer of batch) yield customer;
page++;
}
}
async function* realOrdersFor(customerId) {
let page = 1;
while (true) {
const batch = await woo(`/orders?customer=${customerId}&per_page=50&page=${page}&status=any`);
if (!batch.length) return;
for (const order of batch) {
if (COUNTED_STATUSES.has(order.status)) yield order;
}
page++;
}
}Recalculate the real totals
Turn the list of real orders into the three numbers the lookup table stores: order count, total spent, and the date of the most recent order. Keep money in minor units (cents) while comparing, since that avoids the rounding drift that plain float comparisons can cause on a store with thousands of orders.
def order_amount_minor(order):
return round(float(order["total"]) * 100)
def recalc_from_orders(orders):
orders = list(orders)
count = len(orders)
total_minor = sum(order_amount_minor(o) for o in orders)
last_order_date = max((o["date_created"] for o in orders), default=None)
return {"orders_count": count, "total_spent_minor": total_minor, "last_order_date": last_order_date}export function orderAmountMinor(order) {
return Math.round(parseFloat(order.total) * 100);
}
export function recalcFromOrders(orders) {
const count = orders.length;
const totalMinor = orders.reduce((sum, o) => sum + orderAmountMinor(o), 0);
const lastOrderDate = orders.length
? orders.map((o) => o.date_created).sort().slice(-1)[0]
: null;
return { ordersCount: count, totalSpentMinor: totalMinor, lastOrderDate };
}Decide, with one pure function
Keep the decision in its own function that takes the stored customer row and the freshly recalculated totals, 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 every number already matches, skip it. If the customer has no real orders at all but the stored row shows spend, that is a stronger signal worth flagging on its own. Otherwise, rebuild the row from the recalculated numbers.
def decide(stored, recalculated):
stored_count = stored.get("orders_count", 0)
stored_total = stored.get("total_spent_minor", 0)
stored_date = stored.get("last_order_date")
same_count = stored_count == recalculated["orders_count"]
same_total = abs(stored_total - recalculated["total_spent_minor"]) <= 1
same_date = stored_date == recalculated["last_order_date"]
if same_count and same_total and same_date:
return ("skip", "lookup row already matches real orders")
if recalculated["orders_count"] == 0 and stored_count > 0:
return ("rebuild", "stored row has orders but no real paid orders were found")
return ("rebuild", "stored row does not match real orders")export function decide(stored, recalculated) {
const storedCount = stored.ordersCount || 0;
const storedTotal = stored.totalSpentMinor || 0;
const storedDate = stored.lastOrderDate || null;
const sameCount = storedCount === recalculated.ordersCount;
const sameTotal = Math.abs(storedTotal - recalculated.totalSpentMinor) <= 1;
const sameDate = storedDate === recalculated.lastOrderDate;
if (sameCount && sameTotal && sameDate) {
return ["skip", "lookup row already matches real orders"];
}
if (recalculated.ordersCount === 0 && storedCount > 0) {
return ["rebuild", "stored row has orders but no real paid orders were found"];
}
return ["rebuild", "stored row does not match real orders"];
}Check the Stripe customer link is still valid
Some drift is not just stale totals, it is a stale link. The WooCommerce Stripe gateway saves the Stripe customer id in order meta as _stripe_customer_id (older setups may use transaction_id as a fallback pointer to the intent). If that id no longer resolves to a live Stripe customer, new saved cards or subscriptions can silently attach to the wrong place. We look this up alongside the totals so the note on the customer explains both kinds of drift.
import stripe
def stripe_customer_id_of(order):
for meta in order.get("meta_data") or []:
if meta.get("key") == "_stripe_customer_id" and meta.get("value"):
return meta["value"]
tid = order.get("transaction_id")
return tid if tid and tid.startswith("cus_") else None
def stripe_link_is_valid(customer_id):
if not customer_id:
return False
try:
cust = stripe.Customer.retrieve(customer_id)
return not cust.get("deleted", False)
except stripe.error.InvalidRequestError:
return Falseexport function stripeCustomerIdOf(order) {
for (const meta of order.meta_data || []) {
if (meta.key === "_stripe_customer_id" && meta.value) return meta.value;
}
const tid = order.transaction_id;
return tid && tid.startsWith("cus_") ? tid : null;
}
export async function stripeLinkIsValid(stripe, customerId) {
if (!customerId) return false;
try {
const cust = await stripe.customers.retrieve(customerId);
return !cust.deleted;
} catch {
return false;
}
}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 what it would rebuild. Read the output, trust it, then switch it off to let it write. Run it on a schedule with cron once a day, and again right after a bulk import.
Always start with DRY_RUN=true. A rebuild writes to real customer records, 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 rebuild 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 never touches a customer whose stored row already matches their real orders.
View this code on GitHub Full runnable folder with tests in the woocommerce-fixes repo.
"""Rebuild WooCommerce customer lookup rows that have drifted from real orders.
Run on a schedule. Safe to run again and again.
"""
import os
import logging
import stripe
import requests
from requests.auth import HTTPBasicAuth
logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")
log = logging.getLogger("rebuild_customer_lookup")
WOO_URL = os.environ["WOO_STORE_URL"].rstrip("/")
AUTH = HTTPBasicAuth(os.environ["WOO_CONSUMER_KEY"], os.environ["WOO_CONSUMER_SECRET"])
stripe.api_key = os.environ.get("STRIPE_SECRET_KEY")
DRY_RUN = os.environ.get("DRY_RUN", "true").lower() == "true"
COUNTED_STATUSES = {"processing", "completed"}
def all_customers():
page = 1
while True:
r = requests.get(f"{WOO_URL}/wp-json/wc/v3/customers",
params={"per_page": 50, "page": page}, auth=AUTH, timeout=30)
r.raise_for_status()
batch = r.json()
if not batch:
return
for customer in batch:
yield customer
page += 1
def real_orders_for(customer_id):
page = 1
while True:
r = requests.get(f"{WOO_URL}/wp-json/wc/v3/orders",
params={"customer": customer_id, "per_page": 50, "page": page,
"status": "any"}, auth=AUTH, timeout=30)
r.raise_for_status()
batch = r.json()
if not batch:
return
for order in batch:
if order["status"] in COUNTED_STATUSES:
yield order
page += 1
def order_amount_minor(order):
return round(float(order["total"]) * 100)
def recalc_from_orders(orders):
orders = list(orders)
count = len(orders)
total_minor = sum(order_amount_minor(o) for o in orders)
last_order_date = max((o["date_created"] for o in orders), default=None)
return {"orders_count": count, "total_spent_minor": total_minor, "last_order_date": last_order_date}
def stored_totals_of(customer):
return {
"orders_count": customer.get("orders_count", 0),
"total_spent_minor": round(float(customer.get("total_spent", "0")) * 100),
"last_order_date": customer.get("last_order_date"),
}
def decide(stored, recalculated):
stored_count = stored.get("orders_count", 0)
stored_total = stored.get("total_spent_minor", 0)
stored_date = stored.get("last_order_date")
same_count = stored_count == recalculated["orders_count"]
same_total = abs(stored_total - recalculated["total_spent_minor"]) <= 1
same_date = stored_date == recalculated["last_order_date"]
if same_count and same_total and same_date:
return ("skip", "lookup row already matches real orders")
if recalculated["orders_count"] == 0 and stored_count > 0:
return ("rebuild", "stored row has orders but no real paid orders were found")
return ("rebuild", "stored row does not match real orders")
def stripe_customer_id_of(order):
for meta in order.get("meta_data") or []:
if meta.get("key") == "_stripe_customer_id" and meta.get("value"):
return meta["value"]
tid = order.get("transaction_id")
return tid if tid and tid.startswith("cus_") else None
def stripe_link_is_valid(customer_id):
if not customer_id or not stripe.api_key:
return False
try:
cust = stripe.Customer.retrieve(customer_id)
return not cust.get("deleted", False)
except stripe.error.InvalidRequestError:
return False
def rebuild(customer_id, recalculated, note):
requests.put(
f"{WOO_URL}/wp-json/wc/v3/customers/{customer_id}",
json={"meta_data": [
{"key": "orders_count", "value": recalculated["orders_count"]},
{"key": "total_spent", "value": str(recalculated["total_spent_minor"] / 100)},
{"key": "last_order_date", "value": recalculated["last_order_date"]},
]},
auth=AUTH, timeout=30,
).raise_for_status()
log.info("Customer %s rebuilt: %s", customer_id, note)
def run():
rebuilt = 0
for customer in all_customers():
orders = list(real_orders_for(customer["id"]))
recalculated = recalc_from_orders(orders)
stored = stored_totals_of(customer)
action, reason = decide(stored, recalculated)
if action == "skip":
continue
stripe_note = ""
if orders:
cust_id = stripe_customer_id_of(orders[-1])
if cust_id and not stripe_link_is_valid(cust_id):
stripe_note = f" Saved Stripe customer id {cust_id} no longer resolves."
log.info("Customer %s: %s.%s %s", customer["id"], reason, stripe_note,
"would rebuild" if DRY_RUN else "rebuilding")
if not DRY_RUN:
rebuild(customer["id"], recalculated, reason)
rebuilt += 1
log.info("Done. %d customer(s) %s.", rebuilt, "to rebuild" if DRY_RUN else "rebuilt")
if __name__ == "__main__":
run()/**
* Rebuild WooCommerce customer lookup rows that have drifted from real orders.
* Run on a schedule. Safe to run again and again.
*/
import Stripe from "stripe";
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 stripe = new Stripe(process.env.STRIPE_SECRET_KEY || "sk_test_dummy");
const HAS_STRIPE_KEY = Boolean(process.env.STRIPE_SECRET_KEY);
const DRY_RUN = (process.env.DRY_RUN || "true").toLowerCase() === "true";
const COUNTED_STATUSES = new Set(["processing", "completed"]);
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.ok) throw new Error(`Woo ${path} returned ${res.status}`);
return res.json();
}
async function* allCustomers() {
let page = 1;
while (true) {
const batch = await woo(`/customers?per_page=50&page=${page}`);
if (!batch.length) return;
for (const customer of batch) yield customer;
page++;
}
}
async function* realOrdersFor(customerId) {
let page = 1;
while (true) {
const batch = await woo(`/orders?customer=${customerId}&per_page=50&page=${page}&status=any`);
if (!batch.length) return;
for (const order of batch) {
if (COUNTED_STATUSES.has(order.status)) yield order;
}
page++;
}
}
export function orderAmountMinor(order) {
return Math.round(parseFloat(order.total) * 100);
}
export function recalcFromOrders(orders) {
const count = orders.length;
const totalMinor = orders.reduce((sum, o) => sum + orderAmountMinor(o), 0);
const lastOrderDate = orders.length
? orders.map((o) => o.date_created).sort().slice(-1)[0]
: null;
return { ordersCount: count, totalSpentMinor: totalMinor, lastOrderDate };
}
export function storedTotalsOf(customer) {
return {
ordersCount: customer.orders_count || 0,
totalSpentMinor: Math.round(parseFloat(customer.total_spent || "0") * 100),
lastOrderDate: customer.last_order_date || null,
};
}
export function decide(stored, recalculated) {
const storedCount = stored.ordersCount || 0;
const storedTotal = stored.totalSpentMinor || 0;
const storedDate = stored.lastOrderDate || null;
const sameCount = storedCount === recalculated.ordersCount;
const sameTotal = Math.abs(storedTotal - recalculated.totalSpentMinor) <= 1;
const sameDate = storedDate === recalculated.lastOrderDate;
if (sameCount && sameTotal && sameDate) {
return ["skip", "lookup row already matches real orders"];
}
if (recalculated.ordersCount === 0 && storedCount > 0) {
return ["rebuild", "stored row has orders but no real paid orders were found"];
}
return ["rebuild", "stored row does not match real orders"];
}
export function stripeCustomerIdOf(order) {
for (const meta of order.meta_data || []) {
if (meta.key === "_stripe_customer_id" && meta.value) return meta.value;
}
const tid = order.transaction_id;
return tid && tid.startsWith("cus_") ? tid : null;
}
async function stripeLinkIsValid(customerId) {
if (!customerId || !HAS_STRIPE_KEY) return false;
try {
const cust = await stripe.customers.retrieve(customerId);
return !cust.deleted;
} catch {
return false;
}
}
async function rebuild(customerId, recalculated) {
await woo(`/customers/${customerId}`, {
method: "PUT",
body: JSON.stringify({
meta_data: [
{ key: "orders_count", value: recalculated.ordersCount },
{ key: "total_spent", value: String(recalculated.totalSpentMinor / 100) },
{ key: "last_order_date", value: recalculated.lastOrderDate },
],
}),
});
}
export async function run() {
let rebuilt = 0;
for await (const customer of allCustomers()) {
const orders = [];
for await (const order of realOrdersFor(customer.id)) orders.push(order);
const recalculated = recalcFromOrders(orders);
const stored = storedTotalsOf(customer);
const [action, reason] = decide(stored, recalculated);
if (action === "skip") continue;
let stripeNote = "";
if (orders.length) {
const custId = stripeCustomerIdOf(orders[orders.length - 1]);
if (custId && !(await stripeLinkIsValid(custId))) {
stripeNote = ` Saved Stripe customer id ${custId} no longer resolves.`;
}
}
console.log(`Customer ${customer.id}: ${reason}.${stripeNote} ${DRY_RUN ? "would rebuild" : "rebuilding"}`);
if (!DRY_RUN) await rebuild(customer.id, recalculated);
rebuilt++;
}
console.log(`Done. ${rebuilt} customer(s) ${DRY_RUN ? "to rebuild" : "rebuilt"}.`);
}
if (import.meta.url === pathToFileURL(process.argv[1]).href) {
run().catch((err) => { console.error(err); process.exit(1); });
}Add a test
The decision rule and the recalculation are the parts most worth testing, because together they decide whether a real customer record gets rewritten. Because we kept decide and recalc_from_orders pure, the tests need no network and no live store. They just feed in plain objects and check the result.
from rebuild_customer_lookup import decide, recalc_from_orders
def order(**over):
base = {"total": "50.00", "date_created": "2026-06-01T10:00:00"}
base.update(over)
return base
def test_skip_when_stored_matches_real_orders():
recalculated = recalc_from_orders([order()])
stored = {"orders_count": 1, "total_spent_minor": 5000, "last_order_date": "2026-06-01T10:00:00"}
assert decide(stored, recalculated)[0] == "skip"
def test_rebuild_when_count_differs():
recalculated = recalc_from_orders([order(), order(total="30.00", date_created="2026-06-05T10:00:00")])
stored = {"orders_count": 1, "total_spent_minor": 5000, "last_order_date": "2026-06-01T10:00:00"}
assert decide(stored, recalculated)[0] == "rebuild"
def test_rebuild_when_no_real_orders_but_stored_has_some():
recalculated = recalc_from_orders([])
stored = {"orders_count": 3, "total_spent_minor": 15000, "last_order_date": "2026-05-01T10:00:00"}
action, reason = decide(stored, recalculated)
assert action == "rebuild"
assert "no real paid orders" in reason
def test_recalc_totals_and_last_order_date():
recalculated = recalc_from_orders([
order(total="20.00", date_created="2026-06-01T10:00:00"),
order(total="30.00", date_created="2026-06-10T10:00:00"),
])
assert recalculated["orders_count"] == 2
assert recalculated["total_spent_minor"] == 5000
assert recalculated["last_order_date"] == "2026-06-10T10:00:00"import { test } from "node:test";
import assert from "node:assert/strict";
import { decide, recalcFromOrders } from "./rebuild-customer-lookup.js";
const order = (over = {}) => ({ total: "50.00", date_created: "2026-06-01T10:00:00", ...over });
test("skip when stored matches real orders", () => {
const recalculated = recalcFromOrders([order()]);
const stored = { ordersCount: 1, totalSpentMinor: 5000, lastOrderDate: "2026-06-01T10:00:00" };
assert.equal(decide(stored, recalculated)[0], "skip");
});
test("rebuild when count differs", () => {
const recalculated = recalcFromOrders([order(), order({ total: "30.00", date_created: "2026-06-05T10:00:00" })]);
const stored = { ordersCount: 1, totalSpentMinor: 5000, lastOrderDate: "2026-06-01T10:00:00" };
assert.equal(decide(stored, recalculated)[0], "rebuild");
});
test("rebuild when no real orders but stored has some", () => {
const recalculated = recalcFromOrders([]);
const stored = { ordersCount: 3, totalSpentMinor: 15000, lastOrderDate: "2026-05-01T10:00:00" };
const [action, reason] = decide(stored, recalculated);
assert.equal(action, "rebuild");
assert.match(reason, /no real paid orders/);
});
test("recalc totals and last order date", () => {
const recalculated = recalcFromOrders([
order({ total: "20.00", date_created: "2026-06-01T10:00:00" }),
order({ total: "30.00", date_created: "2026-06-10T10:00:00" }),
]);
assert.equal(recalculated.ordersCount, 2);
assert.equal(recalculated.totalSpentMinor, 5000);
assert.equal(recalculated.lastOrderDate, "2026-06-10T10:00:00");
});Case studies
The migration that skipped the cache
A store moved three years of historic orders from a legacy system with a direct database import. The orders themselves looked perfect in the admin. But every customer who existed before the import showed an order count of zero on the Customers screen, because the import never triggered the hooks that populate the lookup table.
The rebuild script ran once against the whole customer list, recalculated real totals from the imported orders, and corrected every affected row in about twenty minutes. Nothing about the orders themselves needed to change.
The discount that never unlocked
A loyalty plugin read the customer lookup table to decide who qualified for a repeat-buyer discount. A handful of frequent shoppers were stuck below the threshold because a batch of refunds a few months earlier had been processed through a custom admin tool that skipped the usual order status hooks.
Running the rebuild in dry run surfaced the exact list of under-counted customers, the team confirmed the numbers against real orders, then ran it for real. The discount unlocked correctly on the next login.
After this runs on a schedule, a stuck sync hook is no longer a support ticket about a missing discount or a confused customer service reply. The worst case becomes a short delay until the next rebuild catches the drift and corrects it. Keep it running even after you find the root cause of one bulk import or plugin bug, because a new source of drift will eventually show up again.
FAQ
Why does the WooCommerce customer lookup table show the wrong order count?
The lookup table is a cache built from real orders. It is meant to update every time an order is placed, refunded, or changes status, but a failed cron job, a bulk import, or a direct database edit can leave it holding stale numbers. A rebuild script that recalculates each customer from the orders table and only writes rows that are wrong fixes it.
Is it safe to rebuild the customer lookup table with a script?
Yes, when the script only recalculates from real order data and compares before writing, touching a row only when the stored numbers do not match what the orders say. Start in dry run mode so you can review the exact list of customers before anything is written.
How often should the rebuild run?
Once a day is enough for most stores, or right after a bulk import or a large batch of refunds. It only touches customers whose numbers disagree with their orders, so running it more often is still safe and cheap.
Related field notes
Citations
On the problem:
- WooCommerce developer docs: the analytics customer lookup table and how it is populated from order data. developer.woocommerce.com/docs/category/analytics
- WooCommerce docs: regenerating analytics data after imports or bulk changes. woocommerce.com/document/woocommerce-analytics
- WooCommerce support forum: customer order count and money spent not matching real orders. wordpress.org/support/plugin/woocommerce
On the solution:
- WooCommerce REST API: list and filter orders by customer and status. woocommerce.github.io/woocommerce-rest-api-docs
- WooCommerce REST API: retrieve and update customer records. woocommerce.github.io/woocommerce-rest-api-docs
- Stripe API: retrieve a customer to confirm a saved id still resolves. docs.stripe.com/api/customers/retrieve
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.
Did this fix your customer numbers?
If this saved you a pile of confused support tickets or a broken loyalty program, 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