Hi there,
I'm doing some RFM (recency, frequency, monetary) and gap (lapsed period) analysis on constituent segments in the database and wondering if anyone has done the same. I'm especially interested in gap analysis as I'd like to look at number and length of lapse periods (years off) during a customers time with us. Currently looking at ticket buying behaviour but will use it on donations once I've worked out the kinks.
Cheers
H
Heath Wilder
I've done some experimenting with recency, frequency and total monetary value (tickets + contributions). I end up creating a set of Tessitura ranks one for each category. The ranks were decile over a 5 year period within a constituent type. So comparing schools with other schools, corporations vs corporations, individuals vs individuals. This produced a number for each of the ranking groups between 0 and 10 (0 meaning that there were no values in the last 5 years for this customer). With 1 being the least advantageous value and 10 the most advantageous value. Therer's a final value that was a sum of the other scores so that value was between 0 and 30. The intent here was to get broad groupings of accounts in a way that would be understandable by staff around the building.
Shoutout to JCA’s Segmentation Engine, which does a brilliant job of RFV as well as hordes of other analyses that have been hugely helpful to us at the Arsht over the years. has a lot of insights into how impactful Segmentation Engine has been for us.
Thanks I used an ntile function to break up BI.VT_ORDER_DETAIL (removing comps and non performance orders) into 3 quartiles. I love the idea of rolling in / separating contributions out to get a Customer Lifetime Value estimate. That final value score is definitely something that I'll take a look at. I'll raise that as a topic at Analytic Coffee
Thanks for the recommend . I've been dancing around the Segmentation Engine but as we are pushing through more and more parts of the business through Tessitura I need to dive in and do an ROI on it. I might pester Nicole at some point for her opinion . You've got me very intrigued as to the other analyses. I'm always paranoid that the board might learn of my existence and I'll need to drop another graph bomb to cover my escape.
Always happy to demo Segmentation Engine. It works well with Analytics and List Mgr. I’m sure you’d think of a ton of ways to use it in various dashboards.
Oooh. Segmentation engine and dashboards! Sounds like a TLCC talk. I'll have to read up on the data slicing.
To be honest the gap analysis is the thing that's challenging my SQL chops at the moment
I can help out on that front. CTEs are your friend for the Gaps and Islands analysis you’re looking to determine. Of course, much of that depends on what kind of analysis you want to do — my script calculates most recent number of consecutive years of activity based on a fyear. Shoot me an email at dgomez@arshtcenter.org and I’ll send over what I have.
DGomez
You're a good man Charlie Brown.