Site Title

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_vintage
FROM attempts
GROUP 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 QUALIFY
WITH 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 declined
first_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 QUALIFY
last_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 window
final_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 date
SELECT
  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_succeed
FROM final_status
ORDER 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