Case Study 03 · Marketing Analytics
A live analytics system connecting Airtable, Aircall, and Discord into a single automated pipeline syncing call outcomes, free trial follow-ups, and WhatsApp lead data into the CRM every 60 seconds without any manual work from the team.
The Problem
The sales team had leads arriving through multiple channels: WhatsApp, Facebook ads, trial classes, mock tests, ebooks, each tracked differently in Airtable with no unified view of what was converting. Call activity lived in Aircall and was never written back to the CRM. WhatsApp calls weren't tracked anywhere at all.
The obvious fix would have been the WhatsApp Business API. But Meta's API costs money per conversation, requires weeks of business verification, and adds a third-party platform dependency. The team was already doing something simpler: callers were posting lead details into a Discord channel and reacting with emoji to show call status. A thumbs up meant connected. A V flag meant voicemail. An X meant no call. The system existed informally. It just wasn't connected to anything.
The goal was to capture that existing behaviour and wire it directly into the CRM, with no extra steps for the callers and no platform fees.
Before
After
System Architecture
The system is built as three separate Cloudflare Workers, each with a distinct responsibility. They share the same Airtable base but run completely independently. A bug in one doesn't affect the others.
A single Cloudflare Worker that fetches all records from Airtable on page load and renders a full analytics dashboard as HTML. Dark-themed, filterable by lead type and time range, exportable as PDF. No separate frontend hosting needed. The Worker IS the server and the app.
A cron-triggered Worker that queries both Aircall accounts (main + sales2), finds phone calls made after each lead's Lead Added timestamp, classifies them as Done (60s+ with recording) or VM (everything else), and writes the results back to Airtable. Processes 15 leads per run to stay under Cloudflare's free-tier subrequest limit.
A cron Worker that polls two Discord server channels once per minute and translates emoji reactions directly into Airtable field updates. The WhatsApp leads channel auto-creates Sales Sheet records from message data (phone, caller, year level) and updates call outcomes in All Leads. The Zapier updates channel syncs Free Trial follow-up status. Uses a bot token, fully Discord ToS compliant, zero ban risk.
The Discord emoji system is the team's lightweight status protocol. Callers drop a lead into the channel after a WhatsApp call and react with a single emoji: thumbs up for connected, V flag for voicemail, X for no call, B for booked. One tap communicates the outcome to the whole team and, via the Worker, writes it straight into the CRM. No form to fill in, no tab to switch to, no extra step. The Worker just listens to what they were already doing.
Dashboard Sections
The dashboard was designed to answer the founder's specific questions from a voice note sent early in the project. Each section maps directly to a business question.
Lead conversion
Conversion % by Lead Type, by specific offer (trial subject, mock test type), by enrolment term, and by Child's Year cohort. Minimum lead threshold filters out noise.
Speed to lead
10-bucket distribution from under 1 minute to 7+ days. WhatsApp-only contacts counted separately from Aircall contacts. "Never called" flagged distinctly from "deliberately skipped."
Agent performance
Per-agent table: leads handled, total dials, connected calls, pickup %, paid conversions. Shows which closer is most efficient, not just most active.
Danger list
Leads with 3+ attempts and zero connections, still unpaid. The key founder ask: leads being worked hardest with the least result. Top 30 shown with full contact and history.
VM follow-up adherence
For leads where the first call went to VM: how many got a 2nd attempt? A 3rd? Eventually connected? Breaks down by lead type to surface where follow-up is falling off.
Free trial conversion
Dedicated section for trial class leads with follow-up status. Counts only leads where a follow-up was logged (blanks excluded (trial may not have happened yet)).
Technical Challenges
Several non-obvious problems had to be solved along the way.
Each Worker invocation is capped at 50 outbound HTTP requests on the free plan. With 500 leads × 2 Aircall lookups + PATCH writes, the initial sync design hit this immediately. Solution: batch 15 leads per cron run, sort by oldest-synced-first, and let the cron chew through the backlog over several hours.
The WA logger's phone lookup used Airtable's filterByFormula, which evaluates every formula field on every row before returning results. Broken formula fields on the Sales Sheet (returning #ERROR!) made every query take 25+ seconds. Fix: skip filterByFormula entirely and paginate through the table fetching only the Number field, match locally in JS. Result: under 2 seconds.
The original plan was to poll the Discord group DM with a user token. Research into 2026 enforcement revealed Discord had actively cracked down on DiscordChatExporter users with warnings, logouts, and bans for patterns safe for years prior. The fix was migrating the workflow to a Discord server channel and using a proper bot token. Zero ToS risk, real-time updates, and the team's emoji workflow stayed identical.
Discord's API shows what emoji are on a message and how many, but not when each was added. This broke the "First Call Time = when the closer actually called" requirement. Fix: record the time the Worker first detects the reaction (within 60 seconds of it being added), which is accurate enough for sales reporting purposes. A 24-hour lookback window catches any reactions added to older messages.
Two Discord channels needed completely different behaviour. The WhatsApp leads channel parses caller mentions, phone numbers, and year levels from message text and creates or updates Sales Sheet records. The Zapier updates channel filters to specific message types and only updates a follow-up field. Both run inside one Worker with per-channel config objects, sharing the same phone normalisation and Airtable write logic.
Technical Stack
Every component runs on free-tier or existing infrastructure. No servers, no databases, no monthly bills beyond Airtable and Aircall which the business already paid for.
| Component | Technology | Purpose | Cost |
|---|---|---|---|
| Dashboard | Cloudflare Worker (HTML-serving) | Fetches Airtable + renders full analytics UI | Free |
| Sync Engine | Cloudflare Worker + Cron Trigger | Pulls Aircall data every 15 min, writes to Airtable | Free |
| Discord Sync | Cloudflare Worker + KV Storage + Bot Token | Polls 2 Discord channels every minute, writes emoji outcomes and lead data to Airtable | Free |
| CRM / Database | Airtable REST API | Source of truth for leads, call fields, outcomes | Existing |
| Call Data | Aircall REST API (2 accounts) | Call history, durations, recordings, agent info | Existing |
| Secrets Management | Cloudflare Worker Secrets | Stores API tokens, never exposed in code or GitHub | Free |
Result
The sales team went from no visibility into which lead sources converted, to a live dashboard updating every 60 seconds with call-level detail per agent, per lead type, per enrolment term. Emoji reactions in Discord (a behaviour the team was already doing) now write directly to Airtable without anyone changing how they work.
The Discord sync was the real unlock. A bot watches two channels: WhatsApp leads get auto-created in the Sales Sheet with caller, year level, and status set in one pass; Free Trial follow-ups reflect in the CRM the moment someone reacts. The Aircall sync handles phone call data on a 15-minute cadence alongside it.
The entire system (three Workers,, a cron sync, a Discord bot integration, and a full analytics dashboard) was built by someone with a marketing background and no traditional programming experience. Every line of code was generated through Claude, directed by a clear understanding of the business problem and the data architecture needed to solve it.