Rules, filters & calculations

PhonePe transactions (FORWARD_TRANSACTION reports)

• Included: Transaction Status ∈ {SUCCESS, COMPLETED}
• Excluded: FAILED (1.60 crore rows — automated mandate re-debit attempts), PENDING (1.4 lakh), malformed status values (3 rows)
• De-duplication: one row per Merchant Reference Id (report files overlap at their date boundaries)
• Month assignment: by Order Creation Date (client rule 13-Aug), falling back to Transaction Date where blank — the two differ only at midnight boundaries (~1% of rows). Parsed per-row (format="mixed", dayfirst=True — one May file uses DD-MM-YYYY while all others are ISO)
• Period filter: transaction month ∈ {Apr, May, Jun 2026}

Razorpay (Combined Reports, csv + xlsx)

• All months' reports are pooled (they are settlement-window pulls), then de-duplicated by entity_id, keeping the settled = 1 version of a payment when it appears in two windows
• Revenue rows: type = payment only; refund, settlement, adjustment rows are processed separately (refund register, bank transfers, adjustments note)
• Dates: raw created_at truncated to a date, no timezone shift (client rule 13-Aug; matches their workings, which map all evening timestamps to the same date). A +5:30 shift was trialled and reverted — the config constant RZ_TZ_SHIFT_MIN records the decision
• Month assignment: by capture date. Payments created before April (settled inside the quarter) are excluded from revenue and disclosed as prior-period

Google Play

• Apr & May: earnings report (PlayApps_2026MM) rows with Transaction Type = Charge; fee = paired Google fee row (matched on order number)
• Jun: sales report rows with Financial Status = Charged; fee estimated at 15.00% (observed Apr+May effective rate) pending the earnings report

Refunds

• PhonePe: REFUND_TRANSACTION report rows with Transaction Status = COMPLETED
• Razorpay: combined-report type = refund rows
• Google Play: Charge refund rows (earnings) / Financial Status = Refund rows (June sales report), with paired Google fee refund reversals
• Refunds are stored negative and assigned to the refund date's month; each also carries its Original Month (the original transaction's cohort, looked up by transaction id)

• Settled: the transaction's Merchant Reference Id (PhonePe) is found among PaymentType = PAYMENT rows of the Merchant_Settlement_Report / SETTLEMENT_REPORT chunks; Razorpay: payment row has settled = 1
• Unsettled: no settlement row found (June 29–30 T+1 tail pends the July report)
• Amount mismatch: PhonePe |settled amount − txn amount| > ₹0.01 · Razorpay |amount − fee − (credit − debit)| > ₹0.01 (₹1 tokens: fee ₹2.36 > amount, recovered via debit — not a mismatch)
• Orphan settlements are split two ways: (a) prior-period spillover — settlement rows whose transaction date is before April (March 30–31 sweeps etc.), excluded from the quarter; (b) genuine gaps — settlement rows dated inside Apr–Jul whose reference id is absent from every transaction report (real revenue missing from the revenue base; highlighted in red with a drillable register phonepe_gaps.csv)
• Refund-side gaps: settled refund reversals absent from the refund report (blank-status rows), and refund-report rows never recovered in any settlement — both listed in the same gaps register
• Settlement-lag matrix: cohort = transaction month; column = Settlement Month; settled cells use the settlement-report amounts (only the unsettled column uses transaction amounts); each cell is net of refunds where refund's Original Month = cohort and refund month = the column month

• Expected credits: settlement rows grouped by (Settlement Date, Bank UTR) → net amount (PhonePe); Razorpay type = settlement transfer rows; Google Play monthly payout (~15th next month)
• Bank side: Zoho wallet-account extracts — transaction_type = transfer_fund credits (wallet → HDFC Bank)
• Match pass 1: exact amount (±₹1) within ±3 days
• Match pass 2: combined daily sweep — one bank transfer equals the sum of several same-window settlement expectations (±₹1)
• Result: PhonePe 66/66 and Razorpay 66/66 matched, Δ ₹0; Google payouts flagged (~2% below earnings expectation; Apr-16 transfer is the March payout by design)

• Deferred plans: gross amount ≥ ₹249 (i.e. ₹249/299/399/499/599/699)
• Not deferred: gross amount < ₹249 (₹1 mandate tokens, ₹99, ₹149) — recognized in full in the collection month
• Service period: per-plan setting in automation/deferral_config.json (amount → days; currently all plans 91 days / quarterly; set 30 for a monthly plan and re-run), inclusive of the transaction date (end = date + days − 1)
• Base: taxable value = amount ÷ 1.18 (GST is never deferred — it is output liability at collection)

per-day        = taxable ÷ 91
recognized(M)  = per-day × (days of [date, date+90] falling inside month M)
deferred(M-end)= taxable − Σ recognized to date

• Refunds: spread negatively with the same rule from the refund date (a refunded ₹499 reverses over its own 91 days)
• Rounding: each transaction-month contribution is rounded to 2 decimals — the matrix cells are therefore exactly the sum of the evidence files' Revenue columns
• Roll-forward: closing = opening + (collected − recognized-from-current) − released-from-prior

• Basis: creation date (client rule 13-Aug) — every fee line is bucketed by its own CreationDate (fallback TransactionDate), not the settlement date
• PhonePe fee lines: settlement-report rows with non-zero Fee/IGST/CGST/SGST — instruments MANDATE_FEE and UPI_MANDATE_REGISTRATION, bifurcated by instrument in the derivation table. Commission = Σ Fee + Σ(IGST+CGST+SGST). March-dated lines billed in April are shown as prior-period and excluded from Apr–Jul
• Google Play RCM: the service fee is an import of services — 18% IGST payable under Reverse Charge on the fee net of refunds, claimable as ITC; shown separately from (never inside) the commission amount
• Deferral selector (dashboard): the deferral section has per-plan-amount defer/recognize-immediately checkboxes and a gateway multi-select. The default selection is the booked basis (deferral_config.json) whose cells drill to evidence; any other selection is a clearly-labelled what-if view recomputed from per-amount recognition splits (same per-transaction 2dp rounding). To change the BOOKED basis, edit deferral_config.json and re-run.
• Google Play "Tax" rows: tax withheld by Google inside the payout — shown as separate info lines in the commission derivation; never part of commission.
• "Settlement window files": PhonePe sends a monthly Merchant_Settlement_Report for some months (Apr, Jun) and 5-day SETTLEMENT_REPORT window pulls for others (May, Jul). Same columns, same treatment — derivation rows are labelled with their source format only for traceability.
• Bill matching: gateway tax invoices (signed PDFs in Finwert/Bills/, Apr–Jul) are extracted into gateway_bills.json and reconciled against the computed commission both in the commission table (Billed and Δ columns) and in the dashboard's bill-matching table. PhonePe and Razorpay invoices carry 18% IGST on the fee; Google bills carry no GST (import of services — 18% RCM IGST self-assessed). Google's Jun-26 bill line says "May-26" — a description typo; Bill# and date are June
• Razorpay: per-payment fee column (includes GST) plus the fee charged on refund rows (Razorpay debits ₹9.43 + GST alongside each refund); tax column = the GST part; net-of-GST fee = fee − tax
• Google Play: Google fee rows net of Google fee refund rows; no GST inside the report (Google invoices GST separately); June estimated @15%
• Verification identity (settlement basis): fees deducted in month = settled gross + refunds recovered − net credited — matches to the paisa for all months (Google: pending payout report)
• Effective % = fee incl GST ÷ collections net of refunds

Amounts are GST-inclusive @18%:
taxable = amount × 100 ÷ 118
GST     = amount × 18 ÷ 118 (= amount − taxable)

• Computed on collections net of refunds; refunds carry their own negative taxable/GST from the refund month

• Waterfall categories per gateway: raw rows → less FAILED/PENDING/other → less duplicate ids → less non-payment rows (Razorpay) → less out-of-period → used in workings
• PASS rule: successful, deduplicated, in-period row count = register row count, per gateway-month (all 9 PASS)
• Independent check — verify_rows.py rescans the raw files rather than trusting the pipeline

• Place of supply: the gst-states location files, joined on transaction id; refunds joined on refund id, falling back to the original transaction's state
• Name normalization: Hindi names translated (राजस्थान → Rajasthan …), aliases folded (Orissa→Odisha, Pondicherry/UT of Puducherry→Puducherry, "&"→"and", Uttaranchal→Uttarakhand)
• Unidentified/blank/null state → Maharashtra (client rule; supplier's home state) — this is the default "GSTR-1 basis" view
• Tax split (GSTR-1 basis): Maharashtra = intra-state → CGST = SGST = GST ÷ 2; every other state → IGST = GST
• All amounts net of refunds; Taxable/GST computed on the net

• Buckets: calendar day / ISO week / calendar month / year, from each charge's transaction date
• Per-gateway figures are net of that gateway's refunds dated in the bucket; the refunds column shows what was netted
• Dev% (day view) = (day gross − centred 7-day median) ÷ median
• % settled T+1 (day view, PhonePe) = settled next calendar day ÷ settled

Anything not listed in §10 passed every rule.

On the dashboard, hover any dotted-underlined column header for its definition. Key columns:

• Stratification: per gateway — a few random normal charges, the single largest transaction, ₹1 mandate tokens, one unsettled transaction, and random refunds (fixed seed, reproducible)
• Purpose: audit-style test of details — each row lists the 3 manual steps (Ctrl+F the ID in the named source report → confirm in the settlement report → tie the UTR to bank) so a human can validate the whole chain without trusting the automation