Marketing Dashboard & Cost Tracking

Project Overview

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.

Problem Statement

Our client was having difficulty 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 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. 

Strategy

Mamble has developed and maintained numerous marketing cost tracking systems (CTS), the largest of which was a crucial Mar-Tech data infrastructure component for one of the leading US financial companies.

The CTS collects marketing cost and performance-related data (impressions, clicks, and conversions) from various marketing channels through API integrations in a structured and actionable database and dashboard.

Planning

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:

• GetCake
• Facebook
• Adwords
• Yahoo 
• Bing
• Twitter
• Taboola
• Outbrain
• Propelmedia

After having the list of channels to be integrated, we thoroughly investigated the API specifications for each platform, pointing out: 

1. What type of data granularity is the system providing?
2. What is possible to get from each system, and what data is needed?
3. Reporting schedule and ensuring each system can provide accurate data on the previous day. Is live data available, and how accurate is it? 
4. What technical requirements and nuances should be considered when integrating the data into our system?

Execution

Industry

US FinTech companies

The number of API integrations

13+

Marketing data granularity

Ad, publisher, keyword, date and time, geographic locations, device, etc.

Team

• 1-2 person data engineering team
• Data analyst
• Marketing consultant
• Project POC/scrum master

Step 1 

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

Step 2

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.  

Step 3 

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 to 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. 

Step 4

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

Step 5

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 cases where the automatically scheduled reports would not cover the updated or impacted data from the updated or impacted data from the past period and there was a need for a custom data refresh.

Marketing cost tracking system report update UI

Business Impact

• Unified cost, impressions, clicks, and conversions data across various marketing platforms
• Structured, comprehensive, and comparable data across marketing channels
• Actionable input for multi-touch attribution and marketing analytics
• Reporting granularity, not just at the campaign level but as deep as the channels provide.