Best practices: Query optimization

Use this guide to optimize your queries, by making them faster and more reliable. Well-optimized queries are also easier for you to handle if you need to make changes.

Quick links to the sections below:

How to optimize query

Only select the fields that you need. Even though our connectors have many fields to choose from, consider if you really need the extra data. You can always add a new field or create a new query for a different type of data set. 

Don’t make the query too complicated. Queries are much simpler to run without filters. Try using Google Sheets filters on your data, so you only query the data you need. 

Google Sheets filters also gives you more versatility if you need to change the filtering parameters. They allow you to manipulate the data faster and you can even save filtered views for the future. You can find more information here: Sort & filter your data 

Avoid making the query too heavy. The amount of data determines how long the query will run.

Metrics will add to the amount of data as well as the date range. Dimensions also add aggregation calculations. A large number of metrics will have to be broken down by each dimension. This means more dimensions, which means more calculations, which leads to longer queries. 

For example, if you’re querying the last two weeks of data using the date and campaign name, the query will have to calculate the selected metrics for each date per campaign. So even if you have only 4 campaigns, that will multiply the 14 days you are querying for 56 rows of data. This means your query will run 56 calculations — one for each row — for every metric you include. 

Filters also add their own calculations on top of all this. Sometimes heavier queries are needed and can’t be avoided. 

For faster and more efficient queries, always consider how much data you actually need. Using the setting “Combine new results with old” can help reduce the amount of data you’re requesting. More about the “Combine new results with old” setting here: Refresh query to get only the newest data

Remember to use advanced settings. You can find all the advanced settings from this article: Advanced Settings for Google Sheets 

Here are a couple of useful settings for query optimization:

“KEEP_RESULTS_ON_REFRESH_ERROR”: The data won’t be replaced with the error message. 

Even the most simple queries can have errors and the error message will replace the data. 

When using this setting, the error message can be found on the “SupermetricsQueries” tab. 

 “RETURN_NO_DATA_RESULT”: A good setting to remember if you want to pre-create a query.  

Refresh query to get only the newest data. A longer time range makes queries heavy and slower to refresh. You can use the “Combine new results with old” setting or “Append results” setting for queries to have a shorter time range and still save the previously queried data. With these settings, the old data remains, but the new data is added.

If you can’t use either of these settings, you can also delete the query and start over. In this case, you only delete the query and not the data. 

To do this, you can either delete the query from the “SupermetricsQueries” tab by deleting the query row, or from the query info window in the sidebar by selecting “Delete query” (not “Delete query & data”). 

After deleting the query, create an identical query, set the start date where the previous query left off, and set the query to start from the next empty row.

The easy way to duplicate the query is to select “Duplicate” in the sidebar query info window.

Historical data

Data is not available via API forever. APIs can limit the availability of historical data. This varies between APIs, but you can check the known historical raga ranges from this article: Historical range limits 

To store historical data, you can create a “raw data” sheet where you query all the data you need and get data from there to all other sheets. You can see more about raw data here: Raw data

Getting all the historical data at once might make the query too heavy. You can get the data in smaller pieces, like six months at a time. After querying the first six months, delete the query (not the data), start a new query from the next row, set the date range for the next six months, and so on.

After getting all the historical data, you can set an automatic refresh and use the “Combine new results with old” setting to automate your newly optimized queries.

Raw data

Sometimes a query can’t produce data in the format you need. You might want to combine fields or arrange the data in a way that isn’t possible in a query. A “raw data” sheet helps with this by getting the data in one place. 

You can use Google Sheets' own functions to get the data you need from the “raw data” sheet.

First, set up the query with all the fields you need, using the “Combine new results with old” setting. Then, set a scheduled refresh to automatically refresh your query when needed. Finally, in a new tab, create the report you need and reference the data in the “raw data” sheet.

This is also a good way to combine data from multiple data sources. Gather the raw data from each data source into its own tab and combine the data on a separate tab.  Learn more about this in the “Data Blending” section.

Data blending

Google Sheets’s own functions help you combine data from several Supermetrics queries. Use one or many raw data sheets to gather all the data you need before you start blending. Also, decide which fields you want to combine or just have in your actual report.

You can use data reference to another sheet by typing the sheet name and the data location: =Sheet1!A1 or ='Sheet number two'!B4.

You can also get data from another Google Sheets file if you prefer keeping things separate. 

Learn more here: IMPORTRANGE

If you need conditional formatting, you can use the IF function: IF function 

Other useful functions you might want to take a look at are VLOOKUP, HLOOKUP, INDEX, and MATCH.

You can find all Google Sheets functions here: Google Sheets function list

Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.