To tackle the challenge of collection and storing crucial data from various marketing platforms our team developed and implemented a universal and highly functioning system that was later integrated into marketing reporting and performance dashboards.
Marketing reporting can become a real headache, especially when the company spends thousands of daily budget on multiple digital marketing platforms AND, most importantly, has sales operations happening after the digital conversion event and has to merge the actual results with the marketing costs to get an actual picture of what’s working and what’s not.
Our client was facing difficulty in collecting and storing cost (also clicks and impressions) data from various marketing platforms into a single system, which would later be integrated into marketing reporting and performance dashboards. Without the system, the solution was to integrate reporting data separately for each channel, mainly with daily manual inputs and work done by the marketing team.
The main challenge was the structural inconsistency between the necessary data points to be integrated. The digital marketing channels to be integrated had different levels of reporting granularity, while some systems (getCake) would provide only affiliate-level daily data with only the number of clicks and costs, there were other DSPs that would go as deep as a keyword, creative, and publisher level impression, click, and cost data on an hourly basis.
The second challenge in the implementation of the project was the inconsistency of the API integration logic for plugging various DSPs and marketing platforms into the same system.
Before the intensive development kicked off, we made sure to plan the project steps and priorities accordingly. We started by listing the digital channels/platforms to be integrated into the system and ended up with the following prioritized list of marketing platforms to be integrated into the cost tracking system:
After having the list of channels to be integrated, we thoroughly investigated the API specifications for each platform, pointing out:
We started by defining the data architecture we aimed to achieve for the final result by carefully analyzing each channel’s reporting granularity and data points. We came up with a unified glossary of terminology and a mapping of each channel's data points to the unified architecture.
Adwords API reporting types granularity metrics dimensions
After defining the data architecture, we have started implementing the platform integrations separately for each account under the business units for all applicable channels (Facebook, Adwords, etc.) to make sure the granularity goes to the account level as well when needed. The files provided by the channel API implementation were converted into separate daily CSV files in the initially planned format, and those CSV files were uploaded into Google cloud storage buckets. As part of the implementation, daily cron jobs were scheduled to automatically call the APIs and pull the past period (day, week, or month, depending on the channel) CSV reports for each type of report for each channel.
Marketing cost tracking system cron jobs
Those buckets were later connected to Google Big Query data tables, which automatically read each file in the GCS bucket, converted the names of the files into date columns, and merged all separate daily files into a single data table for each type of report for each channel.
We have started cleaning and modifying data according to the initially planned architecture as a third step. As a result, we got a unified database covered by the granularity levels available and came up with these X unified all-channel data tables in GBQ. For platforms that were not supporting a certain level of reporting, we combined the data in a way that it would still be applicable on other levels of available dimensions.
These data tables were later used in the scope of another project which would merge the cost tracking data with conversion and revenue data coming from CRM sources and get comprehensive marketing performance dashboards to be used by all channel managers.
After finishing the implementation of the main functionality of the system, a smart monitoring system was developed responsible for tracking and notifying the success and failure of daily updates of the data per the client's expectations. In the case of failures, the system data engineering support would be notified through a slack bot in a dedicated channel to investigate the problem:
Marketing cost tracking system notification slack bot
As the last step, a control interface was provided to the system users for manually running the data refresh for the selected date range, channel, and report type. This would allow rerunning the data e.g. in the cases when the automatically scheduled reports would not cover the past period updated/impacted data and there was a need for custom data refresh.
Marketing cost tracking system report update UI
With the marketing cost tracking system in place, the client was able to:
Mamble is committed to working with partners to build remarkable projects with excellent marketing solutions and services.