How to compare PPC campaign performance across multiple channels with BigQuery

8-MINUTE READ · By Misty Faucheux

If you run PPC marketing campaigns across many different platforms, then you know the struggle of trying to consolidate your important data into a single location. In most cases, data is downloaded from, say, Facebook or Google Ads, and stored in a folder on someone’s computer or hosted drive. Certain data is pulled out to use in your success/update reports, and the rest is just left in this repository, never really being analyzed or used.

Part of the problem relates to how different platforms don’t communicate with each other and how, typically, the average marketer doesn’t have a repository for “big data” analytics – unless you work for a large enterprise organization that can afford it.

While not entirely new (it was first launched in 2010), Google BigQuery is starting to get more attention because it provides marketers with an enterprise-level, cloud-based data warehouse at usually a comparable or lower cost than the average system. In fact, if your query processes less than 1 TB of data, you can run your processing for free. The system offers 1 TB of on-demand query processing for free each month.

The benefits of Google BigQuery for marketers

The biggest hassle with setting up a data warehouse is that typically you have to get your IT department involved – if you have one – to do this for you. It requires servers that can handle the capacity of the data and run the queries – without crashing or taking days to do the job. Plus, building your own data warehouse can cost hundreds of thousands of dollars per month to man, maintain, secure and run.

BigQuery is different. It’s cloud-based, which means that you don’t worry about the infrastructure. Google takes care of that. You simply use the system to store and analyze your data. Google already has a large server farm to host the data, allowing you to run analysis over large amounts of information. And, it’s extremely fast, easy to use and scalable. You can have limitless amounts of data stored here, including all your historical data.

So, why would you want to preserve all your historical data? Trends don’t appear within a month’s, quarter’s or even a year’s worth of data. Usually, customer trends only become noticeable over extended periods of time. But you’ll only know that if you’re leveraging all your historical data and doing deep analysis on a granular level.

This is where BigQuery can help. The cost of storing your data in the system is very inexpensive, so the largest expense will come from querying the data. This allows you to create a cost structure that will closely align with your business reporting and analysis. Run as many or as few queries per month as you need.

Why you need to blend different data sources

Now, with all Google products, they’re designed…well…for Google products. This limits your initial data to only Google Ads, Analytics and the like. Yet, many of us run campaigns on a variety of platforms – Facebook, Instagram, LinkedIn, AdSense and more. And, like all good marketing campaigns, these are designed to support each other, meaning that you might be running a Google Ads and LinkedIn campaign simultaneously to reach a particular audience. You want to know which one is more effective in doing this and where there are opportunities for optimization.

So, you need to dig into your data across all these multiple channels. How then do you do it with BigQuery? At Supermetrics, we work to create the lives of marketers more manageable by making reporting easier, released a third-party connectors that allow you to pull all your metrics, performance indicators and other data points into Google properties. This ensures that you can see all of your data in a single location and create comprehensive visual reports representing your blended data.

Recently, we released Supermetrics for BigQuery, which is a native BigQuery Data Transfer Service app for non-Google marketing platforms. You can now transfer all your marketing data from Bing, Facebook, Instagram, LinkedIn, Twitter, CRM, Salesforce, Yahoo and others into the BigQuery data warehouse.

Now, all your data from your myriad of platforms can live in a single location and be queried for reporting and analysis purposes.

For example, perhaps you want to pull customer demographics from the past 10 years from Twitter and Google Ads. First, you need to be able to extract this from your platforms, many of which may not even hold data for that long. Then, you have to wait for all the information to be downloaded, which may take a long time.

With BigQuery, all of this data will be immediately pulled into the platform via the third-party connector and be stored for as long as you want it. You can quickly query the data and download or create reports for what you need.

How to move your PPC data into BigQuery

Supermetrics for BigQuery is super easy to use. Besides, the BigQuery connector, the company also offers a Data Studio connector, which can help you visualize the results of your queries to make better, more visually stunning reports.

Every company is gathering more and more data, which means that organizing that data has become more cumbersome. Yet, you want to be able to leverage this data to your advantage since you can glean comprehensive insights from this aggregate data. This is where BigQuery is especially helpful.

Since it can in seconds search your data and pull together specific, granular data. Your data on high availability servers, which nearly guarantees that there will be no downtime. Plus, the processes are done across different computer clusters, ensuring that you get your data when you need it.

So, let’s say that you’ve been running many different campaigns on Google Ads for more than 10 years. Yet, you really want to determine how many of your customers across all your campaigns have been located in Tucson, AZ, and have converted at any point in time when your campaigns have been running. To do this manually, you’d have to download all of your data, consolidate it and then do the analysis. But if you connect Google Ads to BigQuery, you can simply do a search and find exactly the information that you need.

To get started, enroll in the Google Cloud Platform Marketplace. There is a free trial if you’re just signing up for BigQuery.

Follow the steps to finish the process, and click Start My Free Trial. You need to ensure that the BigQuery API is enabled in your Google APIs Console. Typically, it takes up to 24 hours to start seeing data.

Once you’re enrolled in the Google Platform Marketplace, you need to connect to data sources from the Google Cloud Platform Marketplace. Navigate to the marketplace, and do a search for Supermetrics. This will bring up every connector you can use for BigQuery. Click on the data source that you wish to use, and select “Enroll” from the next screen. Now, you have to configure the transfer, including giving it a clear name (i.e. the name of the data source). Follow the steps to configure the connector. Next, click connect source.

A popup will appear asking if you’d like to Enable a third party data connection in BigQuery. Accept the agreement, and select the account you wish to use. You can change the conversion window if you wish a different timeframe. Select email notifications to ensure that you can see if there’s a problem with the data transfer. Finally, hit Save.

How to create a PPC reporting dashboard in Data Studio from BigQuery Data

Once you have all your data sources connected, you can start building complete PPC reports using Google Data Studio.

Why do you need Data Studio if you have BigQuery? Well, BigQuery is simply the repository for your data. So, it’s really just the first step in the process. You have to pull insights from the data, or it’s just really useless. Where BigQuery is the beginning, Google Data Studio is the end.

Besides the BigQuery connector, Supermetrics also offers a Google Data Studio connector, which lets you get all the data from your BigQuery tables and merge that data together as long as they use the same naming conventions.

You must add and authorize the Data Studio connector in Data Studio, similar to how you did in BigQuery. To do this, follow the below steps:

• Log into your Google account, and go to Data Studio.
• Create a new report by clicking Start a new report and then “Blank”.

• In the lower right-hand corner, you’ll see a plus sign and CREATE NEW DATA SOURCE. Click on this. For existing reports, you should go to Resource and Manage added data sources. Then, select ADD A DATA SOURCE.
• Scroll down until you see “Partner Connectors” or search for “Supermetrics” at the top. Look for the BigQuery connector.

• Select and authorize it.
• You will then see a pop up asking you if you wish to allow Supermetrics to access your Google account. Click Allow.
• Click Authorize again to complete the process.
• You might be asked if you wish to select a team. If you do not, skip this step.
• Log into the data source with the right user account.
• Once authorized, you will have to finish setting up the data source.
• After you do, click Connect. This will then take you to the field list.
• Click ADD TO REPORT.

Once added, you can now select the data source and add it to your reports.

With the BigQuery connector added to your account, your connector will do a few items for you automatically, including the following:

• Set data types for fields. This allows Data Studio to quickly understand what comprises certain fields. For example, if a field contains country information, Data Studio can display that content on a map.
• Creates easy-to-read names for fields.
• Does the formulas for common calculated metrics like click-through rate.
• Adds week, month, year and similar time-related fields.

Now that you have Supermetrics connected with BigQuery, we can take the example from earlier a step further. Let’s say that besides the Google Ads campaign, you were also running Facebook and LinkedIn campaigns for the last five years.

With the connector in place, you can add the social metrics to your data, and then get a comprehensive view of your Tucson customers. With a single query, all of this data will be pulled into a single report for you. You can even determine which campaign has performed better.

Final thoughts

While you have to be careful with cloud storage and processing when you’re scaling since it can easily start adding up with the additional charges for accessing the data outside of the storage costs, you really can save money with BigQuery. More than that, however, is the ability to truly leverage all of your data from a single location and understand campaigns and customer trends.

By leveraging the third-party data connectors, you can import all of your data from all of your campaigns into a single location and actually compare the effectiveness of your efforts – no more need for manual analysis and lost historical data.

Both BigQuery and Supermetrics data connectors are easy to use and come with free trials. Start testing the systems today, and see how you can dramatically improve your data points and campaign results.

About Misty Faucheux

Misty Faucheux is an Integrated Online Marketing Specialist at Faucheux Enterprises and a guest writer for Supermetrics. She is a digital marketer, specializing in SEO, SEM, content marketing/writing and social ads. Misty helps companies develop a cohesive online marketing strategy that directly addresses their overall business goals and objectives. You can find her on Twitter, LinkedIn, Instagram and Flickr.