Event Queries and Reports

eTapestry Best Practices: With coordinators updating event information, participants and attendees registering, and options changing, another advantage is having this information in one place and accessible online.Now that you have entered your event information in your donors’ journals, you can query on several aspects of the event. If you remained consistent with marking the journal contacts you created, you will be able to query on those who attended, those who accepted the invite but did not attend and those who were invited but never RSVP'd. You will also be able to query on who purchased tickets and donated to your event, since that information is tracked on transactions.

Contacts vs. Transactions in Query Criteria

All event information should be tracked in the journal, because that is where you can keep track of the historical record of each event constituents participated in. Depending on how you have been tracking your event, you could have information on either contacts, transactions, or both. It is important to make sure you are querying on the correct type of journal item because some of your fields might apply to both locations, but the information is stored in only one.

For example, if you want to find any donation from an event, you would query on the Transaction field instead of the Contacts field, since journal entries for donations are considered transactions and not contacts.

Contact Journal Entries: Querying on Events Tracked through Contact Journal Entries

To keep organized, we suggest that you create a new query category to store all event-related queries.

Create new query category for Event queries
  1. From Queries, click Add a New Category.
  2. In the Name field, enter "Event Queries".
  3. Click Save Category. Your category appears in the list.

Be specific when you begin creating queries in this category. You should name them exactly what the query is finding.

Before the Event

To determine how many guests will be attending prior to the event, you can create a new query track how many people have RSVP'd. You can also find all accounts that have been assigned a table number so that you can see who will be seated together.

Track how many people have RSPV'd before your event
  1. From Queries, click Manage Queries.
  2. Next, click Event Queries. The Edit Query Category page appears.
  3. Under Tasks, click New Query.
  4. Under Query Basics, in the Name field, enter "Constituents That RSVP’d to [Name of Event]". The name of the query should be as specific as possible so that you and other users in your organization know what the query is for.
  5. Under Starting Query, select Base - All Constituents - A.
  6. On the Data Return Type field, set the type of results to Journal Entries.
  7. In the Criteria Matching field, select Match each criteria so that all of your criteria will be matched in the results.
  8. Under Criteria, select Defined Fields in the drop down field.
  9. In the list of defined fields, click Contact Methods and select Event Accepted.
  10. Under Criteria, select UDFs – Contact in the drop down field.
  11. In the list of defined fields, click Event Name and select the name of your event.
  12. To view query results, select Preview in the drop down menu under Tasks and click Save And.
Track table assignments
  1. From Queries, click Manage Queries.
  2. Next, click Event Queries. The Edit Query Category page appears.
  3. Under Tasks, click New Query.
  4. Under Query Basics, in the Name field, name the query "Constituents With A Table Number For [Your Event Name]." The name of the query should be as specific as possible so that you and other users in your organization know what the query is for.
  5. Under Starting Query, select Base - All Constituents - A.
  6. On the Data Return Type field, set the type of results to Journal Entries.
  7. In the Criteria Matching field, select Match each criteria so that all of your criteria will be matched in the results.
  8. In the list of defined fields, click Event Name and select the name of your event.
  9. In the list of defined fields, click Table Number and select Field Has Any Value.
  10. To view query results, select Preview in the drop down menu under Tasks and click Save And.

After the Event

After your event, you might wish to track constituents who were invited to your event and see whether or not they attended. Using query, you can see those accounts who attended that were invited but never accepted the invitation; accounts who sent a RSVP, but did not attend; accounts who were invited to the event regardless of whether they ever accepted or attended; and all invitees for your event.

Track who attended the event
  1. From Queries, click Manage Queries.
  2. Next, click Event Queries. The Edit Query Category page appears.
  3. Under Tasks, click New Query.
  4. In the Name field, enter "Constituents That Attended [Name of Event]". The name of the query should be as specific as possible so that you and other users in your organization know what the query is for.
  5. Set the Starting Query to Base - All Constituents - A.
  6. On the Data Return Type field, set the type of results to Journal Entries.
  7. In the Criteria Matching field, select Match each criteria so all of your criteria will be matched in the results.
  8. Under the Criteria, select Defined Fields in the drop down field.
  9. In the list of defined fields, click Contact Methods and select Event Attended.
  10. Under Criteria, select UDFs – Contact in the drop down field.
  11. In the list of defined fields, click Event Name and select the name of your event.
  12. To view query results, select Preview in the drop down menu under Tasks and click Save And.
Track who didn't RSVP to your event
  1. From Queries, click Manage Queries.
  2. Next, click Event Queries. The Edit Query Category page appears.
  3. Under Tasks, click New Compound Query.
  4. In the Name field, enter "Constituents That Did Not RSVP to [Name of Event]." The name of the query should be as specific as possible so that you and other users in your organization know what the query is for.
  5. On the Data Return Type field, set the type of results to Accounts.
  6. Select Subtract under How would you like the results of each query to interact?.
  7. Under Query 1, select Constituents That Were Invited To [Event Name] - A.
  8. Under Query 2, select Constituents That RSVP’d To [Event Name] - A.
  9. To review query results, select Preview in the drop down menu under Tasks and click Save And.
Track who RSVP'd but did not attend the event
  1. From Queries, click Manage Queries.
  2. Next, click Event Queries. The Edit Query Category page appears.
  3. Under Tasks, click New Compound Query.
  4. In the Name field, name the query "Constituents RSVP'd to [Name of Event] But Did Not Attend". The name of the query should be as specific as possible so that you and other users in your organization know what the query is for.
  5. On the Data Return Type field, set the type of results to Accounts.
  6. Under Combine the Queries, select the Subtract icon.
  7. Under Query 1, select Constituents That RSVP’d To [Event Name] - A.
  8. Under Query 2, select Constituents That Attended [Event Name]- A.
  9. To view query results, select Preview in the drop down menu under Tasks and click Save And.
Track all invitees for your event
  1. From Queries, click Manage Queries.
  2. Next, click Event Queries. The Edit Query Category page appears.
  3. Under Tasks, click New Query.
  4. In the Name field, enter "Constituents Invited, Accepted, or Attended [Name of Event]". The name of the query should be as specific as possible so that you and other users in your organization know what the query is for.
  5. Set the Starting Query to Base - All Constituents - A.
  6. On the Data Return Type field, set the type of results to Journal Entries.
  7. In the Criteria Matching field, select Match each criteriaso that all of your criteria will be matched in the results.
  8. Under Criteria, select UDFs – Contact in the drop down field.
  9. In the list of defined fields, click Event Name and select the name of your event.
  10. To view query results, select Preview in the drop down menu under Tasks and click Save And.

Report on Events Tracked through Contact Journal Entries

After you have created queries to track your event, you can create a report to display more information about the journal entries you created.

Create a report on events tracked through journal entries
  1. From Reports, click Manage Reports. The Report Categories page appears.
  2. Next, select the category where you want to store the report.
  3. Under Tasks, click New Report.
  4. In the Name field, enter "Table Number Assignment." The name of the report should be as specific as possible so that you and other users in your organization know what the report is for.
  5. Under Report Columns, select the information to display on the report:
    1. Select User Defined Fields: Journal. In the list of available fields, click Table Number and Number of Guests Attending. Select Table Number in the Group Report By field.
    2. Select Commonly Used Fields. In the list of available fields, click Name and Date.
    3. Select Journal Fields. In the list of available fields, click Contact Subject, Contact Method, and Note.
  6. To run the report, click Save and Run.
  7. Under Query, select the Event Queries category and the Constituents With A Table Number For [Event Name] - JE query.
  8. Under Delivery Options, select Display Results On Screen in the Report Format field.
  9. Click Submit.

Transaction Journal Entries: Query and Report on Events/Ticket Sales Tracked through Transaction Journal Entries

If you sell tickets to events and track attendance through ticket sales, you can easily query and report on who purchased tickets and who gave gifts to that event.

If you sold your tickets through an event DIY form, make sure you are aware of how the settings were configured so that you can accurately query on those submissions.

Ticket Sales and Gifts Given for an Event

You can find total ticket sales for the event by querying on the approach of Tickets and the name of the event on the transaction.You can also find all gifts given to a particular event by querying on the Event approach and the name of the event on the transaction.

Track ticket sales for your event
  1. From Queries, click Manage Queries.
  2. Next, click Event Queries. The Edit Query Category page appears.
  3. Under Tasks, click New Query.
  4. In the Name field, enter "Ticket Purchases for [Event Name]." The name of the query should be as specific as possible so that you and other users in your organization know what the query is for.
  5. Set the Starting Query to Base - All Constituents - A.
  6. On the Data Return Type field, set the type of results to Journal Entries.
  7. In the Criteria Matching field, select Match each criteria so all of your criteria will be matched in the results.
  8. Under Criteria, select Commonly Used Fields in the drop down field. In the list of available fields, click Approaches and select Tickets.
  9. Under Criteria, select UDFs – Transaction in the drop down field. In the list of defined fields, click Event Name and select the name of your event.
  10. To view query results, select Preview in the drop down menu under Tasks and click Save And.
Track gifts given at your event
  1. From Queries, click Manage Queries.
  2. Next, click Event Queries. The Edit Query Category page appears.
  3. Under Tasks, click New Query.
  4. In the Name field, enter "Donations From [Event Name]." The name of the query should be as specific as possible so that you and other users in your organization know what the query is for.
  5. Set the Starting Query to Base - All Constituents - A.
  6. On the Data Return Type field, set the type of results to Journal Entries.
  7. In the Criteria Matching field, select Match each criteria so all of your criteria will be matched in the results.
  8. Under Criteria, select Commonly Used Fields in the drop down field.
  9. Select Approaches from the list of links. In the list of available fields, click Approaches and select Event under Criteria.
  10. Select UDFs – Transaction in the drop down field. In the list of defined fields, click Event Name and select the name of your event under Criteria.
  11. To view query results, select Preview in the drop down menu under Tasks and click Save And.

Report

You can create a report to view information about the donations or ticket sales for the event information your query is pulling.

Create a report to view donations or ticket sales for your event
  1. From Reports, click Manage Reports. The Report Categories page appears.
  2. Next, select the category where you want to store the report.
  3. Under Tasks, click New Report.
  4. In the Name field, enter "Donations from [Event Name]." The name of the report should be as specific as possible so that you and other users in your organization know what the report is for.
  5. Select Account in the Group By field then select Show Group Totals in the drop down below.
  6. Under Report Columns, select the information to display on the report:
    1. Select Commonly Used Fields. In the list of available fields, click Name, Date, Fund, Campaign, Approach, Pledged, and Received.
    2. Select User Defined Fields: Journal. In the list of available fields, click Event Name and any other event-related fields.
  7. To run the report, click Save and Run.
  8. Under Query, select the Event Queries category and the Donations from [Event Name] - JE query.
  9. Under Delivery Options, select Display Results On Screen in the Report Format field.
  10. Click Submit.

Resources

Queries

Custom Reports