Unlock New Functionality And Save Time With Supermetrics Queries
January 23, 2018
Loyal PPC Hero readers know that we’re big fans of the automation software Supermetrics. The product’s Google Sheets add-on is a powerful tool for PPC marketers to accomplish a wide-variety of tasks; from creating reports to automating bids and budgets, the tool allows digital marketers to work more efficiently and save a ton of time.
If you use the tool, you know that there’s plenty of flexibility offered through the add-on’s primary user interface, a sidebar that offers a step-by-step process for importing data that gives users the option to select numerous metrics and dimensions, and source the data from different date ranges and accounts. Digital marketing nerds can easily get lost in the sheer versatility of the tool.
For the vast majority of reports we set up and data sets we want access to, the side bar gets the job done tremendously. But in rare cases where the options in the side bar aren’t sufficient to accomplish the task at hand, it’s often possible to edit the Supermetrics query directly to unlock additional functionality. I’m going to walk through two use-cases where diving into the queries themselves unlocks some extra functionality and can save you even more time.
Use-Case Number 1: The Unusual Date Range Request
One of our clients requested a report that could update daily and would show month-to-date performance compared to the same period the previous month. We can imagine that this sort of comparison might be useful in cases where the client has end-of-month sales, or other regular events within a month that would have a significant effect on their metrics.
Let’s take a look at the date range options in Supermetrics and see how we might accomplish that. The date range at the top is the primary date range we want data from, and the drop down opened below shows the options we have for the comparison date range:
Hmm. It appears that we may have hit a roadblock. “Previous period of the same length” isn’t quite what we want – that will show the days immediately preceding our primary date range. The year-over-year data won’t satisfy the client’s request, either. We can select a custom date range and manually put in the dates we want, but then we would have to update it manually prior to every refresh.
Luckily, the flexibility of Supermetrics Queries gives us a solution! First, find the Supermetrics Queries sheet. It is hidden by default, so if you haven’t already exposed it, you should be able to find it under the “View” dropdown then “Hidden Sheets”. You can also find the queries sheet by finding “Supermetrics” in the “Add-ons” menu and selecting “Manage Queries”.
Now, let’s write a formula that will give us the date of the day one month ago. We can do this using the “edate” function, which displays the date a number of months before or after a date. The full formula (which will convert the result to the date formatting we need) looks like this – =text(edate(today(),”-1″),”YYYY-MM-DD”).
Now we can grab the date of the first of last month by using the split function to break up the values of the date one month ago. Once you have the year, month, and date split into distinct cells, you can use the append operator to create the date of the first of the previous month by substituting “1” in the day spot. The screenshots below illustrate this process in full.
Finally, let’s go to the query itself to insert this dynamically generated date range. First, find the “Compare to” column. The syntax should be “custom_YYYY-MM-DD_YYYY-MM-DD” where the first date is the beginning of the comparison range and the last date is the end of the comparison range. Again, use the appender operator to replicate this syntax while referencing the comparison date range.
Refresh your query and voila! You’ve successfully updated your report to compare to the same period in the previous month.
There are other cases where it can be useful to edit the query for custom date ranges. Imagine that you have a client that introduced significant changes to their account or business on a specific date. In a case like that, it may be helpful to import just data that’s accrued after that date, but up to the present. You can do this by editing the “Date range type” column like in the example below:
Use Case #2: Bulk Updates
Those of you who have worked with clients with franchises or multiple locations know that reporting can quickly become a challenge, especially if each location has its own stakeholders who appreciate detailed reporting. In cases like these, playing with the Supermetrics queries themselves can be exponentially more efficient than the alternative. Imagine that you’ve created reports with multipole queries for individual locations or franchises, where the only difference between the reports is the location or franchise that is being reported on.
In cases like these, duplicating the template for the report and utilizing the simple “Find And Replace” feature can allow you to upload each query according to quickly create new reports for different locations or franchises. For example, in the screenshot below, we’ve got 15 queries collecting data for a report. We’re filtering the data for campaign names that contain “Location 1” to create a report for that specific location.
Let’s say we want to duplicate this report, but for “Location 2” instead of “Location 1” Rather than update each query separately via the sidebar, we can update the filter for each query at once by replacing “Location 1” with “Location 2” in the Filters column. Then, select, “Refresh all” and your new report will be created!
If you’re a Supermetrics user, you’ll likely find that once you start playing around with the queries themselves you’ll start finding new and better ways to use this already-powerful tool. Chances are, future posts will explore some additional uses, such as referencing data from drop-down menus to make reports more user-friendly and versatile. In the meantime, tweet at us with any new ideas or questions to @ppchero!
Browse By Category
Take the 2018 State of PPC Survey!
We're looking for marketers to take our State of PPC survey! The survey takes about 5 minutes to complete and establishes benchmarks, identify trends, and helps marketers around the world understand what's working and what's not in PPC.
How To Avoid Overlap Between Multiple Similar PPC Accounts
Get tips on how to avoid overlap between multiple similar PPC accounts and start becoming more effective at running ads for multiple or related brands.
Why You Should Utilize a Facebook Catalog For Vehicle Sales
Set yourself up for future success by building a vehicle catalog on Facebook. From remarketing to prospecting, get your inventory in front of buyers and automate the process of ad creation.
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
Amazon Launches Seller Central Sponsored Products Dashboard
Amazon has released a new dashboard feature for the Seller Central marketing platform. Found in the Seller Central campaign manager, the dashboard makes it easier than ever to visualize your campaign data and take away valuable insights from your Amazon marketing account.
Boost Social App Campaign CPIs During High Peaks of Seasonality
Understand when it can become cost-effective to shift budget dollars to social app campaigns during periods of peak interest.
How To Use AdWords Geographic Data Reports
AdWords geographic reports provide performance data by location. This data is vital to identifying efficiencies and growth opportunities. In this article we will cover the three main rebuilt reports in AdWords and cover their uses.