- To start your query, go to Analysis > Information Library
- Click Add an ad-hoc query
- Select the source view of Constituents and click OK.
- The new ad-hoc query screen will appear. First, to see details about what orders this constituent has purchased, expand the Sales Order node in the left column. Next, expand Sales Order Item, then Sales Order Item Ticket, then highlight Program Events. From the middle column, drag Start date to Include Records Where. Set this field to be equal to or between what date parameters you would like to consider for your query. For example, if we are looking for constituents that purchased tickets to 2 or more events this year, our field would look like this: Sales Order\Sales Order Item\Sales Order Item Ticket\Program Events\Start date is equal to this calendar year.
- (Optional) If you would like to only count program events within a certain program or program category, highlight Program beneath Sales Order Item Ticket. From the middle column, use Program record if you'd like to name specific programs or use Program category if you'd like to include any programs within a specific category. Here is an example of how your query might look so far. This query will pull anyone who had a ticket to a Workshop program event this calendar year:
- If you are looking to find constituents who attended multiple events, we recommend using Event Start Date to help determine this. In the left column, highlight Program Events beneath Sales Order Item Ticket and drag Start Date to Results fields to display.
- IMPORTANT NOTE: We do not recommend using query's COUNT function on this field if it is possible that a patron may have purchased multiple price types for one event (for example, an Adult ticket and a Child ticket). Altru will count this as two separate items with the count function (the program for the Adult and the program for the Child ticket), so we recommend synthesizing the information outside of Altru.
- (Optional) If your program events have different names, you can also drag Name from the middle column to Results fields to display as well.
- Next in order to synthesize this information and to import the list back into Altru, we also recommend outputting the Constituent's Lookup ID. In the left column, scroll up to the Constituents node at the top. From the middle column, drag Lookup ID to Results fields to display. Here is an image of what your final query might look like:
- Next, save your query and export it to Excel.
- In order to see which constituents came to multiple events, we recommend counting the date field in a simple pivot table:
- In Excel, highlight your data and on the Insert Tab, click Insert Pivot Table.
- The Create PivotTable window will appear. Under Select a table or range, confirm that Excel has selected the correct data and under Choose where you want the PivotTable report to be placed, we recommend selecting a New Worksheet. Click OK.
- Drag Lookup ID to Rows to summarize by constituent
- Next, drag Program event start date to Values. Here is an example of how this might look in Excel:
- For more information about creating PivotTables in Excel, see Microsoft's Support Solution here. Note: We provide links to third-party websites in an effort to help you resolve your issue. We are not responsible for the information on third-party websites and we cannot assist with implementing resolutions from these websites.
- If you'd like to import the list of program attendees who have attended multiple program events back into Altru, you can copy the Lookup IDs into a .CSV file and import a selection for use in a mailing process. For instructions, see our related solution here: I need to import a list of constituent names and lookup IDs (includes video demo)
Connect and collaborate with fellow Blackbaud users.