I'm trying to track the connections between our promotions and orders a bit more loosely than just by the source associated with the order: my idea was to look for every event in T_PROMOTION where the customer had engaged the promotion in some way (say, opened an email, or clicked on a link) and then look for any orders by that customer within a certain period after the activity.
I assumed that I would look for orders after the activity, but it turns out that the overwhelming majority of promotions associated with orders seem to have their activity logged about 4 minutes after the order date. When it comes to our online ticket sales/email promotions most, though not all, do appear to occur in a reasonable sequence, but I was curious if anyone could tell my why so many promotions generally have their activity logged after an associated order?
I'm also a little disappointed that T_PROMOTION only logs one response and response date per source per customer: it means that any prior activity is lost to the latest action, so a user who opens an email, then opens it again later will lose the first email record, and all record of opening will be lost if they click on a link, and then _that_ will be lost if they later forward it to a friend.
There is a table called T_EPROMOTION_RESPONSE_HISTORY which will give you the history of changes to a promotion as they were sent from one of the integrated email systems. So you can see each clickthrough and response change that was sent. I can't speak as to the timing of how the email systems (WordFly or Mail2) send their messages to our API for updating this table. Each unique URL that was clicked through is stored in TR_EPROMOTION_URL.
Hope that helps!
Thanks Chuck, that certainly opens up the email side of the issue a bit (we use Wordfly).