I'm sure this is going to relevant to lots of organizations. I'm trying to find "lost" customers from 2019 to 2021 in Analytics and represent them against our returning customers.
So, I started with a List from Tessitura. For example, Package Buyers in 2019. From that list, I'm filtering my Dashboard to see what those same customer purchased (or didn't) in 2021.
What I want is first of all a count of every customer from the list who has no orders in 2021 as an indicator widget.
Then, what I want is a pie chart of customers from the list and when they purchased tickets, but include the "None" group, so I can see by % who purchased early, who purchased later, who didn't buy until our season started, and who didn't buy at all. Note: The "Early / Later / Season" is stored in the Custom 10 field on an order, so I have the data. What I can't figure out how to do is count the unique customers in these areas while also including the Null orders in the overall count.
Just to increase the fun, I only want constituents counted in 1 of the sales windows, even if they had multiple orders. I can use a Min in the Custom 10 field because of the way the data is stored (starts with a number based on the order I wanted the data to sort).
Got some details on how to grab customer number from the orders where orders are null? I can do it in SQL easily. Still trying to figure out the Sisense syntax to do the same thing.
Hi David,
Chris Hall's suggestion is a good one, so long as the "did not buy in 2021" doesn't need to dynamically change based on dashboard filters (e.g. Season Type). For example, if the constituent purchased in 2020 and not in 2021, that's straightforward enough, but if they bought both a Broadway and a New Theater production in 2020, and then only New Theater in 2021, and your doing analysis on those two types of products separately, then you either need to configure a Constituent Element per Season Type, or leverage more front-end Analytics formulas that will flex with your filters.
This post digs into it: (+) Booking churn Subscribers in Analytics? - Reporting & Analytics - Forums - Tessitura Network
For the pie chart, instead of using Custom 10 on the Categories, you could go without a field there and instead create a Value per slice of the pie. With that you can create one value for the None group that uses a different formula than the ones for each of the time of purchase categories. I'm not quite clear how to do your constituent counts based on only a single Custom 10 value from all the potential values for a single constituent. I'm thinking a Multi-Pass Aggregation and Filtered Value formulas for let's say the Early group, that for each Constituent ID if the count of Order ID where Custom 10 = Early > 0 and the count of Order ID where custom 10 is Later or Season = 0, then count them, else don't. Then for the Later group, count them if there's > 0 orders that are Later and 0 orders that are Season. And for Season, count any that have Season. Or maybe that's backwards and you want to prioritize Season then Later then Early.