Supermetrics for Google Sheets & Docs is super-simple to use, yet extremely powerful. It won't take more than a minute to get started building your dashboards. Just follow the four simple steps below.
1. Install the add-on using either of these links:
Follow either link and click the blue button to install.
2. When the Supermetrics sidebar opens in Google Sheet or Docs, choose any data source to log in.
Note that you need to log in with a user account that has access to the data source in question. For example, if you choose Facebook, you must log in with a Facebook account that has admin rights to at least one Facebook page or app. Or if you choose AdWords, you must log in with a Google Account that has a AdWords enabled.
3. Log in on the page that opens up, and click to approve permissions when prompted.
Note that if you're logging in to a Google-owned service (Google Analytics, AdWords or YouTube), you can change the Google Account you log in with by clicking on the upper-right corner of the login page.
Frequently Asked Questions
Can I schedule my reports to be refreshed or emailed at specific intervals?
Which data sources can I access with Supermetrics for Google Docs/Sheets, and will you be adding more?
We are constantly adding more data sources. Some of the data sources we support are:
- Google Analytics
- Bing Ads
- Facebook Ads
- Facebook Insights
- Your own database (MySQL, SQL Server or Oracle)
If you wish to see a certain data source added, you can email us at firstname.lastname@example.org. We decide which data sources to add based on the feedback we get.
I'm logged into Google Docs with one Google Account, but for Supermetrics, I want to log in to Google Analytics with a different Google Account. How do I do this?
There are two options:
- You can simply click the Log in link, and in the Google authentication page that opens, click the upper right corner to add the other account. However, in some cases this may lead to you being logged out of the account you use with Google Drive.
- A better way is to follow these steps:
- Right-click the Log in link, copy the URL
- Left-click the Log in link
- For logging in to the other Google Account, open another web browser, or an incognito window in the browser you're using
- Paste the link into the new browser window you opened, and log in with the Google Account you want to use with the data source (eg. Google Analytics)
When you use this setting, traffic from known spam referral sites will be filtered out of your query results. These are sites that send fake traffic to your site in order to get you to visit their site. Normally, filtering out this fake traffic is a good idea as it improves your data quality, though you may get errors with some queries; if you do, try to run the query without this setting.
We base our spam referral filter on a list gathered by web analytics experts and published by Stu Bowker here. We update the list whenever we hear of sites that should be added.
I've added triggers to refresh my queries every hour, but they don't work.
Google Search Console only lets you fetch data on the last 90 days. With this setting, you can get longer time spans of GSC data. How it works is, when you first run a GSC query with this feature turned on, our tool stores the results on our server. When you later request the same data (same accounts, metrics, dimensions and filters), our tool combines the latest results with any old results stored on the server, and returns the merged results to your spreadsheet. To use the setting, simply check the "Combine new results with old" setting on the Options tab in our sidebar. This setting only works when you're splitting by month, week or date.
Note that with this setting on, your query results are stored for the long term on the Supermetrics servers, though they are available only to you, and we don't make any use of the data other than for your queries. After you stop accessing the data, it will automatically be deleted from our servers within six months.
Can I set a custom conversion window for Facebook Ads metrics?
There are a few things you can do:
- Delete the trigger and add it again. This usually fixes the issue.
- If the trigger is set to run very late in the evening (eg. 11 PM), it may fail to complete during the day. So you can try shifting it to start earlier in the evening, or later after midnight.
- If your trigger is working but completing much later than the time you have specified, note that the time you set is the processing start time. Depending on the number and type of queries you have in the file, refreshing it may take several hours (slowing this down is the Google restriction that triggers can only run a few minutes per each hour). Moving the processing start time earlier may be a good idea. Also, check the time zone in the trigger window.
- If you have an email trigger and it's refreshing the queries but the email is not being sent, it may be because the email attachement may be larger than Google allows. Open the trigger window (Add-ons: Supermetrics: Schedule refresh & emailing), and you can see the latest status of your triggger. Email problems should usually be displayed there. To fix this, you should reduce the content in the file, or email individual sheets instead of the whole file. You can also try different file types, some may work better than others.
- Another reason emails sent by triggers are not arriving may be the sender address. We recommend you set the sender address as email@example.com to ensure reliable delivery.
- Check that you have the add-on enabled in the file. If it is, you should see Supermetrics listed in the Add-on menu. If it's not there, choose Get Add-ons and install the Supermetrics add-on.
- If you haven't opened the add-on in a while, it may require reauthorization. You can fix this simply by opening the add-on sidebar (Add-ons: Supermetrics: Launch sidebar).
- You should also check the status of the queries on the SupermetricsQueries sheet (Add-ons: Supermetrics: Manage queries). You may see a specific error that's causing the refresh to fail, or that the refresh get stuck on some particular query.
If the above doesn't help, you can contact us and we'll check the situation. In your email, please include the following information:
- URL of the file
- Description of what's wrong
- The following screenshot:
- Open the file where you have the trigger
- Go to the Tools menu: Script editor
- In the window that opens, go to Resources: All your triggers
- Send a screenshot of the window that opens.
- Screenshot of the SupermetricsQueries sheet
Also, note that triggers won't refresh queries more often than once per day (see previous answer).
For getting more results from Moz and improving performance, you should get your own API credentials from Moz and add them to your request. Once you have the credentials, you use them by adding "APIKEY_X&Y" (where X=Access ID and Y=Secret key) to Options: Advanced settings on the add-on sidebar, or to the Settings column in SupermetricsQueries. With this setting, Moz queries will be done using your own API credentials.
Why do I see discrepancies between Supermetrics results and the Google Analytics web interface?
While most of the time, Supermetrics matches exactly the figures that the Google Analytics web interface displays, sometimes there are discrepancies. There are two common reasons for these discrepancies:
- Google samples the results it delivers to Supermetrics. In some cases, Google uses sampling to calculate the results of queries, which can cause differences between Supermetrics and Analytics. The magnitude of the discrepancies is usually in the range of 1-4 %. To see whether Google has sampled your query results, open the SupermetricsQueries sheet (for example, from the Add-ons menu Supermetrics->Manage queries) and see whether the "Results contain sampled data" row has the value TRUE or FALSE. Sampling occurs most often in queries that include an advanced segment, or where the data is split by multiple dimensions.
- The Google Analytics API and the Google Analytics web interface are not always totally in sync. Supermetrics relies on the Google Analytics API, which may in some cases deliver figures which differ from the Analytics web interface. Small differences can be seen every now and then, but sometimes bugs in Google's end cause major discrepancies. If you suspect something like this may be affecting your reports, there are a few places where you can investigate the issue:
- Analytics API discussion group. When people notice bugs, they are usually posted here:
- Analytics status dashboard. Here you can see the status of the Analytics API and other Analytics services, bugs may be shown here.
- Analytics API notify list. Serious issues may be posted here by Google.
- Analytics query explorer. This is Google's own API tool, which you can use to confirm the results showed by Supermetrics Data Grabber. If this tool agrees with Supermetrics and differs from the Analytics web interface, then it indicates a bug in Google's end. On the other hand, if Google's API tool and the Google Analytics web interface match, and Supermetrics shows something different, that indicates a problem in our end; in this case, please contact us immediately.
- Analytics API discussion group. When people notice bugs, they are usually posted here:
When you see a discrepancy, before notifying us of the issue, please follow these steps:
- Check whether the query results are based on sampled data (select query and click Modify). If this is the case, and the discrepancy is just a few percent, then it's very likely caused by the data sampling.
- Try to fetch the same data with Google's own API tool. If the results match Supermetrics, then the issue is in Google's end and we can't fix it.
Do I need to pay to use Supermetrics for Google Drive?
When you first install the add-on, a free 30 day trial period is started. During this time you can use all features of the add-on. After the trial ends, you can continue to use the tool for free, but will lose access to some features, as specified here.
When you create a new query, our add-on creates a named range for it. The named range is called "zsupermetrics_" + the query ID. When you refresh a query, the add-checks whether the named range for the query ID exists, and if so, places the new data there. If it that named range doesn't exist (or there's no query ID for the query), it will look at the "Range address" column in the SupermetricsQueries sheet. If there's no range specified in that column, it will look at the "Range address (static)" column in SupermetricsQueries.
To move a query, the easiest way is to cut and paste the result range. This will update the named range definition. Alternatively, you can change the named range definition Data: Named ranges. The named range definition is also automatically updated if you add rows above the range or columns to the left of the range.
How can I delete queries? I've cleared the data but it keeps reappearing when I press Refresh.
There are two ways:
- Select a query, wait for the query information window to open in the sidebar, and press the "Delete query" or "Delete query & data" button.
- Open the SupermetricsQueries sheet (go to the Add-ons menu, click Supermetrics and Manage queries), delete the rows with queries you want removed. After doing this, the queries will no longer return data when you run a refresh.
Can others refresh the queries I've created?
By default, if you give another person access to a document where you've added queries, they can use the Supermetrics add-on to refresh those queries, even if they themselves don't have access to the data source (eg. Google Analytics account). We implemented it like this to make sharing and refreshing reports as simple as possible. However, other users can't change the account(s) that the query targets (for example, the Google Analytics views). If they modify them, the query will be run with their own credentials, not yours. This means that you can create a dashboard for one client and share it with them, and they can't modify it to fetch data from your other clients.
If you want to prevent anyone from using your access rights to refresh the queries you've placed, just log out that account. This will remove your authentication information from your existing queries, and you can no longer refresh them. Other people can still refresh them if they themselves have access rights to the data source (eg. the Google Analytics account), but they can no longer refresh them with your access rights.
You can be logged in with multiple accounts (eg. AdWords MCCs) simultaneously. In the add-on sidebar, go to Data source, and select the data source you need log in with (in your case, AdWords). If you are already logged in with one AdWords MCC, you can log in with another one using Switch: Add new. Don't press Log out, as that will erase the authentication information of your other account.
When you run new queries, they are always ran using the credentials of the user you have active in the sidebar. Afterwards, when the query is refreshed, the add-on will try to continue using those credentials. But if that account has been logged out of in the sidebar's Data source tab, then the add-on must use the credentials of the currently active account, if you are still logged into some AdWords MCC.
To make it easier to manage multiple accounts, we have recently added a "Refresh with user account" parameter to the SupermetricsQueries sheet (scroll to the right end). If that parameter has a value specified, our add-on will primarily try to refresh the query with that account's credentials, provided that either of these conditions apply:
- The query has been refresh before with the specified account, and the user who did the refresh has not logged out of the account, or
- The active user (Google Drive account) is logged into that account in the sidebar's Data source tab
Usually there's no need to take any special action. But to ensure the queries are using the new user's authentication credentials, you can follow these steps:
- Ensure new user has access to the Google Sheet
- Ensure new user has access to the data source accounts the query targets (for example, the AdWords accounts or Google Analytics views)
- Logged in to Google Drive as the new user, go to the file in question
- On the SupermetricsQueries sheet, delete the query IDs
- On the SupermetricsQueries sheet, on the very right there's a column marked "Refresh with user account". There you can mark the data source user account that should be used when refreshing the queries (eg. the Google Analytics login email address), if that is different than before.
- Using the Supermetrics add-on, refresh all queries
This method will run the queries from scratch as new queries, using the authentication information of the current logged in user.
Why does the tool only return part of the date range I selected?
This may be due to your row limit. If so, increase the "# of rows to fetch" setting on the "Split by" tab.
Another issue is that the data source may not return dates for which there are no metric values to display. For example, if you fetch conversions by date, AdWords may return only dates that had conversions. If you want to always get all date values, type "SHOW_ALL_TIME_VALUES" to the Advanced settings field in the Options tab of the sidebar. For existing queries, you can add it to the Special settings column in the SupermetricsQueries sheet.
I want to add a query that doesn't return any data right now, but will start returning data later. When I run this, I get a "No data found" error, and no query is added. How can I add this kind of query?
Type "RETURN_NO_DATA_RESULT" to Options: Advanced settings, and then run the query. This will force our add-on to add the query to the sheet even if no data is found.
Do you access or use our data in any way?
Adding your templates to the Supermetrics Template Gallery is a great way to promote your dashboard design, consulting or training services to the thousands of online marketing professionals that use Supermetrics.
To create a template:
- Use our add-on to get the data and add charts, formatting and calculations. Note that everything needs to be on a single worksheet. If you want to have separate areas for data processing and display, you can do the processing in hidden columns, but it must be on the same template sheet.
- After you're happy with the design, delete the results of your queries, so that your own data won't be included with the template.
- On the SupermetricsQueries sheet, delete the contents of these columns: Query ID, Accounts/views (or "Accounts/profiles"), Created, Updated, Last status, Last refresh ID, Results contain sampled data, Refresh with user account.
- In the sharing settings of the spreadsheet, use "Get shareable link" to get a link to the file. Note that anyone will be able to view the file and make a copy of it using the link. Select the "Anyone with the link can view" option, so others can't edit your file.
- Email us and share the file with firstname.lastname@example.org. We will verify that the template works correctly and fills our requirements, and add it to the Template Gallery. In your email to us, you can include a thumbnail screenshot for use in the Template Gallery.
When creating a template, check that there isn't anything very similar already in the gallery. The template should look good and provide some relevant data for online marketers.
This option is currently in beta testing and visible only to some user of our add-on. It's a different way of fetching data into a spreadsheet using a custom function called "SUPERMETRICS".
Compared to normal data queries, the benefits of using a function are:
- It's faster
- Query parameters can easily be modified (they are right above the data and not on the SupermetricsQueries sheet)
- Whenever the query parameters are modified, the data will automatically be updated to reflect the changes
But there are also drawbacks:
- Google allows custom functions to run for just 30 seconds, so this method of fetching data is suitable only for quick queries. If the time limit is exceeded, the function will display "#ERROR", with the details "Internal error executing the custom function."
- The query information won't be visible along your normal queries on the SupermetricsQueries sheet
- The authentication information for accessing the data is stored as an authentication token right inside the query, it's in the first query parameter starting "authentication_...". Anyone that has access to this token can fetch data from the specified data source using the credentials of the user account you used for creating the query. So don't share the token with anyone that shouldn't be able to get that data. If you need to revoke any tokens, you can do so here.
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.