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

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

disputes テーブルには、アカウントのすべての不審請求の申請に関するデータが収録されます。各行は、支払いに対して不審請求が申請された際に作成される Dispute (不審請求の申請) オブジェクトを表します。不審請求の申請のそれぞれには、提出した不審請求の申請の反証資料に関して利用可能なデータも含まれます。

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

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	金額	郵便番号	セキュリティコード
2024/12/14	dp_Vc4o1t1LcNmekPy	ch_NgEZPP8681uRFfL	1,000		成功
2024/12/14	dp_AdNiLsoUzs3Ephw	ch_He4UEdo4814ChDv	1,000	成功	失敗
2024/12/14	dp_SNMevd8pdIsNIY4	ch_0P56ulgUC1uCxbw	1,000	失敗	失敗
2024/12/14	dp_AeMrhF9njoFogFy	ch_mTLUSsnCoCxJFGy	1,000		成功
2024/12/14	dp_VmTiRJeMCvKBOqj	ch_SSsSvrSTdYFslLc	1,000		成功

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

Radar for Teams のデータ

Radar for Teams を使用する場合、操作と述語を伴う Radar のカスタムルールをすべて含む radar_rules テーブルを利用できます。このテーブルで rule_id を取得し、それを rule_decisions テーブルで使用して、ルールの影響を受けるすべての支払いを検索できます。この方法では、Payment Intents と Setup Intents でトリガーされる 3DS ルールも表示されるため、charges テーブルで outcome_rule_id 属性を調べるよりも柔軟性は高くなります。Radar の構築済みのルールには固定のルール ID も含まれます。

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

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 プラットフォームなどの複数の売り手がかかわる決済ビジネスには、特定のリスク管理要件が適用されます。以下は、プラットフォームのデスティネーション支払い企業を不審請求の申請率別に一覧表示した例です。

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 Pipeline には 3D セキュア認証 (3DS) のデータが格納されます。この複雑な例では、それぞれの 3DS ルールについて、複数回試行されることを考慮して、3DS をトリガーした回数とその結果を示しています。

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 つの支払いに対するほぼすべての Radar ルール属性値が含まれます。radar_rule_attributes.transaction_id と disputes.charge_id の各列を使用して、radar_rule_attributes と disputes テーブルを結合することもできます。これにより、不審請求の申請を対象とするルールを作成し、正当な顧客と不正利用する顧客の傾向を把握できるようになります。

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 を活用して不正利用対策を継続的に改善する方法のガイドをご覧ください。たとえば、支払いごとの Radar スコアを確認できる場所などについて説明しています。

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

カードブランドのモニタリングプログラムの指標は、ルールが非常に細かいため追跡するのが困難です。金額と取引件数をそれぞれ使用するタイミングなどの情報は非常に重要です。モニタリングプログラムの通知は即時実行されないので、不正利用とチャージバックレベルを予測してすぐに対処するには、これらの情報を追跡する必要があります。継続的なプロセスでチャージバックと不正利用の指標を追跡することをお勧めします。

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

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;