Skip to content

Support -
Supermetrics Functions

Here you can find support on using Supermetrics Functions. In case your question isn't covered here, please visit our support forum (You can search the forum for answers, send us a private ticket, or create a public forum post)

 

Getting started

  1. Download SupermetricsFunctions.xls or make a copy of this Google Spreadsheet.
  2. Get an authentication token from supermetrics.com/functions-login and copy it to the sheet, and it will automatically load some of your data. 
  3. There are some examples on how to use the functions in the spreadsheet - by modifying it you can create all kinds of reporting solutions. Just type "=Supermetrics(" and some values for the required parameters. You can see all the parametrics listed here.

Note that when using the Excel version, you need to have macros enabled in Excel's security settings for the functions to work (after changing the settings, you may have to restart Excel).

Parameter Reference

getAccountData(token, dataType, includeHeaders)

token

The token obtained from supermetrics.com/functions-login

dataType
The kind of account data you want to fetch. There are three possible values: 'profiles', 'goals' and 'segments'. If you leave this out, profiles will be fetched.
includeHeaders
Whether to include column headers. Use boolean values (TRUE or FALSE in the English Excel)

Supermetrics(token, profileID, metrics, startDate, endDate, dimensions, pivotDimensions, filters, segment, sort, settings, maxResults, maxCategories)

token

The token obtained from supermetrics.com/functions-login. Note that the token defines which data source is accessed.

profile ID
A numeric ID for the profile/account/video/page you want to access. You can see this number in your browser's address bar when looking at the profile's data in GA, or you can use the getAccountData function to fetch a list of all your entities and their IDs. For AdWords queries, input the customer ID of the AdWords account. For YouTube, enter the video ID or "TOTALS" to fetch channel totals.

metrics

A list of metrics you want to fetch, separated by ampersands. You can find a list of metrics via these links:

Example: "visits&pageviews&bounces"

start date

The start date of the time period for which you want to fetch data. Either insert a refrence to a cell with a date, or type a value. In version 2.31 and newer, the value can be anything accepted by PHP's strtotime function, meaning a date like "2014-01-01" or a relative date reference like "first day of last month". In older versions, you need to use either a refrerence to a cell with an Excel date, or type the value in the YYYY-MM-DD format.

end date

The end date of the time period for which you want to fetch data. Either insert a refrence to a cell with a date, or type a value. In version 2.31 and newer, the value can be anything accepted by PHP's strtotime function, meaning a date like "2014-01-01" or a relative date reference like "first day of last month". In older versions, you need to use either a refrerence to a cell with an Excel date, or type the value in the YYYY-MM-DD format.

dimensions

Determines how the data is split into rows. You can split by up to 10 dimensions. List of dimensions can be found via these links:

Example: "source&medium&keyword".

Note that if you include a dimension in the query, the results will need a range of several cells to fit, and thus the function needs to be inputted as an array formula - see instructions on how to do that here.

pivotDimensions
Determines how the data is split into columns. See the "dimensions" parameter for links into list of possible values. By default, categories are sorted in descending order by the first metric, unless the pivot dimension is a time dimension, in which case it's sorted chronologically. If you want the categories sorted alphabetically, you can add PIVOT_SORT_ALPHABETIC to the settings parameter.
filters

You can type a filter string to restrict the data. See instructions here. Example: "visitorType==New Visitor"

segment

You can specify a Google Analytics segment here. You can either use segments created through the GA web user interface, or "on-the-fly" segments - see full instructions here. To use a predefined segment, you can simply type the number of the segment in quotation marks (you can fetch a list of your segments and their numbers with the getAccountData function).

You can include multiple segments by posting this parameter as a JSON array that includes the segment ID and name. For example, this JSON specifies two custom segments:

[{"ID":"sessions::condition::ga:goalCompletionsAll>0,ga:pageviews>2","name":"my segment 1"},{"ID":"sessions::condition::ga:pageviews>5","name":"my segment 2"}]

Note that if you want to see the results separately for each included segment, you need to include the segmentName or segmentID dimension in the query. Otherwise the tool will sum together the results for all included segments. 

sort
If you omit this parameter or mark it as FALSE, the results are sorted alphabetically by the included dimensions. If you set this as TRUE, then they will be sorted by the first metric, in descending order. You can also type a name of a metric or dimension to sort by that field, eg. "visits" will sort by visits in ascending order, and "visits_desc" or "-visits" will sort by visits in descending order.

settings

Special settings for the query, separate with comma. Some possible values:

  • SAMPLING_NOTE displays whether Google has sampled the results (only relevant for Google Analytics queries)
  • AVOID_SAMPLING tries to avoid Google's sampling, will slow down data fetching significantly (only relevant for Google Analytics queries)
  • METRICS_IN_ROWS puts multiple metrics into separate rows instead of columns
  • NO_DIMENSIONS splits by the specified dimensions but doesn't display them
  • COMBINE_DIMENSIONS concatenates dimension values when multiple dimensions have been specified
  • PIVOT_SORT_ALPHABETIC sorts pivot dimension categories alphabetically (instead of descending order by first metric)
  • SUM_ALL when specifying multiple profile/account IDs, this setting will sum all results together
  • ALLOW_SUM_UNIQUE disables the safeguards we have against summing values of unique count metrics (eg. users or unique pageviews). Normally, you wouldn't want these metrics to be summed, as summing can lead to double-counting, so we display a "Can't be calculated" error. But in some cases you may want to override that behaviour, which you can do with this setting.
  • INCLUDE_HEADERS will include headers in the data
  • NO_HEADERS won't include headers in the data
  • FILTER_SPAM_REFERRALS will filter out traffic known spam referral sites (Google Analytics only), see further info here
  • COMPARETO_ using this setting will include comparison to another date range, see below:
    • COMPARETO_YEAR - include comparison to the same date range one year before the selected date range
    • COMPARETO_PREV - include comparison to the previous period of the same length as the selected date range
    • COMPARETO_CUSTOM_START_END - include comparison to a custom date range defined by the values in START and END, for example "COMPARETO_CUSTOM_2014-01-01_2014-01-31" or "COMPARETO_CUSTOM_first day of last month_last day of last month". START and END can be any values accepted by PHP's strtotime function.
  • COMPARETYPE_ used together with the COMPARETO parameter to specify the kind of comparison:
    • COMPARETYPE_PERC - show change from the comparison period in %
    • COMPARETYPE_ABS - show change from the comparison period
    • COMPARETYPE_VALUE - show the value from the comparison period
  • DATES_NOTE displays the query date range (and comparison date range if used) above the results table
  • SHOW_ALL_TIME_VALUES if you split the data by a time dimension (date/week/month/year), using this setting ensures all time values within the date range are displayed, even if there's no data for any of the metrics
  • CONVERSION_WINDOW_ can be used to change the Facebook Ads conversion window from the default, which is 1 day after view + 28 days after click. Use for example "CONVERSION_WINDOW_28D_VIEW" to get conversions within 28 days of an ad view. Valid values for the number of days are 1, 7 and 28, and instead of "VIEW", you can use "CLICK".

 

maxResults
Specifies the maximum number of rows that will be fetched. Default is 10000. Setting a lower value will make the functions work faster.
maxCategories
Number of pivot dimension categories to fetch
This function was previously called getData. If you prefer, you can still use getData instead of the Supermetrics function. The parameter order with the getData is slightly different:

getData(token, profileID, metrics, startDate, endDate, filters, dimensions, segment, sort, includeHeaders, includeDimensionColumns, maxResults)

getTweets(token, searchTerm, columns, maxResults, includeHeaders, resultType, languageCode, untilDate, locale, timeZone)

token

The token obtained from supermetrics.com/functions-login

searchTerm
Function will search for tweets matching this value
columns

Specify which columns you want to incldue in the results. Combine columns with commas. Available columns are

  • TIME Time of tweet
  • TWEET Tweet content
  • LINK Link to tweet
  • TWITTER_NAME User Twitter name
  • NAME User real name
  • LOCATION User location
  • FOLLOWERS User follower count
  • LANGUAGE Tweet language
  • RETWEETS Times the tweet has been retweeted
maxResults
Max number of rows to fetch. Over 100 will slow down data fetching and will use up your quota faster (the quota is 180 queries of 100 rows per 15 minutes).
includeHeaders
Use TRUE or FALSE to specify whether headers should be included in the first data row.
resultType
Specifies what type of search results you would prefer to receive. Possible values: mixed, recent, popular
languageCode

Filter tweets to the given language. See list of language codes here:  http://en.wikipedia.org/wiki/List_of_ISO_639-1_codes (use the two-letter ISO 639-1 codes).

untilDate
Filter tweets generated before the given date. Date should be formatted as YYYY-MM-DD
locale
Specify the language of the query you are sending (only ja is currently effective). This is intended for language-specific consumers and the default should work in the majority of cases.

timeZone

Specify time zone for displayed tweet time, for example "PST" or "Europe/Paris". See list of avaible values here.

Frequently Asked Questions

Why does Excel slow down when I use the functions?

If you add a lot of queries to one workbook, Excel will slow down trying to constantly update the values. A good solution for this is to turn formula calculation into "Manual" in Excel's settings; with this setting, the values will only be updated when you press F9 (or you can add a button for refreshing, see "How can I add a button that updates all the functions I've placed?" below).

One thing that can really slow down the workbook is using "volatile" functions in your formulas and function parameters, as these cause Excel to constantly recalculate the formulas. The volatile functions that should be avoided are:

  • NOW
  • TODAY
  • RAND
  • OFFSET
  • INDIRECT
  • INFO (depending on its arguments)
  • CELL (depending on its arguments)

If you need to use the current date in your parameters, it's better to use the custom getToday() function that we include in the workbook (in v. 2.3 and newer), rather than Excel's default TODAY().

Note that Supermetrics Data Grabber runs multiple queries concurrently and can thus fetch data much faster.

How do I make the results span several cells? When I enter a function, I get a value to just one cell.

With the Supermetrics function (in older versions called "getData"), you can fetch large data sets with a single formula using the 'dimensions' parameter (for example, splitting visits by traffic source). Note that if you add dimensions to the query, the function has to be entered as an array formula, which covers a range of cells (as the results split by a dimension won't fit into a single cell). 

To input an array formula in Excel: 
  • Select a range of cells
  • Type the function to the formula bar
  • Press CTRL, SHIFT and ENTER simultaneously

Note that Google Sheets does this automatically, these instructions are only needed when using the functions in Excel.

How can I add a button that updates all the functions I've placed?

If you set calculation to manual in Excel's settings Formulas tab, you can update the functions by pressing F9, or by creating a button that does the update. To create a button, follow these steps:

  1. Open the VBA macro editor (to find the VBA editor in Excel 2007, check Options: Popular: Show Developer tab in the Ribbon. In Excel 2003, you can open the ditor from Tools: Macro: Visual Basic Editor)
  2. In the editor, select Insert: Module 
  3. In the module that opens, paste this code:
        Sub refreshAll()
            Application.Calculate
       End Sub
  4. Close the Visual Basic editor and return to Excel
  5. In the Developer tab, choose Insert: Button (Form control)
  6. When you have placed the button on the sheet, right-click and choose Assign macro, and as the macro name, type "refreshAll"

 

How do I get the function to return more rows/columns?

You can expand the range covered by an existing array formula by following these steps:
  • Select the upmost left cell of the current array
  • Starting from that cell, select a range going downwards and to the right. The range you select must match or exceed the array’s current range both horizontally and vertically
  • With the larger range now selected, click on the formula bar where the array formula should be visible
  • Press CTRL, SHIFT and ENTER simultaneously

After these steps, the array formula should now be filling the larger range you selected.

Why do I get the #NAME error?

If the functions return a #NAME error, it's probably due to Excel's security settings preventing the execution of macros. Lower the security level from Excel's options:

  • In Windows Excel 2007 and later, go to Excel Options: Trust Center: Trust Center Settings: Macro Settings. Select the last or the second last option. Restart Excel. Click "Enable macros" if prompted.
  • In Windows Excel 2003, go to Tools: Macros: Security. Select "High", "Medium" or "Low". Restart Excel. Click "Enable macros" if prompted.

Another possible cause for this error is misspelling the name of the function.

Why do I get the "Fetching data failed" error?

Most common causes for these errors are

  • You're trying to fetch a metric or dimension that doesn't exits (you might have misspelled the name)
  • You're not connected to the Internet
  • Excel can't connect to the Internet due to a firewall blocking it
  • Excel can't connect to the Internet due to proxy settings preventing it (see instructions below on how to solve this)

Why do I get the #VALUE error?

If the functions return a #VALUE error, it's most likely due to either of these:

  • Forgetting to give a value for some required parameter
  • The value of a parameter is in the wrong format (for example, a text parameter not having quotation marks around it)

How do I use the functions when connecting via a proxy server?

You can either disable the proxy server or modify the function VBA code to work with it. The easiest way is to disable the proxy from Windows Control Panel: Internet Options: Connections: LAN Settings: uncheck "Use a proxy server". 

If you can't disable the proxy, you need to change the VBA code of the functions. 

  • Before the line objhttp.Open "GET", URL, False you need to add objhttp.setProxy(proxySetting, varProxyServer, varBypassList)
  • After the line objhttp.Open "GET", URL, False you need to add objhttp.setProxyCredentials(username, password)

For instructions on how to set the parameters to those lines, see http://msdn.microsoft.com/en-us/library/ms760236(VS.85).aspx and http://msdn.microsoft.com/en-us/library/ms763680(VS.85).aspx

How can I use the functions in an existing workbook I have?

If instead of using the provided template file you want build on an existing workbook of yours, you need to follow these steps to embed the functions into the workbook:

EXCEL

  1. Open the VBA macro editor (to find the VBA editor in Excel 2007, check Options: Popular: Show Developer tab in the Ribbon. In Excel 2003, you can open the ditor from Tools: Macro: Visual Basic Editor)
  2. In the editor, select Insert: Module 
  3. Copy all the VBA code from the template file into your new module
  4. The functions can now be used in the workbook. If they don't work, check that your Excel security settings allow the execution of macros. If they don't, change the setting and restrart Excel.

GOOGLE SHEETS

  1. In Google Sheets, in your existing spreadsheet, open the menu bar and go to Tools: Script Editor: Blank project
  2. Copy all the script code in the template into the Script Editor of the uploaded spreadsheet
  3. In the Script Editor, press Save and return to the spreadsheet

How do I upgrade to a newer version of the functions?

Copy the code from the latest version of the template files to your existing workbooks, overwriting the previous code in those workbooks.. For instructions on copying the code between workbooks, see the previous question, "How can I use the functions in an existing workbook I have?".

Why do I get errors about rate limits or timeouts when using the functions in Google Sheets?

This is due to too many functions being updated simultaneosly. You can avoid this by staggering the queries so that they are not all fired at the same time; to do this, go to Tools: Scripts: Script editor, and in the Supermetrics function (in older versions called "getData"), above the line which has the UrlFetchApp, insert these two rows:
var randnumber = Math.random()*5000;
Utilities.sleep(randnumber);

With this in place, the queries will wait for a random time of between zero and five seconds before being updated, thus it's less likely that you would hit Google's limitations. If you still have trouble, you can duplicate the second row, this will cause a wait of between zero and ten seconds (and of course, you can put this line there as many times as needed, if you're still hitting the limit).

Help forum

Fastest way to find an answer is to use the Search Box.
If you cannot find any answers to your issue, you can either send us a private ticket or create a public forum post.

New help forum

Legacy forum