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?
Hi Matt
It does sound a bit crazy-brave to me.
Maybe a middle ground - if you used one of the order custom fields to hold your calculated source, then at least you could use that in some way as a reporting value; but you wouldn't lose the original source. You could then use that for updating t_promotion if you want to do that...
And I think you'd want to know at least that the target opened the promoted email before you gave it the credit... Could get to be a slightly complex algorithm.
Ken
Matt,
A v12.5 preview/for your consideration, in v12.5 pricing rules can key of the source code on the line item (new in 12.5 LI level sources). The line item source can be set manually but is generally set by the source on the order at the time the line item is added. Therefore if you go with this plan and open an old order with a changed source any new line items you add to the order will use the new source. If you do implement your original plan, when you go to v12.5 you should not adjust the line item source because when you reload the the order the new LI sources could effect your pricing.
I will admit that personally I am more of a fan of Ken's idea. Order source is held in so many places I think it is pretty risky to manipulate it in the back-end. Also keep in mind that the response dropdown for promotions is user defined in TR_RESPONSE (mostly - leave id = 1 'Trans Processed' alone, and check with your email provider about the ones they are using). However you could certainly add a row for this type of purpose and if the response is anything but 1 put it in a "Responded" status (or something like that).
My 2.546 cents (adjusted for currency)
Best,
Anna
Thanks everyone for your input. I do like the sound of what Ken suggested plus Gawain's idea about allocating a source in the reporting stage rather than at the order level.
Food for thought ...