Combine transaction and attendee reports
Combining transaction and attendee reports lets you view your essential data in one place. For example, you can view attendees’ responses to custom questions while analyzing data from a transaction report.
You can easily combine the reports by exporting them to your preferred spreadsheet program and inputting some functions.
Pull the transaction report
First, build and export the transaction report. To do so:
- Go to Transactions and Transaction History
- Select the Campaign filter to choose your specific event campaign
- Apply any filters or add additional columns as needed
- Export the report as a CSV
The transaction and attendee reports must include the Transaction ID column as the common field for comparison. If you’re using Giving Cart, the field will be Order ID on the transaction report.
Pull the attendee report
Next, pull the attendee report from the same campaign:
- Go to Fundraising and Attendees
- Select the Campaign filter to choose the same campaign as the transaction report
- Make sure the Transaction ID column is included
- Add custom questions to the report to view the responses
- Select Columns and Custom Questions
- Search for the questions and mark the checkboxes to add the custom questions
- Drag columns to reorder them or select the X to remove one
- Apply your changes
- Export the report as a CSV
Combine the reports
Now, import the reports into your preferred spreadsheet program. Each report will be its own sheet in the same file. For example, Sheet 1 will be Transaction Report and Sheet 2 will be Attendee Report.
The following instructions use the example of adding one custom question response to the transaction report. To view multiple responses, repeat the steps as needed.
Then, use a combination of the IFERROR and INDEX functions to bring custom question responses from the attendee report into the transaction report (we will also add a FILTER to prevent blank responses). To do so:
- Add a column to the transaction report sheet for the custom question and enter a title in the first row. For example, Company/Business name
- Enter the equation in the second row: =IFERROR(INDEX(FILTER(X, Y, Z),1), "")
- X = the column of custom question responses in the attendee report
- Y = the Transaction ID column in the attendee report and will also include the Transaction/Order ID in the row of the transaction report
- Z = the column of custom question responses again, but filters out any blanks
- IFERROR(..., "") returns a blank if no answer is found. You can set a value or placeholder by adding a word in the quotations.
For example: Here's how the equation will look if column K in the Attendee Report sheet is the column of question responses, column E is the Transaction IDs, B2 is the Transaction ID cell in the first row of the Transaction Report, and we want "n/a" to appear when there is no result.
=IFERROR(INDEX(FILTER('Attendee Report'!K:K, 'Attendee Report'!E:E=$B2, 'Attendee Report'!K:K<>""),1), "n/a")
- Apply the equation to each row of the column
FAQs
Why can’t I add custom question responses directly to the transaction report?
You can ask donors and attendees different custom questions. Questions asked to donors will appear in the transaction report, but questions asked to attendees will only appear in the attendee report.
I don’t use Google Sheets as my spreadsheet program. Can I still use the spreadsheet functions to bring over the fields from the attendee report?
Yes, the functions will work in all spreadsheet programs and the structure of the equation will be the same.