Pricing Rules & List Manager

That's a great idea, although timing is going to be tricky:

1) You'll need a mechanism for updating the list: it's not going to regenerate until some process operates on it.

2) When you come up with that mechanism, you'll need to be sure that you do not include people who have that price rule on one of their sub line items but have not yet completed their order.

Since list regeneration can be expensive, I'd be tempted to start with a manual list, and then add a stored procedure to LP_CUSTOMER_RANK that determines if a pricing rule has been used by that customer, and if so, add them.  There you can pare down your queries to only look for a specific customer and also time limit the searches over orders (LP_CUSTOMER_RANK will fire, among other times, at the completion of an order, so you can limit to orders last updated within a few minutes) to minimize the process time.

Interesting...I was going the generate a list route, but this gives me food for thought as we're going to have a BUNCH of lists re-generating constantly to meet all of our rules.

In that case you'd probably want to add a System Table that matches a Pricing Rule with a List (and perhaps a ticket count?  any other data points of interest?): that way you'd have an easy client configuration when pricing rules change, and also your stored procedure would be able to make the updates in one pass, rather than having a separate query/insert/update per pricing rule.

Oh, and one last thing: what this process would not handle easily (which a regenerated list might) would be refunds/exchanges that remove the pricing rule from the customer's ticket history.  It would depend on how you expected it to behave in those circumstances, of course.

You could always get your DBA to create a SQL Server Agent job that runs on a half hourly basis (would that be enough?) - I would suggest adding it as a step that runs as part of the job that updates the Ticket History table.  The job would just have a line (like the example below) for each of the lists involved.

exec RP_GENERATE_LIST @list_no = 3800

Let me know if you need more info......

Martin

I like that idea rather than relying on the report server...ugh. Thanks Martin!

Michele,

Just curious: I assume the Pricing Rule is something like [Product 1] + [Price Type A] + [Not in List X].  Will you have a messaging rule of [Product 1] + [Price Type A] + [In List X] to notify them of why they are not getting the discount?

This is for one of our member benefits. At a certain level and above, you get a BOGO free ticket. You are only allowed (by law) one complimentary ticket per membership period. So we are trying to exclude those who have used it from using it again during their membership period. It's rather convoluted and I think we're going to have to have some custom work around to it because of the membership period restriction.

This is for one of our member benefits. At a certain level and above, you get a BOGO free ticket. You are only allowed (by law) one complimentary ticket per membership period. So we are trying to exclude those who have used it from using it again during their membership period. It's rather convoluted and I think we're going to have to have some custom work around to it because of the membership period restriction.

Hi Michele, 

Not sure if this would be in any way helpful but what you are thinking of triggered a memory from last summer's conference. I seem to remember at TLCC that someone (Millennium Theater Wales? I think it was Liz Baird) did a presentation where they had a costume table created which involved rankings etc. to handle upsells, my notes say it was in the Patron Retention Online session. Perhaps some of what they did might help you with your project?

Cheers, 

Michaela