Partnerships CRM: Google Sheets Mapping

This page explains exactly how sheet rows map to CRM entities.

Export Row Source

Rows are generated by:

• services/partnerships/exportRows.js

Push writes a flattened row model that combines:

• partner profile fields
• event fields
• involvement fields
• selected timeline/doc summary fields

Identity Columns Used During Pull

Pull/merge tries to resolve records in this order:

1. partner by partnerId when present
2. partner by normalized company/email/contact fallback
3. event by eventId + eventYear
4. event by normalized name/year fallback
5. link by deterministic partner-event key

If critical identity values are missing, row is skipped with a warning.

Header Alias Behavior

Sheet headers are normalized and mapped to canonical keys.

Examples of accepted aliases:

• Sponsorship Tier or tier -> packageTier
• Role or involvementtype -> eventRole
• Amount -> sponsorshipAmount
• partnerTags -> tags

Alias map is defined in googleSheets.js.

Pull Upsert Sequence Per Row

For each row, backend runs:

1. normalize raw values (text, status, tier, amount, dates)
2. upsert partner record
3. upsert or resolve event record
4. upsert partner-event link
5. collect warnings for invalid optional fields

This allows partial success on mixed-quality sheets.

Field Merge Rules

Partner fields

• non-empty sheet values can update CRM fields
• blank values do not blindly wipe existing CRM values
• tags are split, normalized, and deduplicated

Event fields

• event year must remain valid integer range
• date values must be YYYY-MM-DD
• tier labels normalize into tier keys

Link fields

• status normalizes to canonical/custom slug
• amount parses as numeric (non-negative)
• follow-up date validates as YYYY-MM-DD

Status And Tier Normalization

During pull, same normalizers used by API routes are reused:

• status alias -> canonical key
• custom status preserved as normalized slug
• tier labels -> slug keys used by filters/reporting

This is why sheet edits with mixed casing still land cleanly in CRM.

Sheet Formatting Rules After Push

Backend applies readability formatting each push:

• frozen row 1
• grouped header background colors
• column width presets
• status-based cell formatting
• archive/alumni boolean highlights

Goal: keep sheet readable for non-dev operators while preserving machine-friendly columns.

Safe Editing Guidelines For Operators

Share this with anyone editing the synced sheet:

• do not delete identity columns
• do not duplicate the same partner-event pair in multiple rows
• keep dates in YYYY-MM-DD
• keep amount cells numeric
• avoid renaming headers unless alias is documented

When To Use Each Mode

• push: publish CRM state to sheet
• pull: import sheet edits to CRM
• merge: reconcile both directions and normalize output

If unsure, use push first to get a clean baseline.