Query disputes and fraud data

Use Sigma or Data Pipeline to retrieve information about disputes and fraud.

The disputes table contains data about all disputes on your account. Each row represents a Dispute object, which is created when a charge is disputed. Each dispute also includes any available data about dispute evidence that you’ve submitted.

The following example provides some preliminary information about the five most recent lost disputes. It joins the disputes and charges tables together using the disputes.charge_id and charges.id columns. Along with a dispute ID, each row contains an associated charge ID, the amount, and the outcome of the ZIP and CVC checks.

select
 date_format(date_trunc('day', disputes.created), '%m-%d-%Y') as day,
 disputes.id,
 disputes.charge_id,
 disputes.amount,
 charges.card_address_zip_check as zip,
 charges.card_cvc_check as cvc
from disputes
inner join charges
on charges.id=disputes.charge_id
where disputes.status = 'lost'
and disputes.reason = 'fraudulent'
order by day desc
limit 5

Using Sigma or Data Pipeline to create reports about your disputes can help you identify fraudulent payments, which you can prevent by using Radar.

Radar data

If your plan supports this feature, you have a table ( radar_rules) that contains all custom rules with their action and predicate. You can use this to obtain the rule_id, which you use in the rule_decisions table to find all charges affected by rules. This provides more information than looking at the outcome_rule_id attribute in the charges table, as it also shows 3DS rules triggered for PaymentIntents and SetupIntents. Radar’s built-in rules have fixed rule IDs.

The following example shows recent payments allowed by an allow-list and their Radar score to check if potentially fraudulent payments were allowed:

select
 outcome_type,
 card_cvc_check,
 count(*) as cnt,
 avg(outcome_risk_score) as avg_risk_score
from
 charges
where
 outcome_rule_id = 'allow_if_in_allowlist'
 and created >= current_date - interval '14' day
group by
 1,
 2

Platform data

Multiparty payment businesses such as Connect platforms have particular risk management requirements. Here’s an example of listing destination charge businesses on your platform by their dispute rate:

select
 m.value as merchant_external_account_id,
 c.destination_id,
 arbitrary(a.business_name) as destination_name,
 count(*) as cnt_charges,
 count_if(c.paid) as cnt_success_charges,
 count_if(c.paid) * 1.0 / count(*) as success_rate,
 if(
 count_if(dispute_id is not null) > 0,
 count_if(c.paid) * 1.0 / count_if(c.paid),
 0.0
 ) as dispute_rate
from
 charges c
 left join charges_metadata m on m.charge_id = c.id
 and m.key = 'merchant_external_account_id'
 join connected_accounts a on a.id = c.destination_id
where
 c.created >= current_date - interval '120' day
group by
 1,2
order by dispute_rate desc

3D Secure data

Sigma and Data Pipelines contains data on 3D Secure Authentication ( 3DS). This more complex example shows for each 3DS Rule how many times it triggered 3DS and what the outcomes were, considering there might be more than one attempt:

select
 rd.rule_id,
 count(distinct rd.id) as cnt_rule_triggered,
 count(distinct rd.payment_intent_id) * 1.0 / count(distinct rd.id) * 100.0 as pct_pis,
 count_if(at.is_final_attempt) * 1.0 / count(distinct rd.id) * 100.0 as pct_final_attempts,
 count_if(
 at.is_final_attempt
 and at.threeds_outcome_result = 'authenticated'
 ) * 1.0 / count(distinct rd.id) * 100.0 as pct_3ds_final_authenticated,
 count_if(
 at.threeds_outcome_result = 'authenticated'
 and at.charge_outcome = 'authorized'
 ) * 1.0 / count(distinct rd.id) * 100.0 as pct_3ds_authorized
from
 rule_decisions rd
 left join authentication_report_attempts at on at.intent_id = rd.payment_intent_id
where
 action = 'request_credentials'
 and rd.created >= current_date - interval '30' day
group by
 1

All Radar rule attributes and decisions

You also have access to the radar_rule_attributes table. Each row contains most of the Radar rule attribute values for a single charge. You can join the radar_rule_attributes and disputes tables together using the radar_rule_attributes.transaction_id and disputes.charge_id columns, which allows you to write rules targeting your disputes and understand trends in your good and bad customers.

select
 card_3d_secure_support,
 is_3d_secure_authenticated,
 cvc_check,
 avg(risk_score) as avg_risk_score,
 avg(total_charges_per_card_number_all_time) as avg_total_charges_per_card_number_all_time,
 count(*) as cnt_disputes
from
 radar_rule_attributes r
 join disputes d on r.transaction_id = d.charge_id
where
 d.created >= current_date - interval '60' day
group by
 1,2,3
order by
 cnt_disputes desc

For more details about the available columns, see how to continuously improve your fraud management with Radar and Stripe Data.

Tracking monitoring programs

Metrics for card brand monitoring programs are difficult to track because rules are specific. Closely track when to use volume or transaction count because they’re required to estimate fraud and chargeback levels and take action promptly. This is because monitoring program notifications don’t happen immediately. We recommend a continuous process to track and estimate chargeback and fraud metrics.

With Sigma, you can write a query to estimate fraud levels that simulate how card monitoring programs might assess your payments. The query below isn’t perfect (for example, we assume this is a US business where domestic and cross-border payments are counted, but you can adjust the query for your use case). Most importantly, it takes FX (currency exchange rates) into account, and applies the same method of counting payment and fraud periods independently as the monitoring programs typically do.