Age Old Mystery: First Time Buyers by Show and FTB at Venue

Absolutely never attended whatsoever? Does it need to be broken out by show or simply and list? Do you have multiple venues? I could slap something together for you, but those are things that need to be cleared up first. Travis

I wrote a custom view that extracted the first performance details attended from the ticket history so that a criteria for List Manager (and by extension, Extraction Segments) could be created. It might need some customising to exclude anything that isn't a performance - if you sell program vouchers, for example. It was based on first performance attended rather than purchased on purpose - we only wanted to report on subscribers once in the season. Creating a list based on a criteria created from this view would then allow you to use it as a filter in T-Stats. Happy to share - just let me know if you think this'd work for you. Martin

Hi Travis! You are spectacular! Yes. The AD wants to know if this is someone's first time at the PAC, not just at a show or by season. We have two venues. It's a challenge given we do over 300 performances a season and they are different every single season. There is no rhyme or reason to the programming whatsoever. All of our subscriptions are CYO/DYO, etc. Having "said" that. If it's a simpler matter to segregate by show I'm all for it. Anything that gets us closer to the end result is great!! Pulling any kind of cohesive data here has been a great challenge! I would love to learn how you see it working!!

Hi Martin: Fantastic! Thank you so much for your willingness to share your work! We do not sell program vouchers but do sell gala events that I would want to exclude from results. Did you find the need to do a good amount of cleanup after exporting? I utilize a number of macros in Excel to assist with that. Given output is firmly dependent on the initial setup I find we're a bit challenged. Each season I must rebuild shows, move entire houses due to too early on sales, wide-ranging sudden price changes, canceled shows, etc. It's a challenge! As you can imagine this creates a lot of duplication, new price types, etc. I would love to take a look!!! Thank you!!!

Michelle, I'm happy to email you the file - given the problem with formatting here in the forums, I can't attach it here - just drop me a line at martin.keen@nida.edu.au. The script I can send you creates a view that returns all the columns from the Ticket History - it may need a little tweaking to suit your environment but not much - do you have a DBA? The view has logic to exclude performances of a certain type - as defined in the System Table TR_PERF_TYPE. Drop me a line if you want the script. Martin

Here is how I would do it. Create a procedure example below. Create report in SSRS that is grouped by perf_no and stick each one in its own tab for easy viewing or uploads. With that said I also added a parameter to create a list for each distinct perf with a list description with New Patron + perf code, example :" New Patron: R0125D". If you need help with the ssrs let me know but it is pretty straight forward. By doing it this way you can use it as a scheduled report receive the excel spread sheet via email and even have the data in list broken out by perfs so you can spend your time with more important matters. I created a debug variable to show the bits and some variables before putting it live. You could leave it or simply remove appropriate if statements. Very important to set to false once live, make sure your history table name is correct and TEST,TEST,TEST within your TEST db before using and going live. Please keep in mind that I am not 100% in the loop obliviously, so you can add to the output and create more parameters if needed. I only gave you a quick example on my approach. If you would like help implementing this email me offline. Travis update, emailed it to you since it was gibberish

I put my example in my files on the site. Feel free to grab it if anyone else would like to see it. file name = Example new patron finder.txt. It is not in my example, but i did just think of something else to add to this in the while loop. After you count the contents of the new list wrap the list creating bit in a new if statement and test to the count if @count = 0 or @count is null skip and move on to the next one. This will make sure you are not creating list with 0 contents. Update: I forgot sli status. I updated it in my files.

Hi Martin, I'm the DBA, such as one exists, for the consortium Michelle's in. I'd love to see the script, as there are a lot of us here that could use it! svoigt@granadasb.org -- Thanks! Stacey

Travis, My method was to have a view that will just return the first performance attended by the constituent - pretty basic coding really. In our world, we want to find those people for whom production A (or productions A, B or C) was their first attendance. I've also uploaded it to be a file attached to my account here. Stacey - I've emailed this script directly to your email. Martin

Hey Martin, 

Think you could send me that script too? charlotte@belvoir.com.au.

Thank you! Charlotte