Attendance Vs Donors

Beth,

First, that is a real cool question.

Second, I know of no standard tool built into the Tessitura client, or T-Stats that would answer that question exactly in a single go. (There might be something that others have figured out.) The challenges with your question are:
1. You first have to find the earliest member date for each existing customer you want to evaluate. Then:
2. With both the dates and customer numbers from above you have to go through each customers ticket history to find and count all performances for each customer with an order or performance date prior to the first membership date you found above.

Once completed, the answer you would get, would not be a single number, but a set of numbers representing the number of performances prior to the first membership for each customer. These values would represent some sort of distribution of values. You could then summarize that distribution of values with a single number statistics like average(mean), median, standard deviation....

The "standard" tool we would use to answer such a question would be a SQL query written by our DBA or a Busisness Analyst into SSMS. Here at BAM we are also working on non-standard methods using Microsofts Power Query, Power Pivot, and Power BI desktop to answer such questions. Unfortunately, these custom tools will only work if you are hosting your Tessitura onsite. They take a DBA to setup the system to support such activities. They do not work with RAMP.

If you do not have access to those tools, you could also use manual labor, to sample a smallish subset of customers data giving you a sense of these values. 100 or so accounts reviewed by an intern might give you a starting point.

One might also be able to use one of the membership and one of the ticket history reports to export this data for such an evaluation in MS Excel. Others might be able to jump in with the best candidate reports.

I know that this is not much of an answer. However, I hope this gives you some ideas about moving forward. I would love to see if others have a good quick way to do this. We have a lot of cleaver folks in the community. Please share any method you end up using, I'm sure that others have the same type of question.

--Tom Brown
Business Analytics mgr

Brooklyn Academy of Music BAM
Peter J. Sharp Building
30 Lafayette Ave
Brooklyn, NY 11217

www.BAM.org

Tbrown@bam.org
718-724-8135



On Wed, May 4, 2016 at 11:36 AM, Beth Harvey wrote:

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









This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Development Forum. You may reply to this message to post to the Development forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

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 Tom, 

 

I'm really sorry, this went straight to my spam and I didn't check the website because I always get emails - how annoying!

Thank you very much for this detailed response, I will take some time to go through it properly and try a few things - I'll let you know how it goes.

 

Thank you

Beth

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

Beth