Problem

A client approached me with an evergreen problem: “My agency reporting sucks, can you help?”. Oh, I so had not heard that one before! (Actually I had).

This was the client’s situation:

1. Digital marketing agency is servicing around 20 customers.
2. All reporting used to be done in standalone Excel files on client level. First ouch!
3. The raw data was simply pasted into all the Excel files from manually downloaded reports from Google Ads, Microsoft Advertising, and Meta Ads (long live Facebook Ads!). Second ouch! A massive one.
4. As you can imagine, all the reports were similar but not the same. Huge room for errors.

The assignment was to:

1. Automate data pulls. Of course.
2. Build a visually appealing dashboard which will be unified for ALL agency customers.
3. Automate dashboard refreshes.
4. Introduce an option to segment campaigns by inputs 100% controlled by the client. I.e. the client will not need me every time he needs to change their campaign segmentation.
5. Compare agency customers’ performance against targets (budget, sales, revenue, ROI). Again, the client needs to be able to easily manage targets without my intervention.
6. Create advanced metrics such as “estimated spend by the end of the current month”, “average daily spend needed to hit monthly budget”.
7. Create comparison metrics such WoW, MoM, YoY.
8. Have an option to completely exclude certain unwanted campaigns from the final dashboard.
9. Have a filter option mimicking same time periods as those in Google Ads (Last 7 days, this month, etc.).
10. Refresh the data multiple times per day – every 6 hours.

Solution

After a few discussions, we resorted to this setup:

1. Funnel
2. BigQuery
3. Google Sheets
4. Power BI

Let me now talk you through the configuration. The client decided to use Funnel.io to download data from all the marketing platforms. We then use Funnel to push data to BigQuery which we use as our data warehouse. Funnel is basically a much cheaper alternative to Supermetrics for BigQuery.

In BigQuery, we consolidate all the data from:

1. Marketing platforms
2. Google Sheets inputs
   1. A table storing client references such as names and associated account IDs.
   2. A campaign classification table.
   3. A table storing targets such as budgets, order targets, revenue targets.

The word “consolidate” means that we create one master SQL view which is aggregating all the the data into one flat “table”.

We then connect to this “table” with Power BI. where we built the dashboard with all the custom metrics.

Some of the inputs:

Input of campaign classifications:

Input of agency clients’ targets:

By having these inputs accessible in Google Sheets, the client has no dependency on anyone and can freely change the targets and classifications. The inputs sheets are ingested by Funnel and then sent to BigQuery every hour where they are “integrated” into the master view.

End Result

By now, you might be wondering how the final dashboard looks, right? Here we go:

And of course, every nice dashboard needs charts:

 

What to look for in the images?

1. Yes, this is the dashboard for ALL CLIENTS. Notice the blanked out “Client” filter in the upper right corner where my client can filter for any of his agency clients on demand.
2. On the first image, pay attention to the upper left corner where we calculate current cost pacing, compare actual performance against targets etc. Quite helpful for hitting the client targets.
3. On the first image, check out all the WoW, MoM, YoY comparisons below the big “card” metrics.

Conclusion

As much as I’d like to tell you this project was a “piece of cake” – it was not. Actual work had to be done! It took about net 3-4 man days to go from nothing to the state shown on the pictures. Overall, the project took 12 days to deliver which is still pretty quick. The project required advanced SQL to e. g. convert monthly targets to daily granularity and also advanced DAX formulas to calculate all the comparison metrics in Power BI. Also worth to mention that there was no way to meet all the requirements in Google Data Studio – especially around the pacing and comparison metrics. Sorry to say, but Data Studio is just too lame for such advanced stuff.

And what about the client? The client has dumped his messy scattered Excels and finally has a single source of truth for all his clients and employees.

Does your agency need a similar reporting solution?
HIRE ME