Case Study · Retail
Derimod CRM analytics: customer 360, a campaign success datamart and dashboard
Once the stock side had settled, the customer's turn came. We connected customer, membership, points and campaign data from Dynamics CRM, Shopify and the ERP to the same data warehouse; in place of campaign results summarised in a single line, we built a star schema at receipt-line grain — MASS and loyalty campaigns, points earned and spent, by channel, segment and product. Daily segment and points updates are live; the customer 360 view is a prototype; churn and next best offer are on the roadmap.
- Receipt line
- Grain
- 2
- Campaign types
- 5 + 2
- Dimensions
- Daily
- Segment update
Several campaigns on one receipt; product-level effect
MASS (everyone) · loyalty (segment-specific)
Campaign, customer, product, store/channel, date + 2 bridges
Card tier and points every night
Problem
Derimod’s customer data lived in three places: membership, segments and contact in Dynamics CRM; online orders and site behaviour in Shopify; store sales and invoices in the ERP. The campaign report was a single line — “campaign X: this much revenue”. Which segment responded, what the points-spending customer bought, which campaign worked when two overlapped on one receipt, how stores differed from e-commerce — these questions could not be asked.
The data warehouse on the stock side had been running for three years; connecting customer data to the same warehouse was the natural step.
Layers
We started in July 2025 with two documents: a project brief that explains the warehouse to executives as “a four-storey building”, and a technical CDP design.
| Layer | What it does |
|---|---|
| Staging | Data from CRM, Shopify and the ERP lands unchanged; no record is lost |
| Cleansing | Format unification (dates, phones, currency), merging of duplicate customers, missing-data rules |
| Integration | Customer matching by e-mail, phone and name; a single customer id; slowly changing dimensions |
| Presentation | Datamarts: campaign, customer, sales — where the dashboards read |
Flows were designed as 15-minute incremental from the CRM, daily from the ERP, hourly from Shopify; critical tables every four hours. The customer master table carries the three systems’ ids side by side: CRM, Shopify and ERP customer codes in one row.
The campaign success datamart
In December 2025 we placed campaign analytics on a star schema. The critical decision was grain: the receipt line. Because several campaigns can apply to one receipt, product-level effect is critical, basket analysis needs grouping by receipt, and points earned and spent must be tracked line by line.
- FACT_CampaignTransaction — campaign application details, sales mechanics, points mechanics, cost and margin.
- Dimensions — campaign (type, category, scope, timing, target), customer (demographics, loyalty tier: Silver, Gold, Diamond), product hierarchy, store/channel (store, web, mobile, marketplace), date.
- Bridges — campaign–product and campaign–target audience.
The dashboard’s first page is an executive summary: campaign performance trend, MASS versus loyalty comparison, channel breakdown, incremental revenue and ROI. Later pages go deeper by segment, product category and mechanic. The KPI catalogue — revenue per campaign, participation rate, points redemption rate, repeat purchase after a campaign — was written with every metric’s formula.
Live and prototype
- Daily segment and points update — card tier and points balance are computed every night by procedure; points-earning checks are reported.
- Campaign report procedure — the flow that feeds the datamart and serves the dashboard.
- Customer 360 (prototype) — recent orders, e-mail and site activity, segment (“top 10%”), product segment and life-cycle stage, recommended products on one screen; shown to management on a sample customer.
Roadmap
In the 2026 plan CRM continues with three analytics projects: churn — early detection of customers at risk of leaving; next best offer (NBA) — recommendations by customer and product life cycle; campaign and discount effect — measuring a campaign’s incremental contribution with a control group. All will be built on the same datamart.
Why this way
- Grain decides everything. Kept at receipt level, overlapping campaigns and product effect would be lost; line level is expensive but the only correct choice.
- The same warehouse. Had customer data been gathered in a separate system, stock and customer would never meet; “which stock did this campaign clear in which store” can only be answered in one warehouse.
- Measure first, model later. Before churn and NBA, campaign success had to be measurable; the model comes on top of the measurement.