At LoyaltyMatrix back in 2004-2007 we built a pretty interesting marketing datamart based on Ralph Kimball‘s idea of a collection of targeted marts connected by a “Conformed Dimension Data Bus”.
Initially developed & hosted for 24 HourFitness as their marketing database. We expanded it a bit and rolled it out as MatrixOptimizer for clients like LA Times, Chicago Sun, and other subscription based businesses.
These are the high level structure diagrams we used to explain the process to our clients:
- MatrixOptimizer Overview – showing Sales, Usage, & Marketing marts connected by the Conformed Dimension Bus
- Campaign Module Overview – showing high level structure with example data
- Campaign Module Detailed Data Structure Diagram – where marketing campaigns were designed, monitored, and evaluated.
Not obvious from these diagrams is that MatrixOptimizer (aka MO) was a relatively light weight solution. Given reasonable data dumps from a new client we could bring up an initial instance in a matter of weeks – most of that was data discovery, especially around the conformed dimensions. As Ralph K has noted, it’s important to get those ~ right from the beginning.
LoyaltyMatrix surfaced the MO data with their OpenI web portal. Internally we used Tableau for data exploration and R for EDA, predictive modeling, and clustering.
Note that this work was all done on “classical” DBMS, usually MS SQL Server – which back then was the disruptive technology. Today I tend to use a columnar based “wide table” solutions as outlined here starting on slide 18.