alumni_lookup

Event RSVP Converter (Phase 0.5)

Status: Pre-Integration Utility

This is a standalone utility that provides immediate value while the full event check-in integration is in planning. It converts GiveCampus RSVP exports into the format required by the event check-in app (alum-events).

---

Problem Statement

Currently, preparing event attendee data for the check-in app requires:

1. Export RSVP list from GiveCampus (one format)
2. Manual reformatting in Excel/Google Sheets
3. Column mapping and cleanup
4. Import into event check-in app (different format)

This manual process is error-prone and time-consuming for each event.

---

Solution

A web-based converter within Alumni Lookup that:

1. Accepts a GiveCampus RSVP CSV upload
2. Parses and normalizes the data
3. Optionally enriches with Alumni Lookup data (BUID matching)
4. Exports in the event app’s expected format

---

Input Format (GiveCampus RSVP)

Based on actual GiveCampus export files:

Column	Description	Example
Last Name	Guest last name	Jones
First Name	Guest first name	Brody
Guest Email Address	Email	brody.jones@belmont.edu
Guest Affiliations	(often empty)
Event RSVP Status	Attending, Not Attending, etc.	Attending
Checked In?	Yes/No	No
Registration ID	Group/registration identifier	716327
Registrant Last Name	Primary registrant	Jones
Registrant First Name	Primary registrant	Brody
Constituent ID	BQID (C-000000000) or N/A	C-000267547
Event Ticket Type	Ticket type	Attendee
Ticket Category	Category	Individual
Uploaded Offline?	Yes/No	No
RSVP No At	Date if declined	N/A
affiliation_to_belmont	Relationship type	Faculty/Staff, Alum

---

Output Format (Event App)

The event check-in app expects:

Column	Description	Source Mapping
initial_registration_date	Registration timestamp	Generated or default
transaction_id	Transaction ID	N/A
registration_id	Group identifier	Registration ID
guest_constituent_id	BQID	Constituent ID (cleaned)
affiliation_to_belmont	Relationship	affiliation_to_belmont
event_id	Event identifier	User-provided
guest_first_name	First name	First Name
guest_last_name	Last name	Last Name
guest_email_address	Email	Guest Email Address

---

Data Transformations

Constituent ID Cleanup

• Remove N/A values → empty string
• Validate format: C-000000000
• Handle multiple IDs (semicolon-separated in some exports)

Affiliation Normalization

• Alum → Keep as-is
• Faculty/Staff → Keep as-is
• Guest → Keep as-is
• Empty → Guest (default)

RSVP Status Filtering

• Only include rows where Event RSVP Status = Attending
• Skip rows where RSVP No At has a date (declined after RSVP)

Registration Date

• If source has date, use it
• Otherwise, use current date/time

---

Optional Enrichment (Phase 2 Enhancement)

When processing, offer to match against Alumni Lookup database:

1. By BQID: If Constituent ID matches alumni.contact_id
2. By Email: If Guest Email Address matches alumni.pref_email or alumni.bu_email
3. By Name: Fuzzy match on name

This enrichment could:

• Fill in missing BQIDs
• Add BUIDs for internal tracking
• Flag potential duplicates or mismatches

---

User Interface

Location

Settings > Event Converter (or Settings > Import/Export > Event RSVP Converter)

Workflow

1. Upload Page
   • File upload field for CSV
   • Optional: Event ID input field
   • Optional: Event name for reference
   • “Process” button
2. Preview Page
   • Show row count and status
   • Display first 10 rows in preview table
   • Show statistics:
     • Total rows
     • Rows with BQIDs
     • Rows marked as Alum vs Faculty/Staff vs Guest
   • “Download CSV” button
   • “Start Over” link
3. Download
   • Immediate CSV download in event app format
   • Filename: {event_name}_attendees_{date}.csv

---

Access Control

• Requires authenticated user
• No special permissions needed (utility function)
• Future: Could restrict to can_event_manage permission when event integration is complete

---

File Structure

app/
├── controllers/
│   └── settings/
│       └── event_converter_controller.rb
├── services/
│   └── csv/
│       └── event_rsvp_converter.rb
└── views/
    └── settings/
        └── event_converter/
            ├── new.html.erb       # Upload form
            └── preview.html.erb   # Results and download

---

Future Integration Points

This utility is designed to be forward-compatible with the full event integration:

1. Phase 1 (Data Models): Converter logic can be reused by GiveCampusCsvImporter
2. Phase 2 (Import/Export): Column mapping and enrichment patterns transfer directly
3. Contact Model: Field mapping aligns with planned Contact model attributes

The converter service class (Csv::EventRsvpConverter) will eventually be subsumed or extended by the full import service, but the core transformation logic remains valid.

---

Testing

Type	Coverage
Service unit tests	Parsing, transformation, edge cases
Controller tests	Upload, preview, download actions
Fixture	Sample GiveCampus CSV for testing

---

Implementation Estimate

• Service: 2-3 hours
• Controller/Views: 2-3 hours
• Tests: 1-2 hours
• Total: ~1 day of development

This is intentionally lightweight to provide immediate value while the larger integration is planned.