Building a Report

Clarissa Arocena
Clarissa Arocena
  • Updated

To build a report in Spektrix, you'll first have to understand the different data tables, the right report types to choose, the various output fields and criteria sets available, and the data analysis you can do in Excel.

The Report Explorer

You'll find the Report Explorer in Insights & Mailings. There are five main report types:

  • Accounting
  • Analysis
  • Customer
  • Membership
  • Sales

See our Reports in Spektrix for further details on these screens, and how to customize them.

You will need Insights & Mailings Administrator permissions to be able to edit and/or add reports. An admin user at your organization can grant this to you by going to Settings > Users > User roles.

What's in the Data Tables?

The different report types (above) refer to different data tables in Spektrix, and get data from the system in slightly different ways:

Sales

Sales reports show one line per seat, whether sold, reserved, locked or available. What sits against this are details about who is in that seat now: their name, how much they paid, when they booked, and so on.

Analysis

Analysis reports show information from orders (e.g. tickets, memberships, donations, etc.) with one line per item.

These are snapshots of all the items as they have been sold—you will never see unsold seats in an Analysis report. Furthermore, if items are returned, it will be marked as returned in the Analysis data tables and may therefore be filtered out of a report next time you run it.

Accounting

Accounting reports are details of all the actions made against your database: sales, returns, delivery methods, payments, commissions, etc. These are the best reports for account departments as, unlike the Sales and Analysis reports, they are comprehensive and include every action: for example, a returned ticket would be listed twice, once for the sale and again for its return.

Sales and analysis reports can change: if the seat is returned, the information will appear differently when you next run the report. But an accounting report will never change—the historical action will always be logged there.

Customer

Customer reports give a line for every customer in the database. These reports simply show output basics of the customer record, name, address, email address, balance, plus a few extras that we have added, like average spend per order.

You can't get a customer's entire booking history from this report as the report will always output one line per customer, and their history won't fit on one line.

Membership

Membership reports give a line per membership sale. You will find a few useful Standard Reports that use this report type.

How the Data Exports

Demo_Order_History.jpg

Let's say you're looking at an order history.

The transaction was booked as follows (reading in reverse order: from bottom to top, right to left):

Yellow Highlight: The customer bought four tickets, paid cash, and also reserved four tickets for another date

Orange Highlight: He then came back, cancelled the tickets, and received credit for them

Red Highlight: He then returned again, bought some of the reserved tickets, and used the credit to pay for them

At the top is the current status of the order, containing two paid seats.

This history of this order will display differently depending on the report types used.

Sales

Excel_Demo_1_Colour_Coded_2.jpg

In a Sales report, you will see one line per seat. The only information you will see here will be for the two sold seats based on our example order above. The returned seats will show as available as sales reports do not show all actions in an order's history.

Based on the example order above, the Sales report type will only output the actions listed in the rows highlighted in red.

Analysis

Excel_Demo_2_Colour_Coded_3.jpg

In an Analysis report, you will see one line per item. The "Is Returned?" column indicates whether tickets were returned or not, so it is important to take this into account when building Analysis reports.

Based on the example order above, the Analysis report type will output the yellow, red, and orange highlighted rows.

Accounting

Excel_Demo_3_Colour_Coded_2.jpg

In an Accounting report, you will see a line for every action in the order. These lines can't change once they are written to the database, so you will see every stage of the order process (e.g. reservation, sale, return, etc.) listed separately.

Based on the example order above, the accounting report will output all of the actions of the order in full.

Total Charged versus Total Received

The Total Charged and the Total Received columns output slightly differently.

  • Total charged is the amount registered to each item, and this sits against the items (tickets, memberships, etc.) in the accounting report. If you want to know the price of an item, e.g. tickets, you use Total Charged.
  • Total received is the amount paid by the customer, and this sits against the payments in the accounting report. It's not possible to write this back to all the items individually, so it sits on a separate line in these report. If you want to know payment amounts, e.g. card payments, you use Total Received.

The Total Charged must always balance the Total Received. To balance the order, you need to make a payment or payments that equate to the value of all items in that order. You can think about the Sales basket as Total Charged (items) and Total Received (payments).

How to Build a Report

Mockup.jpgCreate a mockup

When you build your own reports, it's advisable to start at the end, with your intended result, and to work backwards.

Here is an example of a report that the Spektrix Dude requires: a report showing Event Genres along the top, postcodes along the side, and quantity of tickets in the middle.

Which Report Type?

The output should give you a clue as to which report type you need to select. Our genre/postcode report is going to be an Analysis report, as it is a snapshot of order items.

Creating the Report

  • Go to All Reports > New Report.
  • Choose the type of report, name and description
  • Choose your criteria set, as best suits your data. For this report, the date customers confirmed a transaction would be an ideal criteria, to limit the data to a useful date range.

Postcodes can be output in various shapes and sizes. Spektrix can output the full postcode (e.g. N1 7EU), or we can use metrics that group them together, like Postcode Area (e.g. N), District (N1) or Sector (N1 7). These are all available under the Customer metric group.

Criteria Sets

Once you have filtered data using the criteria set, the only data making it through to the report is the data you've selected; all the other information disappears and is not available to output. For example, if your criteria set is for transactions made this month, you will not be able to compare within the report against sales made last month, as the data simply isn't there.

Selecting Output

We know three of the items we want to output: the genre, the postcode and the quantity of tickets purchased. Note that When outputting attributes, you cannot choose an individual one; you need to drag the Event Attribute metric into the output, and the report will output them all.

When to use Grouped Mode

It can be extremely useful to group your data, and Spektrix provides a "Switch to Grouped Mode" option in your outputs to do this.

For example, a standard report would always show us one line per item, as below:

Single_item_mode.JPG

But the example above contains a lot of duplicated data, and it could be represented better like this:

Grouped_mode.JPG

If run the report in Grouped Mode, it will only output one line for every duplicated field, which can produce much more useful results.

When to Show Count

You can add a count of the number of times that each field has been repeated by using the Count function. This is available in Grouped Mode and will show you the number of items, events or instances that have been summarised by the grouping.

The events and instances counts will total the number of events and instances contained within the output data, while the items count is more flexible: this will provide a count for all the items you are outputting, which of course will change depending on what you are reporting on. This may be tickets, or customers, or postcodes, or donations—whatever item you have decided to group on will be counted here.

Show_Count.JPG

A count will appear in a new, separate column in your data, called 'Count'.

Running your Report

You have now created a report! You will only be able to run it as an Excel file (or CSV, or XML) as the PDF templates can only be added by Spektrix Support. You should find that this gives you a neat summary of your data, however, and depending on your requirements you may never need a PDF version.

Editing in Excel

Some reports will output perfectly for you, but sometimes you may want to summarise the data further using Excel tools. We especially recommend ordering lists by size, or adding pivot tables to filter your data. Click here for Microsoft's guide to reporting in Excel and feel free to call us for advice, too!

Requesting a Custom Report

There are certain things you can't do in Excel, but Spektrix's custom-made report builder can create some very clever calculations. If your report needs to include some multi-layer filtering, or grouped events, or to only show certain parts of the data, use our handy Report Request Form and we will be in touch!

Was this article helpful?

1 out of 1 found this helpful

Have more questions? Submit a request