Heat-map of ticket sales in R?

First of all, cool idea.  I have done some things like this in the past.  In fact an animated 3d seat map over time, with the Z direction showing the number of holds, and color showing the value of the seat during the run. (It was very crude because there was no way to reproduce historical data "as of" a times in the past.  I included this in a TLCC presentation a few years back.  https://www.tessituranetwork.com/Passthrough?itemUri=/tlcc/2016/Pres/12_06_Attendance_Project.pptm)

I did not do this in R but in Power BI with the sand-dance plugin.  (You certainly could do this in R.) If I remember correctly, I used some of the tables that are used to create the T-Stats data warehouse.  I'm not in front of a computer connected to Tessitura right now.  But, these were the tables that are used to populate the seats cube.  If I remember correctly one of the tables provided the X/Y coordinates you would need to produce the seat map.  

If you would like to talk further please be in touch.

First of all, cool idea.  I have done some things like this in the past.  In fact an animated 3d seat map over time, with the Z direction showing the number of holds, and color showing the value of the seat during the run. (It was very crude because there was no way to reproduce historical data "as of" a times in the past.  I included this in a TLCC presentation a few years back.  https://www.tessituranetwork.com/Passthrough?itemUri=/tlcc/2016/Pres/12_06_Attendance_Project.pptm)

I did not do this in R but in Power BI with the sand-dance plugin.  (You certainly could do this in R.) If I remember correctly, I used some of the tables that are used to create the T-Stats data warehouse.  I'm not in front of a computer connected to Tessitura right now.  But, these were the tables that are used to populate the seats cube.  If I remember correctly one of the tables provided the X/Y coordinates you would need to produce the seat map.  

If you would like to talk further please be in touch.

Hey,

So I started working on this heatmap for one of our stages and the early result looks something like this:

For now it is not very flexible so everytime time I want to plot something I need to upload specific data. I need to figure out now how to do this smart so we can filter out particular performances or select a time range.

This is very cool ("hot" I guess, it's a heat map...).  The stage is at the top I would guess.  Are Blue seats are handicapped reserves? 

What is the color?  Utilization or price or some other metric?

Would love to see your code.  Are you using ggplot2 or something else for the plotting?

You are right, stage is at the top, blue spots are handicap seats. They are blue because in this color scale its the coldest color, meaning that they are taken very rarely.

These are the most popular seats that customers chose over couple years. So the hot red spots are almost always sold out, greens are less popular, and blue, as mentioned before, is the least popular, the coldest seats.

I am thinking what other data could be added to this: average seat price on each seat , for example. Then prices could be adjusted for most and least popular seats.

I could share the code if you want, but for now I have not figured out how to query data directly from R, so I'm exporting data with SQL and then importing it in R and run the code

I have done this in the past at BAM.  I don't have access to SQL server right now.  But in outline of the ideas are:

1. Enable you Active Directory Account or local account on your SQL server to have access at least to some views in the SQL server.  This can be tested in MS Excel with Power Query or in some other ways.

Once you have gotten those connections working I'd use dplyr and the tidyvers stuff comming out of RStudio.  Check out.

https://rviews.rstudio.com/2017/05/17/databases-using-r/ using packages such as: dplyr, DBI, odbc, keyring and pool.

Once you have worked this out, it can work fairly well.  Please be in touch if I can be of any assistance.

--Tom