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?