Unlock New Functionality And Save Time With Supermetrics Queries
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
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!
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.
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
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.
How Much Should I Spend: Aligning PPC Budgets to Goals
Learn about the strategy behind PPC budget recommendations and forecasting.
Load More »