Reporting on the impact of cancellations on income isn't just about being able to see what you've lost in terms of refunds, but also what you've retained in terms of customers exchanging tickets or donating to your organisation. Ideally, to get a full picture of the impact, you want to be able to report on the following:
- Ticket returns (covered in our Reporting on Lost Income article)
- Refunds (also covered in our Reporting on Lost Income article)
- Tickets exchanged to future Instances or alternative Events
- Tickets converted into donations
In this article we're going to take you through some best practice suggestions for reporting on this information, to help you understand the choices your customers make when performances are cancelled. Following these suggestions should also allow you to see which Events/Instances are the hardest hit by returns, and which ones customers are exchanging their tickets for.
Here are the steps we'll cover:
- Creating new Criteria Sets
- Creating a new Customer List
- Running the Customer List through your report
- Reviewing your reports
A quick caveat
The suggestions we make in this article are intended to give you an overview of your post-cancellation situation, and to help you understand your customers' behaviour. For example:
- Of the customers who returned tickets, how much are they receiving in refunds to cash and cards rather than in the form of credit and vouchers?
- Of those same people, how much money have they swapped into future performance or donations?
They're not, however, intended for financial reconciliation purposes. As Spektrix allows multiple Events, items and payments in one transaction, it's likely that you will find orders which contain two or more methods of payment. For example, you could have an order which looks something like this:
- Items:
- £50 worth of tickets
- £15 worth of merchandise
- £10 in donation
- Payments:
- £25 by card
- £50 in Account Credit
Because of this, you can't reliably determine exactly which Event(s) or item(s) each payment (or refund) has been made against. The suggestions in this article, therefore, are based on using Customer Lists and reports in Spektrix to help you understand your customers' behaviour overall, rather than trying to gather exact information about every order.
NOTE: we're going to refer to the standard Payments & Activities (Detailed) report, but you may find it useful to see this information on an Instance level, rather than by Event (e.g. if you tend to put on long-running shows). If that's the case, please fill in this form and request the Payments & Activities report (detailed incl. Instance Date & Time).
Steps to follow
While the Payments & Activities (Detailed) report can provide all of the information you need for the purpose of understanding the impact of cancellations, in order to get the right information you need to narrow down the data you're looking at. There are four main steps to follow for this process, which are as follows:
- Create two new Criteria Sets for your report
- Create a new Customer List looking for customers who have returned tickets over a given time range
- Run the new Customer List through the report, using both new Criteria Sets
- Review your reports
We'll take you through each of these steps, explaining what to create and how.
The first step is to create two Criteria Sets on your Payments & Activities (Detailed) report:
By default, this report is run on a date range; however it combines both returns/refunds and sales/payment into one – therefore you can't use it to clearly compare the two. By creating two separate criteria sets, you can run each one separately in order to get as clear a picture as possible.
We'll explain the criteria to choose for each of these, but have a read of our Criteria Sets article for more information if you're not familiar with building Criteria Sets in Spektrix.
The first Criteria Set will find all items which have been returned and all refunds which have been processed (whether to cash, card or Account Credit).
- First, add the following criteria from the Transaction Items metric wrapper:
- Date Transaction Confirmed: use Fixed date range and enter anew each time you run the report
- Transaction Confirmed: tick this check box
- Total Charged is between £[blank] and £-0.01
- NOTE: the Total Charged metric refers to the value of items purchased or returned, so looking for a negative value here will find all items which have been returned in the selected date range
- Next, add the following criteria from the same wrapper, adding them to the OR section beneath your existing criteria:
- Date Transaction Confirmed: use Fixed date range and enter anew each time you run the report
- Transaction Confirmed: tick this check box
- Total Received is between £[blank] and £-0.01
- NOTE: the Total Received metric refers to the value of payments or refunds being made, so looking for a negative value here will find all refunds in the selected date range
NOTE: make sure that the top half of this Criteria Set uses Total Charged while the second half uses Total Received; also make sure you're including the minus symbol in -0.01.
The second Criteria Set will find all items which have been purchased and all payments which have been processed.
- First, add the following criteria from the Transaction Items metric wrapper:
- Date Transaction Confirmed: use Fixed date range and enter anew each time you run the report
- Transaction Confirmed: tick this check box
- Total Charged is between £0.01 and £[blank]
- NOTE: the Total Charged metric refers to the value of items purchased or returned, so looking for a negative value here will find all items which have been purchased in the selected date range
- Next, add the following criteria from the same wrapper, adding them to the OR section beneath your existing criteria:
- Date Transaction Confirmed: use Fixed date range and enter anew each time you run the report
- Transaction Confirmed: tick this check box
- Total Received is between £0.01 and £[blank]
- NOTE: the Total Received metric refers to the value of payments or refunds being made, so looking for a negative value here will find all payments in the selected date range
NOTE: make sure that the top half of this Criteria Set uses Total Charged while the second half uses Total Received, while this time you're including a positive figure (£0.01).
The next step in this process is to create a Customer List to find all customers who have returned a ticket since you started to process returns – once this is built, you can run it through your report to look at just these customers.
We'll take you through the steps of building this Customer List, but if you're not familiar with this part of Spektrix then have a read of our Customer List Builder article first.
- Create a new Customer List – go to Insights & Mailings > Customer Lists > New Customer List
- Name your Customer List something like Returns Since Cancellation Date
- Create a New Local Segment using a Purchasing Segment
- Add the following criteria from the Tickets metric wrapper (making sure to untick the Only show commonly used criteria option):
- Date Returned: use Fixed date range and enter the date range you want to look for
- Returned: tick this check box
- If you want to narrow things down to only look at specific performances, you can add in the Instances criteria:
- Open the Tickets metric wrapper again (it has to be Tickets, as you need to add this in using AND logic)
- Open the Event Instances sub-wrapper
- Drag Instances down to where it says AND, below Date Returned
- If you want to look for Instances over a particular data range, also add in Start Date from the same wrapper
- Make sure you choose your date range(s) and which Instances you want to include, then click Next and save your segment
Once you've built your segment, drag it into your Customer List and click the Update Count button to see how many customers in total have returned tickets in that time range:
NOTE: make sure you always update the count before you do anything else with your Customer List, to check that the numbers look right – if they look unexpectedly high or low, double check your segment to make sure you're set it up correctly.
Run the Customer List through your report
Once you've created your Customer List and Criteria Sets, the next step is to run your new Customer List through the Payments & Activities report (detailed).
- From the Choose an Action dropdown in your Customer List, choose the Run through a report option:
- From the Select a Report and Criteria Set screen, find your Payments & Activities report (detailed) and click on the green + sign to see your Criteria Sets:
- Click the Select this report and criteria set button for your Returns and Refunds by Date Criteria Set, then choose your date range (i.e. from the date you began to process refunds to today's date) and select Run as PDF
- Repeat this step for the Sales and Payments by Date Criteria set
You should now have two versions of your Payments & Activities report (detailed) saved as PDFs reports:
- One showing the value of refunds/account credit over the time period you selected
- One showing tickets purchased and payments made over the same time period
Value of refunds/account credit
In this version of the report you should be able to see the value of refunds/account credits in the Payments section towards the top of the report:
In this example, you can see that some money has been refunded as cash, but a greater value has been converted to Gift Vouchers and Account Credit, which can be spent on more items in future.
In the Activities Breakdown section of the report you also can see the total value of returned tickets by Event/Instance:
Tickets purchased and payments made
This version of the report will show the tickets that the customers on your list have purchased over the same date range. Here's an example of what the Activities section might look like:
Here's an example of the Payments section, showing the value of each method of payment:
While the first report showed that some customers had received refunds, you can see from this report that some customers have gone on to exchange their tickets and/or make further payments.
If you have encouraged customers towards gifting their returned tickets to you as a donation, then the second report is where you would be able to see the evidence of this:
NOTE: as we mentioned earlier, these reports don't show you exactly how every returned ticket went on to be exchanged/refunded; however, they do go some way towards showing you the broader trends of your customers' behaviour without you needing to analyse every single returned order line by line.
***
If you have any further questions, or would like to discuss anything covered by this article in more detail, please don't hesitate to get in touch with the Spektrix Support team.