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

day	id	charge_id	amount	zip	cvc
14/12/2024	dp_XGQlcFT7W4a3M2X	ch_1My7QCZCnyRbsSb	1,000		pass
14/12/2024	dp_AGVV7ks7x4AgOXC	ch_e621zGN5DnnZ40j	1,000	pass	fail
14/12/2024	dp_h2M1QdxJuMNrr3P	ch_dd3IwwdxjuiI6pE	1,000	fail	fail
14/12/2024	dp_DwSAmrdRfwt1YrI	ch_uP2oJQGcPe2O0uP	1,000		pass
14/12/2024	dp_3xsxkh2DhlXNI3a	ch_mFTNyIsEuTF4dEY	1,000		pass

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 for Fraud Teams Data

If you use Radar for Fraud Teams, you have a table radar_rules that contains all Radar custom rules with their action and predicate. You can use this to obtain the rule_id which can then be used in rule_decisions table to find all charges affected by rules. This is more flexible than looking at the outcome_rule_id attribute in the charges table, as it also shows 3DS Rules triggered for Payment Intents and Setup Intents. 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 on columns available see our guide on How to continuously improve your fraud management with Radar for Fraud Teams and Stripe Data. It explains, for instance, where to find Radar scores per Charges and so on.

Tracking Monitoring Programmes

Card brand monitoring programme metrics are difficult to track because rules are very specific. Some details are crucial, such as when to use volume or transaction count. Tracking them is required to estimate fraud and chargeback levels and take action promptly, because monitoring programme 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 programmes might assess your payments. The query below isn’t perfect (for example, we assume this is a US merchant 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 programmes typically do.

with exchange_rates as (
  select
    date,
    currency,
    rate
  from
    exchange_rates_from_usd
    cross join unnest (
      cast(
        json_parse(buy_currency_exchange_rates) as map(varchar, double)
      )
    ) as t(currency, rate)
  where
    date = (
      select
        max(date)
      from
        exchange_rates_from_usd
    ) -- note the calculation for jpy is decimal and may look off
),
payments as (
  select
    -- technically these values are calculated per statement descriptor for CNP but we assume this equals merchant
    date_format(p.captured_at, '%Y-%m-01') as start_of_month,
    if(
      p.card_brand = 'Visa'
      or p.card_brand = 'MasterCard',
      p.card_brand,
      'Other'
    ) as network,
    count(*) as sales_count,
    -- For US, both Cross-Border and Domestic charges are counted
    -- we can ignore this in CBMP but show it here just to get a magnitude
    count_if(p.card_country != 'US') as sales_count_crossborder,
    count_if(pmd.card_3ds_succeeded) as sales_count_3ds,
    sum(p.amount / fx.rate / 100.0) as sales_volume_usd,
    sum(
      if(
        p.card_country != 'US',
        p.amount / fx.rate / 100.0
      )
    ) as sales_volume_crossborder_usd,
    sum(
      if(
        p.card_country = 'US'
        and pmd.card_3ds_succeeded,
        p.amount / fx.rate / 100.0
      )
    ) as sales_volume_3ds_us_usd
  from
    charges p
    join exchange_rates fx on p.currency = fx.currency
    left join payment_method_details pmd on pmd.charge_id = p.id
    -- for more information you may use
    -- left join authentication_report_attempts aa on attempt_id intent_id
  where
    p.captured_at >= date_trunc('month', current_date - interval '150' day)
    -- CBMPs only consider cleared amounts; refunds still count in the volume unless reversed
    and p.status = 'succeeded'
    and p.payment_method_type = 'card'
  group by
    1,
    2 -- used for VFMP
),
efw as (
  select
    date_format(e.created, '%Y-%m-01') as start_of_month,
    if(
      c.card_brand = 'Visa'
      or c.card_brand = 'MasterCard',
      c.card_brand,
      'Other'
    ) as network,
    -- For US, both Cross-Border and Domestic charges are counted
    -- count_if(card_country != 'US') as efw_count_crossborder
    count(distinct c.id) as efw_count,
    count(distinct if(pmd.card_3ds_succeeded, c.id)) as efw_count_3ds,
    sum(c.amount / fx.rate / 100.0) as efw_volume_usd,
    sum(
      if(
        pmd.card_3ds_succeeded,
        c.amount / fx.rate / 100.0
      )
    ) as efw_volume_3ds_usd,
    -- for VFMP-3DS
    sum(
      if(
        c.card_country = 'US'
        and pmd.card_3ds_succeeded,
        c.amount / fx.rate / 100.0
      )
    ) as efw_volume_3ds_us_usd
  from
    early_fraud_warnings e
    join charges c on e.charge_id = c.id
    join exchange_rates fx on c.currency = fx.currency
    left join payment_method_details pmd on pmd.charge_id = c.id
  where
    e.created >= date_trunc('month', current_date - interval '150' day)
  group by
    1,
    2 -- used for VDMP and ECM/ECP
),
disputes as (
  select
    date_format(d.created, '%Y-%m-01') as start_of_month,
    if(
      c.card_brand = 'Visa'
      or c.card_brand = 'MasterCard',
      c.card_brand,
      'Other'
    ) as network,
    -- For US, both Cross-Border and Domestic charges are counted
    -- count_if(card_country != 'US') as dispute_count_crossborder
    -- Because a payment can have multiple disputes, we count the disputes here
    count(distinct d.id) as dispute_count_all,
    count(distinct if(d.reason = 'fraudulent', d.id)) as fraud_dispute_count_all,
    count(
      distinct if(
        d.network_details_visa_rapid_dispute_resolution,
        d.id
      )
    ) as dispute_count_rdr,
    count(
      distinct if(
        d.network_details_visa_rapid_dispute_resolution is null
        or not d.network_details_visa_rapid_dispute_resolution,
        d.id
      )
    ) as dispute_count_exrdr,
    count(distinct if(pmd.card_3ds_succeeded, d.id)) as dispute_count_3ds,
    count(
      distinct if(
        d.reason = 'fraudulent'
        and pmd.card_3ds_succeeded,
        d.id
      )
    ) as fraud_dispute_count_3ds,
    count(
      distinct if(
        d.reason = 'fraudulent'
        and (
          d.network_details_visa_rapid_dispute_resolution is null
          or not d.network_details_visa_rapid_dispute_resolution
        ),
        d.id
      )
    ) as fraud_dispute_count_exrdr,
    count_if(d.status = 'won') * 1.0 / count_if(
      d.status = 'won'
      or d.status = 'lost'
    ) as win_rate,
    -- The sum of disputes should match and cannot exceed the payment
    sum(d.amount / fx.rate / 100.0) as dispute_volume_usd_all,
    sum(
      if(
        reason = 'fraudulent',
        d.amount / fx.rate / 100.0
      )
    ) as fraud_dispute_volume_usd_all,
    sum(
      if(
        (
          d.network_details_visa_rapid_dispute_resolution is null
          or not d.network_details_visa_rapid_dispute_resolution
        ),
        d.amount / fx.rate / 100.0
      )
    ) as dispute_volume_usd_exrdr,
    sum(
      if(
        d.reason = 'fraudulent'
        and (
          d.network_details_visa_rapid_dispute_resolution is null
          or not d.network_details_visa_rapid_dispute_resolution
        ),
        d.amount / fx.rate / 100.0
      )
    ) as fraud_dispute_volume_usd_exrdr
  from
    disputes d
    join charges c on d.charge_id = c.id
    join exchange_rates fx on c.currency = fx.currency
    left join payment_method_details pmd on pmd.charge_id = c.id
  where
    -- current month data will usually be off due to dispute delays,
    -- we still show it as an indicator but it's better tracked weekly
    d.created >= date_trunc('month', current_date - interval '150' day)
  group by
    1,
    2
)
select
  -- theoretically this might cause gaps if there is a month
  -- without payments but a helper table with continuous dates would complicate this example query
  p.start_of_month,
  p.network,
  -- Used for VDMP/ECP/ECM/HEC
  p.sales_count,
  lag(p.sales_count, 1) over (
    order by
      p.network,
      p.start_of_month
  ) as sales_count_prior_month,
  p.sales_count_crossborder,
  p.sales_count_3ds,
  -- Used for VFMP
  p.sales_volume_usd,
  p.sales_volume_crossborder_usd,
  p.sales_volume_3ds_us_usd,
  e.efw_count,
  e.efw_count_3ds,
  -- Used for VFMP
  e.efw_volume_usd,
  e.efw_volume_3ds_usd,
  -- Used for VFMP-3DS
  e.efw_volume_3ds_us_usd,
  -- Used for VDMP/ECP/ECM/HEC
  d.dispute_count_all,
  d.dispute_count_rdr,
  d.fraud_dispute_count_all,
  d.dispute_count_exrdr,
  d.dispute_count_3ds,
  d.fraud_dispute_count_3ds,
  d.fraud_dispute_count_exrdr,
  d.dispute_volume_usd_all,
  -- Used for EFM
  d.fraud_dispute_volume_usd_all,
  d.dispute_volume_usd_exrdr,
  d.fraud_dispute_volume_usd_exrdr,
  d.win_rate,
  -- we show all the values below for all networks for comparison but they are only relevant for the indicated ones
  -- VDMP deducting RDR actuals
  if(
    p.network = 'visa',
    d.dispute_count_exrdr,
    d.dispute_count_all
  ) * 1.0 / p.sales_count as rdr_chargeback_ratio_for_visa,
  -- ECP/ECM/HECM based on prior month sales
  d.dispute_count_all * 1.0 / lag(p.sales_count, 1) over (
    order by
      p.network,
      p.start_of_month
  ) as all_chargeback_ratio_for_mastercard,
  -- VDMP ignoring RDR and ECP/ECM/HECM for crosscheck
  d.dispute_count_all * 1.0 / p.sales_count as all_chargeback_ratio_for_visa_and_mastercard,
  -- VFMP
  e.efw_volume_usd * 1.0 / p.sales_volume_usd as fraud_ratio_for_visa,
  -- VFMP-3DS
  e.efw_volume_3ds_us_usd * 1.0 / p.sales_volume_3ds_us_usd as fraud_ratio_for_visa_3ds,
  -- EFM based on prior month sales
  d.fraud_dispute_count_all * 1.0 / lag(p.sales_count, 1) over (
    order by
      p.network,
      p.start_of_month
  ) as fraud_ratio_for_mastercard
from
  payments p
  left join efw e on p.start_of_month = e.start_of_month
  and p.network = e.network
  left join disputes d on p.start_of_month = d.start_of_month
  and p.network = d.network
order by
  start_of_month desc,
  network;