SUPERMETRICS FUNCTIONS · 9-MINUTE READ · By Joshua Goy on October 24 2017.

If you are a fan of using spreadsheets for data analysis like I am, be sure to try out a “secret weapon” that I use for marketing reporting – Supermetrics Functions. This tool is a custom function, which can pull multiple metrics from marketing platforms (you have 11 to choose from) directly into the spreadsheets. This function can be typed directly into spreadsheet cells, just like any other function.

As discussed in some of our previous articles Presenting Results To Clients and Excel Automation With Supermetrics in Efficient Media we have been using Supermetrics to automate most of our reports. In this article, we will be showing some examples of how to use Supermetrics Functions to create a date metric chart, combine query data and use multiple filters. And here we start!

Creating A Date Metric Chart

To set up a chart based on dates use the parameters shown in column B. Using “INCLUDE_HEADERS” in Settings helps keep track of what columns are showing what metrics. You can learn more about parameters here.

Using the Supermetrics formula “{=SUPERMETRICS(parameter1,parameter2…)}” the data retrieved is displayed in column D and E. Remember the Supermetrics formula must be entered in as an array by holding CTRL+SHIFT when pressing enter.

The format of the date value shown may not be desired for the chart as it is shown as YYYYMMDD (4-digit year, 2-digit month and 2-digit day) and cannot be changed. Using a formula in column C allows you to fetch the date from column 4 and change its format.

The formula for row 2 is “=DATE(LEFT(D2,4),MID(D2,5,2),RIGHT(D2,2))”. This extracts each part of the date and organizes it into a readable date format for Excel which you can change as needed.

Finally, a chart can be inserted into the worksheet by selecting the data in Columns C to E. Column D is automatically included in the horizontal axis, and will need to be removed from the chart. To remove column D Click the Design tab, Click Select Data, Click Edit under Horizontal (Category Axis Labels) and change the formula to only incorporate column C as shown below:

Account Level Dimension Restrictions & Combining Query Data

It is possible to view data across multiple accounts by typing each of the account numbers in the profile ID section separated by a “,” or “&”. There are some restrictions on dimensions which can be used across multiple accounts which when tried will result in an error. Gender and Audience are 2 examples of dimensions that can’t be summed together across multiple accounts.

Shown above is an example of how you can combine two queries together to get clicks for Gender summed across 2 accounts.

Multiple Filters

To help minimise unneeded data and speed up processing times for your queries it can be helpful to add multiple filters. Filters can be added together as ORs with “,” or used together as ANDs with “;”. For information on filters visit Google Analytics.

As an example, we wanted to fetch data on our expanded ads. For larger clients this would be a lot of data to sort through. So we used “adtype=~Expanded text ad;clicks>0” as a filter. This filter makes sure any ad types of ads that contain the text “Expanded text ad” which also have at least 1 click would only be shown rather than all ads regardless of clicks.

You can then use pivots or formulas to combine values of similar ads together and compare them to one another by performance.

Conclusion

I this article I showed you what Supermetrics Functions is, why it is useful and how to work with this tool to create a date chart and work with data pulled by queries. Functions is an especially useful tool for those who know VBA – then you can can modify every function to fit your specific needs. I will be happy to hear back from you: if you have any further questions, please reach out to our friendly team on: team@efficientmedia.com.au

About Joshua Goy

Ryan_Purkey

Joshua Goy is a Digital Marketing Analyst at Efficient Media and uses Supermetrics Functions to automate his agencies reporting needs. He manages and reports on SEM, display, video and social campaigns. Joshua is always looking for ways to improve efficiency and accuracy of ongoing business processes. You can find him on LinkedIn or Facebook.


x

Get started with Supermetrics.

#1 reporting automation tool for PPC, SEO, social and web analytics.
 Free trial with full features. No credit card required.

Start a 30-day free trial