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.
I did some similar research last year for one of our organizations. I started with pivot tables, and then exported because I wanted to use a Sankey Diagram.
I made a widget for each season type focusing on what other season types they came to by years.
To get to this, I had a season type filter to exclude the focused season type from this widget so I'm not considering crossover with the season type I am pulling them from (Broadway in this case).
Then I had another filter on constituent ID that used a ranking formula to get anyone who had purchased something within the researched season type (so now pulling everyone who purchased Broadway).
Then my rows were for the other season types and values used a unique count of constituent ID turned into a proportion.
Then I would copy this widget and adjust it to be the other season types.
I attempted a graphical representation with a stacked bar, it's not my favorite way of representing the crossover proportions, but it could work.
I hope that gives you some ideas! I have other widgets and examples from this research too that may be helpful.
Happy analyzing!Christine
This seems very fancy, and I love it. Great work! I'll have to chew on it for a while to see how I can use that for Mystic.
To restate:
You set up Season Types, excluded those Season Types from the widget, then Ranked your Constituent IDs to look only at Constituents who did attend that excluded Season Type. You've basically said "Don't look at X Season, but instead look at people who went to X Season, and let's look at what else they went to". Is that right?I would imagine this would port over to Production Seasons, or something like that.
Thanks, I'm excited to see your variations on it!
We use Season Types which is especially helpful when looking year over year. This also helped with another widget where I could count the unique number of season types to see how many attended two or three different season types in a given year (similar to the ideas your initial comment). Though if you have production seasons or season names with similar naming conventions, you could probably use some text filters and bucketing.
The widget filters work as you stated, I have the constituent ID raking filter gives me everyone who came to X season type. Then I hold out X season type from the widget because I don't want it in the rows as it would be crossover with itself. None of it is a perfect calculation, but it seemed to work well enough to identify trends.