Authorisation rate

1-- core.adyen.authorisation_rate
2-- Authorisation rate by day and payment method: of the payments that reached an
3-- authorisation decision, the share that were authorised.
4--
5-- Grain: day (first decision, report time zone) x merchant account x payment
6-- method x payment currency.
7-- Depends on: core.adyen.payment_lifecycle
8--
9-- DEFINITION. A payment counts once, on the day of its first Authorised or Refused
10-- record. authorisation_rate = authorised / (authorised + refused), a ratio of
11-- counts. Re-aggregate the counts before dividing for weekly or monthly rates.
12--
13-- Each payment attempt has its own PSP reference, so a shopper who is refused and
14-- then succeeds counts as one refusal and one authorisation. Adyen's Customer Area
15-- can show a rate that de-duplicates retries or excludes some refusal reasons, so
16-- expect a small gap against it. Refused records only appear if your Payment
17-- accounting report includes them; if refused_count is always zero, check the
18-- report's configuration before reading the rate.
19
20SELECT
21    DATE(LEAST(
22        COALESCE(l.authorised_at_local, l.refused_at_local),
23        COALESCE(l.refused_at_local, l.authorised_at_local)
24    ))                                                                            AS date,
25    l.merchant_account,
26    l.payment_method,
27    l.payment_currency,
28
29    COUNT(*)                                                                      AS decided_count,
30    COUNTIF(l.is_authorised)                                                      AS authorised_count,
31    COUNTIF(NOT l.is_authorised AND l.is_refused)                                 AS refused_count,
32    SUM(CASE WHEN l.is_authorised THEN l.authorised_amount ELSE 0 END)            AS authorised_amount,
33    SAFE_DIVIDE(COUNTIF(l.is_authorised), COUNT(*))                               AS authorisation_rate
34FROM {{core.adyen.payment_lifecycle}} l
35WHERE l.is_authorised OR l.is_refused
36GROUP BY 1, 2, 3, 4