Case Study · Retail
Karaca: data analytics consulting — from the data warehouse to CustoMation
Consulting alongside the data analytics team in 2022–2023 for the store and e-commerce data of the Karaca group — Karaca, Karaca Home, Emsan, Homend, Kaşmir: the warehouse's analytics layer and cube, a product and idle-stock dashboard, store and e-commerce lost sales, a capacity system design, a transfer proposal, and CustoMation, which selects campaign audiences by RFM segment. This page is the umbrella; CustoMation has its own page.
- 6
- Projects
- 5
- Brands
- RFM + behaviour
- Segments
- 3 phases
- Capacity
Dashboard, lost sales ×2, capacity, transfer, CustoMation
Same warehouse, same definitions
The basis of CustoMation's audience selection
MVP → systematic → rollout
Context
Karaca, with five brands and hundreds of points of sale, is one of the largest players in home and kitchen retail. In 2022 a data analytics department was being set up; there was a data warehouse and a cube on Dynamics AX, but no analytics projects running on them. In July 2022 we started on a day-rate basis, sitting with the team: needs analysis, documentation, project management, data architecture, coding and modelling.
The warehouse’s analytics layer first
The data warehouse — product, store and customer dimensions; sales, stock and price facts — existed; what was missing was a layer between the warehouse and the projects. We opened a separate schema for the analytics department: staging tables, daily procedures that write every step to spLog, a run history. The source–target mapping was kept in a map of more than 12,000 rows. The new structures the projects needed — product–store shelf life, daily and hourly sales, initial price and price history, opening/closing stock, stock in transit — were opened one by one with IT as a “data needs” list.
Projects
| Project | What it does | Status |
|---|---|---|
| Product dashboard | Sales, stock, best and worst sellers, ranking by product; idle stock computed by a daily procedure | Live, presented to the teams |
| Lost sales — stores | Sales missed when stock runs out while demand continues: stock-out durations, a “what would have sold” estimate | Algorithm + dashboard |
| Lost sales — e-commerce | The same logic for the site and marketplaces | Design |
| Idle stock | Non-selling stock by brand and store from the last 30 days’ sales–stock relationship | Live, daily |
| Capacity system | Unit and volume capacity of store display areas; product group–area matching; actual and final fill; a three-phase plan (MVP with 5 pilot stores → systematic → rollout) | Design and MVP |
| Inter-store transfer and dispatch proposal | Transfer from low- to high-performing stores; dispatch proposals from the warehouse | Roadmap |
| CustoMation | Campaign audience selection by RFM and behavioural segments; scenario record and history | Live |
The capacity design solved a problem specific to Karaca: stores have no display standard; there are single-brand areas, multi-brand areas, areas that hold both display and boxed product. So capacity was kept in both units and volume; each capacity group was given primary and secondary product groups; placement runs in two passes — primary products first, secondary into what is left — and any remaining stock eats into the back-room target. The MVP ran in five pilot stores and the result was verified by store visits.
The 2023 roadmap
The list drawn up with the team for the second year of consulting: weekly store sales forecasting, e-commerce day-and-hour forecasting, campaign effect analysis and a campaign recommendation system, initial and dynamic pricing, international segmentation, next best offer, gathering all customer data in one place, data awareness training. Part of the roadmap continued as the team’s own projects; the consulting was completed in 2023.
Why this way
- Alongside the team, not instead of it. In a day-rate model the goal was not to deliver a project but for the department to be able to run its own; the owners of the roadmap projects were the team’s analysts.
- A layer between warehouse and project. Without each project’s own staging, log and run history, by the third project nobody would know which table was what.
- Definition first. Idle stock and lost sales were defined in SQL before they reached a dashboard; when presented to the teams, what was argued was the action, not the definition.