Snapshot Reporting

We have just completed some of the work for number 3 below.  We can now track daily hold codes.  We are still working on the latter half of the problem.  Knowing the sales statuses of the seats over the last 15 days.

--Tom

…

718.724.8135

tbrown@BAM.org

From: Self-service Business Intelligence [mailto:groups-selfservicebi@tessituranetwork.com] On Behalf Of Tom Brown
Sent: Friday, February 19, 2016 5:37 PM
To: Thomas Brown <tbrown@bam.org>
Subject: Re: [Self-service Business Intelligence] Snapshot Reporting

So I will jump in with a few examples:

1. How many members of each level do we have on every day of the last 5 year so that we can graph this over time. We want a one page visual understanding of what is going on with memberships over time.
2. In FY2013, FY2014 and FY2015, how many members bought tickets to our Spring Season prior to the first performance.  How many bought during the run of the run of the show. Group this by their membership level at that time.
3. For all of the shows in the last 3 spring seasons.  How many holds of different kinds did we have on each show 15 days prior to curtain.  Did the Seat eventually Sell?
4. Over the past 3 years where were subscribers living.  Provide the results by Zip code at the time, the ticket was placed in an order.  (Note younger folks in NYC move around.  Their current Zip code might be some where in NJ by now. And we are not seeing them a lot of tickets any more.  However when they lived in BAM's zipcode 2 years ago they were a subscriber and we kept the order open for them for the season.  They might have moved mid subscription during the period of the order.)  Where where they actually when they decided to buy that ticket?  (Not where do they live now.)

We have successfully done #1 and spoke about this at conference in 2014.  For this work we were not able to tell exactly what level folks had if there was an upgrade to their membership.  We reported all days of the membership as if they were at the final level.  So there was a bit of historical data shifting that occurred day to day when you run this data set.  (This was due to a few customer upgrades during the year.)  We also did not properly account for early suspensions and cancellations which do not update expiration date, this just changes status.  We reported as if all membership ran the full course init_dt to experation_dt.  We did not count Lapsed periods in which a member might get member benefits, when looking at how many folks could use benefits.

For the rest we have tried, and by in large we have failed.

We are going to make another try at something like number #3 next week using T_ORDER_SEAT_HISTORY data.

Folks please chime in.  We need to help John in his new roll understand our challenges so that he can help us get better data sets so that we can do fantastic Diagnostic and Predictive reporting.

We have just completed some of the work for number 3 below.  We can now track daily hold codes.  We are still working on the latter half of the problem.  Knowing the sales statuses of the seats over the last 15 days.

--Tom

…

718.724.8135

tbrown@BAM.org

From: Self-service Business Intelligence [mailto:groups-selfservicebi@tessituranetwork.com] On Behalf Of Tom Brown
Sent: Friday, February 19, 2016 5:37 PM
To: Thomas Brown <tbrown@bam.org>
Subject: Re: [Self-service Business Intelligence] Snapshot Reporting

So I will jump in with a few examples:

1. How many members of each level do we have on every day of the last 5 year so that we can graph this over time. We want a one page visual understanding of what is going on with memberships over time.
2. In FY2013, FY2014 and FY2015, how many members bought tickets to our Spring Season prior to the first performance.  How many bought during the run of the run of the show. Group this by their membership level at that time.
3. For all of the shows in the last 3 spring seasons.  How many holds of different kinds did we have on each show 15 days prior to curtain.  Did the Seat eventually Sell?
4. Over the past 3 years where were subscribers living.  Provide the results by Zip code at the time, the ticket was placed in an order.  (Note younger folks in NYC move around.  Their current Zip code might be some where in NJ by now. And we are not seeing them a lot of tickets any more.  However when they lived in BAM's zipcode 2 years ago they were a subscriber and we kept the order open for them for the season.  They might have moved mid subscription during the period of the order.)  Where where they actually when they decided to buy that ticket?  (Not where do they live now.)

We have successfully done #1 and spoke about this at conference in 2014.  For this work we were not able to tell exactly what level folks had if there was an upgrade to their membership.  We reported all days of the membership as if they were at the final level.  So there was a bit of historical data shifting that occurred day to day when you run this data set.  (This was due to a few customer upgrades during the year.)  We also did not properly account for early suspensions and cancellations which do not update expiration date, this just changes status.  We reported as if all membership ran the full course init_dt to experation_dt.  We did not count Lapsed periods in which a member might get member benefits, when looking at how many folks could use benefits.

For the rest we have tried, and by in large we have failed.

We are going to make another try at something like number #3 next week using T_ORDER_SEAT_HISTORY data.

Folks please chime in.  We need to help John in his new roll understand our challenges so that he can help us get better data sets so that we can do fantastic Diagnostic and Predictive reporting.