Season crossover

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.

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.

Happy analyzing!
Christine

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.

• (+) Crossover Sales Report - Marketing - Forums - Tessitura Network
• (+) Membership Overlap Report - Reporting & Analytics - Forums - Tessitura Network