Retail Ops capstone scorecard

The retail packet is due Monday

A retail-bank star schema with 11 tables, 60K+ rows, and planted reconciliation traps. Trailing-space vendor names that split GROUP BYs, miscoded products, weekend snapshot gaps, late-posted transactions, joint-account dupes, and test accounts that need filtering before anything ties to the GL.

The retail-banking analyst role at a regional or money-center bank: deposit growth decomposition, deposit beta on MMAs, NSF/OD fee revenue post-CFPB, branch consolidation scoring, NII decomposition, primary-banking-relationship classification, churn leading indicators, and FTP-allocated branch P&Ls against a realistic retail bank with planted reconciliation traps.

Answer questions from the Retail COO, Branch Ops Manager, Treasurer, and Customer Insights Lead — weekly retail dashboards, ALCO-ready deposit beta, NSF/OD scenario modeling, and branch P&L with FTP allocation.

Retail Banking Scenarios

6-table retail_* core (customers, accounts, products, branches, transactions, daily balances) plus retailops_* specialty tables for branch activity, fees, and ATM events. Numbers anchor to 2024-2026 retail banking realities.

Authentic Retail Bank Schema

Average daily balance, weighted deposit beta, primary-relationship EXISTS logic, churn lag features, FTP curve allocation, and the multi-CTE rollups that real retail-bank dashboards run on.

Deposit + Branch + Treasury + Customer Skills

Every mission maps to a real retail-banking-analyst task — weekly retail packs, ALCO submissions, branch-strategy memos, and the SQL that retail teams at JPMC / BofA / PNC actually screen for.

Portfolio-Ready Skills

The branch deposits are the bank.

Peek at the customer book

Product taxonomy inventory

Open accounts right now

Active deposit customers by state

Net new checking accounts this week

Branch 13 missing transactions

CD maturity wall (next 90 days)

Primary banking relationship classification

NSF / OD fee revenue by FICO band

ATM in-network vs foreign economics

Deposit beta on MMAs (cycle-to-date)

Rate-tier balance distribution

Average daily balance for fee waivers

Dormant account audit (180+ days silent)

Onboarding funnel: 90-day primary-relationship rate

Monthly interest income by product category

Branch consolidation candidate score

2025 closed-account exit cohort with tenure

Loan-to-Deposit ratio by region

Cross-sell candidate model: cash-back card

Deposit balance by acquisition channel

Tier-break top-up candidates (Savings/MMA)

FDIC insurance bucket stress test

Reg E dispute aging report

Customer 360 — top depositors

Branch-level P&L with FTP overlay

Deposit beta by product subcategory

NSF/OD fee cap scenario by FICO band

Treasury daily liquidity dashboard

customer_id

household_id

first_name

last_name

date_of_birth

state

segment

fico_current

estimated_income

kyc_risk

acquisition_channel

onboarded_date

status

retail_customers

account_id

product_id

branch_id

open_date

close_date

ownership_type

interest_rate

original_balance

credit_limit

fico_at_orig

maturity_date

retail_accounts

branch_name

region

lmi_tract_flag

branch_type

retail_branches

product_code

product_name

subcategory

default_apr

default_apy

monthly_fee

nsf_fee

od_fee

retail_products

snapshot_id

snapshot_date

ledger_balance

available_balance

days_overdrawn

delinquency_status

retail_balance_daily

fee_id

post_date

fee_code

amount

waived_flag

retailops_fees

event_id

atm_id

event_ts

in_network_flag

surcharge_amount

retailops_atm_events

dispute_id

received_date

dispute_amount

dispute_type

resolved_date

retailops_disputes

txn_id

channel

transaction_type

debit_credit

description

counterparty_name

is_cash_flag

is_reversal

retail_transactions

activity_id

activity_date

teller_count

account_openings

foot_traffic

deposits_taken_count

retailops_branch_activity

rate_id

effective_date

product_category

ftp_rate

retailops_ftp_rates

Retail Ops capstone scorecard

The Brief

You'll practice