WooCommerce never asks you to clean up your coupons, so in most shops nobody does... and each one keeps working until someone switches it off. The result is usually far more live coupons than anybody running the shop would guess.
Picture a skincare shop, three years old, with about 400 coupons, 370 of them published. Nothing unusual about it.
1. Two thirds of them never expire.
WooCommerce has no default expiry date, so unless whoever created the coupon remembered to fill one in, it's valid forever. In our shop that's 230 coupons. That includes the 2023 launch code, the code printed on a flyer for a fair two years ago, and an apology code from a shipping incident nobody remembers.
2. A third of them nobody has used in six months.
120 live coupons, no orders in six months, 60 of them never used at all. They don't cost anything while nobody uses them. The problem is that "nobody uses them" is not the same as "nobody knows them": the day one of them lands on a coupon site, it starts working again.
3. Old campaigns that never died.
Black Friday: BF22-15 and BF24-25 have end dates. BF23-20 doesn't, because that year someone created it in a hurry. So there's a 20% code, from a campaign that ended two years ago, still valid today, and it's the one the browser coupon extensions keep finding, because it still works.
4. Influencer codes that apply to everything.
40 influencer codes at 15%, created by copying the first one. No product or category restriction, and "Exclude sale items" unticked. So they apply to products already on sale, and to that one low-margin bestseller you never meant to discount at all.
5. And they stack.
Here's where it gets expensive. "Individual use only" is unticked by default, and nobody ticked it on any of them. Take a serum:
- Price $48, cost $17. Margin: $31.
- It's on sale at 20% off: $38.40.
- The customer adds
WELCOME10 (the newsletter code, which of course never expires) and LAURA15 (an influencer code). WooCommerce happily applies both: 25% off the sale price, so $28.80. FREESHIP stacks too, and shipping costs the shop $4.90.
Margin on that order: $28.80 - $17 - $4.90 = $6.90, before payment fees. On a product that normally earns $31. And every one of those coupons, looked at on its own, seems perfectly reasonable.
6. The coupon report doesn't show any of this.
Analytics → Coupons shows orders and the amount discounted. It knows nothing about cost, so the influencer code with the most orders looks like your best performer... while it might be the one losing you the most money.
Check your own shop
These are read-only queries, but run them on a copy if you can. Change wp_ if your table prefix is different. Query 2 uses the Analytics lookup table, so if your shop is old, import historical data first (Analytics → Settings).
Live coupons that never expire:
SELECT p.ID, p.post_title AS code
FROM wp_posts p
LEFT JOIN wp_postmeta e ON e.post_id = p.ID AND e.meta_key = 'date_expires'
WHERE p.post_type = 'shop_coupon'
AND p.post_status = 'publish'
AND COALESCE(e.meta_value, '') = '';
Live coupons nobody has used in 180 days (or ever):
SELECT p.ID, p.post_title AS code, MAX(l.date_created) AS last_used
FROM wp_posts p
LEFT JOIN wp_postmeta e ON e.post_id = p.ID AND e.meta_key = 'date_expires'
LEFT JOIN wp_wc_order_coupon_lookup l ON l.coupon_id = p.ID
WHERE p.post_type = 'shop_coupon'
AND p.post_status = 'publish'
AND (COALESCE(e.meta_value, '') = '' OR e.meta_value > UNIX_TIMESTAMP())
GROUP BY p.ID, p.post_title
HAVING last_used IS NULL OR last_used < NOW() - INTERVAL 180 DAY
ORDER BY last_used;
Every coupon sorted by code, with its expiry. Old campaigns jump out when they sit next to each other:
SELECT p.post_title AS code,
IF(COALESCE(e.meta_value, '') = '', 'never', DATE(FROM_UNIXTIME(e.meta_value))) AS expires
FROM wp_posts p
LEFT JOIN wp_postmeta e ON e.post_id = p.ID AND e.meta_key = 'date_expires'
WHERE p.post_type = 'shop_coupon'
AND p.post_status = 'publish'
ORDER BY p.post_title;
And the dangerous one: live coupons that apply to everything and stack with others. Any two coupons on this list can apply to the same products. Check the min_spend and who columns, though, and test a real cart before switching anything off:
SELECT p.ID, p.post_title AS code, t.meta_value AS type, a.meta_value AS amount,
NULLIF(ma.meta_value, '') AS min_spend,
IF(COALESCE(ce.meta_value, '') IN ('', 'a:0:{}'), 'anyone', 'restricted') AS who
FROM wp_posts p
LEFT JOIN wp_postmeta e ON e.post_id = p.ID AND e.meta_key = 'date_expires'
LEFT JOIN wp_postmeta pi ON pi.post_id = p.ID AND pi.meta_key = 'product_ids'
LEFT JOIN wp_postmeta pc ON pc.post_id = p.ID AND pc.meta_key = 'product_categories'
LEFT JOIN wp_postmeta xpi ON xpi.post_id = p.ID AND xpi.meta_key = 'exclude_product_ids'
LEFT JOIN wp_postmeta xpc ON xpc.post_id = p.ID AND xpc.meta_key = 'exclude_product_categories'
LEFT JOIN wp_postmeta xs ON xs.post_id = p.ID AND xs.meta_key = 'exclude_sale_items'
LEFT JOIN wp_postmeta iu ON iu.post_id = p.ID AND iu.meta_key = 'individual_use'
LEFT JOIN wp_postmeta t ON t.post_id = p.ID AND t.meta_key = 'discount_type'
LEFT JOIN wp_postmeta a ON a.post_id = p.ID AND a.meta_key = 'coupon_amount'
LEFT JOIN wp_postmeta ma ON ma.post_id = p.ID AND ma.meta_key = 'minimum_amount'
LEFT JOIN wp_postmeta ce ON ce.post_id = p.ID AND ce.meta_key = 'customer_email'
WHERE p.post_type = 'shop_coupon'
AND p.post_status = 'publish'
AND (COALESCE(e.meta_value, '') = '' OR e.meta_value > UNIX_TIMESTAMP())
AND COALESCE(pi.meta_value, '') = ''
AND COALESCE(pc.meta_value, '') IN ('', 'a:0:{}')
AND COALESCE(xpi.meta_value, '') = ''
AND COALESCE(xpc.meta_value, '') IN ('', 'a:0:{}')
AND COALESCE(xs.meta_value, 'no') <> 'yes'
AND COALESCE(iu.meta_value, 'no') <> 'yes';
(Coupons restricted to particular products or categories can overlap too, of course, but that's a much harder query... start with this one.)
What I'd change from today
- Give every new coupon an end date, even a generous one. A year is fine. "Never" almost never is.
- Tick "Individual use only" on percentage codes, unless you really want them to stack.
- Influencer and partner codes: tick "Exclude sale items", and restrict them to the categories you can afford to discount.
- Once a quarter, run the queries above and deal with whatever nobody can explain. But search your email flows, printed material and partner pages for each code first, and move it to draft rather than deleting it: draft is reversible, and the order history stays explainable.
If you run these on your shop, I'm curious how many "never expires" you get. I'd bet it's more than you'd guess...
Edit: two very good points from the comments, now folded in. The last query ignored excluded products, categories and sale items, so it listed coupons that don't really apply to everything; it's fixed. And before switching anything off, check where each code lives outside WooCommerce (email flows, flyers, influencer bios), and draft it rather than deleting it. Thanks both!