Note
This feature is directed towards advanced users.
In Google Sheets and Excel, the SupermetricsQueries tab is automatically created when you add your first Supermetrics query to the spreadsheet. The tab is normally hidden, and changes to it should be made with caution.
The SupermetricsQueries tab collects the settings of every query in the spreadsheet in one place, such as the data sources, date ranges, metrics, and dimensions used.
You can also see how recently each query was refreshed, if the refresh failed, and an error message that explains why.
Advanced query editing using the SupermetricsQueries tab
The SupermetricsQueries sheet provides detailed information about your queries, and it should be edited with caution. Changes made in this sheet can affect your data, and may cause errors if incorrect data is entered. It’s recommended to use this sheet only if you are familiar with how Supermetrics queries work.
If you have a lot of experience using spreadsheets or Supermetrics, you can make bulk changes to queries across the whole report using the SupermetricsQueries tab — like moving them to new locations, dynamically updating them, or applying advanced settings — without having to adjust individual queries in different tabs.
If you're not completely confident in the adjustments you're making, we advise that you create a copy of your report before making any changes in the SupermetricsQueries tab. This ensures that you'll have a safe copy of all your queries that you can revert to if something goes wrong while you're using the SupermetricsQueries tab.
Note
Please note that if you modify the SupermetricsQueries tab and the queries stop working, Supermetrics support may not be able to help you.
Reverting to an earlier version in version control can also undo any unintended changes.
If you're making changes to one row, you can copy just that row into your clipboard instead. If the results aren't what you intended, paste it back in.
Caution
When modifying the SupermetricsQueries tab, never make changes:
- Above row 21
- In columns F through P
How to find the SupermetricsQueries tab
The SupermetricsQueries tab is hidden by default. Follow these instructions to open it.
- In Google Sheets, click Extensions → Supermetrics → Manage queries.
You can also click Manage queries on the Home tab in the Supermetrics sidebar. - In Excel, right-click on a tab, click Unhide..., and select SupermetricsQueries.
The tab will remain visible in a spreadsheet after you open it for the first time.
See the list below for columns you can and cannot edit. Note that the list is not complete.
How to refresh queries after editing
After editing queries in the SupermetricsQueries tab, you need to refresh all queries. If you refresh each individual query, the change made in the SupermetricsQueries tab will not be saved.
To refresh all queries:
- In Google Sheets, click Refresh all queries on the Home tab of the Supermetrics sidebar.
- In Excel, click the hamburger menu in the top-left corner of the Supermetrics sidebar, and click Refresh all.
Columns on the sheet
Uneditable columns
The following columns contain information about your queries and must not be changed:
| Column name | Information in the column |
|---|---|
| Created (Column G) | The date and time the query was created. |
| Updated (Column H) | The date and time the query was refreshed or updated. |
| Last status (Column I) | The last status of the query. Possible values include:
|
| Execution time (sec) (Column L) | The approximate amount of time in seconds the query took to refresh. A lower number indicates a faster query. |
| Object type (Column M) | Refers to the type of data visualization selected when creating the query. The default and most common value is table. |
| Linked chart ID (Column N) | If Object type is anything other than table, the chart ID is displayed here. |
| Data source (Column O) | The shortcode for the data source. For example, Instagram Insights is IGI and Google Ads is AW. |
Editable columns
| Column name | Information in the column |
|---|---|
| Date range type (Column Q) | Specifies the date range for the query. Can be any date range option from the sidebar dropdown written without spaces. For example: lastmonth, thismonth, yesterday, last3months, last3monthsinc, or custom.
Example: If the current month is April, last3months will return data from January, February, and March. The last3monthsincrange will return data from February, March, and April. If you set the Date range type to custom, you must enter the dates in the Start date and End date columns. |
| Start date (Column R) | The start date for a custom date range. This field is only used when Date range type is set to custom. Use the yyyy-mm-dd format or any format accepted by PHP's strtotime (such as, "first day of last month", "last Monday", or "-28 days"). |
| End date (Column S) | The end date for a custom date range. This field is only used when Date range type is set to custom. Use the yyyy-mm-dd format or any format accepted by PHP's strtotime (e.g., "first day of last month", "last Monday", or "-28 days"). |
Compare to (Column T) | Specifies the type of comparison for the query data. Possible values:
|
Comparison value type (Column U) | Specifies how the comparison values are displayed. Possible values: perc (% change), abs (absolute change), or value (show comparison value). |
Accounts/views (Column W) | The accounts, pages, or views you want to query data from. All accounts must be within square brackets [], each account must be within quotes "", and accounts must be separated by commas ,. The account format (name or ID and name) varies by connector. To check the account format, create a query in the Supermetrics sidebar. Example: ["12345'Test - EU","67890'Test2 - LATAM"] |
Metrics (Column X) | The metrics selected in your query. All metrics must be within square brackets [], each metric must be within quotes "", and metrics must be separated by commas ,. You can find the metric names in our documentation. Example: ["impressions", "clicks", "cost"] |
Dimensions (Column Y) | The dimensions selected in your query. These will be displayed in rows. All dimensions must be within square brackets [], each dimension must be within quotes "", and dimensions must be separated by commas ,. You can find the dimension names in our documentation. Example: ["date", "country", "campaign"] |
Pivot dimensions (Column Z) | The dimensions selected as pivot dimensions in your query. These will be displayed in columns. All dimensions must be within square brackets [], each dimension must be within quotes "", and dimensions must be separated by commas ,. You can find the dimension names in our documentation. Example: ["browser", "device"] |
| Filters (Column AA) | The filters applied to the query. All filters must be within square brackets [], each filter clause must be within curly brackets {}, and each part of the individual filter must be within quotes "". Parts of the individual filter are separated with colons :. The filter definitions are separated with commas ,. Example: Filter that reads "Source / Medium contains google": [{"field":"sourceMedium","operator":"=@", "value":"google","combineToPrev":";"}] Example: Filter that reads "Source / Medium contains google AND Source / Medium contains cpc OR Source / Medium does not contain organic" [{"field":"sourceMedium","operator":"=@", "value":"google","combineToPrev":";"},{"field":"sourceMedium","operator":"=@", "value":"cpc","combineToPrev":";"},{"field":"sourceMedium","operator":"!@", "value":"organic","combineToPrev":","}] |
Sort (Column AC) | Specifies the sorting order for the query results. Only 2 sorts are possible. The format is field1_asc;2nd:field2_desc. Example: date_asc;2nd:clicks_desc |
| Max rows (Column AD) | The maximum number of rows to be displayed. The minimum value is 5 and the maximum is 1000000. |
| Max pivot categories (Column AE) | The maximum number of columns to be displayed. The minimum value is 3 and the maximum is 500. |
Special settings (Column AF) | Includes all settings selected under the Options tab and in the Advanced settings field. All settings are in capitals, words are separated by underscores _, and settings are separated by commas ,. Example: BLANKS_TO_ZERO,DIMENSIONS_MAX_LENGTH_5 |
Troubleshooting
If you encounter issues after modifying the SupermetricsQueries tab, revert the changes or restore an earlier version of the sheet to ensure that the data is correct. If the issues persist, contact our support team.
Note
Please keep in mind that our support team may not be able to assist with issues that result from manual changes to the SupermetricsQueries tab.