How do I generate a csv report or csv export of CiviEvent participants that includes membership status information?

Question

My organization is running CiviCRM 5.51.4. We have a conference and run registration through CiviEvent. We have separate events for regular paid participants, vendors, students, and complimentary tickets. We have a large membership who are already in CiviCRM and register through a token-containing link in an email we send them, however, non-members can and do register for our conference.

We have tried the following through CiviReport:

1. We are able to run an Event Report that will show us all event attendees but does not show us membership status.
2. We are able to run a Membership Report that will show us membership information for event attendees but doesn't show us attendees who aren't registered.
3. We are able to run a Contact Report that will show us all event attendees and their membership information but it's only available as an HTML or PDF, not CSV.

We have tried the following in an Advanced Search:

1. Displaying Results As = Contacts, selecting the event, and then exporting as CSV. This only allows us to select 'Date of Last Membership Payment' as an export field, which is not necessarily indicative of whether or not the person is a current member.
2. Displaying Results As = Memberships, selecting the event, and then exporting the results as CSV. This gives duplicate lines for people with expired/cancelled memberships etc and it doesn't list nonmember event attendees.

We ended up just using the Search Builder to make a CSV of people whose Membership Status = Empty who attended the event and compared that to the #2 result from CiviReport above.

Is there a way to produce a CSV file of every attendee of an event with a column for membership status?

Answer 1

If you use SearchKit, you could find:

• Contacts
• with (required) Contact Participants (using if Event ID and
  Status)
• with (optional) Contact Membership (conditions as appropriate)

Then on the top right of the results table, you can add columns for the contact, membership and participant to your table and then do Action - Download Spreadsheet. Date formats can be challenging with this, but it should give you everything you want in one table. If you want to reformat anything, you can add a table and that gives you some flexibility to use Smarty on the results.

If some of your contacts have more than one event registration or membership, you might want to use Group By Contact ID and maybe list Field Transformations, so you just have one line per contact.