Data modelling: Creating structures that grow with your business

Imagine you are the owner of a successful ecommerce company.

At first glance, describing your operations seems straightforward: You have an inventory of products, customers who place orders for those products, and orders that generate revenue. These concepts are reflected in your ERP, CRM, accounting software, or custom databases that you control. Likely you have one or more tables holding inventory, customers, and orders, and you may even have a business intelligence tool connected to visualize your data. Based on this information you can ask simple questions like how much did I make last month? Who were my most frequent customers? 

But what really drives performance under the hood? More importantly, what levers do you need to pull to make things better next month? Answering questions like these quickly moves into needing to understand causality.

• What makes my customers come back and purchase again?
• Which marketing campaigns drove engagement? Was that engagement directly or indirectly correlated with purchase?
• Did any products (or experiences) cause my customers to not come back?
• Were there hidden operational costs that pulled my profits down?

Answering these critical questions requires linking data across different systems used by various teams across your company.

In all but the smallest organizations, the data generating processes and workflows are owned by diverse groups with varying data skills. Teams across the company naturally choose systems optimised for their needs, be it procurement or customer support. Decisions about buying or upgrading these systems will be done on the team's schedule. Configuration of systems will be adjusted to achieve goals of the team, and all of this will happen throughout the life of the company.

This creates a complex and evolving landscape of systems and data definitions that complicate our ability to answer the all-important causal questions we're interested in to make next month better.

For example, understanding which products cause the most operational costs likely requires information from:

• Procurement if the issues are in sourcing
• Warehouse management if the problems are in packaging and handling
• Your website and customer support if issues arise in sales and usage
• Returns or complaints if the culprit is product quality

With data for these operational processes scattered across specialized tools, creating the story about what is happening in your business means mapping technical formats and business logic is required. The disparate systems lead to inconsistencies in data convention and quality. For example, the warehouse management system may use a product ID as a primary identifier for accurate tracking of an individual inventory item, while the website may default to a product name for ease of communication with customers.

If you load this data into a data warehouse or even a series of spreadsheets to perform this cross-functional analysis, you can't escape the fact that the data will reflect the fragmented systems and teams that created it. Some translation and normalization is needed before you can get reliable answers to questions that cut across team boundaries.

This data integration challenge illustrates Conway’s Law - system design mirrors the communication structures of an organization. The more disparate the teams and systems, the harder it becomes to get a consistent and unified view of data and maintain it over time. This lies at the core of data teams' frustrations when providing decision support to drive the business forward, but is also the source of value when you get it right.

Data modeling tools help to create a map between the technical structure of data and the business questions you want to ask of it. With thoughtful setup, models can be configured to grow gracefully with the underlying changes in the organisation. Getting the fundamentals wrong, however, can leave your teams arguing over data disagreements, forming incorrect conclusions and being blind to developments.  We've seen companies reach technical limits of a set of infrastructure, then spend 12+ months of analysts time migrating content to a more performant solution, slowing decision making and costing significantly more than necessary. Great businesses are able to launch a new product and bring it into the data models within 1-2 weeks, given the right foundations.

Thankfully, technology has advanced significantly and there are now many modeling languages and paradigms that bring structure, resilience and automation to data modelling. Tools like Looker (with it's proprietary LookML modelling language) and the open source data build tool (dbt) have become widely used frameworks over the past decade. Both provide flexible features to improve understandability, manageability, and accessibility of your data.

In this series of posts, we outline what we consider best practices for implementing and managing data models, using examples from LookML and dbt. We don't, however, suggest that these frameworks are the only options for you, as your choice of tooling should be tailored to your company's operating model, and that is a separate discussion. The core tenets of these best practices are transferable both to established frameworks like DAX and newer languages like Malloy, as well as to future innovations yet to emerge.

Our next post will cover an introduction to the structures within the Looker ecosystem and best practices for setting up a project within it, and maintaining health over time. Stay tuned for practical guidance on setting up a solid analytical foundation and getting answers to those all-important, cross-functional and causal questions that drive your business forward.

Drop us a note if this stimulated any thoughts!

FAQ

How should I assess the current state of our data architecture and identify areas that may require restructuring or integration?

We recommend starting out by looking at the data maturity level of the company. Cruxdata does this often for our clients as we work across companies scaling between 50-1000 employees and the areas for investment change during this journey.  For a comprehensive assessment, it is critical to pull away from the infrastructure and look at how data is being used, where the hidden frustrations are and what value can be gained by solving these problems.

What factors or considerations go into the choice of data tools?

The most important considerations are fitness for purpose and value for money. In order to get to answers for these, we typically look at factors like your data maturity level, the importance of data in your business model, the organisation communication and decision making norms, the level of centralisation in the organisational structure, regulatory requirements, and integration with the remainder of your data stack. See our post on benchmarking your data stack for more detail

What are the potential risks or challenges associated with data projects?

The most common challenge with data projects is the valley of dashed hopes and expectations. Many data projects fail to deliver on the goals that they set out to achieve, and in our experience this is often because companies fail to focus on the boring but fundamental foundations. Good data quality starts at the activities that generated it, not only when it is loaded into a data warehouse. Return on data investments isn't generated by yet-another-dashboard but comes from closing the loop to action after generating the insights.