Problem

Yet another customer approached me to solve their reporting needs when it comes to digital marketing campaigns. The customer wanted to aggregate these sources:

1. Google Ads (ad group level)
2. Microsoft Advertising (ad group level)
3. Ringba (call tracking and routing system)
4. A few reference tables in Google Sheets

The expected outcome was a nice Power BI dashboard showing campaigns performance across ~20 accounts.

The challenge was to keep the monthly cost reasonable so the preference was to try to use Make to do all ETL work instead of bespoke (and expensive) tools such as Funnel.io or Supermetrics.

Solution

Over the years, Make has been silently adding a lot of apps around digital marketing and many of these apps focus on downloading performance data. Namely, you can use these apps to download performance data:

1. Google Ads Reports
2. Microsoft Advertising Reports
3. TikTok Reports
4. Facebook Insights
5. LinkedIn Ads Reports
6. GA4

Here, you will noticed Ringba is not supported because it’s a relatively niche app, so to get the data out of Ringba, I used Make’s HTTP app to call Ringba’s API directly.

When executing the project, I had to:

1. Decide where you want to store the data – in my case, it’s BigQuery as usual.
2. Evaluate how much data we’ll be downloading with Make every day – if it’s ~50k rows per data source per day, Make should be good enough for the job.
3. Build Make scenarios
4. Run some aggregations and normalizations in BigQuery via SQL
5. Decide what visualization tool we will you use to connect to BigQuery (typically either Looker Studio or Power BI)
6. Build the dashboards

Scenarios and Outcomes

To get data from Google Ads, I decided to use native transfers in BigQuery so there is no Make scenario. The reason for that is that the native transfers allow you to get a lot of different reports under one configuration. And we also had other use cases for Google Ads so it was a no brainer not to use the native feature.

To get data from Microsoft Advertising, I built a scenario looking like this:

When working with Microsoft Advertising reports on Make, it’s important to realize that the output of the app is a .zip file which first needs to be extracted. Within the archive, you will find your CSV which can be parsed and transformed into a new file suitable for BigQuery upload. When I upload data to BigQuery with Make, I always upload them as CSVs.

To get data from Ringba, I built a scenario looking like this:

This scenario had one tricky part where I first needed to request data export in Ringba and then I needed to check whether the report data was ready for download. If yes, I proceed further, if not, I need to wait for a bit and check again. Once I retrieve the report, I again parse the CSV and convert it to another CSV suitable for BigQuery upload.

Then in BigQuery, I create a few SQL views which mostly aggregate data from multiple tables (Google Ads, MS Ads, Ringba) and the key trick is to use UNION ALL.

Once the final SQL view was ready, I connected Power BI to BigQuery and designed a dashboard looking like this (of course, there are more visual elements than this in real life):

Conclusion

After many years of running PPC campaigns, the customer is finally able to see correlations between Google Ads, MS Advertising, and Ringba. Also, the customer is now able to properly track net profit based on true cost from Google Ads and MS Advertising and true revenue coming from Ringba.

When it comes to operations consumption in Make, we are talking about 20-50 operations per day to get all the needed data so even the cheapest Make plan is enough to run this magic. 😎

Are you facing similar challenges?
HIRE ME

Or maybe you want to try yourself?
GO TO MAKE