Hi folks,
I enjoyed the 'Increasing your skill and confidence with Analytics' webinar a couple of weeks ago, and have been using one of Nate Pearson's formula examples to bucket number of constituents by frequency, by season.
If I wanted to then translate that into the number of tickets sold under each of those buckets eg how many tickets did we sell to those that attended 3 times last season, how would I alter this formula:
SUM( [Constituent ID] , IF (COUNT( [Performance ID] ) = 3 , 1 , NULL) )
I don't have much sql knowledge at all, so any help gratefully received...
Thanks in advance!
Melanie
Hi Melanie,
Replace the "1" with a [Total Ticket Count] to get the total count of tickets attributable to constituents with exactly 3 performances.
Thanks Chris Wallingford that worked a treat! I've also got Ticket Paid Value coming out too now, which is brilliant. It also made sense to me - bonus!
Hi, Happy New Year everyone!
Another followup question on frequency for Chris Wallingford or anyone else who has a solution!
I want to calculate the overall average frequency of bookers by season - I can do this by amalgamating a series of individual bucket formulas (eg average frequency = [(all the one-time attenders x 1) + (all the attenders that came twice x2) + (all the attenders that came 3 times x 3) etc etc] divided by the total number of bookers in a season.
However, we have people who eg come to 47 of our concerts in a season, and I don't want to have to repeat this for every level of frequency up to the total number of concerts in a season - as that is going to take a long time....
Is there an easy way of writing a formula in Analytics as an 'nth term' - so that it then sums up all the individual 'n' elements (bookers who came to 'n' concerts multiplied by 'n')? I know mathmatically this must be possible, but I'm not so great at knowing how to convert this into a formula that Analytics would understand!
Any help from you clever people gratefully received, as ever.
Cheers
Deleted my original post due to my misunderstanding and trying again...
I believe the overall average frequency of bookers by season can be simplified to a count of unique performances per unique booker divided by the unique count of bookers.
SUM ( [Constituent ID] , [# unique Performance ID] )/ [# unique Constituent ID]
Brilliant that certainly looks to be doing the trick Chris Wallingford - I'm getting an average frequency that's a sensible amount (think of a number between 2 and 3...) and a simple solution! And from here I can now calculate average group size I think, which is great news! Thanks so much