Greetings!
We are an organization that produces multiple arts forms (Ballet, Opera, Philharmonic) and I'm trying to create a pivot table that will give me the crossover between any of those products. Each art form is represented by its own season by fiscal year, so creating such a product would also be great for looking at crossover in the same art form year over year as well.
I'm not sure if this is more difficult than I'm giving it credit for or if I just haven't had enough caffeine today... Does anyone have widget that accomplishes this or advice on how to get there?
Thanks in advance for any guidance the hive mind can provide.
Hi Michael,
To restate: you're looking for a report that shows "These people went to more than one Season"- is that right? You could do a value something like
SUM ( [Constituent ID] , IF ( ( [# of Unique Season ID] ) > 1 , 1 , NULL ))
This one is saying "Count up everyone who's reserved tickets for more than one season". Then, filter that widget by that custom formula, set to ' > 0'.
I'm sure there's a cleaner way to do this, but this should set you in the right direction. You could spot-check your results with a widget filtered by 'Constituent ID', and plug in the numbers you find in your first widget.
Hi Nathanael,
Thanks so much for your response. In my ideal world, the table would look like this, but with numbers that would indicate the number of buyers who crossed between the two series referenced by each column/row:
Is that crazy?
Hmm, that's interesting. I will pre-emptively page Chris Wallingford!Let's think about this. For each season, you want the number of people who bought tickets to that show AND tickets to another show (whatever that show happened to be). One way would be to create a version of the formula with each pairing (X + Y, X + Z, etc.) This seems quite time-intensive. How you've laid things out is tricky because you're asking Analytics "How many ticket sales for X Production were for Y production?" which would always yield zero.
I'm trying to think through how to ask analytics "How many sales were for Prod Season X and another Prod Season?" then scale that up. This seems like it should be doable, but I can't crack it. My head keeps wanting to go to a version of these formulas:
[ Ticket Count ] and ( [Ticket Count] , ALL[Production Season] )
which, when filtered by a Row of Production Season, would look at that Prod Season's ticket count, and the ticket count in ALL Production Seasons (filtered by whatever dashboard/widget filters you've got.
I'm stumped! I'll see if I can think this through further.
What do you want to do with people who went to three (or more) different seasons? At the very least you're going to wind up with ticket income duplication. For a smaller scale issue (showing various subscription renewal, non-renewal, returning, etc. customer behaviors) I just broke down and created lists, which I then used to filter separate widgets, largely indicators.
Hiya,
Here are a couple to review. The first reflect a bit of what Christine has outlined. The second is more like the empty crosstab shown above.