What's easiest way to create data extension of subs that haven't opened email in 365 or 730 days (1-2 years)?

I would like to later purge this list. Data Views top out at 6 months. Is there a canned way to get to this type of data? Is SQL queries the best way to do it? Some other way?

1 Answer 1


To get beyond the 6 month data view restriction, I create my own tracking data extensions (with retention) and then extract and import tracking data back into them.

Also, if you kick off the tracking extracts via SOAP API, you can use whatever date range you want.

For projects where more than a year of tracking data is needed I'll set up this process in an Automation.

Alternatively, you create your own tracking Data Extensions, back-fill them with tracking data and then use a set of SQL Queries in an automation to keep them up to date.

  • Thanks Adam! I had been hoping the solution would be more straightforward. Just to confirm: if my goal is not to have this data generally (for segmentation, reporting, etc), and I just need it for this one use case, is there a more direct manner to understand folks that have not engaged for a year or longer, or does your answer still apply?
    – sfdcstack
    Oct 3, 2018 at 15:50
  • Yes. It'd be the same process...and it takes a while to do.. It's generally a lot of data. Oct 3, 2018 at 19:06
  • Adam - last question: is there an area you can point me to that would provide any working examples of something similar for aggregated tracking exports? I'm finding a lot of material on tracking extracts by specific jobID, but I'm looking for overall data. If not by job, is the best way to query the Open, Click etc Data Views in an ongoing basis and pump those into my own tracking data extracts? Thanks
    – sfdcstack
    Oct 4, 2018 at 23:46

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .