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.

Help forum

Search forum

(If you cannot find an answer, you can send us a private ticket or create a public forum post)

Browse legacy forum

Legacy forum