# Interroger les données de litige et de fraude

Utilisez Sigma ou Data Pipeline pour récupérer des informations sur les litiges et les fraudes.

Le tableau `disputes` contient des données relatives à tous les [litiges](https://docs.stripe.com/disputes.md) afférents à votre compte. Chaque ligne représente un objet [Dispute](https://docs.stripe.com/api.md#dispute_object)  créé lorsqu’un paiement est contesté. Chaque litige mentionne également toutes les données disponibles sur les preuves de litige qui ont été soumises.
![](https://b.stripecdn.com/docs-statics-srv/assets/disputes.f293434123d316ff4fafe2524e9b2b0d.png)

L’exemple suivant fournit des informations préliminaires sur les cinq derniers litiges perdus. Il joint les tables `disputes` et `charges` à l’aide des colonnes `disputes.charge_id` et `charges.id`. En plus de l’ID du litige, chaque ligne contient l’ID du paiement correspondant, le montant, ainsi que le résultat des vérifications du code postal et du code CVC.

```sql
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
```

| jour | id | charge_id | montant | code postal | cvc |
| --- | --- | --- | --- | --- | --- |
| 28/09/2026 | dp_orWziM4j7CiRL8J | ch_orWziM4j7CiRL8J | 1 000 |  | réussite |
| 28/09/2026 | dp_orWziM4j7CiRL8J | ch_orWziM4j7CiRL8J | 1 000 | réussite | échec |
| 28/09/2026 | dp_orWziM4j7CiRL8J | ch_orWziM4j7CiRL8J | 1 000 | échec | échec |
| 28/09/2026 | dp_orWziM4j7CiRL8J | ch_orWziM4j7CiRL8J | 1 000 |  | réussite |
| 28/09/2026 | dp_orWziM4j7CiRL8J | ch_orWziM4j7CiRL8J | 1 000 |  | réussite |

En utilisant Sigma ou Data Pipeline pour générer des rapports sur vos litiges, vous pourrez ainsi identifier les paiements frauduleux et les prévenir grâce à *Radar* (Stripe Radar helps detect and block fraud for any type of business using machine learning that trains on data across millions of global companies. It’s built into Stripe and requires no additional setup to get started).

## Données de Radar

Si [votre offre](https://docs.stripe.com/radar/how-radar-works.md#compare-plans) prend en charge cette fonctionnalité, vous avez accès à un tableau (`radar_rules`) qui contient toutes les règles Radar personnalisées avec leur action et leur prédicat. Vous pouvez vous en servir pour obtenir le `rule_id` qui peut ensuite être utilisé dans le tableau `rule_decisions` pour trouver tous les paiements affectés par les règles. Cette méthode est plus flexible que l’examen de l’attribut `outcome_rule_id` dans le tableau `charges`, car il affiche également les règles 3DS déclenchées pour les PaymentIntents et SetupIntents. Les [règles intégrées](https://docs.stripe.com/radar/rules.md#built-in-rules) de Radar ont des ID de règle fixes.

L’exemple suivant montre les paiements récents autorisés en vertu d’une liste blanche et leur score Radar afin de vérifier si des paiements potentiellement frauduleux ont été autorisés :

```sql
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
```

### Données de la plateforme

Les entreprises de paiements à destination de plusieurs bénéficiaires, tels que les plateformes Connect, doivent répondre à des [exigences particulières en matière de gestion des risques](https://docs.stripe.com/connect/risk-management.md). Voici un exemple de liste d’entreprises recevant des [paiements indirects](https://docs.stripe.com/connect/destination-charges.md) sur votre plateforme, classées par taux de litige :

```sql
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
```

### Données de 3D Secure

Sigma et Data Pipelines contiennent des données sur l’authentification 3D Secure (*3DS* (3D Secure (3DS) provides an additional layer of authentication for credit card transactions that protects businesses from liability for fraudulent card payments)). Cet exemple plus complexe montre, pour chaque règle 3DS, combien de fois l’authentification 3DS a été déclenchée et quels ont été les résultats, sachant qu’il peut y avoir plus d’une tentative :

```sql
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
```

### Tous les attributs et toutes les décisions des règles Radar

Vous avez également accès au tableau `radar_rule_attributes`. Chaque ligne contient la plupart des valeurs des [attributs de règles Radar](https://docs.stripe.com/radar/rules/reference.md#supported-attributes) pour un seul [paiement](https://docs.stripe.com/api/charges/object.md). Vous pouvez joindre les tableaux `radar_rule_attributes` et `disputes` à l’aide des colonnes `radar_rule_attributes.transaction_id` et `disputes.charge_id`, ce qui vous permet de rédiger des règles ciblant vos litiges et de comprendre les tendances de vos bons et mauvais clients.

```sql
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
```

Pour plus de détails sur les colonnes disponibles, découvrez comment [améliorer continuellement votre gestion de la fraude avec Radar et Stripe Data.](https://stripe.com/guides/improve-fraud-management-with-radar-and-stripe-data).

## Suivi de programmes de surveillance

Les indicateurs des [programmes de surveillance](https://docs.stripe.com/disputes/monitoring-programs.md) des marques de cartes bancaires sont difficiles à suivre, car les règles sont spécifiques. Surveillez attentivement quand utiliser le volume ou le nombre de transactions, car ils sont nécessaires pour estimer les niveaux de fraude et de rétrofacturation et agir rapidement. En effet, les notifications des programmes de surveillance ne sont pas immédiates. Nous recommandons un [processus continu](https://stripe.com/guides/improve-fraud-management-with-radar-and-stripe-data) pour suivre et estimer les indicateurs de rétrofacturation et de fraude.

Avec Sigma, vous pouvez écrire une requête permettant d’estimer les niveaux de fraude et de simuler la manière dont les programmes de surveillance des réseaux de cartes pourraient évaluer vos paiements. La requête ci-dessous n’est pas parfaite (par exemple, nous supposons qu’il s’agit d’une entreprise américaine pour laquelle les paiements nationaux et transfrontaliers sont pris en compte, mais vous pouvez adapter la requête à votre propre cas d’usage). L’élément le plus important est qu’elle prend en compte les taux de change de devise et applique la même méthode de comptabilisation indépendante des périodes de paiement et des périodes de fraude que celle généralement utilisée par les programmes de surveillance.

```sql
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,
  	arbitrary(date_trunc('month', p.captured_at)) as month_datetime,
    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
),
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 VSEFP
    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 VAMP 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 non_fraud_dispute_count_all,
    count(distinct if(d.reason = 'fraudulent', d.id)) as fraud_dispute_count_all,
    count(
      distinct if(
        d.status = 'prevented',
        d.id
      )
    ) as dispute_count_rdr,
    count(
      distinct if(
        d.status != 'prevented',
        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.status != 'prevented',
        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 can't 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.status != 'prevented',
        d.amount / fx.rate / 100.0
      )
    ) as dispute_volume_usd_exrdr,
    sum(
      if(
        d.reason = 'fraudulent'
        and d.status != 'prevented',
        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 VAMP/VSEFP/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 VSEFP
  round(e.efw_volume_3ds_us_usd, 2) as efw_volume_3ds_us_usd,
  -- Used for VAMP/ECP/ECM/HEC
  d.dispute_count_all,
  d.dispute_count_rdr,
  d.non_fraud_dispute_count_all,
  d.fraud_dispute_count_all,
  d.dispute_count_exrdr,
  d.dispute_count_3ds,
  d.fraud_dispute_count_3ds,
  d.fraud_dispute_count_exrdr,
  round(d.dispute_volume_usd_all, 2) as dispute_volume_usd_all,
  -- Used for EFM
  round(d.fraud_dispute_volume_usd_all, 2) as fraud_dispute_volume_usd_all,
  round(d.dispute_volume_usd_exrdr, 2) as dispute_volume_usd_exrdr,
  round(d.fraud_dispute_volume_usd_exrdr, 2) as fraud_dispute_volume_usd_exrdr,
  d.win_rate,
  -- we show all the values below for all networks for comparison but they're only relevant for the indicated ones
  -- ECP/ECM/HECM based on prior month sales
  d.dispute_count_all * 100.00 / greatest(1,lag(p.sales_count, 1) over (
    order by
      p.network,
      p.start_of_month
  )) as all_chargeback_ratio_for_mastercard_percent,
  -- VSEFP
  e.efw_volume_3ds_us_usd * 100.00 / greatest(1,p.sales_volume_3ds_us_usd) as fraud_ratio_for_visa_3ds_percent,
  -- VAMP
  (d.dispute_count_exrdr + e.efw_count) * 100.00 / greatest(1,p.sales_count) as vamp_ratio_for_visa_percent,
  d.dispute_count_exrdr + e.efw_count as vamp_count_for_visa,
  -- EFM based on prior month sales
  -- Note different limits for Australia and 3DS regulations are not reflected here
  d.fraud_dispute_count_all * 100.00 / greatest(1,lag(p.sales_count, 1) over (
    order by
      p.network,
      p.start_of_month
  )) as fraud_ratio_for_mastercard_percent
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;
```