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

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/10/2 | dp_orWziM4j7CiRL8J | ch_orWziM4j7CiRL8J | 1,000 |  | 通過 |
| 2026/10/2 | dp_orWziM4j7CiRL8J | ch_orWziM4j7CiRL8J | 1,000 | 通過 | 失敗 |
| 2026/10/2 | dp_orWziM4j7CiRL8J | ch_orWziM4j7CiRL8J | 1,000 | 失敗 | 失敗 |
| 2026/10/2 | dp_orWziM4j7CiRL8J | ch_orWziM4j7CiRL8J | 1,000 |  | 通過 |
| 2026/10/2 | 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 のデータ

[ご利用のプラン](https://docs.stripe.com/radar/how-radar-works.md#compare-plans)で機能がサポートされている場合、すべての Radar カスタムルールおよびそのアクションと述語を含むテーブル (`radar_rules`) があります。これを使用して `rule_id` を取得し、`rule_decisions` テーブルで使用して、ルールの影響を受けるすべての請求を見つけることができます。この方法では、PaymentIntent および SetupIntent に対してトリガーされた 3DS ルールも表示されるため、`charges` テーブルの `outcome_rule_id` 属性を見るよりも多くの情報が提供されます。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 と Stripe データによる、不正利用対策の継続的な改善](https://stripe.com/guides/improve-fraud-management-with-radar-and-stripe-data)をご覧ください。

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

カードブランドの[モニタリングプログラム](https://docs.stripe.com/disputes/monitoring-programs.md)の指標は、ルールが固有であるため追跡が困難です。取引量と取引件数をいつ使用するかを綿密に追跡してください。これらは不正利用とチャージバックのレベルを推定し、迅速な対応を取るために必要です。これは、モニタリングプログラムの通知がすぐには届かないためです。チャージバックと不正利用の指標を追跡し、推定するための[継続的なプロセス](https://stripe.com/guides/improve-fraud-management-with-radar-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 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;
```