Hi all,
I'm working on a way to leverage interest weights for segmenting emails for our marketing team. We present a very eclectic mix of performances squeezed into a very short period of time, and so might be promoting a circus, a chamber music concert, modern dance, and a theatre peice all in the same week. In order to avoid sending an email about a dance performance to people who have demonstrated a great deal of past interest in classical music just because they've come to a particular dance performance in the past, I'm looking to find a way to segment based upon calculated interest (like in the weight column in product search in order module) for a set of future performances.
So the functionality I'm looking for is for the marketing team to be able to setup an extraction criteria set by entering a "selector" performance and a set of 2 or 3 "comparator" performances where all are upcoming performances. The resulting segment then will include only those Constituents whose calculated weight for the "selector" is highest among the whole set.
My first instinct is to build a view:
View LV_Calculated_Weight (Customer_no, Weight_PerfA, Weight_PerfB, Weight_PerfC)
and then populate the weight_perf columns with something like:
sum(c.Weight where c.Customer_no = Customer_no and i.inv=@PerfX)
From Tx_Cust_TKW c
join Tx_INV_TKW i on i.tkw = c.tkw
Then I hope to write a query for the criteria set that allows us to define the variables for the performances we're evaluating and selects all Customer_no where WeightPerfA > WeightPerfB and WeightPerfA>WeightPerfC
Can anyone tell me if I'm on the right track here? Can I even pass variable values to a view from a criteria set in the first place?
Thanks everyone for putting up with my poor sql skills, really appreciate your help and I hope you can see what I'm aiming for.
Many Thanks,
Jeff