# 不審請求の申請と不正利用のデータをクエリする

Sigma または Data Pipeline を使用して不審請求の申請と不正使用に関する情報を取得します。

`disputes` テーブルには、アカウントのすべての[不審請求の申請](https://docs.stripe.com/disputes.md)に関するデータが収録されます。各行は、支払いに対して不審請求が申請された際に作成される [Dispute (不審請求の申請)](https://docs.stripe.com/api.md#dispute_object) オブジェクトを表します。不審請求の申請のそれぞれには、提出した不審請求の申請の反証資料に関して利用可能なデータも含まれます。
![](https://b.stripecdn.com/docs-statics-srv/assets/disputes.f293434123d316ff4fafe2524e9b2b0d.png)

次の例では、主張が認められなかった直近の 5 つの不審請求の申請に関する予備的な情報を示しています。ここでは、`disputes.charge_id` 列と `charges.id` 列を使用して、`disputes` テーブルと `charges` テーブルを結合します。各行には、不審請求申請 ID に加えて、関連する支払い ID、金額、および郵便番号とセキュリティコードの確認結果が収録されています。

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

| 日        | ID                 | charge_id          | 金額    | 郵便番号 | cvc |
| -------- | ------------------ | ------------------ | ----- | ---- | --- |
| 2026/4/5 | dp_orWziM4j7CiRL8J | ch_orWziM4j7CiRL8J | 1,000 |      | 成功  |
| 2026/4/5 | dp_orWziM4j7CiRL8J | ch_orWziM4j7CiRL8J | 1,000 | 成功   | 失敗  |
| 2026/4/5 | dp_orWziM4j7CiRL8J | ch_orWziM4j7CiRL8J | 1,000 | 失敗   | 失敗  |
| 2026/4/5 | dp_orWziM4j7CiRL8J | ch_orWziM4j7CiRL8J | 1,000 |      | 成功  |
| 2026/4/5 | dp_orWziM4j7CiRL8J | ch_orWziM4j7CiRL8J | 1,000 |      | 成功  |

Sigma または Data Pipeline を使用して不審請求の申請に関するレポートを作成すると、不正使用による支払いを特定するために役立ちます。不正使用は、*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) を使用して防止できます。

## Radar for Teams のデータ

Radar for Teams を使用している場合、すべての Radar カスタムルールとそのアクションと述語を含むテーブル (`radar_rules`) があります。これを使用して、`rule_id` を取得し、`rule_decisions` テーブルで使用して、ルールの影響を受けるすべての請求を見つけることができます。これは、`charges` テーブルの`outcome_rule_id` 属性を見るよりも、PaymentIntent および SetupIntent に対してトリガされた 3DS ルールも表示されるため、より多くの情報を提供します。Radar の[組み込みルール](https://docs.stripe.com/radar/rules.md#built-in-rules) には、固定ルール ID があります。

以下の例では、不正使用の可能性のある支払いが許可されていないかを確認するために、許可リストによって許可された最近の支払いとその Radar のスコアを表示しています。

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

### プラットフォームデータ

Connect プラットフォームなどの複数の売り手がかかわる決済ビジネスには、[特定のリスク管理要件](https://docs.stripe.com/connect/risk-management.md)が適用されます。以下は、プラットフォームの[デスティネーション支払い](https://docs.stripe.com/connect/destination-charges.md)企業を不審請求の申請率別に一覧表示した例です。

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

### 3D セキュアデータ

Sigma と Data Pipelines には、3D セキュア認証 (*3DS* (3D Secure (3DS) provides an additional layer of authentication for credit card transactions that protects businesses from liability for fraudulent card payments)) に関するデータが含まれています。このより複雑な例は、各 3DS ルールについて、3DS がトリガーされた回数と、複数回の試行を考慮した結果を示しています。

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

### すべての Radar ルールの属性と決定

`radar_rule_attributes` テーブルにもアクセスできます。各行には 1 つの[支払い](https://docs.stripe.com/api/charges/object.md)に対するほぼすべての [Radar ルール属性](https://docs.stripe.com/radar/rules/reference.md#supported-attributes)値が含まれます。`radar_rule_attributes.transaction_id` と `disputes.charge_id` の各列を使用して、`radar_rule_attributes` と `disputes` テーブルを結合することもできます。これにより、不審請求の申請を対象とするルールを作成し、正当な顧客と不正利用する顧客の傾向を把握できるようになります。

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

利用可能な列の詳細については、[Radar for Teams と Stripe Data を活用して不正利用対策を継続的に改善するには](https://stripe.com/guides/improve-fraud-management-with-radar-for-fraud-teams-and-stripe-data)のガイドをご覧ください。たとえば、決済ごとの Radar スコアを確認できる場所などについて説明しています。

## モニタリングプログラムの追跡

[カードブランドのモニタリングプログラム](https://docs.stripe.com/disputes/monitoring-programs.md)の指標は、ルールが非常に細かいため追跡するのが困難です。金額と取引件数をそれぞれ使用するタイミングなどの情報は非常に重要です。モニタリングプログラムの通知は即時実行されないので、不正利用とチャージバックレベルを予測してすぐに対処するには、これらの情報を追跡する必要があります。[継続的なプロセス](https://stripe.com/guides/improve-fraud-management-with-radar-for-fraud-teams-and-stripe-data)でチャージバックと不正利用の指標を追跡することをお勧めします。

Sigma では、クエリを作成して不正使用のレベルを見積もり、カードモニタリングプログラムによる決済の評価をシミュレーションできます。以下のクエリは完全なものではなく (たとえば、ここではアメリカの加盟店を想定しているため国内決済とクロスボーダー決済が対象になりますが、ユースケースに合わせてクエリを調整できます)。一番重要なのは、このクエリが FX (為替レート) を考慮していて、通常モニタリングプログラムが使用するのと同じ方法を適用して、支払い期間と不正使用期間を別々にカウントすることです。

```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 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 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.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 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.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,
  -- Visa deprecated VDMP and VFMP in April 2025 replacing it with VAMP
  p.month_datetime >= date '2025-04-01' as use_vamp_over_vdmpvfmp,
  p.network,
  -- Used for VAMP/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
  round(p.sales_volume_usd, 2) as sales_volume_usd,
  round(p.sales_volume_crossborder_usd, 2) as sales_volume_crossborder_usd,
	round(p.sales_volume_3ds_us_usd, 2) as sales_volume_3ds_us_usd,
  e.efw_count,
  e.efw_count_3ds,
  -- Used for VFMP
	round(e.efw_volume_usd, 2) as efw_volume_usd,
	round(e.efw_volume_3ds_usd, 2) as efw_volume_3ds_usd,
  -- Used for VFMP-3DS
	round(e.efw_volume_3ds_us_usd, 2) as efw_volume_3ds_us_usd,
  -- Used for VAMP/VDMP/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
  -- VDMP deducting RDR actuals (until Apr '25)
  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 (until Apr '25)
  d.dispute_count_all * 1.0 / p.sales_count as all_chargeback_ratio_for_visa_and_mastercard,
  -- VFMP (until Apr '25)
  e.efw_volume_usd * 1.0 / p.sales_volume_usd as fraud_ratio_for_visa,
  -- VFMP-3DS (until Apr '25)
  e.efw_volume_3ds_us_usd * 1.0 / p.sales_volume_3ds_us_usd as fraud_ratio_for_visa_3ds,
  -- VAMP (from Apr '25), RDR counts
  (d.non_fraud_dispute_count_all + e.efw_count) * 1.0 / p.sales_count as vamp_chargeback_ratio_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 * 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;
```