A marketing reporting solution that allows to measure and track ad campaign performance across multiple platforms in a single report. This sample project is based on a hypothetical yet relevant client request and synthetic dataset.
Key high-level metrics such as Impressions, Clicks and Conversions, grouped by Channel and Time period.
Campaign list filtered by Channel and Year / Month.
Drill down to Ad Group Details (for selected campaign)
The hypothetical client explained their need for a unified interactive report to track ad campaign performance across five platforms, highlighting inefficiencies in the current Excel-based process. It requires daily updates of key metrics like impressions, clicks, CTR, spend, conversions, and conversion value, aligning with company branding. Crucially, the customer needs the functionality to drill down into the data, allowing detailed analysis of individual campaigns within the dashboard.
Solution
ETL Pipeline
The challenge was to combine a significant amount of data from various sources into a single dataset and enable a regular and reliable data update procedure.
For data extraction, a 3rd party tool, PowerMyAnalytics, was used. PMA connectors were configured for five different ad platforms: LinkedIn (LI), Facebook (FB), TikTok, Microsoft Ads, and Google. The data model is unified across all these platforms. Data exports in PMA are configured to transport data into BigQuery on a daily basis. In BigQuery, data from various ad platforms is combined into a single dataset. PowerBI accesses this data via the BigQuery connector.
Data visualization
The report is implemented in PowerBI, utilizing its features for visualization, interactivity, and drilling through the data. The PowerBI report presents:
A summary view with headlines of top-level indicators as well as key performance metrics (Impressions, Clicks, and Conversions per channel and per month).
A more detailed Campaigns view with the ability to filter particular campaigns by channel and by Year/Month, and the ability to drill through to Campaign details.
A Campaign details view showing specific Ad group/Ad name hierarchies along with their corresponding performance metrics (Impressions, Clicks, and Conversions).
