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.
Date format
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.
Conclusion
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.