Google Sheets Query Function Need-to-Knows For PPC
If you are a hard-core Excel only user this article is not for you. While Excel is powerful especially combined with Power BI, sometimes Google Sheets is the answer. In an industry such as digital marketing where everything we do is online, highly collaborative, and shared with outside clients Google Sheets has its benefits. We have a lot of clients with special needs when it comes to reporting and we respond to those needs with custom built Sheets dashboards. The two largest benefits of doing this are that the data can be automatically pulled in and it refreshes automatically so the client always has up-to-date insights.
The best instrument for building PPC reports is Google Sheets query function. I will cover some of the most important initial items to learn when starting out. This is not a lesson in the overall syntax of the query function so some familiarity is required.
Referencing Another Cell
It’s possible to create dynamic query data based on information in another cell. I use this when creating reports to have the option to view the metric data one by one instead of laying all metrics out at once. This is especially beneficial when linking the data to a chart. When referencing another cell within a query function you’ll need to get the syntax correct based on the referenced cell format.
For the context of the example below, I have a data validation in C4 (Google) for my platform options and a vlookup in D3 (D) pulling in the column letter of the metric in C3 (impressions) from a separate table. When I change the metric in C3 the letter in D3 automatically changes as well.
Looking at the query function you can see that I am referencing both C4 and D3 in the formula but with slightly different syntax. The data that D3 is referencing is a value which requires a double quote and an ampersand on either side. The C4 cell is referencing data that is text and requires a single quote on the outside in addition to the double quote and ampersand. If you want to know more about the why behind this check out this document, Google Sheets Query Functions.
Another common theme in PPC reports is utilizing date conditions from another cell when querying data. Firstly, your date has to be in the format YYYY-MM-DD. You can accomplish this with a text function.
Furthermore, when referencing this cell within a query, you must include the date function prior to the single quote, double quote, and ampersand.
Combining Multiple Data Sheets
Want to bring data together across multiple sheets? You can do that with a query function. The syntax is easy, add curly brackets at the beginning and end of the data ranges and use semicolons to separate them. Keep in mind that your columns must be in the same order across all sheets, which may require adding in some dummy columns.
Importrange with Query
Sometimes I work with a lot of data and Google Sheets cannot handle all of it in one doc. This means that I have to pull in data from other sheets as I need it. This is somewhat easy with an importrange function inside of a query function. You are adding the importrange function and pulling data from the external doc’s set data range. However, one very annoying thing to note is that you can not use column letters within queries based on imported ranges. Instead, you must use the column number formatted as Col4.
Querying Calculated Metrics
This last one is almost common sense, but definitely worth noting as it once was a foreign concept to myself. If you are using a query function to pull in metrics such as clicks or conversions, you can use the sum() within a query. However, if you need to pull in rates such as CTRs or conversion rates you can’t simply take the sum(), because that doesn’t make sense or the avg(), because this is inaccurate.
You must re-create the formula used to calculate the metric. For example, if you want to query CTR it will look like this: sum(clicks)/sum(impressions), replacing the text with the column letter of course.
Google Sheets query function isn’t perfect and it is missing some functionalities, but it can be a really good tool for manipulating data sets. If you get the query function down pat it will become your new flame and sumifs will only be an occasional fling.
Browse By Category
4 Reasons I Prefer Facebook Ads over Google Ads
4 reasons to think about choosing Facebook ads over Google paid search.
How an EU Commission Ruling Can Mean 20 Percent Lower CPCs for Your Google ...
In June 2017, the EU Commission sentenced Google to pay a fine of 2.4 billion euros. Now, up to 5% of Google’s daily revenue is at stake if they fail to make their Google Shopping channel more competitive. Google is taking aggressive measures to adhere...
How to Make Remote Working Successful
Working remotely has its challenges. Explore 4 tips that will help to make remote work a success!
A bi-weekly newsletter packed full of resources and strategies that will help make you a better PPC expert.
Hanapin Marketing | The PPC Agency of Experts Behind PPC Hero
Best Practices for LinkedIn Sponsored InMail Campaigns
To reach an engaged audience in a professional setting, LinkedIn uses the format of a custom, personalized message directly in the user's inbox with Sponsored InMail.
5 Things That Matter To Brands When Working with An Agency
We wanted a better pulse on what attributes brands consider the most important things when working with an agency, as well as the things they wish their agencies were better at. So we asked! Click through for the results.
The Latest PPC Updates to Catapult Campaigns in 2019
In this webinar, Hanapin’s Kelly Pollock and Optmyzr’s Fred Vallaeys will point out which updates have made (and will make) a major impact on digital marketers, and what we should be preparing ourselves for as we fly into 2019.
Load More »