This analysis demonstrates how payment platforms and ecommerce businesses can use transaction-level data to balance approval rates and loss exposure when dealing with disputed transactions.
Payments platforms face a constant tradeoff:
At the same time, not all merchants perform equally well. Some experience rising decline rates or loss exposure over time and require targeted intervention.
Core business questions:
The analysis uses order-level data with the following key fields:
amount: transaction valuedecision: historical approve / decline outcomedispute_flag: indicator of post-approval disputes (“chargebacks”)ip_risk_score: model-generated risk score (higher = riskier)merchant_id, merchant_name, industryorder_dateTo ensure consistency, all downstream queries rely on SQL views that encode business definitions such as:
These views are defined once and reused across analyses.
Before using ip_risk_score operationally, we validate its behavior.
We examine decline rates and dispute rates across score bands.
library(DBI)
library(duckdb)
library(readr)
library(ggplot2)
library(tidyverse)
run_sql_file <- function(con, path) {
sql <- read_file(path)
dbExecute(con, sql)
}
con <- dbConnect(duckdb::duckdb(), dbdir = ":memory:")
run_sql_file(con, "sql/00_load.sql")[1] 225run_sql_file(con, "sql/01_views.sql")[1] 0run_sql_file(con, "sql/30_review_queue.sql")[1] 0score_validation <- dbGetQuery(con, "
SELECT
CASE
WHEN ip_risk_score < 0.2 THEN '<0.2'
WHEN ip_risk_score < 0.4 THEN '0.2–0.4'
WHEN ip_risk_score < 0.6 THEN '0.4–0.6'
WHEN ip_risk_score < 0.8 THEN '0.6–0.8'
ELSE '0.8–1.0'
END AS score_band,
COUNT(*) AS n,
AVG(CASE WHEN decision = 'declined' THEN 1 ELSE 0 END) AS decline_rate,
AVG(CASE WHEN chargeback_flag = 1 THEN 1 ELSE 0 END) AS chargeback_rate
FROM orders
GROUP BY 1
ORDER BY 1;
")
score_validation score_band n decline_rate chargeback_rate
1 0.2–0.4 8098 0.08804643 0.005186466
2 0.4–0.6 6262 0.09294155 0.005908655
3 0.6–0.8 1700 0.15117647 0.007647059
4 0.8–1.0 178 0.24719101 0.022471910
5 <0.2 3811 0.09052742 0.005510365score_validation %>%
pivot_longer(cols = c(decline_rate, chargeback_rate),
names_to = "metric",
values_to = "rate") |>
ggplot(aes(x = score_band, y = rate, fill = metric)) +
geom_col(position = "dodge") +
labs(
title = "Decline and Chargeback Rates by Risk Score Band",
x = "Risk Score Band",
y = "Rate"
)
Both decline rates and dispute rates rise sharply at higher score ranges, particularly above 0.6. This confirms that higher ip_risk_score values correspond to higher transaction risk.
We next simulate approval policies based on score thresholds via the following logic:
Approve transactions with ip_risk_score ≤ threshold
Decline transactions above the threshold
A “90% approval” policy corresponds approximately to the 90th percentile of the score distribution.
threshold_results <- dbGetQuery(con, "
WITH thresholds AS (
SELECT 0.70 AS q, quantile_cont(ip_risk_score, 0.70) AS threshold FROM orders
UNION ALL
SELECT 0.80 AS q, quantile_cont(ip_risk_score, 0.80) AS threshold FROM orders
UNION ALL
SELECT 0.90 AS q, quantile_cont(ip_risk_score, 0.90) AS threshold FROM orders
UNION ALL
SELECT 0.95 AS q, quantile_cont(ip_risk_score, 0.95) AS threshold FROM orders
),
sim AS (
SELECT
t.q,
t.threshold,
AVG(CASE WHEN o.ip_risk_score <= t.threshold THEN 1 ELSE 0 END) AS approval_rate,
SUM(CASE WHEN o.ip_risk_score <= t.threshold AND o.chargeback_flag = 1 THEN o.amount ELSE 0 END) * 1.0
/ NULLIF(SUM(CASE WHEN o.ip_risk_score <= t.threshold THEN o.amount ELSE 0 END), 0) AS loss_per_approved_dollar
FROM thresholds t
CROSS JOIN orders o
GROUP BY 1,2
)
SELECT *
FROM sim
ORDER BY q;
")ggplot(threshold_results,
aes(x = approval_rate, y = loss_per_approved_dollar)) +
geom_point(size = 3) +
geom_line() +
labs(
title = "Approval Rate vs Loss per Approved Dollar",
x = "Approval Rate",
y = "Loss per Approved Dollar"
) 
Higher approval targets increase loss exposure non-linearly. The curve highlights a region where marginal approvals come at sharply higher cost. This provides a quantitative basis for selecting an operating threshold based on risk tolerance.
Beyond global policy, we monitor merchant-level performance over time. We use monthly merchant KPIs to detect:
Rising dispute rates
Falling approval rates
Sufficient volume to be operationally meaningful
review_queue <- dbGetQuery(con, "
SELECT
merchant_name,
industry,
order_month,
orders,
approval_rate,
chargeback_rate_on_approved,
loss_per_approved_dollar,
cb_rate_delta,
approval_rate_delta
FROM review_queue
WHERE cb_rate_delta IS NOT NULL
ORDER BY cb_rate_delta DESC
LIMIT 10;
")
review_queue merchant_name industry order_month orders approval_rate
1 Velvet Threads Apparel 2025-10-01 1760 0.8420455
2 LuxTime Luxury 2025-10-01 591 0.8612521
3 Aurora Electronics Electronics 2025-10-01 2169 0.9036422
4 GreenHome HomeGoods 2025-10-01 1032 0.9476744
5 SkyTravel Travel 2025-10-01 1200 0.8858333
chargeback_rate_on_approved loss_per_approved_dollar cb_rate_delta
1 0.010121457 0.0073473494 0.004738304
2 0.013752456 0.0214717515 0.002101970
3 0.006632653 0.0078700580 0.001306421
4 0.001022495 0.0004270196 -0.003359199
5 0.004703669 0.0047840864 -0.003790904
approval_rate_delta
1 -0.0794602356
2 -0.0006725711
3 -0.0104278757
4 0.0046073846
5 -0.0011486675Merchants flagged for review exhibit:
Statistically meaningful volume
Increasing dispute rates
Simultaneous drops in approval rates
These merchants represent candidates for operational outreach, policy tuning, or additional controls.
Use risk score thresholds as a primary policy lever
Thresholds near the 90th percentile balance approval volume and loss exposure
More aggressive thresholds produce diminishing returns
Monitor merchants, not just transactions
Aggregate KPIs reveal deterioration before absolute loss becomes large
Month-over-month deltas are more informative than levels alone
Operationalize early warning signals
Flagged merchants can be reviewed proactively
Policies can be selectively tightened rather than globally applied
View the repo for this analysis here: https://github.com/bmdknndy/TransactionFraudRiskAnalytics
dbDisconnect(con, shutdown = TRUE)