Start your query:
- Go to Analysis > Information Library
- Click Add to add an ad-hoc query
- Select the source view of Sales order and click OK
- To narrow your results by Transaction Date, drag Transaction Date from the middle column to Include Records where. Set this to be equal to the date range you are looking for.
- If you'd like to see anyone who attended a particular program, in the left column expand Sales Order Item, expand Sales Order Item Ticket, then highlight Sales Order Item Program. Drag Program record to Include Records Where. Set this to be equal to the program you'd like to consider. For example: Sales Order Item\Sales Order Item Ticket\Program\Program record is equal to General Admission.
- If you'd like to eliminate refunded tickets, in the left hand column highlight Tickets beneath Sales Order Item Ticket. From the middle column, drag Is refunded? into Include Records Where. This will exclude any ticket that has been either fully or partially refunded. For example: Sales Order Item\Sales Order Item Ticket\Tickets\Is refunded is equal to No.
- As an alternative to Step 3, if you'd like to eliminate any cancelled tickets (for example: tickets that were refunded and added back to inventory), you can drag Active to Include Records where. Set this to be equal to Yes. This will only count active tickets. For example: Sales Order Item\Sales Order Item Ticket\Tickets\Active is equal to Yes
- To count the number of tickets sold by price type, with Tickets beneath Sales Order Item Ticket still highlighted, drag Price Type from the middle column to Results Fields to display. In Results fields to display, highlight that field and click the sigma button and check the option to Count the price type field. This will give you a total number of how many tickets were sold.
- If you'd also like to display what price type was sold, drag the Price Type field again to Results Fields to Display.
- To display the membership information for the constituent associated with the Sales Order, in the left hand column, scroll up and expand Constituent. Next, expand Member and highlight Membership. From the middle column, drag out the fields you need. For example, you may want to see Membership Level or Status.
Here is an example of what the final product might look like:
You can then use this raw data in a program such as Excel or Tableau to synthesize the data and find the percentage of members who have visited. You can also use our OData feature to link this query with Excel or Tableau. See our related solution here: How to generate the OData link