CRM Analytics Tool

Executive summary

CRM Analytics is a complete customer-analytics system: feed it raw customer, product, and transaction data — millions of rows — and it returns monthly, decision-ready segmentation, lifecycle, and churn signals for every customer.

It replaces the usual cycle of exporting CSVs and hand-building one-off pivot tables with a reproducible, auditable pipeline. Every number traces back to a documented rule, so the business and the code agree on exactly what "a Gold customer" or "a churn risk" means.

What it looks like

Illustrative mock-up of the CRM analytics dashboard — KPIs, monthly trend and RFM segments.

The analytics it produces

Each month, every customer is scored on independent dimensions built on an RFMT model (Recency, Frequency, Monetary, Tenure), where consumable purchases drive engagement and device or accessory sales are deliberately excluded from the segmentation.

• Value tierFive tiers — Diamond, Platinum, Gold, Silver, Bronze — derived from average monthly consumption, order size, and six-month order frequency, with a passive state for inactive customers.
• Activity statusActive vs. inactive, based on whether a customer purchased within the trailing 12 months.
• Lifecycle eventsNew, Lost, and Reactivated flags, recomputed every month from cumulative purchase windows.
• Churn riskAt-risk high-value customers are flagged for intervention before they lapse.
• TrendsValue-tier mix and lifecycle shifts tracked across rolling 12-month periods.

Brand-configurable rules & documentation

The segmentation logic is defined as data, not buried in code. A single business-logic specification is the source of truth for every threshold and definition — the project's principle is "rules first, code second."

Because the rules are parameterized, each brand can tune its own thresholds — what counts as Diamond, what window defines churn — and the entire history recalculates deterministically. The specification is published as documentation alongside the dashboard, so anyone can audit exactly how a number was produced.

• Business-logic specSegmentation rules and KPI definitions in one authoritative document.
• Source & schema specsInput file formats and the normalized database schema, fully documented.
• Rendered with the appSpecs are rendered to HTML and shipped beside the dashboard for easy auditing.

Backend & data pipeline

The backend is a deterministic, three-stage pipeline: raw data goes in, and pre-computed monthly snapshots plus a static dashboard come out — with no server required at runtime.

Built for millions of rows

Rather than querying transactions live, the system pre-aggregates them into monthly per-customer snapshots. Millions of transaction rows collapse into compact, query-ready summaries, so the dashboard stays instant no matter how large the underlying history grows.

Calculations run as plain, set-based SQL over SQLite, and the Python orchestrator replays them month by month to build a full 12-month history in one reproducible build.

The dashboard

The output is a static, mobile-friendly dashboard: a 12-month value-tier trend, lifecycle-event charts, and headline KPIs such as new customers, churn risk, and high-value accounts at risk — with filtering and CSV export, and no signup or backend to run.

Tech stack

Outcome

The result is a transparent customer-analytics engine that any team can run on its own data with three commands. Segmentation, lifecycle, and churn signals that once took manual spreadsheet work are produced automatically every month, with every figure traceable to a published rule.

The whole system is open source and ships with a live, synthetic-data demo — so the logic can be inspected end to end without exposing any real customer data.