Epica-beauty — bank-issuer share at term=0 and term=1

Inputs

• Channel: channel = 'epica-beauty'
• Time window: last 6 months — date_cohort >= DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH)
• Granularity: 1 month — DATE_TRUNC(date_cohort, MONTH)
• Subscription terms: term=0 (initial payment) and term=1 (first recurring)
• Bank buckets:
  • Bank of America: rolls up BANK OF AMERICA NA, BANK OF AMERICA, BANK OF AMERICA CONSUMER CREDIT, BANK OF AMERICA NA MERRILL LYNCH
  • JP Morgan / Chase: rolls up JPMORGAN CHASE BANK NA, JPMORGAN CHASE BANK NA DEBIT, CHASE BANK USA NA
  • Other issuers: everything else with issuer_bank IS NOT NULL OR NULL
• Metrics:
  • For term=0 — share of successes within the month
  • For term=1 — share of tries within the month
  • Bonus — acceptance rate per bucket per month (successes / tries)

SQL used

WITH base AS (
  SELECT
    DATE_TRUNC(date_cohort, MONTH) AS month,
    subscription_term_number,
    CASE
      WHEN UPPER(issuer_bank) LIKE '%BANK OF AMERICA%' OR UPPER(issuer_bank) LIKE '%BOFA%'
        THEN 'Bank of America'
      WHEN UPPER(issuer_bank) LIKE '%JPMORGAN%' OR UPPER(issuer_bank) LIKE '%JP MORGAN%' OR UPPER(issuer_bank) LIKE '%CHASE%'
        THEN 'JP Morgan / Chase'
      ELSE 'Other issuers'
    END AS bank_bucket,
    SUM(tries)     AS tries,
    SUM(successes) AS successes
  FROM `fashion-web-358113.final_data_payments.acceptance`
  WHERE channel = 'epica-beauty'
    AND date_cohort >= DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH)
    AND subscription_term_number IN (0, 1)
  GROUP BY 1, 2, 3
)
SELECT
  month,
  subscription_term_number,
  bank_bucket,
  tries,
  successes,
  SUM(tries)     OVER (PARTITION BY month, subscription_term_number) AS month_total_tries,
  SUM(successes) OVER (PARTITION BY month, subscription_term_number) AS month_total_successes,
  SAFE_DIVIDE(tries,     SUM(tries)     OVER (PARTITION BY month, subscription_term_number)) AS share_of_tries,
  SAFE_DIVIDE(successes, SUM(successes) OVER (PARTITION BY month, subscription_term_number)) AS share_of_successes,
  SAFE_DIVIDE(successes, tries) AS acceptance_rate
FROM base
ORDER BY subscription_term_number, month, bank_bucket;

Share of successful payments at subscription_term=0

Data — share of term=0 successes

Data — raw counts (term=0)

Share of tries at subscription_term=1

Data — share of term=1 tries

Data — raw counts (term=1)

Acceptance rate per bucket (successes / tries)

Term=0 acceptance rate

Term=1 acceptance rate

Key reads

• JPM / Chase is the best-converting issuer at term=0 — consistently 93–95% acceptance vs ~80–85% for the rest. Share of successes (~10%) sits ~4–5 pp above its share of tries (~6%) — efficient cards.
• BoA has bifurcated. At term=0 it's improved from 66% (Nov 25) to 93% (May 26), now matching JPM. But at term=1 it has collapsed from 55% to 24% over the same window. Share of term=1 tries from BoA rising (3% → 7%) means a meaningful share of revenue is being lost at the rebill step.
• Term=1 acceptance dropped portfolio-wide since March — Other issuers went from 49% in Jan to 21% in Apr. Worth a dive into cascade_steps and retry_attempt on the same table to see whether a routing/retry change broke things.
• Action. Investigate cascade routing for BoA BINs at the recurring step. Compare against JPM's cascade — JPM holds up 45–85% acceptance at term=1 while BoA collapses, so the routing logic for the two issuers likely differs.