Data Modelling with a Purpose

By: Ozzy Karatepe, Data Engineer at CvE

In the fast-paced world of marketing, it’s not uncommon to hear phrases like, “I need a data warehouse to answer this one question,” “I have this query,” “I need this report,” “I need a dashboard to show me this,” or even the occasional, “Where is my dashboard!” These requests are the lifeblood of data-driven decision-making, driving the day-to-day operations of businesses striving to stay ahead in a competitive landscape. However, there’s a common pitfall that many organisations stumble into – they jump headfirst into addressing these immediate needs without laying a solid foundation. The bigger picture, often overlooked in the rush, is the development of a well-thought-out data strategy and the creation of a robust data model. In this blog post, we’ll delve into why it’s essential to look beyond the urgency of the moment and consider the long-term implications of your data decisions in the world of marketing.

Data modelling is the process of defining, standardising, and organising data so that this data is comprehensive. This data can be useful to derive business insights, be flexible to growth and change, and be ready for AI modelling.

Marketing data is ever-growing, consisting of many facets, including advertising, CRM and sales data. It is therefore difficult to characterise marketing data as a whole, but rather easier to characterising each facet.

  • Advertising data is predominantly analytical, append data, where new rows are inserted after a given time. Settling periods for numbers such as spend, to be corrected, meaning the most recent data at any given time would need to be amended, provides an element of updating data. As data is updated, the Date field is vital, as well as for partitioning and for joining, where any join is done on an ID and the date.
  • CRM data is a mixture of customer data, a combination of identity and descriptive social, demographic data, and quantitative and qualitative lead data.
  • Sales data is a combination of customer, order and item data that provides conversion and revenue information.

The variety of data in marketing and the ever-growing list of platforms, CDPs, SSPs and all other elements of marketing make data modelling challenging.

Yet, the analytical aspects of marketing data, alongside the time-constrained projects that demand reports or a dashboard to solve a given problem, mean that a lot of data modelling rushes into a tactic of some sort, commonly one big table (OBT) or Kimball’s star schema (1) with fact and dimension tables.

While these tactics have their benefits and drawbacks that need to be considered, it is the approach of focusing on a tactic over data modelling is the issue. Rather than concentrating on principles that are in line with a data strategy, most projects focus on a tactic to solve a given problem.

The issue of focusing on a tactic is that you tend to focus on the data now.

Many advertising platforms share common fields, you may look at the database to find all your tables with campaign name, impressions and clicks, and immediately think OBT. Again, this may be the right method, especially if you understand how to align all metrics. One platform may use total engagements, and another would break down engagements in shares and likes, that would need to be aggregated together.

However, issues can arise with the granularity of the data, introducing demographic data to OBT to include device, gender, and age, consequently, can lead to metric numbers not matching the source when aggregating and a soaring cloud computing bill.

While using Kimball’s approach may avoid some of these problems, it may struggle to incorporate the variety of data in marketing such as Google Analytics and CRM data. These issues highlight the need to start data modelling with data modelling principles in mind, before looking at any tactic. At the minimum and when approaching tactics, be mindful of normalisation, update, and deletion strategies. How are we approaching the variety of data sources and integrating data from different accounts, different locations, with their own cultures and naming conventions?

Rather than focusing on the tactic, there needs to be greater importance and purpose in data modelling. Starting with aligning data modelling with the data strategy of the organisation and understanding the needs of stakeholders. Understanding the requirements of a business helps to comprehend how the business works. In marketing, requirements vary, and therefore the data and the structure of that data will vary. For example, modelling for attribution aids in understanding what media performs best. When done correctly, it can overcome the issue of lost signal due to identity and lead to a better return on investment (ROI) and faster growth. The data in focus will differ from a business aiming to understand and segment customers. Here CRM data will be at the forefront to understand existing customer groups to identify and find your next customers.  Businesses may have multiple requirements which can lead to different data models for different aspects of the business, usually stored in data marts. With that in mind, it is critical to structure data so that is available and flexible to meet the needs of all stakeholders.

Data modelling should aid in creating business insights, which can only be done with an understanding of the business. This leads to the second principle of understanding the processes of the business and how the data flows. In marketing there is an emphasis on understanding the customer journey. The way a customer navigates from their favourite lifestyle website to an e-commerce website by clicking on a banner ad is a simple example of a customer journey that can be seen in the data. Building a data model with the business processes in mind helps to build an efficient data model that is thought specifically for that organisation. Lastly, look at the data itself, the type of data and how the data is organised. What is the structure of the data, how much of the data is structured and how can we incorporate unstructured or semi-structured data into a data model, and derive value from it?


It can be argued that the computing power and the scalability of storage of modern-day cloud computing means that data modelling is less important and in consulting, where time is of the essence, and deadlines to be met, it is difficult to slow down and consider these aspects, which can lead to query driven modelling. This may be fine for a given project but may be short-sighted, leading to scalability issues, high cost of cloud computing and data debt. Trying to bandage these issues, by moving to different cloud providers, and hiring in the hope that someone can solve these issues can lead to further debt in the form tech and organisational. A solid data strategy will be the foundation of all things data. Data modelling should be a key aspect of that strategy and when forming a model, the methodology should first incorporate the principles of understanding business needs, mapping the business processes and understanding the data before approaching any data modelling tactics. This not only requires knowledge and expertise of data, but it also requires experience and expertise in the business and understanding their needs, especially in how marketing fits within the overall strategy of the business and how to leverage marketing to meet the goals of the business.

Ozan Karatepe
Ozan Karatepe, Data Engineer at CvE

Take control of your data’s future with a well-defined data strategy. By creating a strong foundation, you pave the way for confident decision-making and impactful insights. Harness the power of data and achieve greatness.

Have questions? Don’t hesitate to reach out! Drop me an email at, and I’ll gladly provide the answers you’re looking for.

(1) Kimball, R. (1996) The Data Warehouse Toolkit: Practical Techniques for Building Dimensional Data Warehouses. John Wiley & Sons, New York.

News & Blog