Every data source or platform has its own way of labeling things so when it comes to combining data, creating a complete picture of even the basic things like total ad spend is really tough. To tackle this, marketers need to understand data transformation. But what is data transformation and why should marketers care?
Imagine this: you just became the new marketing manager of a fast-growing company and the first thing you need to do, apart from getting to know your colleagues, is to get your bearings around what has been done so far (and how good it was). So, you ask for a basic data point – total ad spend in the previous period - and you end up getting an email or a PowerPoint slide with a jumble of numbers with different names and in different formats and currencies.
For those versed in spreadsheet magic this is no particular obstacle. But consolidating all this information and turning it into something useful does waste your time and the energy you could’ve used for ideating creative ideas for effective campaigns. Surely, it would be much better if all of this was done automatically, in the background. If only there were a way…
Of course, there is a way, and it’s a part of a process whose acronym you might have heard of – ETL, or Extract-Transform-Load. And right there in the middle is the key step – transformation.
Data transformation is the process of converting formats or structures of various data points throughout a data set. Put simply, it is the operation that allows you to take all data from disparate sources and turn it into a single, coherent database.
Sounds simple, but it really isn’t, especially if you are doing everything by hand. The root cause for the need for data transformation is the fact that, even with widespread global standardization initiatives, there are simply too many differences between data formats and units used in different geographies. The examples are numerous: date, time, length, weight… and don’t get us started on currencies, decimal separators, etc.
There are numerous data transformation methods (aggregation, deduplication, discretization, normalization – just to name a few) and the ultimate goal of all of these operations is to increase data quality and make data-driven reporting faster and more accurate. Data transformation is integral to building a solid foundation of accurate marketing data and is considered one of the 6 building blocks of data governance.
Let’s take a deeper look at everyone’s ‘favorite’ – the date format. The variety is so big that even the most advanced data transformation tools have trouble consolidating dates exported from martech tools into a single format that you can use in your centralized reporting. Even two computers in the same office can be set with different regional settings (US vs. UK English) and if you are working jointly with colleagues in a shared spreadsheet, this difference can make a real mess in your final reports.
Add to this different time formats, and you’ve got a real mess in just one, but very important data point, which allows you to understand the performance of any KPI over time. Above all of this comes the headache of transforming costs, shown in different number formats, currencies, and on top of everything – named differently in many martech platforms. For example, Google Ads uses ‘Cost’ and Facebook Ads uses ‘Spend’ to display the amount of budget spent on campaigns, ad groups, or individual ads.
Put simply, there are two ways you can transform your marketing data; first, by doing it all manually in a spreadsheet or second, by utilizing a data integration platform that automates the data transformation phase of the ETL process.
For many businesses, tackling this manually in a spreadsheet will be the first step. And this means manually copying and pasting numbers into a spreadsheet and reformatting everything by hand.
If you choose this path, it will clearly become clear that this is a bad idea for many reasons. Manual data transformation is not only time-consuming and resource-intensive, but it also introduces inaccuracies and inconsistencies in your data from standard human error. It also drastically reduces the time to value from your data because each and every time you need to compare data sets, someone needs to undertake this laborious task. With, say, two data sets, this is possibly manageable, but with the three, four, five, or more data sources modern marketers commonly work with, this becomes extremely difficult.
Thus, more analytically mature marketing teams should look to automate this process as soon as possible. Not only will doing so save time and resources, but it will also vastly improve the value you can get from your data as well as speed up the time it takes to get there.
For more on data integration check out our guide here!
Book a demo with one of our advisors to learn what Adverity can do for you!