When a payment fails, it almost never ends there. Merchants retry. PSPs retry. Authorisers retry. And sometimes all three do it simultaneously — without knowing the others are doing the same. If an authoriser retries and the transaction succeeds, the analyst at the PSP may never know. If the PSP reroutes to a backup processor, the merchant’s analytics table will never show it.
The result is the same in every analytics table: one billing attempt shows up as separate rows — each with its own transaction ID, timestamp, and status. Without the right deduplication logic, you are not measuring what you think you are.
That logic looks different depending on where you sit in the payment chain. This post walks through how to build it — for merchants, PSPs, card networks, and issuers — and why each one needs a different approach.
1. Why this matters: the true success rate problem
For merchants: the goal is to understand the effectiveness of their retry strategies. Imagine a merchant retries a failed subscription payment three times before it succeeds. Naively, that is a 25% approval rate (1 success out of 4 attempts). But from a business perspective, the payment succeeded, the customer was charged and the invoice was closed. The true success rate for that invoice is 100%.
For PSPs: the goal is to understand the true success rate associated with the processor connections they use stripped of retry noise that was never really a new transaction.
Getting to either number requires knowing that all the retry rows belong to the same billing cycle. That is what deduplication does.
If you count a retry as a new transaction, you overcount volume and overstate failure rates — making your payment performance look worse than it actually is.
2. The key parameters
Before looking at each party’s query, here are the building blocks and what each one means:
3. The merchant perspective
Merchants have the best context. They know the invoice cycle, the customer, the grace period, and whether a payment method was updated mid-cycle. This makes their dedupe query the most precise.
Dedupe query
WITH attempts AS ( SELECT customer_id, invoice_id, card_id, status, attempt_ts, ROW_NUMBER() OVER ( PARTITION BY customer_id, invoice_id ORDER BY attempt_ts ASC ) AS attempt_vintage FROM transactions)SELECT customer_id, invoice_id, card_id, COUNT(*) AS total_attempts, COUNTIF(status = 'success') AS successful_attempts, MAX(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS invoice_resolved, MIN(attempt_ts) AS first_attempt_ts, MAX(attempt_ts) AS last_attempt_ts, -- which attempt number finally succeeded MAX(CASE WHEN status = 'success' THEN attempt_vintage END) AS winning_attempt_vintageFROM attemptsGROUP BY customer_id, invoice_id, card_id
Note: when you drop card_id from the group key, you see whether the invoice was eventually paid — regardless of which funding instrument succeeded. This is usually the right question for subscription businesses.
4. The PSP perspective
A payment service provider processes transactions on behalf of many merchants. They don’t see invoice IDs or customer IDs. They can’t tell if a retry was triggered by a dunning system or a customer clicking “try again.” But they do see something powerful: the actual card numbers — which they store as hashed values (card hashes or tokenised PANs).
The MID problem. Large merchants often operate under multiple merchant IDs (MIDs) — one per geography, product line, or entity. If you group by raw merchant_id, you will undercount success for those merchants. The fix is to create a unified merchant ID that normalises all of a merchant’s MIDs into a single entity before any grouping happens.
The PAN vs network token problem. Some merchants retry declined transactions using a network token where the original attempt used a raw PAN, or vice versa. This breaks the uniqueness of card_hash as an identifier — the same card appears as two different values depending on how the merchant submitted it. Using PAR (Payment Account Reference) solves this: PAR is a stable value that links a PAN and all its associated network tokens back to the same underlying account, regardless of how the transaction was submitted.
The first attempt problem. PSPs and authorisers have no access to invoice IDs, and merchants often retry transactions well beyond 30 days depending on their dunning strategy. This makes it genuinely hard to identify where a retry chain begins. The practical approach: for a given card_hash or PAR and merchant combination, find the earliest attempt and treat that as the origin. If that attempt is successful, the transaction reached a terminal state in a single try. If it is not, every subsequent attempt within a defined window counts as a retry — and you can track how far into the chain a success eventually arrives, or whether it arrives at all.
-- Step 1: identify first attempt per chain using QUALIFYWITH first_attempts AS ( SELECT unified_merchant_id, card_par, amount, currency, psp_txn_id, attempt_ts AS first_attempt_ts, status AS first_attempt_status FROM psp_transactions QUALIFY ROW_NUMBER() OVER ( PARTITION BY unified_merchant_id, card_par, amount, currency ORDER BY attempt_ts ASC ) = 1),-- Step 2: subset only chains where first attempt was declinedfirst_declined AS ( SELECT * FROM first_attempts WHERE first_attempt_status != 'success'),-- Step 3: for declined chains, find all retries within 60 days-- then pick the last attempt status using QUALIFYlast_retry_status AS ( SELECT t.unified_merchant_id, t.card_par, t.amount, t.currency, t.attempt_ts AS last_attempt_ts, t.status AS last_attempt_status, ROW_NUMBER() OVER ( PARTITION BY t.unified_merchant_id, t.card_par, t.amount, t.currency ORDER BY t.attempt_ts ASC ) AS attempt_vintage, COUNT(*) OVER ( PARTITION BY t.unified_merchant_id, t.card_par, t.amount, t.currency ) AS total_retries_in_window FROM psp_transactions t INNER JOIN first_declined d ON t.unified_merchant_id = d.unified_merchant_id AND t.card_par = d.card_par AND t.amount = d.amount AND t.currency = d.currency -- exclude the first attempt itself, only retries AND t.attempt_ts > d.first_attempt_ts -- only within 60 day window from first attempt AND TIMESTAMP_DIFF(t.attempt_ts, d.first_attempt_ts, DAY) <= 60 QUALIFY ROW_NUMBER() OVER ( PARTITION BY t.unified_merchant_id, t.card_par, t.amount, t.currency ORDER BY t.attempt_ts DESC ) = 1),-- Step 4: join first attempt table with last retry status-- to get final transaction outcome after 60 day windowfinal_status AS ( -- chains that succeeded on first attempt (terminal immediately) SELECT f.unified_merchant_id, f.card_par, f.amount, f.currency, f.first_attempt_ts, f.first_attempt_status, NULL AS last_attempt_ts, NULL AS last_attempt_status, 1 AS total_attempts, 0 AS total_retries, 'success_first_attempt' AS chain_outcome FROM first_attempts f WHERE f.first_attempt_status = 'success' UNION ALL -- chains that were declined on first attempt -- final status determined by last retry within 60 days SELECT d.unified_merchant_id, d.card_par, d.amount, d.currency, d.first_attempt_ts, d.first_attempt_status, r.last_attempt_ts, r.last_attempt_status, r.total_retries_in_window + 1 AS total_attempts, r.total_retries_in_window AS total_retries, CASE WHEN r.last_attempt_status = 'success' THEN 'success_after_retry' WHEN r.last_attempt_status IS NULL THEN 'declined_no_retry' ELSE 'declined_exhausted' END AS chain_outcome FROM first_declined d LEFT JOIN last_retry_status r ON d.unified_merchant_id = r.unified_merchant_id AND d.card_par = r.card_par AND d.amount = r.amount AND d.currency = r.currency)-- Step 5: final output — one row per chain, status anchored to first attempt dateSELECT unified_merchant_id, card_par, amount, currency, first_attempt_ts, first_attempt_status, last_attempt_ts, last_attempt_status, total_attempts, total_retries, chain_outcome, -- summary flags CASE WHEN chain_outcome IN ('success_first_attempt', 'success_after_retry') THEN 1 ELSE 0 END AS ultimately_successful, CASE WHEN chain_outcome = 'success_first_attempt' THEN 0 WHEN chain_outcome = 'success_after_retry' THEN total_retries ELSE NULL END AS retries_needed_to_succeedFROM final_statusORDER BY first_attempt_ts, unified_merchant_id
Amount caution: some merchants offer a discount on retry success (e.g. 5% off if the second attempt succeeds). In those cases, grouping on amount will break your dedupe chain — remove amount from the key or use an amount range bucket instead.
5. The card network perspective
Card networks see a different slice: every transaction that flows through their rails, regardless of PSP. This means they can observe cross-PSP retry patterns — something no individual PSP can see. They can also detect cross-processor retry behavior. If a merchant tries PSP A, fails, then tries PSP B, both transactions land at the card network (as long as they are on the same network). The network can group them — and see that acquirers_tried > 1
6. The issuer perspective
Issuers have the most complex view and the least context. They see every transaction that hits a funding instrument — from every merchant, every PSP, on and off their network — but with no invoice ID, no customer ID, and no visibility into dunning strategies. Where a merchant sees a rational retry sequence, an issuer sees a burst of repeated attempts on the same card, a pattern that is difficult to distinguish from fraud or account takeover.
This is why issuer behaviour matters to merchants even though merchant analysts never see issuer data directly. An aggressive dunning strategy that makes sense on the merchant’s P&L can trigger blanket declines on the issuer side — catching legitimate transactions in the same net. And if an issuer places a restriction against a specific merchant ID, retrying through a different PSP may succeed temporarily, but once the rule propagates the benefit disappears. Understanding the issuer’s vantage point is not about having their data — it is about designing retry strategies that do not look like fraud from the outside.
End note:
Every party in the payment chain is looking at the same transaction and drawing different conclusions from it. That is not a data quality problem — it is a structural one. The dedupe key you choose determines which version of the truth you are measuring.
Get it wrong and your approval rate is a fiction. Get it right and you have a foundation for every optimisation decision that follows — retry strategy, PSP routing, dunning cadence, decline recovery.
Start with your vantage point. Build your key from there.
Part 3 will cover how to use true approval rates to benchmark your payment performance against industry peers. Follow along at paymentscafe.com
Site Title 
Leave a comment