Recommendation
Use a single Zap that: triggers on new Gmail messages (filtered by a label or search), normalizes the lead key (email or phone), does a lookup in a master store (Google Sheets or Airtable), then either updates the existing row or creates a new one. Add a final step that labels/emails the original Gmail to mark it processed. This is no-code, reliable for low-to-medium volume, and easy to maintain.

Why this pattern
Using a deterministic unique key (email or phone) plus a Find-or-Create flow prevents duplicates without needing code. Normalizing the key (lowercasing, trimming, removing plus-tags) avoids near-duplicates. Storing a single master sheet/DB makes downstream reporting simple.

Suggested Zap steps (one Zap, simple):
1) Trigger: Gmail — New Email Matching Search (use a label or search query). Example search: newer_than:30d (subject:lead OR subject:signup) has:nouserlabels
2) Action: Formatter > Extract Email (or use Regex if you need phone). If multiple emails in body, choose first.
3) Action: Formatter > Text > Lowercase + Trim + Remove “+tag” from local-part (optional). This gives your normalized_key.
4) Action: Google Sheets — Lookup Spreadsheet Row (or Airtable: Find Record) using normalized_key. Use exact-match column.
5) Filter: Only continue if Lookup returned no row (to CREATE). Or add a path: If found -> Update Row, If not found -> Create Row.
6a) Action (create): Google Sheets — Create Spreadsheet Row with full lead data.
6b) Action (update): Google Sheets — Update Spreadsheet Row to append/merge new fields or update “last seen”.
7) Action: Gmail — Add Label to thread “Processed” (prevents re-processing). Optionally send notification.

Sample filter logic
- Filter: only continue if Normalized Email Exists (Email != "")
- Filter before create: Lookup result equals "not_found" (Zapier exposes Find failure) or use path logic
- Optional: Only create if Lead Score >= X or UTM present: Filter: {utm_source} Exists AND Score > 5

Storage choices & when to pick them
- Google Sheets: Best-for small teams, low volume (<5k rows, 10k rows or performance matters (requires more setup and possibly paid plan).

Common gotchas for large lists
- Zapier task and rate limits: high-volume leads can burn tasks quickly (check plan limits).
- Race conditions: two emails at once can both pass “not found” and create duplicates — use Find+Update with locking if your provider supports it, or batch import instead.
- Google Sheets lookup slow at scale and prone to broken headers; ensure exact-match column and freeze header row.
- Inconsistent email formats (plus-tags, capital letters) cause missed dedupe — normalize.
- Gmail triggers can fire on thread replies; label immediately to avoid repeats.

Decision criteria (quick)
- Use Google Sheets if budget is low, volume is small, and you need quick setup.
- Use Airtable if you want richer records and views with moderate volume.
- Move to a DB/BigQuery if you exceed Zapier limits or need fast, reliable dedupe at scale.

Practical checklist before you turn it on
- Choose unique key (email or phone) and implement normalization.
- Create master sheet/table with an indexed column for that key.
- Build the Zap steps above; test with 10 sample emails.
- Add a “Processed” Gmail label at the end of the Zap.
- Monitor task usage for 7 days; watch for duplicate edge cases.

If you want, I can sketch the exact Google Sheets column names and sample Formatter rules you should use next.

Compare Zapier and Make