Linking marketing and internal data
Are you a medium or large business owner or are you concerned about cost efficiency or marketing? Have you started to feel the limits of Excel or online spreadsheets? And are you making the most of your data? If you answered YES to all three questions, then combining data from marketing systems, internal databases and other sources may be a good fit for you.
Practical applications & examples
Connecting data from many sources can help you in evaluating marketing channels, selecting carriers, buying new merchandise, or even in HR.
General use case
Almost anyone can do a simple, one-off calculation of cost of sales (COS) or margin on a simple calculator. If you need to analyse data over time, segment it by marketing channel or device type (PC, phone) and do it across systems in a few clicks, a calculator won’t be enough.
In this case, you need to combine data from individual systems (Google Analytics, Google Ads, Facebook, Criteo, Google Spreadsheets, SAP, AWS, Ecomail, etc.) into a single database and then work with this database – either to import part of the data between systems (e.g. margin from SAP to Google Ads) or to visualise the reports (PowerBI, Data Studio).
A common use case from practice
The most common requirements of our clients include:
- finding out what the real cost of 1 purchase is (credits, packaging, shipping) and how much of the order is left over
- what is the ROI – return on investment; how much does it cost to acquire a person and what is the retention needed to generate a profit
- Evaluating what brands work across the e-shop (taking into account different devices, marketing channels and labour involved in shipping)
- predicting sales trends based on available data
For larger players
This service counts on linking primarily for larger companies – it is simply not worth it for smaller ones.
“Apart from the development effort within our team, it’s mainly the need to have enough data and then use it realistically for further decision making”
For larger companies and large corporations, the possibilities are more or less limitless.
- Importing the purchase price of a product into Google Ads and then optimising campaigns using automated scripts (monitoring ROAS, CPC, etc.)
- importing costs to orders into Google Analytics, including the share of costs for staff, office or transport (when you subsidise it as part of the “free shipping” service)
- attribution and retention – if the business model is built so that fixed purchase is in the negative or at zero -> how many purchases do you need and at what rate (based on devices, campaigns) to be in the black – especially for regular subscriptions
- Import returns and cancellations into marketing systems (if the share is higher and it makes sense)
- connecting data with “non-traditional” systems outside the Google platform (importing data into GA from your own AWS database, Azure, GCP, etc. or emailing)
- connecting branch traffic data and evaluating the impact of specific campaigns in a given period
- daily RFM analysis differentiated by campaign, facility, region, etc.
Predictive models
When a database with linked data exists, predictive models or AI calculations can be implemented quite well.
Visualisation
Merging marketing data and data from internal systems and other sources ends up in a database. But you probably can’t do anything with this database on your own. So we usually proceed to one of the visualisation tools. Because we work in BigQuery, we usually build the visualisation in Data Studio (for ease of integration and speed of data loading).
For some clients, we also do visualisations in PowerBI or dump the transformed data back into online Google Spreadsheets.
What does working with data look like?
Some of the examples given in the article won’t always be relevant to everyone. However, we can connect a lot of things together, so if this area of advanced analytics is useful to you, we’re sure to find the right solution.
Data sources – input
For a data source to be usable, the system must have its own API that we can connect to and get the data.
The list of these services may include Google Analytics, Google Ads, Criteo, RTB (RTBhouse, Adform), emailing services (Ecomail, Smartemailing, Mailchimp), affiliate systems, online Google spreadsheets or customer databases – Amazon, SAP and others.
Working with the data itself is relatively accurate, as most systems have the data clearly structured and consistent across accounts. Problems arise with different campaign naming, custom dimensions or in custom .CSV outputs that aren’t even .CSV.
Offline data
We can also work with offline data, such as linking data from affiliates. We “only” need to get this offline data into an online form where the data can be accessed through an API.
Limits and complexity
It is important that visualisations or exports of transformed data are used. Otherwise, such work will never pay off.
Tools used and collaboration with developers
The limits of data joining solutions are more or less non-existent. In terms of hardware, we do everything in the cloud – the modern way. The limits of data transfer within the services we use would cover data from the entire Seznam.cz network.
In terms of tools, we use Google Cloud (primarily BigQuery and Keboola). If we run out of space somewhere (the capacity here is min. in Petabytes) or computing power, there are other tools. No need to worry.
In some projects, we also work with the IT department of companies that often already have their own databases, whether just the backend of the website or larger systems. Here it is useful to use this information and link it with data from marketing systems, for example.
Complexity of implementation
As is usually the case, we start with a simple solution that is gradually expanded. Connecting data and then working with the data is not a matter of one afternoon – that is why we also need to continue to work properly with the data and move the performance of the entire company forward.
One of our great benefits is our in-depth knowledge of Google Analytics 4 – which means we are able to implement proposed solutions on a turnkey basis. This is where a lot of pure IT professionals without marketing experience have a problem.
Cost in operations
As part of the cost of running such a system, you have to take into account the maintenance fee (an API may go down, a tool may start issuing data differently, switch to another system, etc.) and the cost of running the tools (Google Cloud, Keboola). We deal with these costs individually according to the number of connections and also the flow of data volume.
The advantage of our solution is undoubtedly security, where Google Cloud offers countless security certificates.
Our experience
Our clients within the data processing and transformation service include Outdoor Concept (Rockpoint, Hannah) and Zaslat.cz with its projects Parcelspot.com and Chytrybalik.cz.