Spotlight Sessions
How to Build Reports
Learn how to turn your data into powerful insights with ease.
Transcription
Okay. I think that’s time. So, I will get started. Uh, big thank you for everyone for taking the time out of your day to join our How to Build Reports spotlight session. I’m gonna start us off today with just a quick introduction. My name is Rebecca. My pronouns are she, her. And I’m reporting lead here at Spectrix. I’ve been at Spectrix for just over four and a half years, and I really enjoy exploring ways to make data accessible for everyone. So, you’ve got over 45 standard reports full of data on your system, but sometimes you might need access to more bespoke information, perhaps to answer a quick question or perform a deeper analysis of your own unique data. So this spotlight session has been created to offer an introduction to the Spectrix database and demonstrate how you can access the data that you store by creating your own reports. Before we begin, I have some quick information to run through. Today’s webinar will last up to a maximum of 30 minutes. We won’t be having a Q&A today, but I’ll run through the best way for you to get answers to any follow-up questions you might have at the end. We are recording today’s session, so we’ll be sending you the video along with a copy of the slides from today’s presentation. And finally, just to let you know that we’ve got live captioning available, which you can turn on and off using the CC button at the bottom of your screen. Okay. Let’s get started and look at our objectives for this session. So, the key to building accurate reports is to understand how you’re accessing the data in your system. Learning the steps to build reports will make more sense if you know why you’re doing each step. So to start with, we’re gonna familiarize ourselves with some important concepts about how data is stored and accessed in Spectrix. The first thing we’re gonna learn about is the Spectrix database and how data is stored and organized. Then I’ll introduce metrics, which are the key to accessing your data. We’ll then go through how the way data is stored correlates to the different report types we have available to build. And finally, I’ll do a live demonstration of report building and how we can use Excel to change the way we display the data. So let’s dive straight in with an introduction to the Spectrix database. Spectrix stores data in a database, which is a collection of tables. Now, imagine each of these icons is a really big Excel spreadsheet full of data, with each spreadsheet holding specific information. These are just some of the examples of the tables in the Spectrix database. There’s actually 207 tables in total, and each of them are named according to the type of information that they hold. They’re constantly being updated with data whenever your customers, colleagues, or the API interact with Spectrix. Let’s take a closer look at one of the tables to see what it looks like. This is a snippet of the actual customer’s table in the Spectrix database. Each row represents a particular item related to the table. So because this is a customer’s table, there is one row per customer. And this is a similar concept for other tables. For example, the seat’s table displays a row per seat. Each column stores a particular bit of information about each row. For example, a customer’s ID, name, or email address. But all this information is stored behind the scenes. So how do we see it? To access the data stored in these columns and all of the columns in the Spectrix database, we use what we call metrics in your Spectrix system, and these are the key to building reports. Let’s take a look at what they look like in the Insights and Mailings interface when we’re building a report, and we’ll also see them live in the demo later. So this box with the plus sign here is giving us access to the customer table. When we expand it, we see a flurry of boxes. Now, each one of these boxes is a metric, each of which corresponds to a column in a table in the Spectrix database. So, all of the information stored in a Spectrix database table can be accessed by the metrics in the support… in the Spectrix report building tool. So to summarize, metrics are how we access specific bits of information from the tables in the Spectrix database. The metrics that you’re able to access during a report build will be based on the data tables you’re accessing in Spectrix. Different report types in Spectrix have been designed so that they can access different tables in the Spectrix database.So we’re now gonna bring together what we’ve learned to understand how different data is outputted in different report types. We’re gonna focus on three of the tables in the Spectrix database. We’ve already covered the customer’s table, which stores one line of data per customer, as shown in this example. Next up, we have the seats table, which stores one line of data for every single seat in your Spectrix system, regardless of whether they have ever been sold or not. For this reason, it’s the only report type that can report on available, locked, and selected seats. Finally, we have the order items table, which stores one line of data for every item in an order. Because the table is looking at items in an order, it can only report on items which have been initially sold or reserved. The order items table can then also tell us if an item which was sold or reserved has subsequently been returned. In this example, the order ID is the same for every row, so we know that every item is in the same order. The customer initially purchased four tickets in their order. We know this because it is one line per item, and there are four lines which are tickets. We can then see that two of the tickets have been returned, and we know that because the Is Returned column is saying true for two of the tickets. It’s very common in analysis reports to exclude any tickets that have been returned. So these are three of our core tables, but how do we access their information? To access their information, we have different report types in Spectrix which can access different tables. To access the customer’s table, we use a customer report. To access the seats table, we use what we call a sales report. And to access the order items table, we use an analysis report. Now each of these report types access the information in the database which is related to their core table. Sometimes, we can then also access information from other tables. To learn more about this, you can watch the spotlight session called Understanding Data Through Effective Reporting, which goes through it in more detail. And we’ll send you a link to that later. Now you might notice the absence of another commonly used report type called an accounting report. These are used for financial reconciliation, and we could, and in the future will, run a session on this type of report and the metrics it uses. But first of all, we’d recommend getting comfortable with these three report types to begin with. Now that we understand how your data is stored and how we can access it through report types and metrics, let’s look into building our own reports. There are two stages in the report build, which can be assessed by asking two questions. The first question is, which data do I want to include in my report? This step allows us to filter down our data to only see the results you want to see. For example, do you want to see sales for only one particular event? Or do you want to see donations only for one particular fund? In Spectrix, filtering this data is called creating a criteria set. Once we have our filtered data, we then need to ask ourselves which information we want to see. Which columns do I want to see in my report? In Spectrix, this is called the output. We’re gonna see this in action now by building two reports. And just as a reminder, to build reports in Spectrix, you’ll need to have the admin role in Insights and Mailings. Okay. So the first report we’re going to build is a customer report, and we’re gonna look at the average spend per ticket and average spend per order for customers who currently hold specific memberships. To start building a report from Insight- from the Insights and Mailings interface, we navigate to the bottom right of the screen and click on new report. The first thing we’re asked to do is choose the type of report, and each report type has a description, so you have a reminder of how the data outputs. So when we select a customer’s report, we can see it shows one row of data for each customer in the database. So I’m gonna name my report Average Spend of Current Members. And then we hit next. The next section we reach is the criteria set, which is where we choose the data we want to run through our report. Remember, you don’t just have to do this when creating a brand new report to filter your data. You can build new criteria sets on all standard reports to see the data you want. For today’s example, I only want to include customers who currently have a gold, silver, or bronze membership, so this is gonna be my criteria set. These here are the tables we can currently access. We’ve got the customer’s table, which includes both individuals and organizations, or we can select them separately.I’m gonna access the customer’s table by clicking on the plus icon, which opens up some of the metrics I have available to select. To see all of the options, you can uncheck this only show commonly used criteria box here. And that will show you all available metrics for this table. So I’m looking for a metric which tells me the type of membership a customer currently has, so that I can filter my data using it. You can use control and F to help your search. So if I start typing in memberships, I can see the right metric here. So the report building tool works by dragging and dropping metrics to the relevant area. So I’m gonna drag down my memberships metric there. And here, I’m able to select the memberships which I want to include in the report. So I use this dropdown here to select the membership I want, and I’m gonna click add. And I’m gonna add my gold, silver, and bronze membership. I’m then just gonna take a note of the dropdown above. Currently it’s set so that a customer would have to ma- have all of the memberships below, which is very unlikely. So I’m gonna change the dropdown, so that customers have to have some of the memberships below. So now the report searches for customers who have at least one of the memberships listed. I’m gonna give my criteria set a name. And click next. So this is the output, where we select the columns that we want to see. So customer reports, I’ll put one line of data per customer. So I’d like to see the customer’s name, so I’ll drag that to my output columns. I’d also like to see which membership they currently hold. Now I can’t see it here, so I’m gonna untick the only show comm- column, cool, commonly used columns box. And I’m just gonna search for membership. Okay, so I want to see that membership name, so I’m gonna drag that one down there. And finally, I’m gonna drag down the average spend per order and the average spend per ticket. Now these average spend metrics are what we call calculated metrics. And these are metrics whi- which perform regular calculations to provide information which wouldn’t normally be available in a single metric. So these are calculated every night in Spectrix to keep the information up-to-date. Okay, so now, we can choose how to sort our data. So to keep all the membership names together, I’m gonna sort alphabetically by membership name. And I’ve done that by dragging down one of my output columns into the sort by section. I’ll hit next, and this is the section where the support team can add a template. Today, we’re looking at unformatted reports, which don’t require a template, so I’m just gonna select okay to save the report. I’m then gonna find my report, and give it a run. So I can run this as either Excel unformatted or CSV. Okay, just zoom in a bit there. Okay. So, I can now see a row for each customer and their average spend. Now, in Excel, you can quickly find information such as the average spend for all customers simply by selecting the relevant column and just navigating to the bottom of your screen. So for example, if I select column C, I can see down here that the average spend per order for all of these customers is 147 pounds and 34 pence. But what I’d like to see is an overview of each membership type. So for this, we could look into creating a pivot table. Pivot tables are an interactive way to quickly summarize large amounts of data. So to create one, I’m gonna navigate to the insert tab, and I’m gonna select pivot table. Now, it asks which data we’d like to include, and it automatic- automatically selects the data in my spreadsheet. So I’m just gonna click ok. And now we can see the pivot table pane where we can drag and drop our metrics to per- to form layouts and perform calculations as well. So what I’m gonna do is I’m gonna drag down memberships name to the rows box. And what that does is it creates a row for each membership type. Then I’d like to calculate the average val- value per ticket and per order for each membership type. So I’m gonna drag down my two metrics into the values box. Now, I’m just gonna make that a bit bigger. Yep. So the pivot table defaults to summing the values, which on this occasion isn’t what we need. So I’m just gonna use these dropdowns here to change the aggregate function it’s performing. So by clicking on value field settings, I’m gonna change sum to average. And I’m gonna do that for both of them. So we can now see the average spend per order and the average spend per ticket for each of my membership types. So this could be useful for both ticket pricing for members, but we can also see here that bronze members are actually spending more per order…… than silver members. So silver members are spending on average 34 pounds per order, whereas bronze members are spending 62. So perhaps we could send bronze members who have agreed to be contacted some information on the benefits of becoming a silver member. Okay. The next report we’re gonna do, I’m just gonna quickly change systems. Okay. The next report we’re gonna do is designed to show the box office team the number of advanced sales for each venue. For this example, advanced sales are defined as all sales which have taken place in the past for all events that are in the future. So we’re gonna be looking at tickets, which are items in an order. So to access the order items table and the metrics in it, we’re gonna be building an analysis report. So again, I’m gonna navigate to the bottom right of the screen and click new report, and we’re gonna choose an analysis report. And that is one line of data per item. I’ll give the report a name, and then I’ll move on to create my criteria set. So we’re gonna be filtering our data to only include ticket sales with a transaction date in the past and an event date in the future. So here, we have the tables that we can access. Every single one of these tables is an order item. And then to the left here, we’ve got the core table, which is all of the order items together, and this can access every single one. But because we’re just gonna focus on tickets, I’m gonna expand the tickets table here. So the first thing we’re gonna filter on are ticket sales in the past, and the metric we need for this is date transaction confirmed. So I’m gonna drag that down to where it says drop criteria here. Okay. So we’ve got confirmation that we are only looking for tickets, and you’ll notice a metric automatically gets added for ease, and this is the returned metric. And we learned earlier that this will either be true or false depending on whether that ticket has been returned or not. Now, we want to exclude any tickets which have been returned. So by leaving this checkbox blank, we’re saying we only want to see tickets where the is returned metric is false. If it was ticked, we’d only be showing returned tickets. So what we’re gonna do is we’re gonna leave that blank to exclude any returned tickets. To show sales which have happened in the past, the best way to do it is to choose a relative date range between the first recorded date until today. So this is currently looking at all ticket sales which have ever happened. So we do need to filter our data a little bit further to show sales for events in the future. And to do this, we can open up this event instances t- table here, and we’re gonna find start date. When we start to drag start date down, you’ll notice we can either drop it in the and section or the or section. Now, dragging a metric to the and section will mean that any tickets pulled into this report must match both of these criteria. Whereas if we drag this to the or section, the tickets would only have to match one of the criteria to be pulled into the report. So we want ticket sales to be both sold in the past and for events in the future, so I’m gonna drag this into the and section. Again, we’re gonna use a relative date range, but this time, we’re gonna say events which start today and until the end of time. Okay. So I’m gonna name my criteria set, and click next. So we want to see advanced sales by venue. So I’m gonna uncheck the commonly used columns box and find the table where venue is stored, and this is against the event instance. Now, when you first start building reports, you might feel a bit lost finding the metrics, but you’ll soon begin to learn where they’re located, and remember to use control and F to help you. So I’m gonna search for venue, and here, I can see venue names. I’m gonna drag that to my output columns. I’m also gonna sort by venue name as well. And for now, I’m gonna leave it at that and click next. And I’ll click okay. And I’m gonna find my report, and give that a run. Okay. So, we can see here that we’ve got one line for every ticket. So this is quite a large report, and it’s not easy to see the ticket sales broken down by venue. So here’s where a really useful function comes in called grouped mode. I’m gonna head back to my report and go straight to the output.Here changes your report into grouped mode. And what this does is it goes through each of the metrics in your output and groups any of the same values together. So in our report, any lines of data with the same venue name will be grouped together on one line. And then, we can tick this box here called Show Count. And what this does is count how many lines have been grouped together. Remember, one line equals one ticket. So when the report outputs the number of lines that have the same venue name, it is also telling us the number of tickets sold for that venue. So let’s hit Save. And we’re gonna run this again. Okay. So we can now see a breakdown of advanced ticket sales for each venue. If you’d like to look at group mode further, again, the Spotlight Session I mentioned earlier goes through it in more detail. So we’ve now built two reports, which I hope you found useful. So to end our session today, we’ll have a recap of what we’ve learned. So a Spectrix database stores data in tables. We can reference the information stored in tables using metrics. Different report types exist to allow us to access metrics from different tables. We filter the data for reports using metrics in a criteria set. We choose the columns we want to see by selecting metrics in the output. And pivot tables can be used to quickly summarize large amounts of data. And finally, grouped mode in Spectrix groups any lines with the same values in a metric together. If you’d like to test your knowledge of what you’ve learned today, we’ve got some ideas here of reports that you can build in your system. Now, don’t worry about jotting these down. We are gonna send the slides over in a followup email shortly. Along with the slides, we’re going to include links to support center resources that will provide further information about how to build custom reports, including two brand new articles called Introduction to Building Custom Reports and How to Build a Custom Report. And finally, here’s a bit about what’s coming up next in our events program, including the in-person Spectrix Hub starting in a few weeks’ time. And I will actually be at the Edinburgh date, so it would be really great to see you there. We’ll be including links to these events in the follow-up email along with a recording of the session and a copy of the slides. And just before we finish, I’d really like to ask for your help. You’ll shortly see a survey pop up in your browser and it would be of massive value to us as we evaluate and plan future events if you do fill it in. So, please take a moment to leave feedback and as well as any suggestions or ideas for topics that you’d like us to cover. And a massive thank you from me and from everyone at Spectrix for joining us today. And I would really like to see you again soon. So thank you. Bye.
In addition to a powerful suite of over 45 standard reports, your Spektrix system equips you with the ability to create custom reports tailored exactly to your objectives and priorities. This session gives you the tools you need to get specific, empowering you to get the answers you want from your data.
-
Step-by-step guidance on how to build your own reports from scratch.
-
Key metrics in Spektrix and how to use them.
-
How to manipulate data to discover nuggets of analytical gold.
