StarThinker
Published in

StarThinker

Campaign Comparison Dashboard

A request came into our team asking for help comparing different campaigns in the the same dashboard. The team needed to compare time, geography, and performance metrics faster than the three week process it normally took to source, refine, and present the data. The impact was not just time savings but strategic planning improvements at the VP level.

The tools we chose for the job were DataStudio, BigQuery, and a few StarThinker tasks. The solution is built for CM360 but can be easily expanded to include any other product. The result is the CM360 Campaign Comparison Recipe. You can request access and view the sample dashboard here.

What The Solution Does

The solution compares Impressions, Clicks, and Conversions across Time, Geography, and Devices. The comparison uses a left and right selection where multiple advertisers, campaigns, ads, or placements can be grouped into cohorts. Each cohort is then shown as either red or blue data in all the charts.

Create on the fly Cohorts of any combination of advertisers, campaigns, ads, and placements.

To make working with large data easier, the filters at the top allow narrowing not only time and geography but also KPI percentiles. For example, moving the impression slider to 80% will show only the Zip Codes, DMAs, and States with the top 20% impression rank.

Filters are applied to both cohorts and allow percentile filtering.

In addition, the impression rate and population density metric was added from public data to allow filtering by regional attributes. Impression rate is NOT REACH, it is derived from dividing the impressions served by the population over 16 years of age within a zip code area.

Click rate relative to population per zip code.

How The Solution Works

First, data is pulled from CM360 Reporting and API to assemble a table of impressions, clicks, and conversions down to the placement level. The data tends to be very large, so the combination of reporting and API data improves speed and reduces cost.

Some BigQuery join processing is done to join the reporting data with public geography data to construct percentiles and clean up naming conventions.

The core logic is a BigQuery view that unions the data adding cohort labels. This creates rows that include both cohorts as values but one is always zero. The approach allows filters to work on both cohorts in DataStudio but keeps the values in separate columns.

The remaining logic exists within the DataStudio data source. Fields are added to compute ratio fields and deltas. These need to be computed in DataStudio because they are influenced by the filters. For example, when choosing a specific DMA both the numerator and denominator change in ratios. Also, when changing members of each cohort the deltas will change.

Zip code impression deltas are computed real time as the Cohorts are adjusted in the dashboard.

Where can The Solution Be Modified

The solution can be extend to other products by replacing the CM360 specific tasks with other products such as DV360 or Google Ads. In addition KPIs such as store visits or sales rates can be added as additional computed fields and pages.

How Can The Solution Be Deployed

First, be sure to use the Get Access link to join the group that grants access to ALL StarThinker assets including this dashboard.

If your organization has a centralized StarThinker UI, check the list of solutions for CM360 Campaign Comparison or ask the engineers to synchronize it with the latest GitHub release.

The quickest deployment is the Colab Notebook. For all other deployments, check the specific instructions.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store