Hello!
I'm wondering if anyone ever examines typical ticket buying history prior to a constituent becoming a donor. I'm trying to find out typically how many performances someone will attend before becoming a donor.
I've looked at T-Stats and extractions, and I can't seem to find the right combination... does anyone have a way that they do this that they could share?
Thank you!
Beth
Agreed- an interesting question. I think finding this set is doable in excel with some time and formula knowledge: combining a ticket report (attendance by performance, or single sale & package order listings), and a devo report (current const. giving by list, or new cont./memberships) (also faster if you already have a custom report that spits out both for a list, or quite possibly 1 or 2 output sets will work, especially if you need to look at ticket history tables instead of order info). Depending on number of donors in your base you might start w/ a list with first gifts in a certain fiscal year, using an extraction to break down years perhaps. Or be brave and do a longer history which would give the final set a larger sample size.
1. Starting w/ the giving report- sort by ID & gift date old to new and remove duplicates which gives you first gift date.
2. On the tickets side, combine the perf dates into one row w/ a concatenate procedure (for ex: http://www.exceltactics.com/combine-data-multiple-rows-one-cell/) (this puts them in one cell from which you can move text to columns to separate after step 3. there's probably a more elegant solution too that I haven't learned yet)
3. Reference/copy the ticket dates onto the giving report w/ vlookup formula using ID numbers on both reports
4. Compare gift and the range of ticket dates w/ countif formula.
5. Analyze results- mean, median, mode, std dev, distributional modeling, etc
6. Share?
-Zach
Hi Zach,
Thank you very much! I will spend some time trying this out and let you know how it goes!
Sorry for the slow reply, I'm just finding emails now in my junkmail.
Thanks again