1

I'd like to query the open, click and sent data views to ultimately find who

  • has never opened or clicked a mail (since May 2019)
  • and how many mails they've been sent (since May 2019)

I understand that open, click and sent data views by default only hold data 6 months back.
What would the workaround be to ie. get historic click data available in a data extension for the last 2 years?

Hope the question makes sense.
Thank you.

Improve this question
2
  • 3
    I would strongly not recommend doing this inside of SFMC. Depending on the send volume, we're talking about multiple hundreds-of-millions-of-rows for the Sent data alone. Most will automate a weekly tracking extract backfill process and offload it to a data warehouse with enough horsepower to handle that volume.
    – Adam Spriggs
    May 3, 2021 at 14:26
  • ^ This is an important callout that I missed in my answer. The volume of data can be absolutely overwhelming and will be virtually impossible to query against in the platform if it's sufficiently large.
    – Jason Hanshaw
    May 3, 2021 at 14:56

1 Answer 1

Reset to default
7

You'll need to use Tracking Extracts in order to obtain the data beyond the window provided in data views. For reference, your automation to pull this data should look something like this:

Step 1: Tracking Extract activity to pull engagement activity for a given period.

Step 2: File Transfer Activity to transfer your file to the FTP.

Step 3: File Transfer Activity to unzip your newly transferred file.

Step 4: Import activity to add your unzipped data to your logging data extension.

To note, you'll be limited in the range of data that you can pull in a single automation run. Tracking Extracts can only be configured to pull in 30-day increments, so you'll have to execute this many times to pull the full range of the data. There are ways to automate this process, but that's a bit beyond the scope of this question.

Improve this answer
2
  • Thank you, Jason, for the to-the-point and useful answer. To be able to count number of opens, clicks and sends for a subscriber I only need to create .csv outputs for Opens, Clicks and Sent in my Tracking Extract Configuration, right?
    – Felix Sanchez
    May 4, 2021 at 6:47
  • 1
    That's correct. You'll need to do this a number of times, however, as you can only get batches of 90 days worth of data in each extract when running the extract through the UI. It's possible to get longer timeframes when scheduling through the API, but two years is a lot of data in most accounts and you'd end up with timeouts.
    – Macca
    May 4, 2021 at 7:49

You must log in to answer this question.

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