How to keep your ad history forever: warehouse and incremental deduplication

Centralize everything in a data warehouse

The solution starts by changing where the data lives: instead of using Google Sheets or Looker Studio to store it, use a data warehouse like BigQuery.

The first step is loading the historical data for all your ads, once. That download can take quite a while, but it runs a single time, covering the entire time range the platforms allow.

There’s an important detail here: Meta keeps the last 3 years on a rolling basis. A day goes by, and it deletes a day from 3 years ago. If we load those 3 years into our warehouse, they stay forever, and we stop losing data with every day that passes.

The images, in your own cloud

The platforms don’t store ad images forever either. When you request an image through the API, they return a URL that expires after 7 days. Build a report with that data today, and next week the images stop working.

The fix is storing every creative in your own cloud, so it stays on your side and doesn’t depend on the platform. That way you keep the metric history (how much each ad spent and how many conversions it brought) together with all the images.

Incremental deduplication: keeping everything fresh, efficiently

Once the history is loaded, the data updates in the most efficient way possible: incremental deduplication.

The idea: always fetch the last 10 days, which is the attribution window on most platforms. During those days, each ad’s metrics keep shifting because attribution keeps adjusting, following each platform’s internal rules. Past the window, the data for any given ad never changes again.

So: for 10 days the recent data gets refreshed, and then it becomes part of the permanent history. This way you can sync any number of accounts and any volume of ads, because a warehouse handles millions of rows without slowing down your dashboards. And with the images stored, you can build visual reports, which is the ideal when analyzing ad-level data: the image or the video is what matters most.