I have lost count of how many times a client has handed me a “dashboard” that was really a bloated spreadsheet. They are frustrated because the numbers do not add up, the load times are awful, and answering something as basic as “how much did we sell last quarter?” takes a PhD in Excel formulas. The visualization tool is rarely the problem. Their Data Modeling is.
In Analytics Engineering, the shift worth making is away from tech specs and toward business logic. A data model is the blueprint for the whole analytics house. A chaotic blueprint means the house comes apart the moment you scale it. A sound one means your team finds answers while the coffee is still hot.
Three levels of precision
I would not start a custom WooCommerce build by writing functions in functions.php with no plan, and a database schema is no different. Professional Data Modeling happens in three stages:
- The conceptual model is the napkin sketch. Nothing technical here: you name the entities, such as Customers, Orders and Tickets, and make sure the business and the devs mean the same things by them.
- The logical model is the blueprint, where the structure gets defined. Attributes, relationships, and whether a given link is one-to-many or many-to-many.
- The physical model is the construction plan. You pick data types, set primary keys and tune indexes for the platform you are actually running on.
Skip the first two, jump to the physical model, and you are close to guaranteed to miss a business rule that costs a full sprint to fix later.
OLTP vs. OLAP: writing versus reading
One of the biggest gotchas in our ecosystem is running analytics straight off the production database. WordPress and WooCommerce are Online Transaction Processing (OLTP) systems, tuned for speed and integrity during writes, with highly normalized tables that keep the same data from being stored twice.
Normalization keeps John Smith’s address out of 50,000 rows, and it is miserable for reading. A simple report turns into complex JOINs across dozens of tables. That is why we build a separate Online Analytical Processing (OLAP) system. Turning write-optimized data into read-optimized data through denormalization is the engineering work.
For how this plays out in store performance, see my breakdown of WooCommerce 10.5 Performance and Scalable Analytics.
The star schema
For analytics Data Modeling, the Star Schema still wins. The approach, popularized by Ralph Kimball, splits data into facts and dimensions.
- Facts are the quantitative measurements, such as revenue and quantity.
- Dimensions are the context around them: date, product, customer.
When you need time travel, that is Slowly Changing Dimensions (SCD) Type 2. Rather than overwriting a customer’s old address, you write a new row with a validity flag, so a report can show where they lived at the time of the purchase.
-- Example: Implementing an SCD Type 2 Table Structure
CREATE TABLE bbioon_dim_customers (
customer_skey INT AUTO_INCREMENT PRIMARY KEY, -- Surrogate Key
customer_id INT, -- Original Business ID
customer_name VARCHAR(255),
customer_city VARCHAR(100),
effective_date DATE,
expiry_date DATE,
is_current BOOLEAN DEFAULT TRUE
);With a surrogate key, your fact table joins to the exact version of the dimension that existed during the transaction. That is what keeps historical drift out of the reports.
On architectural bottlenecks more broadly, there is my guide on Fixing Data Architecture for Analytics.
If this modeling work is eating your dev hours, I take it on. I have been wrestling with WordPress since the 4.x days.
The takeaway
Analytics is not an afterthought. Good Data Modeling moves the data and structures it so that asking a bad question becomes hard. The principles hold whether you are working with transactional fact tables or snapshot logic. Get it right the first time and you will not spend a weekend debugging a “broken” dashboard that was only ever built on a house of cards.
