Oveit’s granular reporting allows you to gain a deeper understanding of their attendees’ registration details. And while we collect and offer any information processed through our platform, it’s impossible to export it into one single report. The level of complexity shown by our system, together with the multitude of different set-ups you can opt for, offer a merely infinite number of possibilities. Yet, throughout this article, we’ll show you how to combine various reports and have a crystal-clear image of the customer journey for each attendee.
To do it, we are going to use the following reports:
Orders – the report containing all information regarding the orders placed for your event, including the billing information and total amount paid.
Attendees – the report containing all information about the attendees associated with each of your tickets.
Now, let’s check and see how to combine this information and get a better understanding of each attendee’s journey. As you will see below, by combining this information, we will learn everything there is to know (event-related) about the people attending your experience.
Log in to your Oveit account.
Navigate to the “Reports” section. Locate and download the Orders Report for the specific event you want to analyze.
The Orders Report provides information about each order placed for the event. It includes details such as the order number, purchaser’s (individual or company) name, billing information, and the discount code used (if applicable).
Download the Attendee Report. This report is directly connected to the event for which it’s collecting data and you’ll find it right under the event for which you plan to analyze the data.
The Attendee Report provides detailed information about each attendee’s registration. It includes fields such as name, email, registration date, and any additional data collected through the registration form.
The attendee information we’re looking for
Now, let’s look at what we’re planning to obtain by combining information from these two reports. As all the attendee data is available through the Attendees Report, we’re probably looking to see how they registered. As Oveit allows you to set up an unlimited number of discount Vouchers (each with its own discount codes), you can track to see who used what code. How they paid, or who placed the order for a specific attendee.
Here is how Excel, through the VLOOKUP formula, helps us get the information.
Combining the event Reports using VLOOKUP
Open both the Attendee Report and the Orders Report into separate worksheets.
Both reports have a common field – the order number. This field is used to link the attendee’s information with the respective order.
In the Attendees report, in the first unfilled column, enter the VLOOKUP in the second cell. The formula should look like this: =VLOOKUP(order_number, orders_report_range, column_number, FALSE).
Decoding the formula
“order_number” refers to the cell containing the order number in the Attendee Report. In our case, we will select the first Order (cell B2)
“orders_report_range” refers to the range of cells in the Orders Report having the order numbers and associated information. In our case, we will select the entire Orders worksheet as we want to search through all the information.
“column_number” represents the column number in the Orders Report that contains the desired information you want to extract (e.g., discount code). As we are looking to extract de discount code located in column L, we will enter 12 (for the VLOOKUP formula to correctly return the value that we are looking for, we need to enter the number of the column from which we’re extracting the information). Alternatively, if we were looking to bring to the “Attendees” report the payment method for each ticket, we would look to extract information from the Payment method (column I), we would need to enter the number of the column; 10.
“FALSE” ensures an exact match is found. In our case, the formula will return “0” for attendees that haven’t used a discount code, and the exact code for those that used one.
p.s. Depending on your Excel version, it’s possible that the Orders column is stored as numbers inside text cells. Please make sure that your values are stored as numbers, otherwise, the formula will be invalid.
Analyzing the Combined Data
Once you use the VLOOKUP formula, you will see the desired information (e.g., discount code) from the Orders Report corresponding to each attendee in the Attendee Report. Furthermore, you can repeat this process and combine relevant data from any two (or more) reports downloaded from our system, including cashless payments. Just imagine the level of knowledge when combining attendee demographics with purchasing habits (including products sold through our cashless payment system).