How to compare event registrations with attendance

Three lists from one join

Attended   =COUNTIF(Checkins[Email key], [@[Email key]]) > 0
No-shows   =FILTER(Registrations[Email], NOT(Registrations[Attended]))
Walk-ins   =FILTER(Checkins[Email], COUNTIF(Registrations[Email key], Checkins[Email key]) = 0)

Webinars: minutes watched

Webinar platforms export one row per join, so people who dropped out and rejoined appear several times. Sum minutes per email before joining: =SUMIFS(Attendees[Minutes], Attendees[Email key], [@[Email key]]), then flag anyone who watched at least half the session.

Attendance and your CRM

Join attendees to CRM leads to update their status and to compare conversion: leads who attended vs leads who registered but didn't. That comparison is often the best evidence of whether events work.

Emails typed on registration forms contain typos. Check no-shows for near matches with walk-ins (same name, similar email) before sending follow-ups.

Clean the emails first

=LOWER(TRIM(SUBSTITUTE([@Email], CHAR(160), "")))

Registration forms and badge scanners export emails with stray spaces, capitals and non-breaking spaces (CHAR(160)) pasted in from other tools. Clean both lists with the same formula before matching, or attendees will show up as no-shows.

Follow-up lists

• Attended: thank-you email with the slides or recording.
• No-shows: the recording and the next date — they were interested enough to register.
• Walk-ins: add to your contact list only if they gave consent at check-in.

Export each list separately rather than one file with a status column, so the right message goes to the right people.

Across several events

Stack every event's registrations and check-ins with an Event column, then count events attended per person. People who registered for three events and attended none are worth removing from future invitations; people who attended every event are your most engaged audience.

Step by step

1. Export registrations with name, email, company, ticket type and registration date.
2. Export check-ins from the badge scanner, door list or webinar platform with email and check-in time.
3. Clean the email on both sides with the formula below and put the result in an Email key column.
4. Add Attended to the registrations table, then build the no-show and walk-in lists.
5. Calculate the attendance rate per ticket type or registration source.

In Excel 365 and Google Sheets, the FILTER formulas above spill the lists directly. In Excel 2019, add Attended as a column, then filter the table on FALSE for no-shows; for walk-ins, add a column in the check-ins table with =COUNTIF(Registrations[Email key], [@[Email key]]) = 0.

Second example: attendance by ticket type

Registered =COUNTIFS(Registrations[Ticket], [@Ticket])
Attended   =COUNTIFS(Registrations[Ticket], [@Ticket], Registrations[Attended], TRUE)
Rate       =[@Attended] / [@Registered]

The overall 58% hides a large gap: paying registrants nearly all came, while fewer than half of free registrants did. For the next event, that's a reason to over-allocate free places or send free registrants an extra reminder.

Troubleshooting

Checking the result

Registered attendees plus no-shows must equal registrations, and registered attendees plus walk-ins must equal the number of distinct cleaned emails in the check-ins. If the event has paid tickets, compare no-shows with your refund list too: a no-show who was refunded shouldn't receive the recording. For combining several events' files before counting, see pulling data from multiple sheets into one, and for the matching technique itself, finding matches between two lists.

Matching names when emails differ

After the email join, a handful of walk-ins are usually registrants who used another address. A second key built from name and company catches most of them:

Name key        =LOWER(TRIM([@[First name]])) & "|" & LOWER(TRIM([@[Last name]])) & "|" & LOWER(TRIM([@Company]))
Possible match? =COUNTIFS(NoShows[Name key], [@[Name key]]) > 0

Add the formula to the walk-ins list and review every TRUE by hand before moving that person from no-show to attended. Don't automate it: two people with the same name at a large company are common enough to cause wrong merges.

Doing it in SQL

For large events or a series of webinars, the three lists come from two joins. This runs in DuckDB and SQLite:

-- No-shows
SELECT r.email FROM registrations r
WHERE NOT EXISTS (SELECT 1 FROM checkins c WHERE LOWER(TRIM(c.email)) = LOWER(TRIM(r.email)));

-- Walk-ins
SELECT DISTINCT LOWER(TRIM(c.email)) FROM checkins c
WHERE NOT EXISTS (SELECT 1 FROM registrations r WHERE LOWER(TRIM(r.email)) = LOWER(TRIM(c.email)));

NOT EXISTS keeps each registrant once even when they checked in several times, which a plain join would not.

Common mistakes

• Exporting check-ins before the event ends, so late arrivals appear as no-shows.
• Counting speakers, staff and exhibitors in the attendance rate.
• Sending the no-show email to people who cancelled their registration — exclude cancelled tickets first.
• Treating partial webinar viewers as full attendees when reporting to sponsors.

Which method when

Whichever you choose, keep the cleaned registration and check-in tables after the event; they're the inputs for comparing events later and for checking how many attendees became customers.

Frequently asked questions

How do I find event no-shows?

Match the registration list to check-ins by email; registrants with no check-in are no-shows.

How do I calculate attendance rate?

Divide the number of registrants who attended by the number registered.

How do I handle webinar attendees who joined several times?

Sum their minutes per email before joining to the registration list.

How do I match attendees who used a different email?

Compare names and companies for unmatched check-ins against unmatched registrations, and review likely pairs by hand.

Should walk-ins be added to my mailing list?

Only if they gave consent at check-in.

How do I count unique attendees across sessions?

Use UNIQUE on the cleaned emails of all check-ins, then COUNTA of the result.

How do I compare attendance by ticket type?

Count registrants and registered attendees per ticket type with COUNTIFS, then divide attended by registered for each.

Should walk-ins be added to my mailing list?

Only if they gave consent at check-in. Otherwise record them for attendance figures but don't add them to marketing lists.

How do I count partial webinar attendance?

Sum minutes per email, divide by the session length, and set a threshold, such as half the session, for counting someone as attended.

Related guides

• How to match Google Ads conversions to CRM deals
• How to match HubSpot contacts to Stripe customers
• How to combine student grades and attendance records
• How to match support tickets to customer accounts
• How to match survey responses to your customer list