Reading Reports

One of the challenges we're working on at the moment is helping people read reports.

The funny thing is, writing reports is easy. (Easy, if your SQL and XSLT are up to scratch, but they aren't very hard skills to learn). When we started out, the system shipped with hardly any reports, and I said to people "I've got few reports here, but if there's a report that you need that we don't have, we'll write it for you. Writing reports is easy". So now, we have loads of reports. And it turns out that reading reports is hard.

Even reading report names is hard. Because we now have loads of reports, and because it's hard to describe what they do and how they work in a single sentence, our most common report related support question is "Which report contains the information I want?". Right now someone's going over all of them with a view to rationalising them and improving the clarity of the description. I don't know what the answer is going to be but I expect it will look like more tags and more filtering options.

The reason why it's hard to find the report you want is that there are lots of ways a report can do its calculations.

Suppose you sell a ticket on the 1st of the month for show A, and then you exchange it for a ticket for show B a week later.

Some reports are based on the transaction log, so if you ask for the sales for show A broken down by day for that month, you see a positive entry on the 1st, and a negative entry on the 8th. If you ran the report just for the first week of the month, you wouldn't know that anything had been cancelled at all. Reports based on these transaction logs are useful for payment reconciliations: if you're trying to reconcile what was in the bank at the end of the 1st with what the database says you've sold, then you absolutely need to know about that sale: from the point of view of the bank statement at the end of the day, the cancellation hasn't happened yet.

Some reports are based on the current status of the orders / tickets. If you run a report saying "how many seats have been sold for show A as of now" and then you cancel an order and then you run the report again, the cancelled order no longer shows up. We don't care that it had been sold in the past and then cancelled, we just want to know whether or not it is sold right now. And even these reports can be broken down by day: "of the tickets that are currently sold for this show, how many were sold per day over the last month" is a valid question. If you ran a report like that on our scenario above, the tickets that were sold and then cancelled wouldn't show up at all - even if you filtered the report down to "the first 7 days of the month".  Reports based on the current state of orders are useful for sales analysis and promoter returns: if you want to know whether an advert you bought on the 2nd performed well and you're looking at sales you made on the 3rd, you definitely want to ignore any sales that were made on the 3rd and subsequently cancelled: those don't count towards your ad performance.

So the second most common question is "Why do these two reports on the same date range return different numbers?". And the answer to that is usually this difference in the basis for calculation: both methods are "right" in different contexts, but they'll give you slightly different answers.

If it's not that, then it's either "One figure includes booking fees and transaction fees and delivery fees" (useful for payment reconciliation) and "One figure is just the ticket value net of fees" (useful for communicating with promoters: the fees you charge are none of their business), or sometimes even "One report's date range is filtering by performance date and the other is filtering by order date".

Given that people consuming reports don't much want to understand how they work, and that reports can work in different ways that it's hard to express without a couple of paragraphs of text, that makes reading reports hard.

What people say they want is "a sheet with all the data so that I can see the various combinations for myself". But although such reports exist, they're not much use. The raw data is just a list of ticket amounts with transaction dates - plus an awful lot of parameters that they have to be grouped by in order to mean anything much: show or performance or genre or order date or performance date or sales channel or ledger code... The best reports answer a specific question: generic lists of numbers to allow someone to figure it out themselves don't get used much. You can reconstruct a sales movement report over a month by giving someone the job of running the "payment reconciliation today" report every day of the month and coping the figures into a spreadsheet (I've seen it done in venues converting from another system to Monad), but really, your sales movement by day is one thing and your payment reconciliation another.

Because good reports can only answer one question at a time, if you're looking for strategic information on what you're doing well and what you could be doing better, don't go looking in your day-to-day operational reports. Your front of house report is designed to give the FOH manager everything they need to get the audience in and the show open: it will be hard work spotting long term sales trends in it. Your payment reconciliation reports are designed to make sure the numbers we calculate for sales today align with the numbers in the bank account - broken down by transaction date and payment type, but not by show or genre. Your promoter sales returns show the final destination of the show's sales performance, but not the route it took to get there.

Reading reports with an eye to identifying challenges and opportunities means reading reports to identify patterns, and those patterns won't be visible if you aren't asking the right questions. You might be interested in how far in advance of a show people book, and a report like "Chart: Advance Booking Trends" with parameters "All Films" / "Performance in the last week"


(scale obscured because this is from real data)

might make you think that most of your cinema sales are on the door and you shouldn't worry too much about advance booking... but drilling down into just a single PG rated film shows a different story:



People who've promised their children that they're going to see a film behave differently from the general public - who knew?! I didn't, until I looked.

And a music event tells a very different story:

With people either sure they're going well in advance (dedicated fans - but not enough to sell out) and people deciding on the night, but almost no sales in between.

Your ticketing system will be collecting an enormous amount of data for you, and that data is there to be analysed, but in order to do that you have to start with a question. You can't just stare at reports hoping that the answer will leap out at you like magic lawyer speed reading a telephone directory in a movie.

Let's suppose we want to sell more tickets, because who doesn't? Well, to sell more tickets, we need to know why people buy tickets, and what stops people from buying tickets. Making people want to buy tickets is someone else's job (and, the show has to be good), but it's easy to want to go to a show and not buy a ticket. There are loads of plays and films and gigs I want to see that I don't buy tickets for. So let's look at what stops people buying tickets. Is it money? Then let's look at sales by day of the month. If most people get paid at the start of the month we might expect to see on average more orders on the 5th than on the 25th. Is it the time it takes to make the booking? Then let's look at sales by time of day, and if people are too busy to research and book tickets at work we might see a spike at 7pm when they get home. Is our box office open to help with these calls, or do most staff go home at the same time as our customers do? Is it that they're too sober? Let's look as see if there's a spike at 11pm when the pubs kick out. Is it the time that it takes to attend the performance? Then let's get some hard numbers on how much Saturday tickets out sell Wednesday tickets, and use that to inform how much more we charge for a ticket on the weekend or during half term.

I don't even work at a venue, and there's a bunch of questions right there that I find interesting and that I wish that my customers asked more often. We don't currently have reports that break down sales by time of day or day of week, because literally no one has ever asked, but that's exactly the kind of report I would be delighted to write if I thought it would ever get used.

Things I hate it when people ask for them (2): A discount on the first 50 tickets sold

Now, we can do this. We can set up a package that applies the discount, and make the package use a promo code, and we use the special promo code "AUTOMATIC" which is always applied to the basket without someone needing to type it in, and then make the promo code so that it can only be activated 50 times and after that it stops working.

I just think that we shouldn't because it is basically a terrible idea. It leads to all sorts of weird behaviour when you get near the limit and it doesn't effectively motivate people to buy more tickets. 

Suppose 49 tickets have been sold, and I add 2 to my basket. One is at the full price, and one at the discount price. This looks odd and you get a call in the box office and you have to explain to me why the prices are weird.
Then someone else add a ticket to their basket. They see it at full price.
I decide I can't be bothered and remove the tickets from my basket. They look at their basket. Suddenly their ticket has gone down in price!

For general admission shows it's much clearer to have two separate products on sale: "Cheap tickets (Only 50 Available!)" and "Normal Tickets". When the Cheap Ticket have sold out it's clear that someone should manually add a normal ticket to their basket.

For allocated seating it makes more sense to use price bands. The cheap seats should be the bad seats at the back. Someone who will only go if they get the discount can do so, and they can sit at the back of the stalls, and someone who wants the front row can pay extra for it even if they're first in the queue.

If you want an early bird discount, make it time based, not quantity based. If someone sees an offer advertised and it expires tomorrow, it provokes them to buy today. If they see an offer advertised and it says "First 50 tickets cheap", and then they try to buy a ticket, they either find:
* The cheap tickets have been sold, and they're disappointed.
* The cheap tickets haven't been sold, and perhaps they can leave it until tomorrow, and they don't buy now.
neither of which causes immediate action.

A time based discount encourages people to book early, which you want. A  quantity based discount encourages people to book first, which they have no control over, because that's down to how many other people have booked already.

If the show flops, your quantity based discount still applies to those sad tickets being sold on the door. If the show sells well, you probably didn't need to give a discount. Either of these outcome is the opposite of revenue maximisation.

Rather than keeping a strict count of exactly how many discounted tickets have been sold, one alternative would be to use dynamic pricing to check every 30 minutes whether more than 50 tickets have been sold, and if they have been, puts the price up from your earlybird price of e.g. £20 to the full price of e.g. £25. This removes the problem with weird edge cases where a basket has a mixture of discount tickets and full price tickets. But it does mean that if 49 tickets have been sold, and then we do our check, and then in the next 30 minutes another 10 tickets are suddenly sold, and then we put the price up, the promoter looks at their sales return and says "I told you only to sell 50 tickets at the discount price and you sold 59! You owe me £45 you bastards", which also a pain, unless they understand and agree it might go over.




In short: there are loads of better ways of doing promotional discounts. Make it a 3 for 2 offer. Put a code on your Facebook page and make it give the discount "for a limited time" and keep an eye on it. Make an earlybird discount with a fixed end date. Use dynamic pricing to tweak the price for you. Wait to see how early sales go and THEN do a discount if the answer is "badly". Do flash sales and promote them on socal media.

Things I'm pretty certain aren't mathematically possible and I wish people would stop asking for (1)

1) A report linking payment types to shows.

Which boils down to: directly linking individual payments to individual tickets.

Oh, I understand why a finance dept might ask for it. Especially a finance dept that usually deals with invoices and BACS payments. This ticket has been bought, so it must have been paid for. Which payment paid for it?

But.

It is possible to have a mixed basket with split payments.

I know that it hardly ever happens, but it's designed into the system, and I can't take it out, and I can't do anything that's fundamentally incompatible with the fact that you can have tickets for Show A and Show B in an order, and pay for them with Payment 1 and Payment 2 of different payment types.

If I have an order with 2 tickets for Sooty in Space (£10 each) and 2 tickets for The Importance of Being Ernest (£15 each) totalling £50, and it's been paid for £5 gift voucher and the balance (£45) by card, there isn't an easy answer to the question "Which ticket was paid for by the gift voucher?".

The only sensible thing to do is split the gift voucher between the shows based on face value; £2 to Sooty and £3 to Importance. So the £45 of card payments is assigned £18 sooty and £27 Importance.

Suppose now we cancel the Sooty tickets and refund £20 back to the card.

Suddenly the proportions have changed. The £2 of gift voucher that was paying for Sooty is now paying for Importance tickets: our original assignment must have been incorrect, and we now need to go back and change it to £20 card against Sooty (so that we can refund it) and £25 card + £5 gift voucher against Importance.

The means that that amount of money in the "credit card income" column for Importance has gone DOWN, as the result of an unrelated cancellation. No Importance tickets have been cancelled, and the promoter of Importance might be very strict about not allowing refunds, and is going to need a lot of explanation as to why their numbers have retrospectively changed.

The only way I can see to actually make this work is to treat every single ticket like its own account ledger. There's a £10 debit on the Sooty ticket and then a £9 card credit and a £1 gift voucher credit, and then for the refund there's a £1 gift voucher debit and a £1 card credit (matching with the Importance ticket, so the first phase of doing a refund is doing an internal transfer between one ticket and another) and THEN a £10 card debit and a £10 cancellation credit, leaving the ticket's account balanced a £0. I've seen a bunch of ticketing system databases in the course of doing data conversions, and no one does anything as mental as this.


How does everyone else handle this? Should I just stop whinging and just split every payment down to every ticket?

Ticket Ballot

Finally got to use the system to run a ticket ballot. Everything went according to plan!

It's a nice combination of lots of small bits of already existing functionality that come together to be more than the sum of their parts.

Firstly, we create a Marketing List signup, that allows people to join a specific marketing list. This functionality was motivated by Waiting Lists - and general enquiry forms - but also works for this, for no extra development effort.

So we make the signup available, and the (lots) of people sign up to the list, creating accounts with passwords, addresses, etc, and using the existing Custom Form functionality to supply additional details.

We give them time to sign up - and then we close the ballot.

Next, we dedupe the list of signups - by name, email address, postal address, and additional id info - using existing functionality, so people with multiple email accounts don't get multiple chances.

Then, we copy the signup list, and use the existing "Random Filter" marketing tool to draw N names from the signup. We then use the existing "Add User Status" marketing tool to give these people a "Ballot Winner" membership status for the next day... and then extract the names and send them an email informing them that they've won the chance to buy a ticket. The ticket product, of course, is on sale only to people with the "Ballot Winner" status, so those people can buy tickets and no one else can.

At the end of that period, if there are any unsold tickets, we draw more names, and repeat the process - copy the signup list, remove the people who won in round 1, and then filter down again.


What I've learned from this first go is:
1. The click through rate is surprisingly low, and if you have enough tickets for N orders, and you only draw N names, you'll need to do several rounds.

2. Rather than insisting that everyone who wins the ballot gets a chance to buy a ticket, it might be more efficient - especially if time is limited - to draw 2N names, and change the communication so that it's clear that they aren't *guaranteed* a ticket - there's still competition, it's just much more civilized, with 2N people after 2N tickets, rather than 1000N people after N ticket.

3. It's much, much calmer than throwing a few tickets on sale and hoping that the servers don't melt.

On Being Sold Out

The plan is to further improve our queue (which stood up to the load once the stored procedure was fixed) rather than cut over to a 3rd party stand-alone solution, but here's a useful and much more detailed breakdown of the difficulty of announcing a show as "Sold Out":

https://www.linkedin.com/pulse/ticketing-onsales-can-you-sell-out-2-minutes-learn-why-sodemann

from the CEO of https://queue-it.com/

YMMBT On Sale Postmortem

Firstly: apologies if you're reading this because you lost your place in the queue. Whilst we found and fixed the problem quickly, it wasn't quickly enough to stop a lot of sessions timing out. It was more of a scrum than a queue, and the difference between position 1 and 3000 might only have been a fraction of a second, but we appreciate it's frustrating and disheartening to see a nice low number turn into a horrible big one.

What happened to the queue?

There was a problem with the stored procedure that calculated where you were in the queue. It previously read

UPDATE UserSessions SET SessionLastActivity = GETDATE(), SessionExpires = DATEADD(mi, 5, GETDATE()) WHERE SessionId = @SessionId

SELECT 
SessionId, 
IsQueued, 
(SELECT COUNT(*) FROM UserSessions AS USother WHERE IsQueued = 1 AND USother.SessionStart < USthis.SessionStart) AS AheadInQueue, 
(SELECT SettingValue FROM SettingValues WHERE SettingGroupId = '00000000-0000-0000-0000-000000000000' AND SettingName = 'QueueMessage') AS Message
FROM UserSessions  AS USthis WHERE USthis.SessionId = @SessionId

and we were unable to generate sufficient load to make this go wrong in testing, but with over 10,000 real people hammering it it started to generate deadlocks. That's when so many people are trying to read and write to the same bit of the database at the same time that it gets stuck. The fix was simple: it now reads

UPDATE UserSessions SET SessionLastActivity = GETDATE(), SessionExpires = DATEADD(mi, 5, GETDATE()) WHERE SessionId = @SessionId

SELECT 
SessionId, 
IsQueued, 
(SELECT COUNT(*) FROM UserSessions  WITH (NOLOCK) AS USother WHERE IsQueued = 1 AND USother.SessionStart < USthis.SessionStart) AS AheadInQueue, 
(SELECT SettingValue FROM SettingValues WHERE SettingGroupId = '00000000-0000-0000-0000-000000000000' AND SettingName = 'QueueMessage') AS Message
FROM UserSessions  AS USthis WITH (NOLOCK) WHERE USthis.SessionId = @SessionId

which avoids locking the UserSessions table whilst counting up how many people are ahead of you in the queue.

Unfortunately, diagnosing the problem took just over 5 minutes. And the SessionExpiry, which is updated every time the queue is checked, is set to 5 minutes from now - if someone joins the queue and then decides not to wait and closes their browser, we don't want them hanging around blocking everyone else for ages.

So if the problem had been resolved within 5 minutes, everyone's queue position would have been unchanged - which is what we expected when we told people to refresh. As it was, sessions which hadn't managed to contact the server and hadn't updated their expiry time for 5 minutes expired, which means their place in the queue was lost (and refreshing or not would have made no difference). As soon as we noticed this happening we extended the SessionExpiry for all the remaining sessions whilst we worked on the problem, but by then we'd already lost a chunk of the first sessions.

Once the queue was fixed, the rest of the system bore the load reasonable well.We peaked at letting 500 simultaneous sessions onto the site - which was unnecessary. Even if the site could have supported everyone who was interested browsing tickets all at once, it would have been a terrible experience; every time you looked at a ticket someone would have snatched it out from under you, and actually getting something in the basket would be luck for a few, and frustration for everyone else. For future on sales with this type of product (lots of small distinct blocks) we think we'll limit the number of simultaneous users to 100 or so; we can handle more, but expect it would feel better if less crowded.

Sold Out?

One of the things that proved difficult on the night - and contentious afterwards - was deciding when to tell everyone the show had sold out. The problem is that some people get past the queue, get tickets in their basket, get as far as the credit card page, and then decide that perhaps they can't afford it after all, so they close their browser and walk away. We give people a bit longer before the session times out once people are on the credit card page, which means that there can be quite a long period during which not all tickets have been sold - so we aren't "sold out" - but no tickets are available, so there's very little point in people sticking around. It's hard to communicate this in 140 characters or less, though. "There are no more tickets available right now but some are stuck in baskets and might become available in an hour or two when they time out, but we can't say for sure" is an accurate but not a very punchy message.

What next?

The main problem - reading the queue position causing deadlocks - is fixed.

We'll leave the site at 100 simultaneous users, so that they can browse the performances and times without someone nabbing the tickets out from under them.

We're going to move the IsQueued status out of the main UserSessions table and into a separate table to join to at the earliest opportunity, and change how we calculate the queue position so that it's easier on the database server.

We are considering not displaying the queue position at all. Not only is it relatively expensive to calculate, it makes people feel worse when things go wrong - or even, when things go right. Just being told that you're an a queue and not knowing if an internal hiccup shuffles your position would be much less upsetting than seeing your number go from 600 to 9000. The very term "Queue" implies an orderly line, but when huge numbers of people arrive at the site at the same moment, it's not really about who got there first; getting a lower queue position is a matter of fractions of a second and pure luck.

Whether we display the queue position or not, we're going to work with YMBBT to rethink the communications, how we give people information on their chances of getting tickets, and perhaps define criteria to trigger messages ("Only N tickets left", "No more tickets are available - some are in baskets", "All tickets actually sold") so we don't have to decide on the fly.

We're also going to improve the layout of the queue page, moving the dynamic message up to the top of the page, move some of the currently hard-coded text "Hang in there" into the message so we can get rid of it when no longer appropriate, and add the facility to redirect everyone en-masse to an arbitrary URL once the tickets have sold out (or nearly sold out).

When an error occurred, the queue position was displayed as "999", which was just an error place holder, and not a real queue position. 999 is far too low for a placeholder, and it's been updated to 999999.

And finally, we will continue to suggest and encourage the use of ticket ballots for heavily over-subscribed shows, avoiding the problems and queues inherent in first-come-first-served on-sale dates.

The White Whale

So this is interesting, but not totally surprising.

One of the things (I think) that people want to do when marketing a show is get people to book in advance - not just because that gives you money and numbers early, but because if someone see a show advertised and thinks "that looks interesting, but it probably won't sell out, so I'll just get a ticket on the door" then a lot of the time when the night comes around they then say "fuck it, I'm just going to the pub instead". Making people pay money - and I think even a small amount of money would do - for a ticket means they've made a commitment to going to see the show, and are therefore less likely to not bother to turn up. If you are keeping track of the money, then money taken on the door is just as good as money taken in advance, but money not taken on the door because they didn't go is not as good as money taken in advance.

Conversely, when you're giving tickets away for free, advance booking is difficult, because anyone who things "that looks interesting" can grab a ticket just in case, and are then in no danger of "missing out" if it sells out, but are just as likely to not bother going as someone who hadn't bought a ticket at all.

If you're absolutely committed to giving your tickets away for free - and I understand why you might want to do that - and you don't want people to have to drive across town only to find the queue on the door is a mile long and they don't get in - I'm not sure what the options are.

1. Charge a deposit with the booking, and return it when they show up? Unfortunately gets you involved with all the credit card processing fees, which won't get refunded.

2. Name and shame the no shows? You'd need scanners for access control then, and it would be very embarrassing if you let someone in without scanning them and then accused them of wasting a ticket.

3. Make getting the tickets a bit more of an effort. How about a lottery? So you've got 400 seats for the White Whale, and more than 400 people who say they want to go (because it sold out). Instead of just giving the free seats away first-come first-served, you could collect names, and then when either X times as many people have registered as you have seats, or there's only Y days left before the performance (X = 2 or 3, Y = 7 or 14?) you do the draw. That way, people who registered and "Win" tickets feel more special for having won them. And I think that this might make them more likely to use the tickets than if they just got them for free.

I like 3. And the other benefit to 3 is that there's a built in cooling off period, so if you tell someone they've won a ticket, then it's the ideal time for them to say "actually, I'm not that fussed", and you can easily roll the dice again and give it to someone else.