0

I need advice on best way to pull daily tracking data from SFMC. I currently have a set of monthly queries that run on the 1st of every month and pull in tracking data to a data extension. This works fine, but I often get requests for this data mid month, and I don't want to go into SFMC and do a manual pull of it.

So I'd like to set up a daily query, BUT let's say an email ran at 5pm yesterday and my query runs at 12am today. That's hardly enough time to allow an email to gather opens and clicks...

I know I can use the API to actually pull real-time data on demand, but I've been instructed not to use the API.

How can I structure a set of queries to pull daily data, but also allow enough time for opens and clicks to come in?

I thought that maybe I could run the queries every 3 days, but again, if someone sent an email a few hours before the query runs, we have the same problem. The only thing I can think of is some kind of daily run, with some overlap of 5 days or so, that updates a core table or something, but I'm having a problem fully visualizing this.

Can anyone offer some advice and a method that could achieve this?

1

I'm thinking you could try for example

Where Datediff(d,o.CreatedDate, GetUTCDate())  between 0 and 3

It will always pull everyone for the last 3 days (e.g. _opens). If you ran this daily (or hourly) and kept updating your output rather than solely appending, then it will keep incrementing the results for the last 3 days.

Hope this helps

2
  • How did it work out for you? Was my answer worth an up vote ? – Gravitai Feb 19 '18 at 15:59
  • Man I haven't tried it! But I upvoted you anyway. It's on my list to try, just haven't gotten to it yet. will report back. – garek007 Feb 20 '18 at 17:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.