If a data source API can't pull older periods of historical data, or if large data sets are loading slowly, the "Combine new results with old" setting in Supermetrics for Google Sheets can help. This setting works by building a historical dataset and then creating a rolling window that adds new, up-to-date data each time you refresh the query.
This feature requires the Date dimension. If the data source you're pulling from doesn't have the Date dimension, use the APPEND_RESULTS setting instead.
Note
For the Combine new results with old setting to work, your query must meet the following conditions:
- The date range is rolling. This means that the query pulls data from a date range not bound to any dates. Good examples are Last 7 days or from “-10 days” to “-1 day”.
- The data is not split by column.
Do not change the sorting. The automatic sorting should be ascending by date, as the setting needs this to work as intended.
We can’t guarantee that the Combine new results with old works with other dimensions than Date. Dimensions such as Week might work but this is not a supported use case.
Instructions
- Open the Supermetrics sidebar in Google Sheets.
- Click any cell to create a new query. Note that this feature doesn't support splitting by column instead of row for the date dimension, so split by row instead.
- Set the query's date range as far back as you need it to go to capture your historical data. If you're pulling a long range, optimize your query using the instructions below.
- Click Get data.
- Once the query has run, and your historical data has appeared in the sheet, click the query results of the first query.
- Wait for the query to load in the sidebar, and click the pencil icon to edit the query.
- Change the query's date range to as long as you want the rolling refresh window to be. For example, a range of "Last 7 days" will refresh the last 7 days of data each time the query runs.
- Note that both start and end dates need to be rolling dates. For example, a date range from 2023-01-01 to "today" won't work because the date range is still refreshing the same dates — but a custom date range from "-15 days" to "-1 day" works because these dates are not static.
- Click Options.
- Check the box for Combine new results with old.
- Click Update query.
Each time this query runs, it will now update only the time range specified in the modified query. We recommend setting up a scheduled refresh to simplify and automate this process.
If your query tracks conversions, make sure the minimum date range for the modified query is as long as the longest conversion window. For example, if you're using 28-day click, the range must be at least 28 days long to ensure those click counts are updated properly
It's not possible to change a query's metrics or dimensions when this setting is enabled. If you need to make changes, create a new query with the required features instead and follow the steps above.
Optimize your query
If your historical dataset is large — months or years — we recommend splitting queries to ensure that you’re not unnecessarily pulling large amounts of data with each refresh.
One way to do this is to build a query that begins at the beginning of your historical range and splits time periods into smaller chunks.
Example: Pulling data for 2019–2021
Let's say we want to pull a historical range of 2019–2021 with "Combine new results with old" enabled. We don't want to pull 3 years of data each time the query refreshes.
First, we create a query for our metrics and dimensions with a date range of 2019. We run that query.
Then, we modify this query's date range to 2020, enable "Combine new results with old", and run the query again.
Next, we modify the query's date range to 2021, and run it again.
Finally, we modify the query's date range to whatever we want our rolling window to be, ensuring it matches our conversion window if we're using one, and run it one last time. Then we set up a scheduled refresh to update the query automatically.
Now our query will retain historical data and add new data without having to pull 3 years of data each time it runs.
Avoid duplicate results
If your data or data source changes in a way that causes duplicate results with this setting — for example, if a campaign name changes in the data source, which also changes historical campaign names, duplicating the new campaign in your reporting — there are some steps you can take to resolve the issue.
- Open a Google Sheet and the Supermetrics sidebar.
- Create a query with the Campaign ID and Date metrics.
- Follow the steps in the Instructions section above to enable the "Combine new results with old" setting.
- In a second sheet, build a report with Campaign ID, Campaign name, and a metric like Impressions, with the "Combine new results with old setting" enabled.
- In the first sheet, use vlookup to match campaign IDs between your 2 sheets, and bring the Campaign name into the first sheet.
In this example, the second sheet acts as a lookup table. Vlookup always returns a singular value, and if the second sheet is sorted by latest first, it will bring the most recent campaign name even for historical values.
Note that some dynamic metrics (like Tweet ID, for example) can look like duplicates when they aren't, as their IDs might change from one day to another.