Querying authentication conversion

Use Stripe Sigma to retrieve information about authentication, conversion, and the SCA exemptions used.

See the authentication_report_attempts table under the Analytics Tables section of the Sigma schema. Each row within the authentication_report_attempts table represents data about an individual attempt object. Our full-page documentation also shows the schema in a split-view format.

Attempt conversion information

You can get a report for every attempt, with each PaymentIntent or SetupIntent having possibly more than one attempt.

Observação

In some cases there are multiple attempts for a single transaction, such as when a payment is declined and then retried. To filter to a specific transaction, use the is_final_attempt column. This column is eventually consist after a few days.

The following example query uses the authentication_report_attempts table to retrieve a list of payment intents that were successfully authenticated using the challenge flow.

select
  attempt_id,
  intent_id,
  payment_method,
  threeds_reason as step_up_reason,
  charge_outcome
from authentication_report_attempts
where intent_type = 'payment'
  and threeds_outcome_result = 'authenticated'
  and authentication_flow = 'challenge'
  and is_final_attempt
limit 5

attempt_id	intent_id	payment_method	step_up_reason	charge_outcome
payatt_1IRdZ9F…	pi_1Hn8d…	card_charge	requested_by_radar_rule	authorized
payatt_1I4AFxF…	pi_1J8Ljt…	card_charge	requested_by_radar_rule	authorized
payatt_1HvmxU…	pi_1HhsH…	card_charge	requested_by_radar_rule	authorized
payatt_1I5npGF…	pi_1IdKak…	card_charge	requested_by_radar_rule	authorized
payatt_1HcbWZ…	pi_1IAhBh…	card_charge	requested_by_radar_rule	authorized

SCA exemption information

You can also query information on the SCA exemptions used by Stripe or the issuing bank. See Exemptions to Strong Customer Authentication.

The following query shows the payments that used a low risk direct authorization SCA exemption that was declined for a reason unrelated to the requested exemption.

select
  attempt_id,
  intent_id,
  charge_outcome,
  charge_outcome_reason
from authentication_report_attempts
where intent_type = 'payment'
  and sca_exemption_requested = 'low_risk'
  and sca_exemption_mechanism = 'authorization' -- direct to authorization
  and sca_exemption_status = 'non_sca_decline'
  and is_final_attempt
limit 5

attempt_id	intent_id	charge_outcome	charge_outcome_reason
payatt_3JeL…	pi_3JeL…	issuer_declined	insufficient_funds
payatt_1Itw…	pi_1Itw…	issuer_declined	do_not_honor
payatt_1Ini3…	pi_1Ini3…	issuer_declined	do_not_honor
payatt_1IiO7…	pi_1IiO7…	issuer_declined	do_not_honor
payatt_1I0hGm…	pi_1I0hGk…	issuer_declined	insufficient_funds

Impact of deduplication

The following query shows how removing duplicates with is_final_attempt affects the calculation of the authentication rate for setups.

Observação

Our deduplication logic looks for groups of declined transactions (except for the last, potentially) with the same customer_id, currency, and amount, appearing close together in time. Such groups are treated as a single unit for conversion calculations. In the Sigma table, we include all raw data, but also include a column, is_final_attempt, that you can use to filter to a representative transaction from each group.

with setup_attempts as (
  select
    created,
    is_final_attempt,
    threeds_outcome_result in (
      'attempt_acknowledged',
      'authenticated',
      'delegated',
      'exempted'
    ) as threeds_succeeded
  from authentication_report_attempts
  where created between date'2021-10-29' and date'2021-11-03'
    and intent_type = 'setup'
    and is_threeds_triggered
)
select
  date_trunc('day', created) as day,
  1. * count_if(threeds_succeeded)
    / count(*) as authentication_rate__raw,
  2. * count_if(threeds_succeeded and is_final_attempt)
    / nullif(count_if(is_final_attempt), 0) as authentication_rate__deduped
from setup_attempts
group by 1
order by 1

day	authentication_rate__raw	authentication_rate__deduped
2021-10-29	0.59	0.80
2021-10-30	0.60	0.81
2021-10-31	0.59	0.81
2021-11-01	0.61	0.83
2021-11-02	0.62	0.83