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.
David Judd,
I think I understand what you are asking. Here are my $0.02 if they are helpful.
From what I understand from your note, I would likely do this in a SQL view that has one row per customer and the columns that you need to show all of the variability. (There would likely be a CTE or two involved in the creation of the view because of the mixed units of analysis involved.) Then over in Lists or in a Set of Lists I would expose this information about grouping of customers to Analytics. Making each of the lists "dynamic" and "Analytics". Then run the Generate a List report nightly against the needed lists prior to the refresh time of my Tessitura Analytics.
I'd then reference the lists in Analytics to show the results.
There might be another clever way to do this just within analytics but it does not come to mind quickly.
--Tom
Not quite, though that would in theory work. I can do this in Lists, but I'm trying to figure out in Analytics how to do what I know how to do in SQL. Basically, I'm trying to do an Outer Join in Analytics. I have a Filter with Customer ID's (my list). I'm trying to create an indicator widget that looks at that list of people and finds those who do NOT have an order in the cube. I seem to only be able to do Inner Joins, so my "lost" customers are just dropping off my dashboard. Sure, I could create another list of customers who have packages in 2019 and nothing in 2021 and bring that list into a Dashboard, but then I have two dashboards, one for what people who did come back bought, and a very lonely "here are the people who didn't" dashboard. Was just trying to put both of these items on the same dashboard, if possible. This question has come up multiple times with Senior Staff (who have we lost and what did they used to buy and, for those that did come back, did their buying patterns change?). I figure this is a pretty universal question coming out of 2020.
Hello David,
With Analytics it isn't straightforward as in SQL to do an outer join to data that doesn't exist. What I would do to accomplish this need would be to use the TX_ANALYTICS_DIVISION_ELEMENT table. In this table you could create a constituent element that would indicate that the customer has not made any purchases this year. This could be something like total ticket amount for the 2021 season. Then in your dashboard you can use the value in the constituent element to filter the dashboard to just the customers that have a total ticket amount greater than 0.
This way you get the logic done in SQL and then can use the results in Analytics.
Let me know if this helps with your need.
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.