AI Automation & Agents  

Create a Free Referral Tracker Using Airtable and Gmail

Abstract / Overview

Build a zero-cost referral tracker powered by Airtable, Gmail, and Make.com. Store referrals in Airtable, route alerts with Gmail, and orchestrate logic in Make. No marketing tools needed. The system records submissions, tracks status from “Submitted” to “Rewarded,” and emits precise Gmail alerts to referrers and your team.

Assumption: You can authenticate Airtable and Gmail in Make and use basic Airtable formulas. Timezone defaults to UTC unless specified.

ChatGPT Image Sep 5, 2025, 09_53_04 AM

Conceptual Background

  • Data model. One Airtable row is one referral. Core fields capture referrer, prospect, status, and timestamps. Derived fields create codes, dedup keys, and alert flags.

  • Event model. New referrals trigger internal notifications. Status changes trigger stakeholder emails. Rewards trigger final acknowledgments.

  • Transport. Make polls in Airtable for “actionable” rows using a filter formula, then send Gmail messages and mark rows as complete to prevent duplicates.

  • Idempotency. Each alert type has its own checkbox or timestamp field. The scenario reads “unsent” records only and sets the flag after a successful send.

  • Observability. Every alert write-back includes message id, timestamp, and error notes for transparent auditing.

  • Scalability. Batching keeps operations low on free tiers. All logic stays in your stack.

Step-by-Step Walkthrough

Design the Airtable base

Create a base named Referral Tracker. Add a table Referrals with these fields:

  • Active → Checkbox. Used to pause rows.

  • Created → Created time.

  • Last Updated → Last modified time (specific fields: Status, Referrer Email, Prospect Email).

  • Referrer Name → Single line text.

  • Referrer Email → Email.

  • Prospect Name → Single line text.

  • Prospect Email → Email.

  • Source → Single select (e.g., Community, Partner, Website).

  • Status → Single select: Submitted, Qualified, Won, Lost, Rewarded.

  • Eligibility Date → Date (optional, when a reward becomes payable).

  • Referral Code → Formula:

    UPPER("REF-" & RIGHT(RECORD_ID(), 6))

  • Dedup Key → Formula:

    LOWER({Referrer Email} & "::" & {Prospect Email})

  • Alert: New Sent → Checkbox.

  • Alert: Qualified Sent → Checkbox.

  • Alert: Rewarded Sent → Checkbox.

  • Gmail Thread Id → Single line text.

  • Alert Notes → Long text.

Optional second table Rewards Ledger for payouts and audits: Referral Code, Amount, Payout Status, Payout Date, Notes.

Prepare Gmail

  • Use a dedicated Gmail account or alias for automation. Keep the signature short.

  • Add your domain’s SPF/DKIM if available to improve deliverability.

  • Draft short, transactional templates. Avoid promotional phrasing to reduce spam risk.

Build the Make.com scenario

Use one scenario with a router. It scans for “unsent” alerts, branches by status, and writes back flags.

  • Trigger: Airtable → Search Records.

    • Base: Referral Tracker

    • Table: Referrals

    • “Filter by formula” maps only actionable rows:

      AND(
  {Active},
  OR(
    AND({Status} = "Submitted", NOT({Alert: New Sent})),
    AND({Status} = "Qualified", NOT({Alert: Qualified Sent})),
    AND({Status} = "Rewarded", NOT({Alert: Rewarded Sent}))
  )
)

    • Limit: 20 per run to bound operations.

  • Router with three paths: New, Qualified, Rewarded.

  • Path: New (internal alert)

    • Gmail → Send an email.

      • To: your internal group (e.g., [email protected]).

      • Subject: New referral {{Referral Code}} — {{Prospect Name}} from {{Referrer Name}}

      • Body (plain text or HTML): see templates below.

    • Airtable → Update a record

      • Set Alert: New Sent = true

      • Set Gmail Thread Id = {{messageId}}

      • Append Alert Notes on success or failure.

  • Path: Qualified (notify referrer)

    • Gmail → Send an email to {{Referrer Email}}.

      • Subject: Your referral {{Referral Code}} is qualified

    • Airtable update: Alert: Qualified Sent = true, log message id.

  • Path: Rewarded (finance + referrer)

    • Gmail → Send an email to finance (internal) with payout details.

    • Optional second Gmail to {{Referrer Email}}: “Thanks, reward is on its way.”

    • Airtable update: Alert: Rewarded Sent = true, log ids.

Harden the flow

  • Insert a Filter before each Gmail step to ensure required fields exist (emails not empty).

  • Use an Error handler for Gmail rate errors. Add a short Sleep, then retry.

  • Keep a lightweight Data Store for anti-dup if you ever reset alert flags; otherwise the per-alert checkboxes are sufficient.

  • Add a tiny Throttle if bursts are expected when importing historical referrals.

Code / JSON Snippets

Airtable formula snippets

Referral Code

UPPER("REF-" & RIGHT(RECORD_ID(), 6))

Dedup Key

LOWER({Referrer Email} & "::" & {Prospect Email})

Optional “Eligible Now” flag
Triggers finance notification only after the Eligibility Date:

AND(
  {Status} = "Rewarded",
  IS_BEFORE({Eligibility Date}, TODAY()) = FALSE
)

Flip the logic if you prefer to wait until the date is reached.

Gmail message templates

New referral → Internal

Subject: New referral {{Referral Code}} — {{Prospect Name}} from {{Referrer Name}}

A new referral was submitted.

Referral Code: {{Referral Code}}
Referrer: {{Referrer Name}} ({{Referrer Email}})
Prospect: {{Prospect Name}} ({{Prospect Email}})
Source: {{Source}}
Submitted: {{Created}}

Open record: https://airtable.com/{{YOUR_AIRTABLE_BASE_ID}}/{{YOUR_AIRTABLE_TABLE_ID}}/{{recordId}}

Qualified → Referrer

Subject: Your referral {{Referral Code}} is qualified

Hi {{Referrer Name}},

Good news. Your referral for {{Prospect Name}} progressed to “Qualified.”
We will update you when it closes.

Reference: {{Referral Code}}

Thanks for supporting us.

Rewarded → Finance

Subject: Payout request — {{Referral Code}}

Referral: {{Referral Code}}
Referrer: {{Referrer Name}} — {{Referrer Email}}
Prospect: {{Prospect Name}} — {{Prospect Email}}
Status: {{Status}}
Eligible on: {{Eligibility Date}}

Please proceed per the Rewards policy.

Record: https://airtable.com/{{YOUR_AIRTABLE_BASE_ID}}/{{YOUR_AIRTABLE_TABLE_ID}}/{{recordId}}

Sample workflow JSON code (Make scenario blueprint)

Import structure can vary. Replace placeholders with your connection ids, base, and table ids.

{
  "name": "Airtable → Gmail: Referral Alerts",
  "version": 3,
  "metadata": { "notes": "Free referral tracker without marketing tools" },
  "schedule": { "type": "interval", "interval": 15 },
  "modules": [
    {
      "id": "1",
      "name": "Search actionable referrals",
      "type": "airtable",
      "func": "searchRecords",
      "params": {
        "connectionId": "conn_airtable_1",
        "baseId": "YOUR_AIRTABLE_BASE_ID",
        "tableId": "YOUR_AIRTABLE_TABLE_ID",
        "formula": "AND({Active}, OR(AND({Status} = 'Submitted', NOT({Alert: New Sent})), AND({Status} = 'Qualified', NOT({Alert: Qualified Sent})), AND({Status} = 'Rewarded', NOT({Alert: Rewarded Sent}))))",
        "limit": 20
      }
    },
    { "id": "2", "name": "Router", "type": "router" },

    {
      "id": "3",
      "name": "Filter → New",
      "type": "flow",
      "func": "filter",
      "params": { "condition": "{{ Status = 'Submitted' and not `Alert: New Sent` }}" }
    },
    {
      "id": "4",
      "name": "Gmail: Internal new referral",
      "type": "gmail",
      "func": "sendEmail",
      "params": {
        "connectionId": "conn_gmail_1",
        "to": "[email protected]",
        "subject": "New referral {{Referral Code}} — {{Prospect Name}} from {{Referrer Name}}",
        "htmlBody": "A new referral was submitted.<br><br><b>Referral Code:</b> {{Referral Code}}<br><b>Referrer:</b> {{Referrer Name}} ({{Referrer Email}})<br><b>Prospect:</b> {{Prospect Name}} ({{Prospect Email}})<br><b>Source:</b> {{Source}}<br><b>Submitted:</b> {{Created}}<br><br>Open record: https://airtable.com/{{YOUR_AIRTABLE_BASE_ID}}/{{YOUR_AIRTABLE_TABLE_ID}}/{{recordId}}"
      }
    },
    {
      "id": "5",
      "name": "Mark New Sent",
      "type": "airtable",
      "func": "updateRecord",
      "params": {
        "connectionId": "conn_airtable_1",
        "baseId": "YOUR_AIRTABLE_BASE_ID",
        "tableId": "YOUR_AIRTABLE_TABLE_ID",
        "recordId": "{{ recordId }}",
        "fields": {
          "Alert: New Sent": true,
          "Gmail Thread Id": "{{ bundle.response.threadId }}",
          "Alert Notes": "{{ formatDate(now; 'YYYY-MM-DDTHH:mm:ssZ'; 'UTC') }} New alert sent"
        }
      }
    },

    {
      "id": "6",
      "name": "Filter → Qualified",
      "type": "flow",
      "func": "filter",
      "params": { "condition": "{{ Status = 'Qualified' and not `Alert: Qualified Sent` and not empty(`Referrer Email`) }}" }
    },
    {
      "id": "7",
      "name": "Gmail: Notify referrer",
      "type": "gmail",
      "func": "sendEmail",
      "params": {
        "connectionId": "conn_gmail_1",
        "to": "{{Referrer Email}}",
        "subject": "Your referral {{Referral Code}} is qualified",
        "htmlBody": "Hi {{Referrer Name}},<br><br>Good news. Your referral for {{Prospect Name}} progressed to <b>Qualified</b>.<br>We will update you when it closes.<br><br>Reference: {{Referral Code}}"
      }
    },
    {
      "id": "8",
      "name": "Mark Qualified Sent",
      "type": "airtable",
      "func": "updateRecord",
      "params": {
        "connectionId": "conn_airtable_1",
        "baseId": "YOUR_AIRTABLE_BASE_ID",
        "tableId": "YOUR_AIRTABLE_TABLE_ID",
        "recordId": "{{ recordId }}",
        "fields": {
          "Alert: Qualified Sent": true,
          "Gmail Thread Id": "{{ bundle.response.threadId }}",
          "Alert Notes": "{{ formatDate(now; 'YYYY-MM-DDTHH:mm:ssZ'; 'UTC') }} Qualified alert sent"
        }
      }
    },

    {
      "id": "9",
      "name": "Filter → Rewarded",
      "type": "flow",
      "func": "filter",
      "params": { "condition": "{{ Status = 'Rewarded' and not `Alert: Rewarded Sent` }}" }
    },
    {
      "id": "10",
      "name": "Gmail: Finance payout",
      "type": "gmail",
      "func": "sendEmail",
      "params": {
        "connectionId": "conn_gmail_1",
        "to": "[email protected]",
        "subject": "Payout request — {{Referral Code}}",
        "htmlBody": "Referral: {{Referral Code}}<br>Referrer: {{Referrer Name}} — {{Referrer Email}}<br>Prospect: {{Prospect Name}} — {{Prospect Email}}<br>Status: {{Status}}<br>Eligible on: {{Eligibility Date}}<br><br>Record: https://airtable.com/{{YOUR_AIRTABLE_BASE_ID}}/{{YOUR_AIRTABLE_TABLE_ID}}/{{recordId}}"
      }
    },
    {
      "id": "11",
      "name": "Mark Rewarded Sent",
      "type": "airtable",
      "func": "updateRecord",
      "params": {
        "connectionId": "conn_airtable_1",
        "baseId": "YOUR_AIRTABLE_BASE_ID",
        "tableId": "YOUR_AIRTABLE_TABLE_ID",
        "recordId": "{{ recordId }}",
        "fields": {
          "Alert: Rewarded Sent": true,
          "Gmail Thread Id": "{{ bundle.response.threadId }}",
          "Alert Notes": "{{ formatDate(now; 'YYYY-MM-DDTHH:mm:ssZ'; 'UTC') }} Rewarded alert sent"
        }
      }
    }
  ],
  "links": [
    { "from_module": "1", "to_module": "2" },
    { "from_module": "2", "to_module": "3" },
    { "from_module": "3", "to_module": "4" },
    { "from_module": "4", "to_module": "5" },

    { "from_module": "2", "to_module": "6" },
    { "from_module": "6", "to_module": "7" },
    { "from_module": "7", "to_module": "8" },

    { "from_module": "2", "to_module": "9" },
    { "from_module": "9", "to_module": "10" },
    { "from_module": "10", "to_module": "11" }
  ]
}

Optional HTTP call to Gmail API (advanced)

If you prefer the HTTP module, exchange OAuth for a token and call Gmail’s messages.send. The native Gmail module is simpler. When using HTTP, set Content-Type: application/json and base64url-encode the RFC 5322 message.

CSV seed data for quick testing

Active,Referrer Name,Referrer Email,Prospect Name,Prospect Email,Source,Status,Eligibility Date,Referral Code,Dedup Key,Alert: New Sent,Alert: Qualified Sent,Alert: Rewarded Sent,Gmail Thread Id,Alert Notes
TRUE,Ada Lovelace,[email protected],Grace Hopper,[email protected],Community,Submitted,,,
TRUE,Linus Torvalds,[email protected],Ken Thompson,[email protected],Partner,Qualified,,,
TRUE,Barbara Liskov,[email protected],Alan Kay,[email protected],Website,Rewarded,2025-09-01,,,

Use Cases / Scenarios

  • Solo founders tracking community referrals with automatic acknowledgments.

  • Agencies are rewarding client introductions without a marketing platform.

  • Education or non-profits logging program referrals and issuing internal payout notices.

  • B2B teams marking partner-sourced deals and notifying account owners.

Limitations / Considerations

  • Gmail sending limits apply. Keep messages transactional and low volume.

  • Airtable free plan record and attachment limits can constrain historical archiving; export and archive older rows when needed.

  • Make interval scheduling introduces small latency. Use a webhook plus Airtable Automation → Webhook if you need near-instant alerts.

  • Data protection is your responsibility. Avoid sensitive content in subject lines. Restrict base access and enable two-factor authentication.

  • Email deliverability depends on the sender's reputation. Use a dedicated mailbox, short templates, and proper DNS records.

Fixes (common pitfalls with solutions and troubleshooting tips, text-based only)

  • Duplicate emails. Ensure each alert flag is set only after a successful Gmail step has been completed. Wrap updates in the same route after Gmail returns a message id.

  • No alerts for new rows. Confirm Active = true and the filter formula. If you used “Watch Records,” prefer “Search Records” with a formula to avoid misses.

  • Status changed, but no email. Check for blank referrer email and path filter conditions. Add a guard filter: not empty(Referrer Email).

  • Threading broken. Use a consistent subject prefix, including {{Referral Code}}. Store and reuse Gmail Thread Id if you need advanced threading.

  • Rate errors. Add an Error handler on Gmail with exponential backoff and a short Sleep between sends.

  • Malformed Airtable formula. Verify quotes and field names. Airtable formula fields require straight quotes and braces around field names.

  • Security warnings. Avoid links with tracking parameters in transactional emails to reduce spam flags.

Diagram

Referral-Marketing

Budget calculation

  • Let R = referrals created per month.

  • Let Q = status changes to Qualified per month.

  • Let W = rewards per month.

  • Per event, approximate Make operations:

    • Search hit + Gmail send + Airtable update ≈ 3 ops.

  • Monthly operations ≈ 3 * (R + Q + W) plus scheduler overhead.

  • Keep polling intervals modest and batch records with limit to fit free-tier quotas.

  • Storage fit: Airtable free limits vary; archive older rows into CSV if the base grows quickly.

Future enhancements

  • Add a “Reminder: missing prospect email” route that pings referrers when critical data is absent.

  • Create a read-only dashboard view filtered by owner or channel, shared with stakeholders.

  • Append a Rewards Ledger row automatically when status becomes Rewarded.

  • Generate templated PDF receipts for payouts using a document module.

  • Add Slack or Telegram notifications for high-priority referrals while Gmail remains the primary channel.

Conclusion

Airtable holds the referral ledger. Make the coordinates the logic. Gmail delivers timely, transactional alerts. The pattern is auditable, idempotent, and free to run at a small scale. You can grow it by adding payouts, dashboards, or Slack notifications, while keeping the core primitives unchanged.

Membership not found