Do you own a medium or large business, or are you responsible for cost efficiency or marketing? Have you started to feel the limits of Excel or online spreadsheets? And do you use all 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 use & examples
Connecting data from many sources can help you evaluate marketing channels, select carriers, purchase new merchandise, or even it can help you in HR.
General use case
Simple and one-time calculation of the cost of sales (COGS) or margin can be done by almost anyone using a simple calculator. If you need to analyze 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, it is necessary to combine data from individual systems (Google Analytics, Google Ads, Sklik, Facebook, Criteo, Google Spreadsheets, SAP, AWS, Ecomail, etc.) into a unified 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 visualize the reports (PowerBI, Data Studio).
A common use case from practice
The most common requests from 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 laboriousness of shipping)
- predicting sales trends based on available data
For bigger players
This service counts on connecting mainly larger companies – it is simply not worth it for smaller ones.
Apart from the demands of development within our team, it’s primarily the need to have enough data and then use it practicably to make further decisions.
For larger companies and large corporations, the possibilities are more or less unlimited.
- importing the purchase price of the product into Google Ads and subsequent campaign optimization 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 red 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
- importing returns and cancellations into marketing systems (if the share is higher)
- linking data to “non-traditional” systems outside of the Google platform (e.g. importing data into GA from your own AWS, Azure, GCP, etc. database or emailing)
- connecting branch traffic data and evaluating the impact of specific campaigns in a given period
- daily RFM analyses differentiated by campaign, facility, region, etc.
When there is a database with linked data, predictive models or AI calculations can be implemented quite well.
The merging of marketing data and data from internal systems and other sources ends up in the database. But you probably can’t do anything with this database on your own. So we usually proceed to one of the visualization tools. Because we work in BigQuery, we usually build the visualization in Data Studio (for ease of integration and speed of data loading).
For some clients, we also do visualizations in PowerBI or write the transformed data back to online Google Spreadsheets.
What does working with data look like?
Some of the examples given in the article will not always be relevant to everyone. But we can connect a lot of dots, so if this area of advanced analytics is useful to you, we’re sure to find the right solution.
Data sources – input
In order for a data source to be usable, the system must have its own API that we can connect to and retrieve the data.
The list of these services can include Google Analytics, Google Ads, Sklik, 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 unified across accounts. Problems arise when there are different campaign names, different custom dimensions names, or in custom .CSV outputs that even aren’t .CSV.
We can also work with offline data, such as linking data from branches. We “only” need to get this offline data online, where the APIs can be used to access the data.
Limits and difficulty
It is important that visualizations or exports of transformed data are used. Otherwise, the work will never pay off.
Tools used and collaboration with developers
The limits of data linking solutions are more or less non-existent. In terms of hardware, we do everything in the cloud – the modern way.
When it comes to the tools, we use Google Cloud (primarily BigQuery and Keboola). If we run out of space (capacity is at least 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. It is useful to use this information and link it with e.g. data from marketing systems.
Difficulty of implementation
As it is usually the case, we start with a simple solution, which is gradually expanded. Connecting data and then working with it is not a matter of one afternoon – that’s why it is also necessary to continue to work properly with data and move the performance of the entire company forward.
One of our great advantages is our in-depth knowledge of Google Analytics – which means we are able to implement the proposed turnkey solutions. IT professionals without marketing experience usually have problems with that.
Costs in operation
As part of the cost of operating such a system, you have to take into account the maintenance fee (API may go down, some tools may start performing data differently, there may be a 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.