Crossover Sales Report

Hi Jean,

Crossover reporting as you've described is possible in Analytics. There are three approaches I'll describe here. Ultimately, we need a filter on Constituents who purchased a particular Production Season.

The more obvious way might be to create a Tessitura List, and then schedule the Generate List report to run prior to you Analytics load each night. With that in place, follow this help page for filtering a dashboard on a Tessitura List. Then add on Production Season with a count of Constituent ID, and see everything they've purchased.

The second approach, instead of using a Tessitura List, is to filter on Constituent ID, and then go straight to the Ranking type filter tab. From there, select to Top 100000 by a formula like ( [Total Ticket Count] , [Production Season] ), where in this filter value formula, Production Season is set to your base production season out from which you'll look at crossover. Then add on Production Season with a count of Constituent ID, and see everything they've purchased. One additional tidbit here is that unsold seats have a dummy customer ID on them in Analytics, so a unique count of Constituent ID will be one patron account higher than what you might expect. To correct that, you might add a filter on Type from the CONSTITUENT dimension, and exclude (none) and maybe also General Public for good measure.

The third approach is a more explicit filter on Constituent ID that doesn't involve deciding how many Constituent ID to include (e.g. TOP 100000), but also doesn't support the native, click-to-select, click-to-filter way of doing things. Here we still filter on Constituent ID, but go straight to the Advanced type filter tab. From there, paste in this code, and update Chicago 2020 with the exact name of the base Production Season from which you looking for crossover:

{
  "attributes": [
    {
      "dim": "[PERFORMANCE DETAIL.Production Season]",
      "filter": {
        "members": [
          "Chicago 2020"
        ]
      }
    }
  ],
  "custom": true
}

I don't have evidence, but my guess is that the Advanced Attribute filter will perform better than the Ranking type filter, because it has less work to do not having to rank all the Constituent IDs by their Total Ticket Count within the selected Production Season, but it's also definitely less friendly to set up.

Best,
Chris

Hey Chris Wallingford,

Firstly, thanks for this detailed outline! Amazing

I've set up the second approach and it works perfectly. But I do have one question: Is it possible make the base production season filter dynamic so it updates based on another filter?

I've got it set up so the whole dashboard is filtered by production season. Then this particular widget ignores that filter and I have the base production season manually set. But if it could go "oh, I see you've set the filter at the dashboard level to this, so I'll do the same" that would be amazing... does that make sense :D haha

Hey Chris Wallingford,

Firstly, thanks for this detailed outline! Amazing

I've set up the second approach and it works perfectly. But I do have one question: Is it possible make the base production season filter dynamic so it updates based on another filter?

I've got it set up so the whole dashboard is filtered by production season. Then this particular widget ignores that filter and I have the base production season manually set. But if it could go "oh, I see you've set the filter at the dashboard level to this, so I'll do the same" that would be amazing... does that make sense :D haha

Hi Phillip,

Afraid not. At least not without producing some custom javascript behind the widget to dynamically write the filter while the widget is being processed. If you have a javascript developer on staff who can help, I can provide the Sisense documentation resources in support of that effort.