As noted in the article What does the "Combine new results with old" setting do?, this feature can be used to overcome shortcomings of the data source API not being able to pull older periods of historical data or to help quicken the loading or larger data sets. Here's an example setup for using this feature:
- Set up your data source query as normal in the Supermetrics sidebar.
- Set the date range settings for as far back as you need data, or as far back as the data source allows for. For simplicity, this example is set to the last week, but you can also do this with the last year or several years.
- Run the query to populate the data into the sheet.
- Back in the sidebar, navigate to the Options section and check the box for "Combine new results with old" to enable the feature. Any refresh after this point will append the data to the bottom of the existing data.
- Go back to the date range setting and update it to a smaller range - one week, last 2-3 days, etc. - whatever is appropriate to update the data in the refresh window you are going to use.
You can also use custom date ranges to set the values to things like "Today" or "-X days", if you have very specific windows you need to check and need it to update dynamically.
- Run the query again. In this example, it was set to last 7 days, so it appended the missing dates to the existing information. Now each time the query is run in the future it will append the missing sections of the last 7 days to to the end. Note that it should not add duplicate dates/rows, but only append what is new/missing.
- The final step, which is optional, is to set up a refresh schedule so that this is updated automatically! For directions on how to set this up, see Can I schedule my reports to be refreshed or emailed at specific intervals?
There's a couple of known issues with this setting to be aware of:
- This only works when the "date" dimension is included in the query. Otherwise it's unable to tell what is old versus new data.
- There's a bug that prevents this from working when splitting by column instead of row for the date dimension. You have to split by row for now to get it to work properly. Hopefully the behavior is fixed in the future to also support columns.
- If you enable the feature after the first run as been completed and there's already data, it may cause unexpected behavior (header row re-added, data offset, etc.) To avoid this, you want to start a new query with the setting already enabled before you run it for the first time. If you missed this, you can still fix it though by deleting all the existing data, enabling the setting and then pulling the old historical data, and then changing the date range to the permanent setting.
- There's a bug with using this setting with sorting on the data that puts the newest dates at the top of the table. It cannot read those values and so it may overwrite earlier content. We are investigating if we can fix this, but to workaround around it please use the default "automatic" sort with the dates going from oldest to newest.