Avoid schedule failures of large data queries with Smart Refresh

Smart refresh allows you to fetch a large date range and periodically refresh part of it. This significantly reduces query failures and increases the speed of your queries. 

Generally working with large date ranges is tricky, as the query may fail because of uncertainties like increased server load on the Data Platform side. Smart refresh works by refreshing only a part of the entire data so that only a small request is made on the Data Platform.

This is also useful in cases where you would want to update only the part of data that changes. Let’s take an example. You want to query and incrementally save 1 year’s worth of data in Facebook but when refreshing, you want to update only the last 14 days data. Smart refresh comes handy in these situations. You can continue to save data for years and incrementally add new data and refresh only the previous 14 days. 

Here is how we can use Smart Refresh,

1. Before we begin, let’s make sure to enable Advanced scheduler access by going to My Account -> Preferences.
2. Create a new query or open an existing one that you want to work with.
3. Choose the metrics and dimensions that you need, but also make sure to add a β€œDate” dimension. Note that Smart Refresh requires selecting a Date dimension to function.
4. Specify the full date range for the data you wish to pull.
(Note: the feature only works if the date range is greater than 30 days.)
5. Enable Smart Refresh for the query, check the checkbox labeled Use smart refresh. You will notice a new field appear, prompting you to set the refresh period in days.
6. In the Refresh the last n days field, specify the desired refresh period. Refresh period determines which portion of the entire date range is updated during subsequent query runs.

7. Running the query now will fetch data for the entire specified date range and write it to the sheet initially.
8. Now if we save the query and rerun it, it will fetch data only for the refresh period we selected (i.e 14 days) and merge it with the existing dataset.

Lets go over a couple of examples where Smart Refresh comes handy

Example 1

Suppose you manage Facebook Ads campaigns and want to analyze performance within a specific attribution window of 14 days or 28 days. With the Smart Refresh feature, you can easily maintain up-to-date data within these windows.

For a 14-day attribution window, set the refresh period to 14 days. When you run the query, it will fetch and update data for the last two weeks, aligning with the attribution window. The same applies to a 28-day window, where the refresh period is set to 28 days.

Example 2

Imagine you’re managing an e-commerce website and need to track daily sales performance. However, running queries for a large dataset spanning several years can be time-consuming. Smart Refresh simplifies the process and ensures you focus on the latest data.

While setting up your query, specify the full date range for the data you want to pull, such as the past 2 years. Enable Smart Refresh and set the refresh period to the last 30 days. When you run the query, it will initially fetch and write the data for the entire 2-year range. Subsequently, each time you run the query, it will only fetch and merge the data for the last 30 days. This way, your analysis stays current without the need to retrieve and process the entire historical dataset

By using Smart Refresh in your query, you ensure that your historical data remains intact and up-to-date effortlessly. With each query execution, only the data within the defined refresh period will be fetched and refreshed in your spreadsheet. 

Hope this was useful, please let us know if you have any feedback for us.

Was this helpful?