What is BigQuery to marketers and why should marketers be using it? (part 1)

Overview

BigQuery is a data warehousing platform that allows marketers to uncover what’s working and what can be improved in their digital marketing campaigns. With this invaluable insight, BigQuery allows companies to increase the effectiveness of their marketing campaigns by 79% and tailor these campaigns to their relevant goals. This article will demonstrate the tremendous capabilities of BigQuery to marketers and why it is a must-use for every digital marketer today. The sample of data used in this report is real data from a Business-to-Business (B2B) software-as-a-service (SaaS) company. All currencies are in Hong Kong Dollars (HKD) where 1 USD ~ 7.8 HKD.

Objectives

In this article, we will demonstrate 3 use-cases of BigQuery in the analysis of 2019 and 2020 Google Ads Data for a specific company:

1. Predicting which variables (Day Of The Week, Device, Location, etc.) have the highest conversion rates
2. Predicting which Bidding Strategy Type has the lowest cost per conversion and lowest cost per click
3. Comparing 2020 Google Ads performance to 2019 to see if cost per conversion and cost per click was lower or higher

Use Case #1: Predicting which variables (Day Of The Week, Device, Location, etc.) have the highest conversion rates

There are many different variables that can impact whether or not a conversion takes place. For the sake of simplicity, we will analyze three of these variables and try to determine which of these are optimal for maximizing conversions. 

Firstly, we will look at the data regarding the Devices used and see whether a particular device is superior in its average conversions. In order to account for differences in the total number of data for each device, we will consider the average conversion metric as a means of comparison. 

According to the table generated by BigQuery, when the device is a Desktop there are more than 4 times as many average conversions than when it is a High End Mobile. In addition to this, it is important to note that there were no conversions on a Tablet. This provides insight by suggesting that more focus may be needed to be put into the mobile/tablet version of the website in order to make it easier to convert. Similarly, it also suggests that if the company wants to run its next campaign to maximize conversions, then it should aim its AdWords to those who are likely to use a desktop. 

Next, we can examine and compare the average conversions for the different days of the week.

The table highlights that Friday is the most successful day in terms of generating conversions. Marketers can use this insight by potentially launching a new campaign around these top three days in order to maximize average conversions. 

Finally we will investigate which country has the greatest average conversions.

The summary table illustrates the top 8 countries that this company targets as well as their respective average conversions. From the table above, it can be seen that the most successful countries are India and China in terms of its average conversions. This is important to note as it can advise the target locations for the company’s future campaigns that aim to maximize conversions. From the visual interpretation of this table, it does appear that the location of the company’s Google Ads plays a significant role in predicting whether a conversion takes place. 

Key Insights

Overall the Google Ads data for the 2019 and 2020 campaigns provides some valuable insights for future marketing plans. First, we can see that there needs to be an increased focus on improving the mobile/tablet version of the website in order to boost average conversions. In addition to this, our investigation has revealed the importance of the day of the week as a predictor of whether or not a conversion takes place. Finally, this analysis reveals locations such as Singapore, where the company may need to change its current marketing strategy in order to boost conversions. All in all, this helps to reinforce which areas of the marketing are working and which need improvement for future campaigns.  

Use Case #2: Predicting which Bidding Strategy Type would have lowest cost per conversion and lowest cost per click

First, we need to be certain about the number of different bidding strategies used in the dataset. We can do this by querying the data on Ad Groups and count how many of each bidding strategies are used in total:

From the table, we can observe that the most used bidding type strategy is to Maximize Clicks, while the least popular was to Maximize Conversions alongside ‘Null’. According to Google Ads Script, “If an ad group has an anonymous bidding strategy, or no bidding strategy, null is returned.”   

Now, let's analyze the total number of clicks, costs, impressions and conversions between all the Bidding Strategies. In order to account for the popularity of each strategy, we shall divide each attribute by its frequency (i.e. taking the average). This will allow for a fair comparison between the different bidding strategies:

From this table breakdown, it appears that while the bidding strategy type that maximizes average clicks is the “CPC” method, it has resulted in very little total conversions. On the other hand, the target CPA (Cost Per Acquisition) method is the best in terms of its average conversions even though it has a relatively lower average click metric.  

According to the table, the Target CPA method minimizes the cost per conversion. However, despite this it has the greatest cost per click relative to all the other methods. From the table we can see that the ‘Maximum Conversion’ strategy has the second lowest cost per conversion metric and the lowest cost per click. If we compare the two strategies of Target CPA and Maximize Conversions, we see that the cost per conversion is 92.5% higher in the “Maximize Conversions” strategy. Contrastingly it is 95.7% lower in terms of its cost per click. Therefore, using this analysis it may be hard to pin-point one certain optimal method as this may differ depending on the goal of the campaign. 

Key Insights

From our analysis of the different bidding strategies used by this company over the 2020 and 2019 campaigns, we can see that each method has its own advantages and disadvantages. Depending on the goal of their next campaign, the appropriate method should be chosen; for instance, if the goal is to increase awareness about the brand then the company should lean towards the CPC method of bidding since it has the highest average clicks. On other hand, if the goal of a campaign is to minimize the cost per conversion then we can conclude that the best Bidding Strategy is the “Target CPA”. Therefore, BigQuery allows marketers to analyze which bidding strategies are fit for their digital marketing goals. 

Use Case #3: Comparing 2020 Google Ads performance to 2019 to see if cost per conversion and cost per click was lower or higher

In order to compare the performance of the Google Ad campaigns from 2019 and 2020, we should first query the conversion table and compare which of the two groups had more successful conversions. 

In order to get accurate data on the conversions for both years that have resulted from Google Ads campaigns, we have pulled data from Google Analytics into BigQuery. This is a key feature of BigQuery as it allows you to aggregate and analyze data from multiple sources, which leads to tremendous insights. 

According to the table, although the 2020 campaign has been able to gain more total conversions, we can see that it has spent more money per conversion. This is reflected by it having a larger cost per conversion than the Ads in the 2019 campaign.  

Despite this, a possible explanation for the difference in total conversions may be due to differing goals of the campaign. Therefore, we shall explore the other metrics and compare these between the two years.

According to the table, it can be observed that the average clicks for the 2019 campaigns is slightly higher than that of the 2020 campaigns. This suggests that the 2019 campaigns were more successful in raising brand awareness than the 2020 campaigns. This is further reinforced by the fact that the average impressions for 2019 is significantly higher than 2020. Contrastingly, the Cost Per Clicks in 2020 is less than 2019. This is very interesting since the cost per conversion and total conversions in 2020 was greater than in 2019. Thus, this suggests that there were a greater amount of cheaper (by CPC) Google Ads adopted in 2020. On the other hand, the data paints the picture that in 2019 there were fewer more expensive advertisements that had a greater conversion rate. 

Key Insights

Overall, our analysis has shown the differences in the performance between Google Ads in 2020 and in 2019. The Ads in 2020 seem to be more cost effective, which is reflected in the lower cost per click statistic. On the other hand, the 2019 campaigns seemed to have greater interactions, which can be seen through its greater average clicks and impressions. Additionally, it seems to have more success in leading to a conversion as reflected in its lower cost per conversion metric. All in all, our analysis suggests that 2019 had more effective campaigns than 2020. Thus, depending on the goal for future campaigns, whether it be maximizing awareness or revenue, the company can model its marketing campaigns on those from 2019. Therefore by allowing for the aggregation and warehousing of data from multiple sources, BigQuery provides the marketer a key insight on how to model future marketing campaigns based upon prior campaigns.