_ _
.__|_._ ____.._ _.| _o _____|___|_ _.._ |___|o _ .__|_ _
| | | | |(_|| |(_||\/_>|_> (_)| |_)(_|| ||<(_||(/_| ||__>
/
╭──────────────────────────────────────────────────────────────────────────╮
│ Транзакционный лог розничного банка не даёт единой картины ценности к... │
╰──────────────────────────────────────────────────────────────────────────╯
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
RFM analysis of bank clients
RFM-сегментация 1 983 клиентов банка по 9 492 транзакциям (после отбрасывания строк с флагом аномалии): коэффициент Джини 0,324, топ-20 % клиентов дают 38,2 % выручки, средний RFM-балл — 7,50.
Navigation
▶ Diagram: RFM segmentation of 1,983 bank clients — opens in a browser, no install, no build
On this page: Situation · Task · Actions · Result · Background · Method · Results · Chart gallery · Hypotheses · Recommendations · Project layout
Situation
Транзакционный лог розничного банка не даёт единой картины ценности клиента:
кого стоит удерживать, кто вот-вот уйдёт и на какой сегмент направлять маркетинг.
Задача — сегментировать клиентов банка по транзакционному поведению через скоринг
Recency, Frequency, Monetary, сопоставить комбинации квартилей именованным
сегментам и отдать результат в CSV + Excel + графиках + интерактивном дашборде.
Task
Превратить один синтетический транзакционный лог в адресуемый сегмент для каждого
клиента и доказать, что сегментация имеет бизнес-смысл, а не принять это на веру.
Actions
- Загрузка и очистка — разобрать даты, отбросить строки с
is_anomaly == 1.
- Таблица RFM — по каждому
customer_id: recency, frequency, monetary_value.
- Скоринг по квартилям 1..4 → трёхзначный
RFMClass и RFMScore (3..12).
- Сегментация по комбинации, а не по сумме, — поэтому
444 и 233 больше не делят один бакет.
- Концентрация — кривая Лоренца и коэффициент Джини, плюс
concentration_ratio по каждому сегменту.
- Гипотезы — пять проверяемых и три непроверяемых; каждая проверяемая отдаёт сырое и Holm-скорректированное p-значение.
- Экспорт — CSV, форматированный Excel, графики (
images/) и дашборд на Streamlit.
Result
- Джини = 0,324 — топ-20 % клиентов держат 38,2 % выручки.
- 126 Champions — 6,4 % клиентов → 12,1 % выручки (ratio 1,91).
- 643 Hibernating — 32 % базы, недобирают по выручке (ratio 0,61).
- 992 из 1 983 ≈ 50 % — At Risk + Hibernating, главный пул для реанимации.
- 54 теста — чистые функции пайплайна, сверка README с CSV и smoke-тест дашборда.
Сегментировать клиентов банка по транзакционному поведению через скоринг
Recency, Frequency, Monetary, сопоставить комбинации квартилей именованным
сегментам и экспортировать CSV + Excel + графики + интерактивный дашборд.
Background
Synthetic transaction log for 2,000 clients of a retail bank. The goal is to identify high-value clients, flag at-risk ones, and give marketing a targetable segment per customer.
- Time period: 2023-01-01 → 2024-03-30 (synthetic, reproducible via
generate_data.py, seed 42)
- Volume: 10,000 transactions → 9,492 after the anomaly drop, 1,983 unique active clients
- Schema:
customer_id, transaction_date, amount, product_category, payment_method, is_anomaly
Quick start
uv sync --all-extras # or: pip install -e ".[dev,dashboard]"
python generate_data.py # builds data/bank_rfm_dataset_10k_fixed.csv
python rfm_analysis.py # writes data/rfm_output.csv, data/rfm_output_report.xlsx, images/*.png
python hypotheses.py # runs the testable hypotheses over the RFM output
pytest # 54 tests — pipeline functions, README/CSV binding, dashboard smoke
streamlit run dashboard/app.py # interactive dashboard (5 tabs)
Method
- Load & clean — parse dates, drop rows flagged
is_anomaly == 1.
- RFM table — per
customer_id: recency (days since last txn vs snapshot +1d), frequency (txn count), monetary_value (sum).
- Score quartiles 1..4 (higher = better):
R_Quartile: 4 = most recent, 1 = longest agoF_Quartile: 4 = most frequent, 1 = rarestM_Quartile: 4 = highest spend, 1 = lowestRFMClass: 3-digit string (444 = best, 111 = worst); RFMScore = sum (3..12)
- Segment — combo-based (not sum-based), so
444 and 233 no longer share a bucket:
| Condition | Segment |
|---|
| R≥4 & F≥4 & M≥4 | Champions |
| R≥3 & F≥3 & M≥3 | Loyal |
| R≥3 & F≥2 & M≥3 | Potential Loyalists |
| R≤2 & F≥3 | At Risk |
| R≤2 & F≤2 | Hibernating |
| else | Need Attention |
- Concentration — Lorenz curve + Gini coefficient verify segmentation has business sense; per-segment
concentration_ratio = revenue_share / customer_share flags over/under-indexing.
- Export — CSV (
data/rfm_output.csv), formatted Excel (data/rfm_output_report.xlsx), charts (images/), interactive dashboard (dashboard/).
Results
| Segment | Customers | Avg recency | Avg frequency | Avg monetary | Avg RFM |
|---|
| Champions | 126 | 11.35 | 8.16 | 27,608.83 | 12.00 |
| Loyal | 376 | 32.13 | 6.40 | 20,692.31 | 10.32 |
| Potential Loyalists | 79 | 32.62 | 3.97 | 16,777.17 | 8.57 |
| At Risk | 349 | 123.85 | 6.30 | 19,381.56 | 8.37 |
| Need Attention | 410 | 30.39 | 4.07 | 8,962.92 | 7.20 |
| Hibernating | 643 | 177.47 | 2.92 | 8,786.79 | 4.56 |
Mean RFM score across the base is 7.50 — exactly the midpoint of the 3–12 range. Monetary concentration: Gini = 0.324 — the top 20% of customers hold 38.2% of revenue. Champions over-index on revenue (ratio 1.91), Hibernating under-indexes (ratio 0.61).
Chart gallery
Every chart below follows the same reading order: what it shows (description) → the chart → what it means (insights).
1. Segment distribution
Description. Horizontal bar chart of customer counts per RFM segment, ordered from most to least populated. Answers the first portfolio question: how big is each cluster of the client base?
Insights.
- Hibernating (643, 32%) is the single largest pool — roughly 3 out of 10 clients sit in the low-engagement bucket.
- Champions (126, 6%) is small but strategically critical; At Risk (349) is the larger imminent-churn group.
- Combined, At Risk + Hibernating = 992 of 1,983 ≈ 50% of the base — the main re-engagement opportunity.
2. Monetary by segment
Description. Boxplot of lifetime monetary value per customer by segment (log scale), ordered by segment median. Shows where high spenders actually live.
Insights.
- Champions carry the highest average monetary (≈ 27.6k), followed by Loyal (≈ 20.7k) and At Risk (≈ 19.4k) — value concentrates in the segments scoring highest on R/F.
- Hibernating (≈ 8.8k) and Need Attention (≈ 9.0k) sit at the bottom — recent-but-small accounts and dormant ones with limited upside.
- The log-scale spread confirms skew: within every segment a small minority dominates spend, which is what the Gini of 0.324 summarizes globally.
3. Recency vs Frequency (size = Monetary)
Description. Scatter of every customer by recency (days since last transaction) and frequency; point size encodes monetary value, color encodes segment. The canonical RFM map in one view.
Insights.
- Champions cluster in the low-recency / high-frequency corner (top-left): 11.4 days since last txn, 8.2 txns each; Hibernating fills the bottom-right (177.5 days, 2.9 txns).
- At Risk sits between the active and dormant clusters — 123.9 days of recency but still 6.3 txns, far more frequent than Hibernating's 2.9 — the clearest win-back target before they slide.
- Bubble size shows the long tail of high-monetary clients spread across several segments, not only Champions.
4. Lorenz curve — monetary concentration
Description. Cumulative share of revenue vs cumulative share of customers (poorest → richest). The distance from the equality diagonal is the inequality, summarized by the Gini coefficient.
Insights.
- Gini = 0.324 — a meaningfully unequal distribution of monetary value, though far from extreme.
- Top-20% of customers generate 38.2% of revenue — about 1.9× their proportional share (38.2 / 20), so the curve bends, but this book does not follow an 80/20 Pareto shape.
- Segmentation still has business sense: the top quintile carries nearly twice its weight in value, so targeting the right segment pays off above its customer count.
5. RFM score distribution
Description. Histogram of the composite RFM score (3–12) across all customers; dashed line marks the mean (7.50). A quick shape check of overall engagement health.
Insights.
- The distribution is roughly bell-shaped around a mean of 7.50 — exactly the midpoint of the 3–12 range, so overall engagement is balanced rather than skewed low.
- Left tail (scores ≤ 4, 340 clients) is the dormant mass; right tail (score 12, 126 clients) is exactly the Champions count.
- Fewer clients at the extremes than in the middle — most of the book is "average," which is where segmentation adds the most leverage.
6. RFM matrix heatmap
Description. 4×4 grid of customer counts per (Recency × Frequency) quartile combo — the classic RFM matrix. Cell shade = count; number inside each cell = average monetary value of that cell.
Insights.
- The (R=4, F=4) cell (recent + frequent, 196 clients) is the Champions core with avg monetary ≈ 23,434 — nearly 1.6× the whole-book average of ≈ 14.5k (total monetary 28.67M ÷ 1,983 customers).
- The (R=1, F=1) corner (248 clients) is the deep-sleep mass — the single largest cell.
- Avg monetary rises more with frequency than with recency (reading down columns vs across rows) — frequency is the stronger value signal, so retention campaigns should target frequency, not just freshness.
7. Customer share vs revenue share by segment
Description. Grouped horizontal bars: each segment's share of customers (%) against its share of revenue (%). A revenue bar visibly longer than the customer bar means the segment over-indexes on value.
Insights.
- Champions: 6.4% of clients → 12.1% of revenue (ratio 1.91) — the single most valuable segment to protect and reward.
- Need Attention: 20.7% of clients → 12.8% of revenue (ratio 0.62) — a cost-reduction candidate, not an investment target.
- Hibernating: 32.4% of clients → 19.7% of revenue (ratio 0.61) — worth cheap win-back touches, but cap campaign spend per client.
- At Risk: 17.6% of clients → 23.6% of revenue (ratio 1.34) — the highest-leverage win-back pool, since it still over-indexes on value while its recency decays.
8. Average transaction value by segment
Description. Boxplot of average transaction value (AOV = monetary / frequency) per customer by segment, log scale. Splits "big spenders" into "few large purchases" vs "many small purchases".
Insights.
- Potential Loyalists have the highest AOV (median ≈ 4.2k), just ahead of Champions (≈ 3.4k) and Loyal (≈ 3.1k) — value per transaction is highest in the segments that buy premium products.
- Need Attention has the lowest AOV (≈ 2.1k), with Hibernating next (≈ 2.6k) — recent but small tickets, and the dormant pool buys small when it buys at all.
- For Hibernating/Need Attention, raising AOV is more realistic than raising frequency first — a focused upsell beats a broad engagement push.
9. Monthly activity by segment
Description. Monthly revenue (top panel) and transaction count (bottom panel) by segment across the full 15-month window. Shows how each segment's activity evolves over time.
Insights.
- Hibernating's volume and revenue decay visibly over the window — its share is front-loaded in early months, which is exactly the pattern the Recency metric captures.
- Champions and Loyal stay flat through the window despite small client counts — stable, reliable revenue.
- The late-window decline in At Risk activity tracks how quickly they approach Hibernating status; intervention timing should sit in this window.
Visualizations
Declarative dashboards over the same CSV outputs, built with dbt-charts (dct). No HTML/JS hand-written — each board is YAML in charts/, and every query runs against the registered CSV sources in dbt_charts.yml.
dct validate charts/ --strict # structural gate (no warehouse needed)
dct serve # live preview of every board
mkdir -p renders && dct render charts/rfm_overview.yml --format html --output renders/rfm_overview.html
| Board | What it shows |
|---|
charts/rfm_overview.yml | KPI row (clients, avg monetary/recency/RFM), clients per segment, avg monetary by segment, recency-vs-frequency scatter |
charts/rfm_matrix.yml | RFM score distribution, R×F quartile heatmap, top RFM classes |
charts/rfm_concentration.yml | Concentration ratio per segment, customer vs monetary share, Lorenz curve |
charts/transactions_profile.yml | Product mix, payment-channel mix, amount by product, anomaly rate by channel |
Boards carry interactive filters (segment / payment method) and render to HTML, PNG, SVG, or PDF.
Hypotheses
Five original + three added hypotheses; five testable on this schema, three untestable. Every testable hypothesis reports both the raw and the Holm-adjusted p-value (Holm–Bonferroni across the five tests).
| # | Hypothesis | Test | Raw p | Holm p | Result |
|---|
| H1 | Higher Monetary ↔ more distinct products | Spearman ρ (customer level) | < 0.001 | 0.000 | ρ = 0.61 — supported |
| H5 | Mobile-bank users have higher Monetary | Mann-Whitney U, two-sided; n = 391 mobile vs 1,592 other | 0.0003 | 0.0012 | significant but the effect runs the OPPOSITE way — not supported as stated |
| H6 | Segment × product-category associated | χ² independence, customer level (dominant category); dof 25 | 0.002 (permutation) | 0.006 | χ² = 52.32, Cramér's V = 0.073 — статистически детектируемо, практически ничтожно (association есть, но actionable: false при пороге V ≥ 0,10) |
| H7 | Mobile users have lower Recency | Mann-Whitney U, two-sided | 0.43 | 0.80 | not supported |
| H8 | Large-amount flag independent of payment channel | χ² independence; dof 4 | 0.40 | — | χ² = 4.04 — independent, as expected (not rejected) |
| H2 | Low Recency → higher retention | — | — | — | untestable: no cohort panel / retention label |
| H3 | Hibernating clients reactivate via email | — | — | — | untestable: no campaign exposure data |
| H4 | High Frequency → higher NPS | — | — | — | untestable: no NPS / satisfaction field |
Reading the three surprises. H5 is statistically significant but in the wrong direction: mobile-majority customers have a lower median monetary (11,840 vs 13,445), and the groups also differ in transaction count (median frequency 4 vs 5) — a confound, so the raw comparison does not isolate channel. H7's apparent effect disappears entirely (69 vs 66 days, p = 0.43). H8 was reframed away from "anomaly rate by segment": segments are built on the anomaly-dropped frame, so dropping a transaction lowers its customer's frequency and can move them into a lower segment — a mechanical link that made the old segment comparison (Hibernating 7.5% vs Champions 2.4%) significant for reasons unrelated to amounts. Per-channel anomaly rates are flat and independent: карта 5.50%, банковский перевод 5.02%, криптовалюта 4.87%, Apple Pay 4.66%, Google Pay 4.41%. H6 is the quiet one: after the customer-level fix the test is significant (Holm p = 0.006), but Cramér's V = 0.073 is negligible — segments and product-category mix are detectably associated and useless for targeting. hypotheses.py therefore reports actionable: false, and the association should not be read as support for segment-targeted cross-sell.
Run python hypotheses.py to reproduce.
Recommendations
- Champions (126, 6,4 %): программы удержания и просьбы о рекомендациях — сегмент переиндексирован по выручке (ratio 1,91) и держит самый высокий AOV среди верхних сегментов.
- At Risk (349, 17,6 %): активность недавно просела, но исторически это частые и дорогие покупки (ratio 1,34 по выручке) — кампания реанимации, пока они не сползли в Hibernating (см.
docs/experiment_winback.md).
- Hibernating (643, 32,4 %): самый большой пул — дешёвые письма для возврата, но без больших вложений: средний monetary у них самый низкий.
- Need Attention (410, 20,7 %): активность свежая, а частота и чек низкие — апселл на рост M, иначе уйдут тихо.
- Potential Loyalists (79, 4,0 %): самая высокая ценность транзакции (высший AOV), но самый маленький сегмент и средняя частота — апселл премиальных продуктов на рост F.
Docs & artifacts
| File | What |
|---|
docs/data_dictionary.md | Field catalog for source + RFM tables |
docs/sql_templates.md | SQL equivalents of the pipeline (PostgreSQL/ClickHouse) |
docs/metrics_framework.md | Input→Output→Outcome, leading/lagging per segment |
docs/experiment_winback.md | A/B design for a win-back campaign (FINER, sample size) |
docs/exp001_analysis_plan.md | Pre-registered analysis plan for EXP-001 (MDE +5 pp, decision rule) |
scripts/ab_test_analyzer.py | SRM, significance, power and guardrail checks — --design, --baseline, --sensitivity, --results |
docs/prd_segment_targeting.md | PRD — expose rfm_segment to marketing |
dashboard/app.py | Streamlit dashboard (Overview / Segments / RFM map / Concentration / Raw) |
charts/*.yml | dbt-charts declarative boards (validate + render + serve), each with its own filter variables |
dbt_charts.yml | dbt-charts project config — CSV source registry |
presentation/slides.md | Marp deck summarizing the project |
Caveats
- Data is synthetic (see
generate_data.py); segment sizes and correlations reflect the generator, not a real bank.
- Quartile thresholds define "high/low" mechanically; validate against business expectations before acting.
recency is computed against a snapshot date (max txn + 1 day), not today.
Project layout
rfm_analysis.py canonical pipeline (load -> score -> segment -> export)
generate_data.py synthetic dataset generator
hypotheses.py testable + untestable hypothesis checks
utils/ Excel report generation
dashboard/ Streamlit interactive dashboard
charts/ dbt-charts declarative YAML boards
presentation/ Marp slide deck
docs/ data dictionary, SQL templates, A/B design, PRD, metrics
tests/ pytest suite (54)
data/ dataset + outputs (gitignored where regenerable)
images/ charts
renders/ dbt-charts HTML/PNG renders (gitignored where regenerable)
notebooks/ exploratory notebooks
.github/ CI workflow (validate + test)
License
MIT — see LICENSE.
