Having Troubles with Setting Up Your PPC Reporting Architecture?

Many of us are trying to solve the same problem: How do I get world class PPC reporting which is reliable. relatively cheap and is not complicated to manage? Well, I have been through this as well. Where did I end up? Read more to find out.

Stage 0: No reporting

Basically, you have no reports. You just watch the metrics in Google Ads, Bing Ads, or Facebook UI. You need to visit every system one by one.

Stage 1: Excel

Everyone knows it, right? You go to Google Ads, download some CSVs, insert them into some input sheet in your main Excel dashboard and refresh your pivot tables. Quite simple process but definitely not a solution while having more than 5 clients in your agency. You have even learned how to use Power Query in Excel to create refreshbale reports but you soon realize it’s not enough.

Stage 2: Wow, Supermetrics for Google Sheets is so Cool!

So you are wandering around the Internet and you randomly find out that there is this Supermetrics for Google Sheets tool which can download data from most of the paid advertising system into a Google Sheet on regular basis. Sounds like heaven! You quickly start creating basic dashboards, mostly campaign level in Google Sheets. Your clients are amazed.

GO TO SUPERMETRICS  FOLLOW US

Stage 3: Supermetrics for Google Sheets is still Cool but Cannot Cover All My Needs 🙁

You have been playing with Supermetrics for a while, you have many dashboards in Google Sheets setup but then custom requests start coming in such as:

  1. Client: Hey, this Google Sheets report is really cool but can we add keyword level performance data day by day? Well, you can but soon you will run out of the 5,000,000 cell limit in the Google Sheets. Say you have 5,000 KWs in your account generating impressions on daily basis and you need 5 metrics. You also need to know the name of account, campaign, adgroup, match type, keyword, and final URL. How long will your report last? 5,000,000/(5,000*(5 metric columns + 6 attribute columns)) = 90,9 days. Your cool KW level report would become useless (=“irrefreshbale”) in 91 days.
  2. Client: Hey, I like your Google Sheets report but could you add also data from our internal CRM? Nah, not really. As long as you have your CRM data published to a public CSV link and the file is around 10 MBs, then you import that data to Google Sheet with IMPORTDATA function. However, who would want to have CRM data exposed like that?
  3. Client: Hmm, I like these Google and Bing tables but can you put all the data into 1 table so I can compare performance across both engines? Nah, you can but have to use quite clumsy QUERY function for appending the data into 1 table on a new sheet and then use this new sheet as input for your pivot tables. This will double (x multiply, depending on how many engines you have) all the cells which, again, count towards the 5,000,000 cell limit.

So you now have very likely pretty reliable campaign level reports but you are getting little frustrated with all the stuff you cannot really achieve.

Stage 4: Google Data Studio is Here, Everyone is Data Scientist Now! Or Isn’t?

When the Google Data Studio came to the market 2 years ago, there were big hopes for it. But it still falls short of some operations which it should do without any questions by default.  Let’s sart with “append” or UNION ALL operation which you’d assume Google Data Studio should do from the day 1. Well, even 2 years later, it’s still a bit shaky. There is now a feature called “data blending” which still can only connect only 5 sources together. Before data blending was not available, you could use Supermetrics for Google Data StudioGenerally speaking, it works well but you need to pay for using Supermetrics.

Also, when your data needs to be cleaned up first before importing it to Google Data studio, all the operations you need to do may not be possible. Some custom columns can be solved via calculated fields but some things simply cannot be done.

How about my favorite CRM problem? How do you load data from custom CRM systems to Google Data Studio automatically? Not really possible, you can only upload CSVs or build a custom connector just for every CRM you need.

Also it’s worth to mention that Google Data Studio may not be for everyone. Some operations can be quite advanced.

GO TO SUPERMETRICS  FOLLOW US

Stage 5: Load Raw Data to Big Query First

So after you have been using Google Data Studio for a while, you again came to a conclusion and there are some limitations you cannot bypass. So what’s next? You need to start importing your data somewhere else, cleaning up it and joining it outside Google Data Studio and then load the final dataset into Google Data Studio. So what’s “somewhere else”? Well, in our case and as the headline suggest, it’s Big Query.

Big Query is a serverless data warehouse running in the Google Cloud Platform. It’s automatically scalable, you don’t need to have any database admin. You just need open up a project in Google Cloud Platform and you are good to start looking around Big Query in less than 15 minutes.

For our clients, we load all the raw data from Google Ads, Bing, Facebook, Outbrain, Criteo, Shopify, Search Console, Adjust and various CRM & call tracking systems such as CallRail to Big Query via our own API connectors. We then run some simple ETL processes in Big Query to normalize the data across various sources, consolidate column names, join CRM leads or Calls with clicks  etc. The output is ONE table or view ready to be imported to Google Data Studio or to Power BI. This approach removes the headaches performing data blending in Google Data Studio, you don’t have to use multiple data source since you use just one: Big Query connector. The pro is also that you don’t have to pay for Supermetrics connector.

This solution is not obviously for everybody since it’s quite advanced but at the same time, it’s quite reliable no matter how big your clients are. We have been running this setup for more than 2 years. Having your data stored in Big Query and running SQL queries on the top of the data does not come for free but it’s still quite affordable to run. Big Query can handle billions of rows.  Just to give you an example, a client with 3,000,000 KWs in the account pays around $50/month for Big Query’s processing power. And yes, we store all the KWs on the daily basis 🙂

Update: Starting in 2019, Supermetrics team has released a new product called Supermetrics for BigQuery which can do the heavy lifting of uploading the data to BigQuery.

GO TO SUPERMETRICS  FOLLOW US

Up to this point, I have mentioned Google Data Studio vs Power BI few times. Which solution to choose? Read more here.

Stage 6: Man, This PPC Reporting is Actually Quite Complex.

You realize that PPC reporting is still taking you quite some time every week and you still have not achieved what you wanted to. Even with all the supposedly cool tools you are using. So you give up and hire someone to help you out. Yes, we can help you! Visit our offering here.

***************************

What’s your experience with PPC reporting? Which stage are you at? Do you face same issues? Leave comments in the section below!

Leave a Reply

Your email address will not be published. Required fields are marked *