Reporting on Lost Income

Billy Fluck
Billy Fluck
  • Updated

Any time you have to cancel performances, especially in large numbers, it’s important to make sure you have clear visibility on what the financial impact is going to be on your organisation, whether that’s the immediate consequences of cancelling these particular performances or the impact on ticket revenue for future shows. In this article we’re going to talk you through how to find the relevant information using reports in Spektrix.

Here’s what we’re going to cover:

Using the Payments & Activities Report

The first thing to do is check the standard Payment & Activities Report, which you can find in Insights & Mailings > Report Explorer.

Use the By Date criteria set to specify the date range that you want to use, and the report will then give you a net total of both the payments you took (and in what forms), and of your sales and returns for each ticket sold.

Running this Standard Report may give you all of the information that you need, but if you have long-running Events and you’re still selling tickets for non-cancelled Instances in the future it might not give you a clear enough picture about what’s going on.

Building or requesting custom reports

If you need further information which the Payments & Activities Report doesn’t provide, you should be able to find this using a Custom Report - which you can either build yourself, or request that the Spektrix Support team add into your system for you.

We have some pre-built custom reports which we can provide for you to use if you need them, so take a look at the details below and let us know if you would like any of these adding into your system.

NOTE: normally we ask that you submit any requests for report additions/changes via a dedicated Report Request form, but as these are pre-built reports please just get in touch over the phone or via email as normal if you would like to request any of them.

Alternatively, if you’re comfortable building your own reports, we’ve included screenshots on how to set up each of these reports - they all use the Accounting report type.  

A note on report format
While Spektrix Standard Reports (as well as many of the Custom Reports we build) have PDF templates to help make the information they contain as easily understandable as possible, any reports you build yourself - and the pre-built reports we’ve listed below - will all output as basic data files. We recommend you run these as CSV reports.

The benefit of outputting these reports as CSVs is that you’re not tied to a single way of displaying the information - when you’re working on cancelled performances, with the potential for rapidly changing requirements depending on the exact purpose you need each report for, this is usually the best option.

In addition, if you need tweaks to these reports in order to see additional data (for example seeing revenue against Events split into individual Instances), it’s usually possible to change these CSV reports very quickly.

TOTAL CHARGED and TOTAL RECEIVED metrics
Several of the reports in this article use the Total Charged metric, which shows the value of an item to the customer purchasing (or returning) it. For the purpose of clarity, here’s a quick explanation of how that is calculated:

  • For Tickets, this is the face value of the ticket (the Price metric), plus any Ticket Commissions attached to it (the Commission metric)
    • Therefore Total Charged = Price + Commission 
  •  If you have Agent Commission Structures setup on your system, then because these are inside commissions, they are deducted from a ticket’s value
    • Therefore Total Charged for sales made to Agents = Price + Commission - Inside Commission

In contrast, one report uses the Total Received metric, which refers instead to the actual payments/refunds made within a transaction - as opposed to the value of the items.

Finding the value of returned tickets
A simple Accounting report can show you the total amount of ticket revenue that you’ve returned for cancelled Instances, in transactions within a specific date range.

For example, an output like this:

You can request one or both of the following reports, for this purpose:

  • Returned Tickets per Event
  • Returned Tickets per Instance

Bear in mind that customers may have exchanged into different performances or left money as a donation (if you’ve been asking for donations from customers with tickets to cancelled performances). In these cases, only looking at ticket returns may not give the full picture, so you might want to look at refunded payments - which you can do using the report below.

Building this report yourself
If you want to build this report, look for only returned Tickets (excluding comps) over a date range.

Note: to build separate per Event and per Instance  versions, you need the same report type and criteria set, with slightly different output fields.

Here’s what to use:

  • Report type: Accounting
  • Criteria set - use the Tickets metric wrapper:
    • Date Transaction Confirmed - use Fixed date range and leave blank so you can enter each time you run the report
    • Transaction Confirmed: selected
    • Total Charged is between £[blank] and £-0.01

  • Output columns as follows:
    • Use these fields for your per Instance version
    • For the per Event version, use the same fields except for Event Date/Time, which you should not include

Calculating the total number of refunded payments
You may want to simply look at the money that you’re issuing back to customers as the clearest indication of income that’s been refunded and not exchanged into other performances. For example, you may want to look at the total number of Card/Cash Refunds and Account Credit issued per day with a report like this:

Note: all of the times display as midnight (00:00) as the report is grouping an entire day into one row.

You can request the following report for this purpose:

  • Refunded Payments

Building this report yourself
You want to look only for Payments within a given date range which are less than £0 (i.e. refunds and not positive payments). Here’s what to use:

  • Report type: Accounting
  • Criteria set - use the Payments metric wrapper:
    • Date Transaction Confirmed - use Fixed date range and leave blank so you can enter each time you run the report
    • Transaction Confirmed: selected
    • Total Received is between £[blank] and £-0.01

  • Output columns as follows:

Finding the original (pre-refund) sales figures
Running any of the Event Sales Reports in Spektrix - such as the Event Sales Report (Summary) - will always give you the current state of sales for an Event as it stands right now - showing revenue figures for all seats currently sold.

If you’ve returned tickets for cancelled performances, however, you’ll probably want to see what the figures of sales were for Events before you began this process so that you can compare the figures with your current Event Sales Report.

If you didn’t run an Event Sales Report before you started returning tickets, a simple Accounting report can give you these figures for any point in time. Here’s an example:

You can request the following reports for this purpose:

  • Sales at a Point in Time, Event Breakdown
  • Sales at a Point in Time, Instance Breakdown

Building this report yourself
You want to find the status of all Instances happening this year, but only looking at Sales and Returns up until a specific date (i.e. the day before you started returning tickets).

Note: to build separate Event Breakdown and Instance Breakdown versions, you need the same report type and criteria set, with slightly different output fields.

  • Report type: Accounting
  • Criteria set - use the Tickets wrapper, then pull from Event Instances within that:
    • Start Date: select This year
    • Date Transaction Confirmed: use a Fixed date range, leaving the first box blank and completing the second box with the day before you started refunding
    • Transaction Confirmed: selected

  • Output columns as follows:
    • Use these fields for your Instance Breakdown version
    • For the Event Breakdown version, use the same fields except for Event Date/Time, which you should not include

Measuring overall sales trends

You may want to measure overall sales trends and see how they’ve been affected by the cancellations you’ve been making. Running the Payments & Activities Report (daily breakdown) report will give you sales totals day by day.

However, you may want to view higher-level figures to see overall trends compared to recent weeks, or the same point last year. A simple Accounting report can show you the total amount of revenue per day (positive or negative) through your Spektrix System. Here’s an example:

You can request the following report for this purpose:

  • Overall Income Trends by Day

Building this report yourself
You’ll want to consider daily totals in all income over a given period (for example since 1 Jan 2019). Here’s what to use: 

  • Report type: Accounting
  • Criteria set - use the Transaction Items metric wrapper:
    • Date Transaction Confirmed: use a Fixed date range, filling in the first date as ‘1 January 2019’ (or whichever date you would like to start from) and leaving the second date blank
    • Transaction Confirmed: selected

mceclip0.png

If you wanted to limit this criteria set to only look at sales, excluding returns, you would need to create a copy of the criteria, adding in:

    • Total Charged is between £0.01 and £[blank]

mceclip1.png

NOTE: you could also limit this criteria set to only look at ticket sales if you wanted, by selecting your criteria from the Tickets metric wrapper, instead of Transaction Items.

  • Output columns as follows:

***

If you would like any of these reports added to your system, have any questions about setting them up yourself, or would like to discuss reporting on lost income in any further detail, please don’t hesitate to get in touch with the Spektrix Support team to talk things through.