Hi,
I've just been sitting here the last couple of days cranking out statistics for the rest of the Marketing team on response rates and all that sort of stuff. Which brings us back to the age-old problem that you can have a promoted inhouse source for a customer but it only gets attached to their order if a) it's manually processed by a box office operator who selects the correct inhouse source or b) they enter a promo code on the web that activates that source code or c) you embed the source code in the URL.
Which, of course, misses out on the large number of people who read an email or a brochure, bypass our links completely and then go and buy something from the web later and get the generic web source.
So here was my wild and crazy idea - could I possibly write a nightly routine that would take people who have ordered over the web with the generic source and then change their source code back-end to an inhouse source if they happened to have one? The most difficult part, of course, would be to work out some sort of system for stack-ranking existing in-house sources so that the correct one gets picked (and that may well be insurmountable, given how keen we are on source codes here). But assuming such an auto-calculation of a source could be done, my question is - where would this need to be changed?
I'm thinking:
- Change the source code on T_ORDER
- Look up the corresponding T_TRANSACTION row (presumably by order_no) and change the source_no entry there.
- Look up T_PROMOTION, find the inhouse source I want to use on that customer's record and update the response to "TransProcessed" and put in the response_dt.
Would this work? Or am I missing something highly dangerous that could blow up our Tessitura?
Here's another thought: Choosing which source is most relevant to any particular order is hard enough, but what if the emphasis changes at some later date? Now how do you distinguish between those with an explicit source, and those who didn't who you assigned a source post-hoc?
I've attempted to address the same problem in the past, but instead of changing the recorded source, I instead made the determination for the sake of a report: that way if different metrics were desired, I could make the change, then re-run the report and come up with a new set of numbers.