Supermetrics Meetup: Using Data to Power your Next Step @Google Amsterdam, Dec 4th 2024

andre@maxlead.com

Journey to �Cross Ad Channel Performance reporting at scale

andre@maxlead.com

Agenda

01

Introduction

2 minutes

​

02

03

04

Project deliverables

9 minutes

​

05

Technologies we used

4 minutes

​

06

07

Lessons learned: Challenges that surprised us

2 minutes

​

Questions

2 minutes

​

Journey to Cross Ad Channel Performance reporting at scale

What we wanted to achieve

2 minutes

​

Challenges that we could see coming

4 minutes

​

Wat staat�op de agenda

andre@maxlead.com

Me in 25 seconds

André Koot

Product Manager Marketing Data warehouse�Solutions

​

​

• Background in IT
• 17 years @ an International IT/Software company
• 17 years @Maxlead in roles: tracking/measurement, analytics and data
• Helping clients with various marketing data challenges
• Big fan of data modelling, out in nature, hifi and ice hockey
• andre@maxlead.com, LinkedIn (/in/andre-koot/)

Clients I’ve worked with:

andre@maxlead.com

Nick Hansen Wagensveld is also with us

• The goto person for planning and preparing campaign launches:
• Goals, Strategy, Tactics, Marketing mix selection, Budget forecast, etc.
• After take off making sure the campaign keeps heading in the right direction:
• Monitoring, analysing and optimizing to bottom line campaign result
• nick.hansen@maxlead.com

Clients I’ve worked with:

Nick Hansen

Wagensveld

Performance

Marketeer

andre@maxlead.com

Company that we work for

We are Abovo Maxlead:�

• 230 specialists in marketing and media, based in Hoorn and Oegstgeest (Netherlands). �
• We put our energy and dedication into maximizing our clients' marketing campaigns.�

From a full funnel, cross-media marketing campaign to a new website, and from a fresh corporate identity to a complete omnichannel marketing strategy.�

• We are continuously looking for the most effective marketing and media mix, in close collaboration with our customers.�
• The largest independent marketing and media agency in the Netherlands.

andre@maxlead.com

What we wanted to achieve

which operates as the centre point for data from 11 different ad platforms

configurable,

Like many of us, we have built countless marketing dashboards in the past….

But developing a integrated,

cross ad channel dashboard,

including direct buying campaigns from numerous independent publishers

with flexible target options built on a universal framework…

Is a different ball game.

andre@maxlead.com

What we wanted to achieve

Important for our clients:

Single source of truth

No interruptions of service / blackouts

Creating a fast lane for the CMO with this project�Rationale: Customer's corporate data teams are already overloaded with work

Performant at any date range window and any drill down segmentation

Pro-rata pacing monitoring on all online KPIs that matter with drilling down options, in multiple dimensions

andre@maxlead.com

What we wanted to achieve

Important to us:

Reducing the time spent on reporting for ad specialists and performance marketers by 50% or more

Better collaboration & communication across domain teams

Single source of truth

Work with any BI tool�(that can connect to a major cloud data warehouse vendor)

andre@maxlead.com

Challenges we expected when we started

“How to deal with differences in campaign/data structures and semantics between ad platforms?”

“How on earth do we enter and update all those targets for our ad inventory?”

“Can we get away with pro-rata target pacing only on the top campaign level? �(fairly easy to achieve)��Or must we also facilitate scenario’s with pacing all the way down in the hierarchy - on ad/creative/proposition level?”�(harder in a number of ways)

“Besides metrics like spend, impressions, clicks, etc. across platforms, how to incorporate the wide range of different conversion types that all have their purpose in the marketing journeys in our client base?”�(b2b, b2c, high-mid-low funnel)

“What must we do so everybody will actually use the system??”

“What to do with the wild mix of active naming ‘conventions’ out there?”��“How to deal with historical data already stored in various ways?”

​

Recognise any of this??

andre@maxlead.com

Make or Buy?

Unfortunately we did not find a fit pre-build solution for our use case.

So we started brainstorming and designing…

andre@maxlead.com

Transformation of RAW source tables

Dashboards & Reports

Building blocks we needed

Daily ingestion raw data into a data warehouse

��Applying business rules & Targets

Creating Marts report tables

Transformation

and

or

PowerBI, Tableau, Looker, etc

andre@maxlead.com

Deliverable - End result

Cross ad channel performance dashboard

Drilling down and comparing any Ad Channel using universal classification labels

Pro-rata pacing of any online KPI or metric in upper-mid-lower funnel.

Note: anonymized,sampled and blurred out respect to our clients

Annotation, Findings, Conclusions at a central location next to the data

Pivot & compare by Ad Channel, Campaign, Proposition, Tactic, … basically any classification label

Trends KPIs.

andre@maxlead.com

Deliverable - End result

Cross ad channel performance dashboard (yet another layout)

Strong focus on budget pacing:�Month2Date, Last 7 days and last month’s spend

Note: anonymized,sampled and blurred out respect to our clients

Cumulative

Development over time in conjunction with target

These were just a couple of screenshots of how a dashboard looks like.

​

However, the real magic, heavy lifting and complexity is handled in the data warehouse, invisible to the regular user.

andre@maxlead.com

Project deliverables

Classification system - Taxonomy as glue, keeping things together

…But for our online marketing ecosystem we did not find any standard that we could easily adopt and apply.

Humans have been creating classification systems for ages. �They help us understand complex environments and systems better.

andre@maxlead.com

Project deliverables

Classification system - Taxonomy as glue, keeping things together

Artist Impression

So we designed a universal classification system ourselves for performance comparison across ad platforms

andre@maxlead.com

Project deliverables

Taxonomy as glue, keeping things together - IDs -> Taxonomy

With these taxonomy labels we can classify the vast majority all our campaigns, cross channels:

tx_datasource, tx_client_communication_campaign_name, tx_user_journey_marketing_model_name, tx_user_journey_marketing_model_postion, tx_campaign_type, tx_tactic ,tx_channel, tx_subchannel, tx_targeting, tx_device_category, tx_age, tx_site, tx_site_directory, tx_proposition ,tx_vertical, tx_ad_format, tx_version, tx_gender, tx_profession, tx_relationship_type, tx_interest_business, tx_interest_personal, tx_in_market_audience, tx_marital_status ,tx_childeren, tx_income, tx_accommodation, tx_personal_transportation

​

Note: Labels that are optional, irrelevant or for sake of reporting gain not needed, will get the value "(not set)".

​

Additional use case: The cross channel inventory classification (labels) will help us in machine learning projects. Will save a ton of time for scientists in bringing order is noisy data.

The Prefill command will analyse the inventory and automatically create links to matched taxonomy. Those inventory items which cannot be matched will be put on the todo list of the editor.

andre@maxlead.com

Project deliverables

Once the data is taxonomized (labelled) we can apply targets

Across industries we have selected the most common target metrics:

Impressions, clicks, media spend, CTR, CPM, CPC, etc. �And conversion rate, CPA, ROAS, revenue for various conversion types (high funnel - low funnel, B2B and B2C business models)

Note: metrics that are optional, irrelevant or for sake of reporting gain not needed, can be left empty or set to 0.

andre@maxlead.com

Technologies that were used

Behind the scenes - ELT process (Extract Load Transform)

Select what is needed, test and schedule:

Proper naming for efficiency and reducing errors:

Product notice: SuperMetrics also has a more modern UI with very useful data processing capabilities.

But for our use case, the classic portal and U/I is already sufficient.

andre@maxlead.com

and

Technologies that were used

Behind the scenes - Storage and queries

Raw data lands in the DWH and is organised per source in BigQuery datasets:

Table naming benefit: It tells me & code what is inside and who it is for

Effectively querying the data for a full load or incremental processing:

-- here in this use case, per day a table shard

SELECT

date, campaign_name, campaign_id, SUM(impressions) AS impressions, SUM(clicks) AS clicks, SUM(cost_eur) AS cost_eur

. . .

FROM

`project_id.src_google_ads_v11plus__dwh_bq_supermetrics.GOOGLEADS_C_CAMP01_DAYS_01_00010_*`

WHERE

PARSE_DATE("%Y%m%d", _table_suffix) >= DATE_SUB( CURRENT_DATE(), INTERVAL 30 day )

. . .

GROUP BY

. . .

​

andre@maxlead.com

Technologies that were used

Behind the scenes - ELT process (Extract Load Transform)

Product notice:

As we adopted DBT a couple of years ago and it still satisfies our needs. However, Google’s DataForm (free) would also be a suitable option for this use case.

Upstream (Bronze)

Mid stream (Silver)

Downstream (Golden)

Data transformation workflow:

From left to right:

1. Source models derived from SuperMetrics ingested raw tables (bronze) -> �2. Transformed models -> �3. Report ready marts models/table(s) (Golden)

- Lineage graph - �Every node is a data model capturing restructured data to hand over to the next node (model)

and

65 models transformed into 1 reporting model that has it all: �Optimized for Ease of use, Performance and Cost.

andre@maxlead.com

Technologies that were used

BI - Tools

Short & sweet: Any BI tool with a direct or indirect connection to Google BigQuery can be used.

Or a BI tool with a connection to AlloyDB, Amazon Redshift, Databricks, Microsoft Fabric, Snowflake, Starburst.

andre@maxlead.com

Lessons learned:

• Start with the End in Mind:
• Select the required reporting granularity level to apply taxonomy and targets to
• E.g. Campaign level, Group level or down to Ad/Creative level. Or all of these options.�
• Select only those taxonomy dimensions required for the cross channel reporting project
• Label the inventory and put the rest of the taxonomy on “(not set)”

�

• Stickiness to the old habits of humans
• Highlight the advantages of the new system and add extra productivity features.��
• Overcoming API Limitations
• Explore alternatives and document solutions:
• E.g. In Google Ads, ad variations do not �inherit the ad label of the base ad. �We discovered that the group label is still �available, so we use that instead

​

​

andre@maxlead.com

Why we choose SuperMetrics, BigQuery & DBT

1. Robust & reliable ingestion service that has been around for a long time.
2. Good integration with Google BigQuery
3. A ton of different connectors to select from
4. Sales and technical support with a truly personal touch
1. Helpful in supporting our use cases
2. Ability to think out of the box
5. Just one hour time difference
6. …

​

1. No resource maintenance
2. Robust, reliable, scalable, performant
3. Pay-as-you-go @ fair rates
4. Secure and compliant
5. Built-in ML options
6. …

1. Follows software engineering principles and best practices
2. SQL- Centric transformations
3. Integrates most common cloud warehouse vendors
4. For Small one-man-shop to multiple domain corporate data teams
5. Great user experience
6. …

​

andre@maxlead.com

Questions?

Feel free to contact me andre@maxlead.com or

LinkedIn (/in/andre-koot/)

andre@maxlead.com