As you work with spreadsheets you pick up formulas, tricks, and formatting lessons to make reporting easier. I often see users get stuck when it comes to combining these pieces into a complete report.
We recently onboarded a client and needed to replicate one of their old reports. Most of the report created manually, updating a row at a time, every day. This is not a sustainable practice and I can’t imagine committing someone to this process.
In this article, we’ll work through a few examples from this report. Each step will assist in automating your own complex report and creating dynamic tables to save your time and sanity.
Making the Most of Our Query()
Let’s dive in! This post will specifically focus on creating a dynamic table. The data table will adjust to changes in campaigns, dates, cleans up the column names and calculates subtotals for each section.
We will primarily rely on the query function, which might be the best reason to use Google Sheets. If you’d like another take on the topic, check out Briana Ogle’s post, Google Sheets Query Function Need-to-Knows For PPC
Working with dates
In order to filter by dates, we’ll need to modify our SELECT. Queries use text arguments so we can’t throw a plain date in there and expect it to be interpreted correctly. We need to use the text() and date() functions to insert a date in our query.
If you try this and receive a parse error, check your quotation marks. Most issues are caused by a mix-up between the single and double-quotes.
In this example, we used the first of July. It’s a month to date report. If you wanted an always current month to date report, you could add a dynamic range. Instead of a static 7 for the month, you could replace it with =month(today()). This returns the index of the current month and keeps your date() function current all year.
If you need to get more creative with your own report check out all the date functions available in Google Sheets.
Filler rows
Sometimes you want a column that simply acts as a label. In this example, we need a platform name. You can quickly add this to any query by adding the value in your “Select “. Inserting the desired text is the equivalent of setting all rows in the column equal to that value.
This makes it easy to label sources for your data or add extra information to the results for help with future filters or analysis.
Labels
Labels clean up your queries by customizing column names. By default the query will return sum(Clicks), sum(Conversions), sum(Conversions/Revenue).
These tell you where the number came from but they aren’t readable. Alternatively, you can use labels to rename columns to more appropriate terms for your business. Maybe you call conversions transactions or maybe Impressions are views.
If you were paying attention to the last screenshot you’ll notice that our first column is labeled “BING” which was our filler value for the first column. We can remove that with a label!
Labels are created with a simple command at the end of the query. All you have to do is put the current name and what you’d like to replace it with.
In the example above we replaced the names of all our calculated columns and replaced the name of our filler column.
Combining multiple queries
Now we have our data and labels set up for Bing. We still need Google too. We could place another query below our Bing query but that gets messy. If the number of rows changes the query might not have room to expand and would return an error. If we put a large number of rows in between them, we would need to revisit this document to hide and expand rows as needed.
You can run multiple queries together by wrapping them in curly brackets and separating them with semi-colons.
This runs each formula sequentially. No matter how many rows are returned by the formula the next formula will be appended afterward.
Row counts can still change between runs though. You may want to explore conditional formatting to apply the needed formatting to your tables.
Subtotals
Now we have all our ad data in a single table but it’s a little messy. It requires too much mental math to compare aggregate performance across platforms.
We can add total and subtotal rows between queries. Instead of a query(), we can insert the other formulas we’d like to use. In this example, we use a combination of sumifs() to line up the data with our month to date queries. For consistency, you could use a query() for everything but I prefer single line returns.
Seeing it All Come Together
Once we combine all these pieces, we have a new table that automatically updates month to date numbers and displays them in a dynamic table.
Compared to a more manual approach, we’ve saved a large amount of pivoting, summing, copying, pasting, and row editing. Once the data updates, our report tables will automatically update to adjust for campaign changes across platforms.
If you’d like to increase your spreadsheet skills further. Check out The Complete Guide to Excel for PPC. It’s based on Excel but almost all of it carries over to Google Sheets as well!
