Reporting on Lost Income and Cancellations

Lost income and event cancellations can have a financial impact on an organization. Through Standard and Custom Reports in Spektrix, you can measure the impact this has.

You can use Reports in Spektrix to calculate income lost due to ticket returns and cancellations, analyze overall sales trends, and understand how payments are being refunded.

The resources in this article require a working knowledge of understanding, running, and building Reports. Before starting, we recommend reading the following articles:

• Introduction to Reporting
• Introduction to Building Custom Reports
• How to Build a Custom Report
• Criteria Sets

In this article, we will look at both Standard and Custom Reports and how to use them to report on lost income and cancellations. The two main areas of focus are:

• Returns and Lost Income in Daily Activities: A look at three different reports that report item returns and refunded payments more broadly within day-to-day business activities and can help identify trends.
• Returns and Lost Income by Events and/or Instances: A look at two different Custom Reports that report on total Ticket returns by Event or Instance. These reports are best used for cancellations.

REMINDER: Most Reports included in this article focus on lost income due to ticket and ticket commission returns. You can also report on the return of other items including ticket subscriptions, memberships, and merchandise in some of the Reports we mention.

Before taking a deeper look at these reports, we also recommend reading the following articles on Ticket Returns and cancellations:

• Performing a Return and Refund
• Reporting on Ticket Returns
• The Spektrix Ticket Converter
• How to Cancel Events and Instances

Returns and Lost Income in Daily Activities

There are a number of Reports that report item returns and refunded payments more broadly within day-to-day business activities and can help identify trends. These reports look at data at a given date or date range and can report on tickets, ticket subscriptions, memberships, donations, merchandise, and commissions as well as refunded payments.

• Payments & Activities Report using custom criteria sets: A Standard Report that uses custom Criteria Sets to compare returns and refunds with sales and payments.
• Overall Sales Trends: A Custom Report that looks at the total values of items day-to-day. Similar to the Payments & Activities Report, this report will give you sales and returns but through higher-level figures.
• Total Refunded Payments: A Custom Report that looks at the value and number of refunded payments. The refunded payments can be broken down by type, such as credit card refund or account credit. This Report will only analyze the payments and not the item to be returned.

These Reports are best used for general reporting on returns and refunds in day-to-day activities. To analyze specific individual ticket returns, including viewing refund methods and return reasons, read Reporting on Ticket Returns.

TIP: These reports can be scheduled to be received in your inbox daily through Report Schedules. Read more in the article Report Schedules.

Payments & Activities Report using custom Criteria Sets

The Payments and Activities Report is an Accounting Report that shows all payments received over a given period. The Report is broken down by Activities and summarized for the specified time period. The Report is best used for financial reporting.

The Payments & Activities Report includes the total value of all payments and refunds received at the given time. By adding custom Criteria Sets, you can also choose to report only on returns and refunds or report only on sales and payments. Follow the steps below to create these Custom Criteria Sets.

Comparing the three reports (Payments & Activities Report and the report run by Returns and Refunds and then by Sales and Payments) can show broader trends of your customers’ behavior.

Find more information on the Payments & Activities Report in A Guide to Standard Reports in Spektrix.

Creating New Criteria Sets for the Payment & Activities Report

When creating the two new Criteria Sets, follow the steps in the Criteria Sets section of How to Build a Custom Report and additionally set the parameters as follows:

• Returns and Refunds Criteria Set

  1. Select the Transaction Items wrapper > Date Transaction Confirmed metric with the Transaction Confirmed box checked. Using the AND rule, connect the Transaction Items wrapper > Total Charged metric. Set the range for Total Charge by leaving the first text field left blank and inputting -0.01 in the second text field.
  2. Connect the next set of metrics to the above set using the OR rule.
  3. Select the Transaction Items wrapper > Date Transaction Confirmed metric with the Transaction Confirmed box checked. Using the AND rule, connect the Transaction Items wrapper > Total Received metric. Set the range for Total Received by leaving the first text field left blank and inputting -0.01 in the second text field.

  

• Sales and Payments Criteria Set

  1. Select the Transaction Items wrapper > Date Transaction Confirmed metric with the Transaction Confirmed box checked. Using the AND rule, connect the Transaction Items wrapper > Total Charged metric. Set the range by inputting 0.01 in the first text field and the second text field left blank.
  2. Connect the next set of metrics to the above set using the OR rule.
  3. Select the Transaction Items wrapper > Date Transaction Confirmed metric with the Transaction Confirmed box checked. Using the AND rule, connect the Transaction Items wrapper > Total Received metric. Set the range by inputting 0.01 in the first text field and the second text field left blank.

  

TIP: You can also create these new Criteria Sets for these Payments & Activities Reports: Payments & Activities Report (by User), Payments & Activities Report (Daily Breakdown) and Payments & Activities Report (Detailed).

Running the Payments & Activities Reports

When running your Report for the purposes of Reporting on returns, follow the steps in How to Run a Report and additionally set the parameters as follows:

• Run the report using the Returns and Refunds Criteria Set.
• Input dates, leave blank, or select a relative date range in the Date Transaction Confirmed dropdown menu. Make sure the date range is the same for both Total Charged and Total Received.
• Keep the Transaction Confirmed box checked.
• Keep the values the same for Total Charged and Total Received.
• Run as PDF or Excel.

Run the report two more times, once using the Sales and Payments Criteria Set and the last time with the standard By Date Criteria Set. Make sure to use the same date ranges for all reports run.

Reading and Understanding the Payments & Activities Reports

• Payments and Activities Report - Returns and Refunds

  When running the Payments & Activities Report with the Returns and Refunds Criteria Set, the report will output only the negative values.

  

• Payments and Activities Report - Sales and Payments

  When running the Payments & Activities Report with the Sales and Payments Criteria Set, the report will output only the positive values.

  

• Comparing Payments and Activities Reports

  Comparing the reports can give a broader look at customer behavior and identify trends. Using the example, we will compare the Payments by Type section and Activities Breakdown for Events.

   

  Payments By Type

  When running the Report using the standard Criteria Set, we can see the following Payments by Type for the month of August.

  

  Next we will take a look at only refunds from that same time period.

  

  Next we will take a look at only payments from that same time period.

  

  When comparing cash values, the Payments & Activities Report reported $5,924.50 in cash payment for the month of August. Using the custom Criteria Sets, we see $513.50 was refunded in cash and $6,438.00 was paid in cash.

   

  Activities Breakdown for Events

  When running the Report using the standard Criteria Set, we can see the following Activities Breakdown for Events for the month of August.

  

  Next we will take a look at only returns from that same time period.

  

  Next we will take a look at only sales from that same time period.

  

  When comparing Ticket activities for As You Like It, the Payments & Activities Report reported $1,949.50 in net income for the month of August. Using the custom Criteria Sets, we see tickets valued $703.00 were returned and tickets valued $2,642.50 were sold.

Overall Sales Trends

You may want to measure overall sales trends and see how they’ve been affected by ticket returns and cancellations.

Running the Payments & Activities Report (daily breakdown) report will give you sales totals day by day. However, if you want to view higher-level figures to see overall trends compared to recent weeks, or the same point last year, a Custom Report on Overall Sales Trends can show you the total amount of revenue per day (positive or negative) through your Spektrix System. This is also good for comparing daily trends which you can summarize using a Pivot Chart and Tables; we’ll cover an example of this in Using the Overall Sales Trends Report Data.

Creating a Custom Report on Overall Sales Trends

When creating your Custom Report, follow the steps in Introduction to Building Custom Reports and How to Build a Custom Report and additionally set the parameters as follows:

1. Set the Report Type as Accounting (read more about Report Types in Introduction to Building Custom Reports).
2. Give the report a Name (required), for example, “Overall Sales Trends”
3. Give the report an optional Description, for example, “Daily total values of transaction items over a period of time.”
4. Create a Criteria Set using the Transaction Items wrapper > Date Transaction Confirmed metric with the Transaction Confirmed box checked.

   

5. Switch the Output to Grouped Mode and in the Groups in the Output Columns, include the Transaction Items wrapper > Date Confirmed metric with Date selected from the dropdown and the Transaction Items wrapper > Item Type metric along with other metrics you may want to group.
6. In the Aggregate Columns include the Transaction Items wrapper > Total Charged metric with Sum selected from the dropdown and the Transaction Items wrapper > Total Received metric with Sum selected from the dropdown. Check the Show Count? box. Add other metrics you may want to aggregate.

Running the Overall Sales Trends Report

When running your Custom Report, follow the steps in How to Run a Report and additionally set the parameters as follows:

• Run the report using the Criteria Set you created
• Input dates, leave blank, or select a relative date range in the Date Transaction Confirmed dropdown menu
• Keep the Transaction Confirmed box checked
• Run as Excel (unformatted.xlsx) or your chosen format

Reading and Understanding the Overall Sales Trends Report

The Overall Sales Trends Report will output one line per Item Type, per day with the total sum charged or total sum received and its count.

The difference between the Total Charged metric and the Total Received metric is that items that are charged typically include tickets, merchandise, donations and memberships while items that are received are payments.

Items that are charged will typically have a separate category based on whether it is a sale or a return, for example a Ticket Sale and a Ticket Return. All sale items include only positive values, and all return items include only negative values.

On the other hand, received items include different payment types and are not categorized differently if a Customer makes a payment or is refunded. Their values include aggregate data, which can contain both positive and negative values. For example if Customer A makes a cash payment of $15 and Customer B is refunded $5 on the same day, the total Cash Payment for the day will be $10.

Account Credit and Take Deposit item types are listed with negative values as they are liabilities to your organization.

Delivery method item types are included in transactional data as well.

TIP: COBO refers to “Care of Box Office” and is often known as Will Call. In reports, it will be listed as only Cobo delivery.

Using the Overall Sales Trends Report Data

An unformatted Custom Report outputs the data in a simple format. Using Excel, you can turn the data into a table or a Pivot table or chart to filter, sort, or display the data in various ways.

For example, the following data was filtered to only show Ticket Returns and Sales. We then used a Pivot table and chart to show the trends on a line graph.

Total Refunded Payments

This report is useful if you want to see the total value of refunded payments. This will also give 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.

You can create a Custom Report that looks at the total value and number of refunded payments on a given date or range. Payments can be further broken down by payment type, for example, credit card refund, cash refund, or account credit.

REMINDER: This report will analyze only the refunded payments and not the items being returned.

Creating a Custom Report on Total Refunded Payments

When creating your Custom Report, follow the steps in Introduction to Building Custom Reports and How to Build a Custom Report and additionally set the parameters as follows:

1. Set the Report Type as Accounting (read more about Report Types in Introduction to Building Custom Reports).
2. Give the report a Name (required), for example, “Total Refunded Payments.”
3. Give the report an optional Description, for example, “Total value and number of refunded payments on a given date.”
4. Create a Criteria Set using the Payments wrapper > Date Transaction Confirmed metric with the Transaction Confirmed box checked. Using the AND rule, connect the Payments wrapper > Total Received metric. Set the range by leaving the first field blank and inputting -0.01 in the second text field.

   

5. Switch the Output to Grouped Mode and in the Groups in the Output Columns, include the Transaction Items wrapper > Date Confirmed metric with Date selected from the dropdown, the Transaction Items wrapper > Item Type metric, and the Custom Payment wrapper > Custom Payment Name metric along with other metrics you may want to report on.
6. In the Aggregate Columns include the Transaction Items wrapper > Total Received metric with Sum selected from the dropdown. Check the Show Count? box. Add other metrics you may want to aggregate.

Running the Total Refunded Payments Report

When running your Custom Report, follow the steps in How to Run a Report and additionally set the parameters as follows:

• Run the report using the Criteria Set you created
• Input dates, leave blank, or select a relative date range in the Date Transaction Confirmed dropdown menu
• Keep the Transaction Confirmed box checked
• Run as Excel (unformatted.xlsx) or your chosen format

Reading and Understanding the Total Refunded Payments Report

The Total Refunded Payments Report will output one line per Refund Type per day. If a Custom Refund Type is included, the report will output its name. The total value and number will be summed.

• The Count in this Report is the total number of times a payment type appears in a day. This does not necessarily correlate to the number of orders in a day. A single order can have mixed payments or receive multiple of the same type of payments.

  

  In the example, if you were to take a closer look at the transactions made on August 9, you will see that there are two Account Credit issues but were confirmed in the same order, the first $27 at 14:40 and the second $33 at 14:44. The count of Account Credit payments would be two but the number of orders is one.

Using the Total Refunded Payments Report Data

An unformatted Custom Report outputs the data in a simple CSV format. Using Excel you can turn the data into a table or a Pivot table or chart to filter, sort, or display the data in various ways.

For example, the following data was filtered to only Cash Payments and then turned into a Pivot table and chart to show the trends on a bar graph.

Returns and Lost Income by Events and/or Instances

Your organization may be interested in looking at returns by Event or Instance, which is helpful when assessing the financial impact of cancellations.

Custom Reports can give summaries on total value and number of returned tickets by event or instance or report on ticket sales on a given date or range, which can be helpful in understanding sales figures pre-cancellation.

• Total Tickets Returned: A Custom Report that takes a look at the total value and number of tickets returned sorted by Event Instance.
• Historical Ticket Sales Figures: A Custom Report that gives the event sales summary at a given date. Typically Event Sales Reports (Detailed) output seat status at the time of running report. This report takes a look at historical data, best used for determining sales figures pre-cancellation.

Total Tickets Returned

You may want to look at the total value and amount of tickets returned during a specific date range. Because of the detail provided in this Report, we’d recommend this as especially useful for any major cancellations.

WARNING: The report will take a look at every transaction where a ticket has been returned. This may include tickets that are returned more than once in a given time period. If you want to see the current status of seats, then we recommend a Sales Report.

Creating a Custom Report on Total Tickets Returned

When creating your Custom Report, follow the steps in Introduction to Building Custom Reports and How to Build a Custom Report and additionally set the parameters as follows:

1. Set the Report Type as Accounting (read more about Report Types in Introduction to Building Custom Reports).
2. Give the report a Name (required), for example, “Total Tickets Returned.”
3. Give the report an optional Description, for example, “Total value and amount of returned tickets sorted by Event Instance.”
4. Create a Criteria Set using the Ticket wrapper > Date Transaction Confirmed metric with the Transaction Confirmed box checked. Using the AND rule, connect the Ticket wrapper > Total Charged metric with the first text field left blank and -0.01 in the second text field. Again using the AND rule, connect the Ticket wrapper > Event Instances wrapper > Instances metric with the Instances included in metrics box checked.

   

5. Switch the Output to Grouped Mode and in the Groups in the Output Columns, include the Transaction Items wrapper > Item Type metric, the Events wrapper > Event Name metric, and the Event Instances wrapper > Event Date/Time metric with Date selected from the dropdown along with other metrics you may want to report on.
6. In the Aggregate Columns include the Purchase Items wrapper > Price metric with Sum selected from the dropdown, the Commissionable Items wrapper > Commission metric with Sum selected from the dropdown, the Transaction Items wrapper > Total Charged metric with Sum selected from the dropdown. Check the Show Count? box. Add other metrics you may want to aggregate.

Running the Total Tickets Returned Report

When running your Custom Report, follow the steps in How to Run a Report and additionally set the parameters as follows:

• Run the report using the Criteria Set you created
• Select the Event Instance(s) you would like to report and check the Instances included in metrics box
• Input dates, leave blank, or select a relative date range in the Date Transaction Confirmed dropdown menu
• Keep the Transaction Confirmed box checked
• Keep the values the same for Total Charged
• Run as Excel (unformatted .xlsx) or your chosen format

Reading and Understanding the Total Tickets Returned Report

The Total Tickets Returned Report will output one line per Instance with the Sum of Price of Tickets Returned, their Commission, and the Total Charged, which is Price plus Commission. The data will also include the amount of Tickets Returned.

Historical Ticket Sales Figures

You can Report on historical ticket sales figures by creating a Custom Report.

In the article How to Cancel Events and Instances, we recommend running a Sales report such as the Event Sales Report (Detailed) or the Event Instances Occupancy Report to provide you with a snapshot of your sales at the time of cancellation. If you did not run the report before beginning the return process, you can still report on historical ticket sales figures by following the steps below.

Sales Reports will always output information relating to the current status of seats in the system and will not report on the transaction history of a seat so it is best to run an Event Sales Report before you begin returning tickets.

Creating a Custom Report on Historical Ticket Sales Figures

When creating your Custom Report, follow the steps in Introduction to Building Custom Reports and How to Build a Custom Report and additionally set the parameters as follows:

1. Set the Report Type as Accounting (read more about Report Types in Introduction to Building Custom Reports).
2. Give the report a Name (required), for example, “Historical Ticket Sales Figures.”
3. Give the report an optional Description, for example, “Ticket Sales figures at a given date or range.”
4. Create a Criteria Set using the Ticket wrapper > Date Transaction Confirmed metric with the Transaction Confirmed box checked. Using the AND rule, connect the Ticket wrapper > Event Instances wrapper > Instances metric with the Instances included in metrics box checked.

   

5. Switch the Output to Grouped Mode and in the Groups in the Output Columns, include the Events wrapper > Event Name metric and the Event Instances wrapper > Event Date/Time metric with Date selected from the dropdown.
6. In the Aggregate Columns include the Purchase Items wrapper > Price metric with Sum selected from the dropdown. Add other metrics you may want to aggregate.

Running the Historical Ticket Sales Figures Report

When running your Custom Report, follow the steps in How to Run a Report and additionally set the parameters as follows:

• Run the report using the Criteria Set you created
• Select the Event Instance(s) you would like to report and check the Instances included in metrics box
• For Date Transaction Confirmed, select Fixed Date Range and keep the first date blank and select the last date you would like to report (typically the day before returns were made)
• Keep the Transaction Confirmed box checked
• Run as Excel (unformatted.xlsx) or your chosen format

Reading and Understanding the Historical Ticket Sales Figures Report

The Historical Ticket Sales Figures Report will output one line per Instance with the Sum of the Price of Tickets before they were returned. Depending on what output metrics you use, the type of data shown in your Report may vary.

REMINDER: The Sum of Price value in the Historical Ticket Sales Figures Report is equal to the Gross Yield value in the Event Sales Report (Detailed) pre-cancellation.

Further Reading

To continue learning about Reporting and Event cancellations, why not check out the following articles:

• A Guide to Standard Reports in Spektrix
• Report Schedules
• Reporting on Ticket Returns
• The Spektrix Ticket Converter
• How to Cancel Events and Instances