There are a number of challenges with big data. It’s spread across different platforms and devices. What's more, it’s messy. Yes, you’re thinking what we’re thinking: That’s more than just one challenge. But what if there was one solution for all these challenges? Well, there is - and it’s called ETL.
What is ETL exactly?
ETL stands for ‘extract, transform and load’, and essentially describes a three-step process. Extracting data is about acquiring it from a particular source, and is arguably the most crucial step of the three. In order for ETL to be done effectively, data needs to be collected directly from its source and in its rawest form.
Transforming data, on the other hand, requires it to be stripped from its various formats, cleaning and harmonising it. In other words, it makes it easy-to-read for the end user. It is important to remember that ensuring the highest possible data quality is crucial during this process. Therefore, you need to consider standardising the criteria according to which your data will be cleaned and transformed; make sure that duplicates are being detected and removed; and sort the data into relevant, predetermined categories.
Once you have that covered, your ETL tool will help harmonise your data and move on to loading it into a target database, including business intelligence (BI) and database applications, where it will be ready for analysis.
.png?width=581&name=etl-diagram%20(1).png)
ETL is the foundation of today’s data-driven marketing, even if it’s an abbreviation you don’t hear too much about. It essentially separates the analysis part from everything that comes before it. So, instead of data analysts going directly to each source and manually sorting through the data, an ETL tool can get the job done for them.
What does ETL do for your day-to-day marketing?
Think about the data you work with on a daily basis: It’s diverse and often complex. Moreover, there’s no shortage of options for how this data can be formatted. For instance, something as straightforward as a timestamp can still be formatted in a multiple ways:
- 23-Oct-2018,
- 23-10-2018,
- 23/10/2018,
- 2018-10-23,
- October 23, 2018
Or what about currencies? If you work in the EU, but your campaigns are being distributed all over the globe, you might want to better understand, what you pay for. If this is done on-the-fly with transformations, your data is clean and useful as soon as you get it:
- US Dollar -> Euro
- CAN Dollar -> Euro
- GB Pound -> Euro
- YEN -> Euro
And a super powerful example is to map products to a specific category - this is very useful to group campaigns in a meaningful way to smooth your analytics process
- Iphone X Launch -> Mobile
- Iphone Summer Campaign -> Mobile
- Ipad 4 Back-to-School -> Tablet
- Ipad Mini Holiday campaign -> Tablet
- Macbook 13” Student edition -> Desktop
- Macbook Pro Business campaign -> Desktop
Slice and dice your marketing data
All the above describe the same thing, yet each option is different. And while it seems like a trivial and easy to repair thing, remember that this will be done for all your campaigns in the future and you will never to correct this again. And clearly, your data is not made up of timestamps, currencies, campaign names alone. Add location coordinates, customers’ names, devices IDs, sellers URLs and time zones to the mix, and you got yourself a full-time data-formatting job.
Investing in a next-generation ETL tool is therefore key. A failure to do so is likely to leave you overwhelmed in your attempts to tackle a pile of data that will simply take far too long to prepare for analysis. A clear win for inefficiency. So, what will the data look like, I hear you asking. Well, you will get a lot of filters, drill downs, column, segments and extra rows in your analytics.
To make this even more specific, let’s take another example. As a marketer, some of your main priorities are to generate leads that convert to sales. Close inspection of your data will allow you to better understand and predict customer behaviour. Your finance department, in the meantime - also interested in the sales you make - will likely be collecting some of the same data, yet it may be using different categories or naming conventions. To avoid working on your own isolated silos, you need a tool that formats and stores everything in one centralised location for you. Consider ETL as your new best friend.
ETL testing
In order to unlock the true potential of ETL, you’ll first need to ensure that the process is running smoothly. Is the data you end up working with really as high-quality as you think it is? Is it accurate, consistent and complete? ETL testing can help you find the answer to those questions.
The goal of ETL testing is to assure you, as a marketer and end user, that the insights you are deriving are as reliable as they can be.
Completeness testing, for instance, looks into whether or not your data has been collected in its entirety. You can also use testing to check if your ETL tools detects invalid values, like empty fields or unknown characters, and cleans them up before the data is transformed.
Testing the after-transformation, on the other hand, involves making sure that all the data has been processed the way you want it. Checking for accuracy is likely the most important step in ETL testing. That is the point at which you make sure that your heterogenous data is being transformed correctly, into the right formats and categories. Without this, the rest of your data analytics process will be chaos.
Eventually, you need to make sure that you test the overall performance of your ETL process. How much data can it handle at any one time, are there any bottlenecks and what can be done to minimise them? Find the answers to those questions and optimise your ETL process to avoid any doubt being cast over your data insights later on.
ETL and the cloud
While ETL has been around for decades, today’s fast pace of technology requires marketing professionals to keep up. The cloud, for one, has done away with many of the storage and computer processing constraints of the past, making it possible to automate and expedite a large part of the ETL process. That, in turn, requires transforming and analysing ever-larger amounts of data.
Bringing ETL to the cloud is often a big step for many marketers given that their data mostly resides on servers and data warehouses. The key to making this transition lies in finding an ETL tool that works well in the cloud and allows for easy migration in the case you want to change providers. It also needs to be able to communicate effectively any on-premise server(s) you may have in place, as well as any online data sources.
At the end of the day, ETL is there to make your job as a marketer easier and more efficient. What you need is a tool that can bring your ETL to the next level, so you can focus on the deriving the insights your company really needs.
