Reconciler WooCommerce core: tax, totals, and analytics
Customer lifetime value drifts
A regular buyer emails to ask why the store's records show them spending far less than they know they have. You open the customer in WooCommerce and the lifetime value looks stale, too low, or too high, and it does not match the orders you can see with your own eyes. Lifetime value is a cached number, not a live one, and caches drift. Here is why it happens and a small script that recomputes it correctly from real paid orders.
WooCommerce stores lifetime value as a cached number on the customer, not as a live sum of orders, so it can fall behind when a refund happens outside WooCommerce, an order is edited after the cache was set, or a sync job is skipped. Run a small Python or Node.js job on a schedule that reads every paid order for a customer, nets out refunds using the WooCommerce REST API, checks the refund amount against Stripe when a PaymentIntent id is available, and writes the correct total back. Full code, tests, and a dry run guard are below.
The problem in plain words
Every time you look at a customer in WooCommerce, or open the Analytics, Customers report, you see a number called lifetime value or total spent. It looks like it was just calculated. It was not. WooCommerce keeps that number in the customer's stored meta and in a separate Analytics lookup table, and both are only updated when specific events fire, like an order changing to Completed or a refund being processed through WooCommerce itself.
The moment money moves without going through that exact path, the cached number stops matching reality. A support agent refunds a customer directly in the Stripe dashboard because it was faster. A developer edits an order's total after the fact to fix a shipping mistake. A plugin conflict quietly breaks the Analytics sync job. None of these show up as an error. They just leave a number sitting on the customer that used to be correct and no longer is.
Why it happens
WooCommerce Analytics rebuilds customer and order stats through lookup tables that are meant to stay in sync with the orders table, but the sync depends on hooks firing at the right time. A few common reasons the cached lifetime value ends up wrong:
- A refund is processed straight from the Stripe dashboard or another payment tool instead of through the WooCommerce order screen, so WooCommerce never runs its refund hook and never adjusts the cached total.
- An order's total is edited directly, through a script, an import, or a database change, after the customer's lifetime value was already cached from the old total.
- The WooCommerce Analytics lookup tables (
wp_wc_customer_lookupandwp_wc_order_stats) fall behind because a scheduled Action Scheduler job failed silently or was paused during a migration. - An order moves between paid and unpaid statuses more than once, for example Processing to Refunded back to Processing, and each transition assumes the previous cached value was correct.
This is a known soft spot. The WooCommerce Analytics report has documented cases where regenerating stats is the only fix after bulk order edits or an import, precisely because the cached numbers do not self heal. See the citations at the end for the exact references.
Lifetime value should always be a sum, never a stored fact. The orders themselves, plus their real refund totals, are the source of truth. A recompute job is a safety net that runs on a schedule, adds up what actually happened to each customer, and corrects the cached number whenever it disagrees.
The fix, as a flow
We do not touch checkout or the refund flow. We add a job that runs on a schedule, walks every customer, pulls their paid orders through the WooCommerce REST API, and works out what they actually spent after refunds. When a PaymentIntent id is saved on an order, we also ask Stripe directly for its refunded amount, since Stripe is the one place a refund cannot be faked or forgotten. If the recomputed total disagrees with the cached lifetime value by more than a cent, we write the correct number back.
Build it step by step
Get access to both systems
You need a Stripe secret key and a WooCommerce REST API key pair (a consumer key and a consumer secret) with read and write access to customers and orders. 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 STRIPE_SECRET_KEY="sk_live_..."
export WOO_STORE_URL="https://yourstore.com"
export WOO_CONSUMER_KEY="ck_..."
export WOO_CONSUMER_SECRET="cs_..."
export DRIFT_TOLERANCE_CENTS="1"
export DRY_RUN="true" # start safe, change to false to writenpm install stripe
export STRIPE_SECRET_KEY="sk_live_..."
export WOO_STORE_URL="https://yourstore.com"
export WOO_CONSUMER_KEY="ck_..."
export WOO_CONSUMER_SECRET="cs_..."
export DRIFT_TOLERANCE_CENTS="1"
export DRY_RUN="true" // start safe, change to false to writeList every customer, then their paid orders
Page through customers with the WooCommerce REST API, then page through each customer's orders. We only care about orders in a paid state, Processing or Completed, since those are the ones that count toward lifetime value.
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 list_customers():
page = 1
while True:
r = requests.get(f"{WOO_URL}/wp-json/wc/v3/customers",
params={"per_page": 50, "page": page, "orderby": "id"},
auth=AUTH, timeout=30)
r.raise_for_status()
batch = r.json()
if not batch:
return
yield from batch
page += 1
def list_orders_for_customer(customer_id):
page = 1
while True:
r = requests.get(f"{WOO_URL}/wp-json/wc/v3/orders",
params={"customer": customer_id, "per_page": 100, "page": page},
auth=AUTH, timeout=30)
r.raise_for_status()
batch = r.json()
if not batch:
return
yield from batch
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");
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* listCustomers() {
let page = 1;
while (true) {
const batch = await woo(`/customers?per_page=50&page=${page}&orderby=id`);
if (!batch.length) return;
for (const customer of batch) yield customer;
page++;
}
}
async function* listOrdersForCustomer(customerId) {
let page = 1;
while (true) {
const batch = await woo(`/orders?customer=${customerId}&per_page=100&page=${page}`);
if (!batch.length) return;
for (const order of batch) yield order;
page++;
}
}Read the real PaymentIntent id off each order
The WooCommerce Stripe plugin saves the PaymentIntent id as order meta under _stripe_intent_id, and older orders sometimes only have it saved as the transaction_id field with a pi_ prefix. Checking both means we can look up the charge behind almost any paid order.
def intent_id_of(order):
"""The saved Stripe PaymentIntent id, from meta _stripe_intent_id or transaction_id."""
for meta in order.get("meta_data") or []:
if meta.get("key") == "_stripe_intent_id" and meta.get("value"):
return meta["value"]
tid = order.get("transaction_id")
return tid if tid and tid.startswith("pi_") else Noneexport function intentIdOf(order) {
for (const meta of order.meta_data || []) {
if (meta.key === "_stripe_intent_id" && meta.value) return meta.value;
}
const tid = order.transaction_id;
return tid && tid.startsWith("pi_") ? tid : null;
}Sum orders and refunds with two pure functions
Keep the arithmetic in its own pure function that takes plain order data and an optional map of Stripe refund amounts, and returns a total. Working in minor units, cents, avoids floating point rounding errors piling up across dozens of orders. A second pure function compares the recomputed total to the cached lifetime value and decides whether it drifted.
PAID_STATUSES = {"processing", "completed"}
def order_total_minor(order):
return round(float(order["total"]) * 100)
def order_refunded_minor(order):
total_refunded = order.get("refunds")
if total_refunded:
return sum(round(abs(float(r.get("total", 0))) * 100) for r in total_refunded)
return round(abs(float(order.get("total_refunded", "0") or 0)) * 100)
def compute_customer_clv(orders, stripe_refunds_by_order_id=None):
stripe_refunds_by_order_id = stripe_refunds_by_order_id or {}
total = 0
counted = 0
notes = []
for order in orders:
if order.get("status") not in PAID_STATUSES:
continue
woo_refunded = order_refunded_minor(order)
stripe_refunded = stripe_refunds_by_order_id.get(order["id"])
refunded = woo_refunded
if stripe_refunded is not None and stripe_refunded > woo_refunded:
notes.append(f"order {order['id']}: Stripe shows a larger refund than Woo's cache")
refunded = stripe_refunded
total += max(0, order_total_minor(order) - refunded)
counted += 1
return total, counted, notes
def decide(customer, computed_total_minor, tolerance_cents=1):
cached_minor = round(float(customer.get("total_spent") or 0) * 100)
if computed_total_minor == 0 and cached_minor == 0:
return ("no_orders", "no paid orders and no cached value")
if abs(cached_minor - computed_total_minor) <= tolerance_cents:
return ("ok", "cached lifetime value matches recomputed orders")
direction = "higher" if cached_minor > computed_total_minor else "lower"
return ("drift", f"cached lifetime value is {direction} than the recomputed total")const PAID_STATUSES = new Set(["processing", "completed"]);
export function orderTotalMinor(order) {
return Math.round(parseFloat(order.total) * 100);
}
export function orderRefundedMinor(order) {
if (order.refunds && order.refunds.length) {
return order.refunds.reduce((sum, r) => sum + Math.round(Math.abs(parseFloat(r.total || 0)) * 100), 0);
}
return Math.round(Math.abs(parseFloat(order.total_refunded || "0") || 0) * 100);
}
export function computeCustomerClv(orders, stripeRefundsByOrderId = {}) {
let total = 0;
let counted = 0;
const notes = [];
for (const order of orders) {
if (!PAID_STATUSES.has(order.status)) continue;
const wooRefunded = orderRefundedMinor(order);
const stripeRefunded = stripeRefundsByOrderId[order.id];
let refunded = wooRefunded;
if (stripeRefunded !== undefined && stripeRefunded > wooRefunded) {
notes.push(`order ${order.id}: Stripe shows a larger refund than Woo's cache`);
refunded = stripeRefunded;
}
total += Math.max(0, orderTotalMinor(order) - refunded);
counted += 1;
}
return { totalMinor: total, counted, notes };
}
export function decide(customer, computedTotalMinor, toleranceCents = 1) {
const cachedMinor = Math.round(parseFloat(customer.total_spent || 0) * 100);
if (computedTotalMinor === 0 && cachedMinor === 0) return ["no_orders", "no paid orders and no cached value"];
if (Math.abs(cachedMinor - computedTotalMinor) <= toleranceCents) {
return ["ok", "cached lifetime value matches recomputed orders"];
}
const direction = cachedMinor > computedTotalMinor ? "higher" : "lower";
return ["drift", `cached lifetime value is ${direction} than the recomputed total`];
}Ask Stripe for the real refund amount
When an order has a PaymentIntent id, retrieve it from Stripe with the charge expanded, and read amount_refunded off that charge. This is the number that cannot lie, it comes straight from the processor that moved the money. We only use it when it is larger than what WooCommerce has cached, since that is the exact shape of the bug, a refund WooCommerce never learned about.
def get_stripe_refunded_minor(order):
intent_id = intent_id_of(order)
if not intent_id:
return None
try:
intent = stripe.PaymentIntent.retrieve(intent_id, expand=["latest_charge"])
except stripe.error.InvalidRequestError:
return None
charge = intent.get("latest_charge")
if not charge or not isinstance(charge, dict):
return None
return charge.get("amount_refunded")async function getStripeRefundedMinor(order) {
const intentId = intentIdOf(order);
if (!intentId) return undefined;
let intent;
try {
intent = await stripe.paymentIntents.retrieve(intentId, { expand: ["latest_charge"] });
} catch {
return undefined;
}
const charge = intent.latest_charge;
if (!charge || typeof charge !== "object") return undefined;
return charge.amount_refunded;
}Write the corrected total, guarded by dry run
When the action is drift, write the recomputed total back onto the customer as meta, expressed in dollars to two decimal places. On the first few runs, leave DRY_RUN on so the script only reports what it would change. Read the output, trust it, then switch it off. Run it once a day with cron, or right after a bulk refund.
Always start with DRY_RUN=true. Reading every customer's order history takes API calls and time, and writing to a customer touches a record other reports depend on, so you want to see its plan before it acts. Once the report looks right, turn it off.
The full code
Here is the complete recompute job 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 every run recomputes from scratch instead of trusting the last run's output.
View this code on GitHub Full runnable folder with tests in the woocommerce-fixes repo.
"""Recompute WooCommerce customer lifetime value from real paid orders.
Walks each customer's paid orders, nets out refunds using the WooCommerce
REST API, double checks the refund total against Stripe when a PaymentIntent
id is on the order, and writes the correct lifetime value back onto the
customer as meta. Read only by default. Run on a schedule.
"""
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("recompute_clv")
stripe.api_key = os.environ["STRIPE_SECRET_KEY"]
WOO_URL = os.environ["WOO_STORE_URL"].rstrip("/")
AUTH = HTTPBasicAuth(os.environ["WOO_CONSUMER_KEY"], os.environ["WOO_CONSUMER_SECRET"])
DRIFT_TOLERANCE_CENTS = int(os.environ.get("DRIFT_TOLERANCE_CENTS", "1"))
DRY_RUN = os.environ.get("DRY_RUN", "true").lower() == "true"
PAID_STATUSES = {"processing", "completed"}
CLV_META_KEY = "_clv_recomputed_cents"
def intent_id_of(order):
for meta in order.get("meta_data") or []:
if meta.get("key") == "_stripe_intent_id" and meta.get("value"):
return meta["value"]
tid = order.get("transaction_id")
return tid if tid and tid.startswith("pi_") else None
def order_total_minor(order):
return round(float(order["total"]) * 100)
def order_refunded_minor(order):
total_refunded = order.get("refunds")
if total_refunded:
return sum(round(abs(float(r.get("total", 0))) * 100) for r in total_refunded)
return round(abs(float(order.get("total_refunded", "0") or 0)) * 100)
def compute_customer_clv(orders, stripe_refunds_by_order_id=None):
stripe_refunds_by_order_id = stripe_refunds_by_order_id or {}
total = 0
counted = 0
notes = []
for order in orders:
if order.get("status") not in PAID_STATUSES:
continue
woo_refunded = order_refunded_minor(order)
stripe_refunded = stripe_refunds_by_order_id.get(order["id"])
refunded = woo_refunded
if stripe_refunded is not None and stripe_refunded > woo_refunded:
notes.append(
f"order {order['id']}: Stripe shows {stripe_refunded} minor units refunded, "
f"WooCommerce cache shows {woo_refunded}; using Stripe's figure"
)
refunded = stripe_refunded
total += max(0, order_total_minor(order) - refunded)
counted += 1
return total, counted, notes
def decide(customer, computed_total_minor, tolerance_cents=DRIFT_TOLERANCE_CENTS):
cached_minor = round(float(customer.get("total_spent") or 0) * 100)
if computed_total_minor == 0 and cached_minor == 0:
return ("no_orders", "no paid orders and no cached value")
if abs(cached_minor - computed_total_minor) <= tolerance_cents:
return ("ok", "cached lifetime value matches recomputed orders")
direction = "higher" if cached_minor > computed_total_minor else "lower"
return (
"drift",
f"cached lifetime value ({cached_minor}) is {direction} than the recomputed "
f"total from paid orders ({computed_total_minor})",
)
def list_customers():
page = 1
while True:
r = requests.get(
f"{WOO_URL}/wp-json/wc/v3/customers",
params={"per_page": 50, "page": page, "orderby": "id"},
auth=AUTH, timeout=30,
)
r.raise_for_status()
batch = r.json()
if not batch:
return
for customer in batch:
yield customer
page += 1
def list_orders_for_customer(customer_id):
page = 1
while True:
r = requests.get(
f"{WOO_URL}/wp-json/wc/v3/orders",
params={"customer": customer_id, "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 get_stripe_refunded_minor(order):
intent_id = intent_id_of(order)
if not intent_id:
return None
try:
intent = stripe.PaymentIntent.retrieve(intent_id, expand=["latest_charge"])
except stripe.error.InvalidRequestError:
return None
charge = intent.get("latest_charge")
if not charge or not isinstance(charge, dict):
return None
return charge.get("amount_refunded")
def write_lifetime_value(customer_id, total_minor):
dollars = f"{total_minor / 100:.2f}"
requests.put(
f"{WOO_URL}/wp-json/wc/v3/customers/{customer_id}",
json={"meta_data": [{"key": CLV_META_KEY, "value": dollars}]},
auth=AUTH, timeout=30,
).raise_for_status()
def run():
drifted = 0
checked = 0
for customer in list_customers():
orders = list(list_orders_for_customer(customer["id"]))
stripe_refunds = {}
for order in orders:
if order.get("status") in PAID_STATUSES:
refunded = get_stripe_refunded_minor(order)
if refunded is not None:
stripe_refunds[order["id"]] = refunded
total_minor, counted, notes = compute_customer_clv(orders, stripe_refunds)
checked += 1
action, reason = decide(customer, total_minor)
for note in notes:
log.info("Customer %s: %s", customer["id"], note)
if action != "drift":
continue
log.warning(
"Customer %s (%s paid orders): %s. %s",
customer["id"], counted, reason, "would write" if DRY_RUN else "writing",
)
if not DRY_RUN:
write_lifetime_value(customer["id"], total_minor)
drifted += 1
log.info("Done. Checked %d customer(s). %d %s.", checked, drifted, "to fix" if DRY_RUN else "fixed")
if __name__ == "__main__":
run()/**
* Recompute WooCommerce customer lifetime value from real paid orders.
*
* Walks each customer's paid orders, nets out refunds using the WooCommerce
* REST API, double checks the refund total against Stripe when a
* PaymentIntent id is on the order, and writes the correct lifetime value
* back onto the customer as meta. Read only by default. Run on a schedule.
*/
import Stripe from "stripe";
import { pathToFileURL } from "node:url";
const stripe = new Stripe(process.env.STRIPE_SECRET_KEY || "sk_test_dummy");
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 DRIFT_TOLERANCE_CENTS = Number(process.env.DRIFT_TOLERANCE_CENTS || 1);
const DRY_RUN = (process.env.DRY_RUN || "true").toLowerCase() === "true";
const PAID_STATUSES = new Set(["processing", "completed"]);
const CLV_META_KEY = "_clv_recomputed_cents";
export function intentIdOf(order) {
for (const meta of order.meta_data || []) {
if (meta.key === "_stripe_intent_id" && meta.value) return meta.value;
}
const tid = order.transaction_id;
return tid && tid.startsWith("pi_") ? tid : null;
}
export function orderTotalMinor(order) {
return Math.round(parseFloat(order.total) * 100);
}
export function orderRefundedMinor(order) {
if (order.refunds && order.refunds.length) {
return order.refunds.reduce((sum, r) => sum + Math.round(Math.abs(parseFloat(r.total || 0)) * 100), 0);
}
return Math.round(Math.abs(parseFloat(order.total_refunded || "0") || 0) * 100);
}
export function computeCustomerClv(orders, stripeRefundsByOrderId = {}) {
let total = 0;
let counted = 0;
const notes = [];
for (const order of orders) {
if (!PAID_STATUSES.has(order.status)) continue;
const wooRefunded = orderRefundedMinor(order);
const stripeRefunded = stripeRefundsByOrderId[order.id];
let refunded = wooRefunded;
if (stripeRefunded !== undefined && stripeRefunded > wooRefunded) {
notes.push(
`order ${order.id}: Stripe shows ${stripeRefunded} minor units refunded, ` +
`WooCommerce cache shows ${wooRefunded}; using Stripe's figure`
);
refunded = stripeRefunded;
}
const net = Math.max(0, orderTotalMinor(order) - refunded);
total += net;
counted += 1;
}
return { totalMinor: total, counted, notes };
}
export function decide(customer, computedTotalMinor, toleranceCents = DRIFT_TOLERANCE_CENTS) {
const cachedMinor = Math.round(parseFloat(customer.total_spent || 0) * 100);
if (computedTotalMinor === 0 && cachedMinor === 0) {
return ["no_orders", "no paid orders and no cached value"];
}
if (Math.abs(cachedMinor - computedTotalMinor) <= toleranceCents) {
return ["ok", "cached lifetime value matches recomputed orders"];
}
const direction = cachedMinor > computedTotalMinor ? "higher" : "lower";
return [
"drift",
`cached lifetime value (${cachedMinor}) is ${direction} than the recomputed ` +
`total from paid orders (${computedTotalMinor})`,
];
}
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* listCustomers() {
let page = 1;
while (true) {
const batch = await woo(`/customers?per_page=50&page=${page}&orderby=id`);
if (!batch.length) return;
for (const customer of batch) yield customer;
page++;
}
}
async function* listOrdersForCustomer(customerId) {
let page = 1;
while (true) {
const batch = await woo(`/orders?customer=${customerId}&per_page=100&page=${page}`);
if (!batch.length) return;
for (const order of batch) yield order;
page++;
}
}
async function getStripeRefundedMinor(order) {
const intentId = intentIdOf(order);
if (!intentId) return undefined;
let intent;
try {
intent = await stripe.paymentIntents.retrieve(intentId, { expand: ["latest_charge"] });
} catch {
return undefined;
}
const charge = intent.latest_charge;
if (!charge || typeof charge !== "object") return undefined;
return charge.amount_refunded;
}
async function writeLifetimeValue(customerId, totalMinor) {
const dollars = (totalMinor / 100).toFixed(2);
await woo(`/customers/${customerId}`, {
method: "PUT",
body: JSON.stringify({ meta_data: [{ key: CLV_META_KEY, value: dollars }] }),
});
}
export async function run() {
let drifted = 0;
let checked = 0;
for await (const customer of listCustomers()) {
const orders = [];
for await (const order of listOrdersForCustomer(customer.id)) orders.push(order);
const stripeRefunds = {};
for (const order of orders) {
if (PAID_STATUSES.has(order.status)) {
const refunded = await getStripeRefundedMinor(order);
if (refunded !== undefined) stripeRefunds[order.id] = refunded;
}
}
const { totalMinor, counted, notes } = computeCustomerClv(orders, stripeRefunds);
checked++;
const [action, reason] = decide(customer, totalMinor);
for (const note of notes) console.log(`Customer ${customer.id}: ${note}`);
if (action !== "drift") continue;
console.warn(
`Customer ${customer.id} (${counted} paid orders): ${reason}. ${DRY_RUN ? "would write" : "writing"}`
);
if (!DRY_RUN) await writeLifetimeValue(customer.id, totalMinor);
drifted++;
}
console.log(`Done. Checked ${checked} customer(s). ${drifted} ${DRY_RUN ? "to fix" : "fixed"}.`);
}
if (import.meta.url === pathToFileURL(process.argv[1]).href) {
run().catch((e) => { console.error(e); process.exit(1); });
}Add a test
The two pure functions, the summing function and the decision function, are the part most worth testing, since together they decide what gets written over a customer's stored history. Because neither one does any network I/O, the tests need no live store and no Stripe account. They just feed in plain objects and check the numbers and the action.
from recompute_clv import compute_customer_clv, decide
def order(**over):
base = {"id": 1, "status": "processing", "total": "50.00", "total_refunded": "0"}
base.update(over)
return base
def test_clv_sums_paid_orders_only():
orders = [
order(id=1, status="processing", total="50.00"),
order(id=2, status="pending", total="999.00"),
order(id=3, status="completed", total="20.00"),
]
total, counted, notes = compute_customer_clv(orders)
assert total == 7000
assert counted == 2
def test_clv_nets_out_woo_refund():
orders = [order(id=1, status="processing", total="50.00", total_refunded="20.00")]
total, counted, notes = compute_customer_clv(orders)
assert total == 3000
def test_clv_prefers_larger_stripe_refund_over_stale_woo_cache():
orders = [order(id=1, status="processing", total="50.00", total_refunded="0")]
total, counted, notes = compute_customer_clv(orders, {1: 5000})
assert total == 0
assert len(notes) == 1
def test_decide_drift_when_cache_is_stale_high():
action, reason = decide({"total_spent": "120.00"}, 7000)
assert action == "drift"
assert "higher" in reason
def test_decide_ok_when_cache_matches():
action, _ = decide({"total_spent": "70.00"}, 7000)
assert action == "ok"import { test } from "node:test";
import assert from "node:assert/strict";
import { computeCustomerClv, decide } from "./recompute-clv.js";
const order = (over = {}) => ({ id: 1, status: "processing", total: "50.00", total_refunded: "0", ...over });
test("clv sums paid orders only", () => {
const orders = [
order({ id: 1, status: "processing", total: "50.00" }),
order({ id: 2, status: "pending", total: "999.00" }),
order({ id: 3, status: "completed", total: "20.00" }),
];
const { totalMinor, counted } = computeCustomerClv(orders);
assert.equal(totalMinor, 7000);
assert.equal(counted, 2);
});
test("clv nets out woo refund", () => {
const orders = [order({ id: 1, status: "processing", total: "50.00", total_refunded: "20.00" })];
const { totalMinor } = computeCustomerClv(orders);
assert.equal(totalMinor, 3000);
});
test("clv prefers larger stripe refund over stale woo cache", () => {
const orders = [order({ id: 1, status: "processing", total: "50.00", total_refunded: "0" })];
const { totalMinor, notes } = computeCustomerClv(orders, { 1: 5000 });
assert.equal(totalMinor, 0);
assert.equal(notes.length, 1);
});
test("decide drift when cache is stale high", () => {
const [action, reason] = decide({ total_spent: "120.00" }, 7000);
assert.equal(action, "drift");
assert.match(reason, /higher/);
});
test("decide ok when cache matches", () => {
assert.equal(decide({ total_spent: "70.00" }, 7000)[0], "ok");
});Case studies
The support team that refunded straight from Stripe
A support team found it faster to issue refunds from the Stripe dashboard during a busy week, skipping the WooCommerce order screen entirely. Weeks later, marketing pulled a list of top customers by lifetime value for a loyalty campaign and invited several people who had actually been refunded in full.
The recompute job, run once in dry run, listed every customer whose cached total no longer matched their real paid orders. Fixing the list before the campaign went out saved an awkward round of "why did you invite me back" replies.
The migration that skipped the Analytics sync
A store migrated a batch of historical orders through a CSV import tool that inserted orders directly into the database. The orders themselves were correct, but the Analytics lookup tables and the cached customer totals never picked them up, so a large group of long time customers showed a lifetime value of zero.
Running the recompute job against just that segment corrected every customer in about ten minutes, using nothing but data already sitting in the store.
Once this runs on a schedule, lifetime value stops being a number you have to trust blindly. Every drift gets caught and corrected within a day, and segments, loyalty tiers, and reports built on lifetime value stay honest even when a refund or an edit happens outside the usual path.
FAQ
Why does a customer's lifetime value stop matching their orders in WooCommerce?
WooCommerce caches the total on the customer instead of adding up orders every time a page loads. That cache can fall behind when a refund is issued from the Stripe dashboard and never re-syncs, when an order is edited after the total was cached, or when the Analytics lookup tables miss a sync run. A recompute job that sums paid orders and nets out real refunds fixes it.
Is it safe to overwrite a customer's stored lifetime value with a script?
Yes, as long as the script only sums orders that are actually paid, nets out refunds correctly, and treats Stripe as the source of truth when Stripe and WooCommerce disagree about a refund. Start in dry run mode and review the list of customers it would change before it writes anything.
How often should the lifetime value recompute job run?
Once a day is enough for most stores, since lifetime value is a slow moving number used for segments and reports, not for checkout. Run it more often right after a bulk refund or a plugin change that touches orders.
Related field notes
Citations
On the problem:
- WooCommerce Analytics documentation: how customer and order stats are stored in lookup tables and when they need to be regenerated. woocommerce.com/document/woocommerce-analytics
- WooCommerce developer docs: the customer lifetime value report and the fields behind it. github.com/woocommerce/woocommerce/wiki
- Stripe docs: refunds and how amount_refunded is tracked on a charge. docs.stripe.com/refunds
On the solution:
- WooCommerce REST API: list and retrieve customers, including the total_spent field. woocommerce.github.io/woocommerce-rest-api-docs
- WooCommerce REST API: list and retrieve orders and their refunds. woocommerce.github.io/woocommerce-rest-api-docs
- Stripe API: retrieve a PaymentIntent with the latest charge expanded. docs.stripe.com/api/payment_intents/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 reports?
If this saved you a segment full of wrong numbers or an awkward campaign, 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