Recover orders lost to declined cards: 34 web orders worth £4,610 went unpaid this month, and nothing reminded the buyers
Estimated impact
About £1.6K a month in orders now lost at payment
How this was calculated
- Arithmetic
- 38 web orders a month with a failed card × (43% − 11%) paid within a day ≈ 12 more paid orders. 12 × £135.60, the average unpaid web order = £1,627 a month.
- Assumption
- Web buyers answer an email the way app buyers answer the app’s reminder, and web cards keep failing at this month’s rate.
Actions to take
Email web buyers whose card fails a link back to their order within the hour. The app reminds users and failed app purchases have a 3.9x higher second payment attempt rate. Hold back one in ten at random, so the difference in paid orders shows what the email itself brought in.
Why this came up
52 orders in the last 30 days had their first card payment fail. Orders placed in Orion’s app get a reminder to finish paying, and 6 of those 14 were paid within a day, 43%. The website sends nothing, and 4 of its 38 were, 11%. The 34 web orders never paid were worth £4,610, £135.60 each on average. This complements the checkout accounts you are pursuing: those keep buyers after their first order, this saves the orders lost before they are paid.
Show queryHide query
WITH failed AS ( SELECT o.ord_id, o.src_sys_cd AS placed_on, o.ord_ccy_cd, o.amt_2, MIN(p.pmt_ts) AS first_try, MIN(p.pmt_ts) FILTER (WHERE p.pmt_stat_cd IN (2, 5)) AS paid_ts FROM marketplace.orn_ord_hdr o JOIN marketplace.pmt_txn p ON p.ord_id = o.ord_id LEFT JOIN marketplace.cust_mst c ON c.cust_id = o.cust_id WHERE o.dt_1 >= CURRENT_DATE - 30 AND o.src_sys_cd <> 'INTERNAL_TEST' AND NOT COALESCE(c.is_test_acct, false) GROUP BY o.ord_id, o.src_sys_cd, o.ord_ccy_cd, o.amt_2 HAVING MIN(p.pmt_ts) = MIN(p.pmt_ts) FILTER ( WHERE p.pmt_stat_cd = 3 AND p.pmt_mthd_cd = 'CARD' ))SELECT f.placed_on, COUNT(*) AS card_failed, COUNT(*) FILTER ( WHERE f.paid_ts < f.first_try + INTERVAL '1 day' ) AS paid_within_a_day, COUNT(*) FILTER (WHERE f.paid_ts IS NULL) AS never_paid, ROUND(SUM(f.amt_2 * fx.to_gbp) FILTER ( WHERE f.paid_ts IS NULL ), 2) AS never_paid_gbpFROM failed fCROSS JOIN LATERAL ( SELECT COALESCE(MAX(x.rate) FILTER (WHERE x.ccy_from = f.ord_ccy_cd), 1) / MAX(x.rate) FILTER (WHERE x.ccy_from = 'GBP') AS to_gbp FROM lgcy.fx_daily_rate x WHERE x.fx_dt = (SELECT MAX(fx_dt) FROM lgcy.fx_daily_rate WHERE fx_dt <= f.first_try::date)) fxGROUP BY 1ORDER BY 2 DESCThe marked lines come from the Context Layer, not the schema:
- Status 3 is a failed payment; 2 and 5 are captured.
- Test orders are left out, including those from test accounts.
- An unpaid order has no captured amount, so it is valued at its checkout total after discounts.
- Rates are quoted to dollars on business days only, so each order takes the latest rate on or before its day.
Orders whose first card payment failed
Orders placed in the last 30 days, by where they were placed.
| Placed on | Card failed | Paid within a day | Never paid | Never paid, value |
|---|---|---|---|---|
| WEB | 38 | 4 | 34 | £4,610 |
| APP | 14 | 6 | 8 | £1,090 |
2 rows