Looker for fintech: TPV, chargebacks, churn, loan book

A weekly business review at a payments and lending fintech rests on about twenty metrics, and nearly all of them hide a definition trap: attempted or settled volume, a dispute attributed to the transaction month or the opening month, a balance summed like a flow, a customer counted once per version of their profile. The approach that lasts is to write each rule once in dbt, test it, and let Looker expose nothing but sums and ratios of totals. We have published a complete reference repository, with its own data generator, to show each pattern in code.

Settled, not attempted: TPV, revenue, take rate

A card payment goes through authorisation, clearing and settlement. An authorisation can be reversed or expire, a declined attempt moved no money, a refund returns it. Counting authorised volume overstates payment volume by the reversal rate and makes take rate drift with settlement lag.

Attempt status	Counts toward TPV	Carries revenue
Settled (card payment or transfer)	Yes	Yes, fee plus interchange
Settled (account top-up)	No, it funds the account without paying anyone	Yes if a fee exists
Authorised, not yet settled	Not yet	Not yet
Declined	No	No
Refunded	No, it is reported through the refund rate	No, despite the fee present in the source

The last row explains why the definition must live in one place. The source feed keeps fee and interchange on refunded rows; the status filter is what makes revenue right, and it is written once in int_transactions_enriched. Take rate is revenue divided by TPV, a ratio of two totals. Currency conversion happens once in dbt, at the dated rate that matters for each fact: transaction date for payments, opening date for disputes, snapshot date for balances. Looker never multiplies by a rate.

Authorisation and decline mix

Authorisation rate is card payment attempts not declined divided by all card payment attempts, and decline rate is its complement, split by reason. A mean of monthly rates is not an annual rate. The reason mix is what makes the metric actionable: a rise in insufficient-funds declines describes the customers' finances, a rise in suspected-fraud declines at night on tiny amounts describes a card-testing campaign.

Chargebacks: internal ratios and scheme thresholds

Visa and Mastercard monitor merchants on a monthly dispute ratio, but the two schemes do not compute it the same way, and a fintech that acquires or issues needs to know both.

Programme	Ratio	Thresholds
Visa, VAMP (since April 2025, replacing VDMP and VFMP)	Reported fraud (TC40) plus disputes (TC15), divided by settled transactions (TC05), card-not-present	Acquirer: 50 basis points Above Standard, 70 Excessive. Excessive merchant: 220 basis points in Europe, North America and Asia-Pacific, lowered to 150 on 1 April 2026; minimum 1,500 fraud and disputes per month
Visa, enumeration	Enumerated authorisations (approved and declined) over all authorisations	20 percent and 300,000 enumerated transactions
Mastercard, ECM	Chargebacks in the month divided by transactions in the preceding month	ECM: at least 100 chargebacks and 1.50 percent. HECM: at least 300 and 3.00 percent. Exit after three consecutive months below the threshold

Two lessons for the semantic layer. First, an internal ratio will never exactly reproduce the scheme's, whose numerator and denominator are its own transaction codes; the internal metric exists to see the trend before the acquirer's letter arrives. Second, the attribution date changes everything: the repository computes the rate by transaction month, as the schemes do, on fct_transactions, and keeps fct_chargebacks by opening month for the team working the queue. Totals match, monthly splits do not, and the glossary says why. Since disputes arrive up to 120 days after the transaction, the latest months are incomplete by construction, and the tile says so.

The dashboard draws a reference line at 0.9 percent, the former standard threshold of Visa's programmes. The thresholds in force are those in the table above; the line is a tile parameter, not a definition.

Active customers and churn at customer-month grain

Churn is the metric three teams compute three ways. The repository fixes it on a fct_customer_months table: one row per customer per calendar month, from their first active month to the end of the data, produced by a month spine and window functions in int_customer_activity.

Flag	Definition
Active	At least one settled transaction in the month
New	First active month
Returning	Active in a month after the first
Reactivated	Active after at least one inactive month, a subset of returning
Churned	Active the previous month, inactive this month

Monthly active customers equal new plus returning, every month, and a dbt test checks it. Churn rate is churned customers divided by the previous month's active customers. A second measure, active in the trailing 30 days, exists for the "today" view, and the glossary states that the two are not comparable.

Loan book: a snapshot, not a flow

Loan book outstanding is a balance. The repository carries it in fct_loan_snapshots, one row per loan per month-end, with outstanding principal and days past due. The measure is described as point in time, the dashboard groups it by snapshot month, and an is_latest_snapshot flag gives a safe single value without a date grouping. The opposite trap is classic: a "loan book" tile that adds twelve month-ends together the moment someone removes the month from the query.

Delinquency rates at 30 and 90 days are ratios of principal, not of loan counts, and the weighted average APR is the sum of balance times rate divided by outstanding: a mean of per-loan rates would give a different and wrong number.

SCD2: customer history and cohort accuracy

A customer changes segment, passes identity verification, closes an account. dim_customers keeps one row per version with validity dates. The question is which version each fact should see.

The repository resolves the version in dbt, through a range join on the fact's own timestamp, and places a version key customer_sk on every transaction, every customer-month and every loan snapshot. Revenue by segment then reflects the segment the customer was in when the money moved; current_segment sits next to it for the other reading. The customers Explore filters on is_current so each customer counts once.

The costliest mistake is joining the version table on the natural key: every transaction is then multiplied by the customer's number of versions, and a COUNT DISTINCT hides the problem on counts while leaving it in the sums. The second benefit concerns cohorts: the cohort_retention derived table carries each cohort's size across all its rows with FIRST_VALUE, so the denominator does not depend on which months the user filters.

Personal data

Names, email and date of birth live in dim_customers next to the business attributes so the join stays simple, and each field carries required_access_grants: [pii_access]. A refinement groups them under a "PII" label, and the derived age tier inherits the grant of the date of birth it descends from. Row filtering by country is a separate mechanism driven by the same user attribute: an analyst scoped to one country sees that country's rows and no personal data, an administrator sees everything.

The reference repository, step by step

The code is public: looker-semantic-layer-fintech, MIT licence.

The data. With no suitable public dataset, a deterministic generator (data/generate.py, standard library only) produces nine CSV files loaded as dbt seeds: 5,000 customers with SCD2 history, accounts, cards, 1,200 merchants with MCC codes, 159,000 transactions over 24 months, chargebacks with scheme reason codes, loans with monthly snapshots, daily FX rates. The output is byte-identical on any machine, and CI fails if a CSV was edited by hand.

dbt. Staging, two intermediate models, four facts and three dimensions, 134 tests including business invariants: a chargeback only exists on a settled card payment, a declined attempt carries no revenue. The glossary lists 19 business metrics and 37 components, M1 to M56, and the linter rejects a measure whose identifier is not in it.

LookML. Six Explores, each anchored on one fact at one grain, many_to_one joins only, one datagroup, one retention derived table, one access grant and a country filter. The weekly_business_review dashboard has seven tiles and three filters.

Continuous integration. Three jobs: seed regeneration, dbt build incremental on pull requests, and a linter that, beyond the usual rules, resolves every field reference in views, Explores and the dashboard before merge.

FAQ

Why does our internal chargeback rate differ from the acquirer's?

Because the scheme counts its own transaction codes, applies its exclusions, and Mastercard divides by the preceding month's transactions. The internal metric should be defined once, explained, and tracked as a leading signal; exact reconciliation happens on the acquirer's report.

Do we need one model per currency?

No. One conversion in dbt, at the dated rate relevant to each fact, with a EUR column next to the native amount. Native amounts stay visible with a warning not to sum them.

How should settlement lag be handled?

By adding a settlement date to the fact and a "TPV by settlement date" variant, which the repository lists as the next step. The essential point is never to let an authorised attempt enter TPV before it settles.

Does the repository work on our own data?

The staging layer is the only one that knows the generator. Replace the nine stg_ models with your tables and keep the output column names: the intermediate, marts and LookML layers stay identical.

Next step

If your weekly review shows one TPV in finance and another in product, start with the status table above: one row per status, one decision per row. The repository then shows how that table becomes a boolean, a measure and a tile.

See the Semantic layer on BigQuery, dbt and Looker service, and read next: Why your numbers disagree and LookML audit checklist.

Sources

• Visa, Visa Acquirer Monitoring Program Overview, 2025 fact sheet, ratio formula, acquirer and merchant thresholds, enumeration ratio, reduction to 150 basis points on 1 April 2026.
• Chargeback Gurus, Visa Acquirer Monitoring Program (VAMP), programme timeline and replacement of VDMP and VFMP.
• Mastercard via J.P. Morgan, Excessive Chargeback Program, Merchant Program Guide, ratio calculation, ECM and HECM thresholds, exit conditions.
• Checkout.com, What is the Mastercard Excessive Chargeback Program, basis-point formula.