UPI failure rates: separate customer declines from system declines

The escalation this analysis usually starts with

A payments product manager posts at 9:15 pm: "Success rate dropped to 81% this evening. Is it us?" Before anyone opens a dashboard, three questions decide the answer:

• 81% of what — attempts or payments people were trying to make?
• Were the failures customers typing the wrong PIN, or banks not responding?
• Was it everywhere, or one bank in one hour?

The synthetic example below reproduces exactly that evening.

Columns worth asking the switch or PSP team for

intent_id is the column most teams forget to request. Without it, a customer who fails once and succeeds on retry looks like one failure and one success, and you cannot tell whether anyone was actually stuck.

Build decline_map from the response-code specification your PSP or sponsor bank gives you, and have someone from payments operations sign it off. The grouping is a business judgement: "insufficient balance" is the customer's side, "remitter bank not reachable" is the system's side, and a handful of codes are genuinely ambiguous. Keep those in their own group rather than forcing them.

Reproduce the evening in SQLite

All data is synthetic: 900 payment intents across three hypothetical remitter banks between 18:00 and 20:59, with retries after timeouts and wrong PINs.

CREATE TABLE decline_map (response_code TEXT PRIMARY KEY, reason_group TEXT, description TEXT);
INSERT INTO decline_map VALUES
 ('00','SUCCESS','Approved'),
 ('C01','CUSTOMER','Incorrect UPI PIN'),
 ('C02','CUSTOMER','Insufficient balance'),
 ('S01','SYSTEM','Remitter bank timeout'),
 ('P00','PENDING','No final response yet');

CREATE TABLE upi_attempts (
  attempt_id INTEGER PRIMARY KEY, intent_id INTEGER, attempt_no INTEGER,
  created_at TEXT, payer_bank TEXT, amount REAL, response_code TEXT);

-- 900 synthetic payment intents between 18:00 and 20:59
WITH RECURSIVE k(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM k WHERE n < 900),
base AS (
  SELECT n AS intent_id,
         printf('2026-09-22 %02d:%02d:00', 18 + n % 3, n % 60) AS created_at,
         CASE n % 5 WHEN 0 THEN 'Bank A' WHEN 1 THEN 'Bank A'
                    WHEN 2 THEN 'Bank B' WHEN 3 THEN 'Bank B' ELSE 'Bank C' END AS payer_bank,
         100 + (n * 37) % 2400 AS amount,
         CASE WHEN n % 41 = 0 THEN 'P00'
              WHEN n % 17 = 0 THEN 'C01'
              WHEN n % 23 = 0 THEN 'C02'
              WHEN n % 5 IN (2,3) AND n % 3 = 2 AND n % 2 = 0 THEN 'S01'
              ELSE '00' END AS first_code
  FROM k)
INSERT INTO upi_attempts (intent_id, attempt_no, created_at, payer_bank, amount, response_code)
SELECT intent_id, 1, created_at, payer_bank, amount, first_code FROM base
UNION ALL
-- people retry after a timeout or a wrong PIN; some retries fail again
SELECT intent_id, 2, datetime(created_at, '+40 seconds'), payer_bank, amount,
       CASE WHEN first_code = 'S01' AND intent_id % 4 = 0 THEN '00'
            WHEN first_code = 'S01' THEN 'S01'
            ELSE '00' END
FROM base WHERE first_code IN ('S01','C01');

Then three queries, each answering one of the questions above:

-- 1. Attempt-level rates, split by reason group
SELECT m.reason_group, COUNT(*) AS attempts,
       ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM upi_attempts), 2) AS pct_of_attempts
FROM upi_attempts a JOIN decline_map m USING (response_code)
GROUP BY m.reason_group ORDER BY attempts DESC;

-- 2. Intent-level: did the person eventually pay?
WITH final AS (
  SELECT intent_id, MAX(response_code = '00') AS paid,
         MAX(response_code = 'P00') AS still_pending
  FROM upi_attempts GROUP BY intent_id)
SELECT COUNT(*) AS intents, SUM(paid) AS paid,
       ROUND(100.0 * SUM(paid) / COUNT(*), 2) AS intent_success_pct,
       SUM(still_pending) AS pending
FROM final;

-- 3. System-decline rate by payer bank and hour (first attempts only)
SELECT payer_bank, strftime('%H', created_at) AS hour, COUNT(*) AS first_attempts,
       ROUND(100.0 * SUM(m.reason_group = 'SYSTEM') / COUNT(*), 1) AS system_decline_pct,
       ROUND(100.0 * SUM(m.reason_group = 'CUSTOMER') / COUNT(*), 1) AS customer_decline_pct
FROM upi_attempts a JOIN decline_map m USING (response_code)
WHERE attempt_no = 1
GROUP BY payer_bank, hour ORDER BY payer_bank, hour;

What the three outputs say

Attempt level. 1,005 attempts were sent (900 first attempts plus 105 retries). 81.0% succeeded, 8.76% were customer declines, 8.16% system declines and 2.09% were still pending. This is the "81%" in the escalation.

Intent level. 814 of 900 intents ended in a successful payment: 90.44%. Retries recovered part of the damage. Reporting 81% to leadership overstates how many customers could not pay, because each retry adds a row.

Bank and hour. Customer declines sit between 7.5% and 11.7% in every cell, which is ordinary noise across groups of 60 to 120 attempts. System declines are zero everywhere except Bank B at 20:00, where 45.0% of first attempts timed out. Across all banks, 20:00 shows an 18.0% system-decline rate, which would have looked like a platform-wide problem if you had cut by hour alone.

The answer to "is it us?" is: probably not. One remitter bank failed for one hour. Of the 54 customers who hit that timeout, 26 got through on the second attempt and 28 failed again.

Mistakes that turn a UPI report into an argument

• Pending counted as failure, or as success. A pending transaction may still complete or reverse. Report it as its own line and re-run the numbers after the reconciliation window your operations team uses.
• Customer declines in the incident metric. Wrong-PIN rates move with festive spikes and new users, not with outages. Mixing them in makes every busy evening look like an incident.
• Retries treated as independent. A customer who retries three times during an outage creates three failures. Attempt-level rates exaggerate outages in proportion to how persistent users are.
• Small cells. A merchant with 12 attempts in an hour can show a 25% failure rate from three events. Put the attempt count beside every rate and set a minimum volume before a cell is highlighted.
• Clock sources. App timestamps and switch timestamps can differ. Use the server-side request time for hourly cuts.

The note to send the payments head

Between 20:00 and 20:59 on 22 September, first-attempt system declines were 45.0% for Bank B and zero for Banks A and C. Customer declines were within their usual 7–12% band. Of 900 payment intents that evening, 90.4% eventually succeeded. Recommended: raise the Bank B timeout pattern with the sponsor bank; no change needed in our app. Pending: 21 transactions await final status.

Four lines, one number per claim, and a stated action. The attempt-level 81% belongs in the appendix with its definition.

The query patterns here — conditional sums, a mapping table and two grains of analysis — are everyday SQL for data analysts trained on our DA course. A related customer-side view of UPI data is in categorising UPI bank-statement narrations.