Jawaban singkat: bot AI ekstrak data lead dari chat (nama, email, kebutuhan, budget, timeline) → validasi format → cek duplikat → simpan ke Google Sheets CRM ringan → segmentasi otomatis (Hot/Warm/Cold) → notif sales. Semua visual n8n, no-code. Artikel ini arsitektur, prompt ekstrak, dedup logic, dan dashboard lead.
Mengapa Rekap Lead Manual dari WhatsApp Itu Gagal?
- Terlewat: Admin sibuk balas chat → lupa catat lead → lead hilang.
- Tidak konsisten: Admin A catat “Nama: Budi, Email: bud@…” Admin B catat “Budi S – butuh paket pro”.
- Tidak actionable: Data mentah di catatan/WA → tidak bisa filter “lead hari ini”, “lead hot”, “follow-up minggu ini”.
- Tidak terintegrasi: WhatsApp terpisah dari CRM/Excel → double entry, data pecah.
- Tidak ada segmentasi: Semua lead sama → sales call lead cold yang belum siap, lead hot tidak diprioritaskan.
Arsitektur Lead Capture Otomatis (n8n Visual)
AI Lead Extractor Prompt (Node AI – Output JSON)
{
“is_lead”: true/false,
“nama”: “nama lengkap / null”,
“email”: “email@domain.com / null”,
“telepon”: “08xxxxxxxxxx / null”,
“kebutuhan”: “deskripsi singkat kebutuhan / null”,
“produk_minat”: “kode/nama produk / null”,
“budget”: “angka (juta) / range / ‘belum tentu’ / null”,
“timeline”: “minggu ini / bulan ini / bulan depan / ‘belum punya’ / null”,
“source”: “WA_Organic / WA_Ads / WA_Referral / WA_Group / WA_Broadcast”,
“intent”: “INQUIRY / ORDER / DEMO / PRICING / COMPARISON / SUPPORT”,
“confidence”: 0.0-1.0
}
ATURAN:
– is_lead=true hanya kalau ada minimal: nama ATAU email ATAU telepon + kebutuhan/produk_minat
– is_lead=false untuk: FAQ murni, chit-chat, spam, existing customer order (sudah di Order_Masuk)
– Budget: ekstrak angka → “5 juta” → 5, “5-10jt” → “5-10”, “dibawah 1jt” → “<1"
– Timeline: normalisasi → “besok” → “minggu ini”, “bulan depan” → “bulan depan”
– Source: default “WA_Organic” kecuali konteks jelas (klik ads, referral, grup, broadcast)
CONTOH:
User: “Halo, saya Budi, email budi@email.com. Mau tanya Paket Pro untuk tim 10 orang. Budget 5-10jt, mau mulai minggu depan.” → {“is_lead”:true,”nama”:”Budi”,”email”:”budi@email.com”,”telepon”:null,”kebutuhan”:”Paket Pro untuk tim 10 orang”,”produk_minat”:”Paket Pro”,”budget”:”5-10″,”timeline”:”minggu depan”,”source”:”WA_Organic”,”intent”:”INQUIRY”,”confidence”:0.95}
User: “Brp harga Paket A?” → {“is_lead”:false,”nama”:null,”email”:null,”telepon”:null,”kebutuhan”:null,”produk_minat”:”Paket A”,”budget”:null,”timeline”:null,”source”:”WA_Organic”,”intent”:”PRICING”,”confidence”:0.9}
User: “Saya Citra, 08123456789. Butuh konsultasi pajak startup. Kapan bisa call?” → {“is_lead”:true,”nama”:”Citra”,”email”:null,”telepon”:”08123456789″,”kebutuhan”:”Konsultasi pajak startup”,”produk_minat”:”Konsultasi Pajak”,”budget”:null,”timeline”:”minggu ini”,”source”:”WA_Organic”,”intent”:”INQUIRY”,”confidence”:0.9}
Format Google Sheets CRM (Tab “Leads_CRM” – Kolom Wajib)
| Kolom | Contoh | Sumber | Keterangan |
|---|---|---|---|
| Lead_ID | LD-20260115-001 | Auto: LD-YYYYMMDD-NNN | Unique |
| Nomor_WA | 628123456789 | Trigger | Primary key dedup |
| Nama | Budi Santoso | AI Extract | |
| budi@email.com | AI Extract | Validasi format | |
| Telepon | 08123456789 | AI Extract / Nomor_WA | Normalisasi 628xxx |
| Kebutuhan | Paket Pro tim 10 orang | AI Extract | |
| Produk_Minat | Paket Pro | AI Extract | |
| Budget | 5-10 | AI Extract | Juta rupiah |
| Timeline | minggu depan | AI Extract | minggu ini / bulan ini / bulan depan / belum punya |
| Source | WA_Organic | AI Extract | WA_Organic/Ads/Referral/Group/Broadcast |
| Intent | INQUIRY | AI Extract | INQUIRY/ORDER/DEMO/PRICING/COMPARISON/SUPPORT |
| Segment | HOT | Auto Scoring | HOT/WARM/COLD (lihat scoring di bawah) |
| Status | BARU | Default | BARU/DIHUBUNGI/NEGO/CLOSED_WON/CLOSED_LOST |
| Score | 85 | Auto Scoring | 0-100 (lihat rumus) |
| Waktu_Lead_Masuk | 2026-01-15 14:30:00 | Auto Timestamp | |
| Waktu_Update_Terakhir | 2026-01-15 14:30:00 | Auto Timestamp | |
| Assigned_Sales | Admin1 | Manual / Auto Round-robin | |
| Catatan_Sales | Manual | Log call, meeting, proposal | |
| UTM_Source | utm_source=facebook | Jika dari Ads (via link params) | |
| UTM_Medium | utm_medium=cpc | Jika dari Ads | |
| UTM_Campaign | utm_campaign=promo_jan | Jika dari Ads |
Auto Scoring & Segmentasi (Rumus di Sheets / Node Function n8n)
| Kriteria | Bobot | Segment |
|---|---|---|
| Ada Budget (budget ≠ null & ≠ ‘belum tentu’) | +30 | HOT ≥ 70 |
| Ada Timeline (timeline = ‘minggu ini’ / ‘bulan ini’) | +25 | |
| Ada Kebutuhan Jelas (kebutuhan ≠ null & panjang > 10 char) | +20 | |
| Ada Email Valid | +15 | |
| Intent = INQUIRY / ORDER / DEMO | +10 | |
| Ada Telepon Valid | +10 | WARM 40-69 |
| Intent = PRICING / COMPARISON | +15 | |
| Ada Nama | +5 | |
| Hanya FAQ / Support / Chit-chat | 0 | COLD < 40 |
| Tidak ada data kontak valid | -10 |
Segment: Score ≥ 70 = HOT (notif 🔥 URGENT), 40-69 = WARM (notif 🟡), < 40 = COLD (notif 🟢, masuk nurture flow).
Dedup Logic (Cek Duplikat Sebelum Insert)
- Primary Key: Nomor_WA (paling reliable, user tidak ganti WA mudah)
- Secondary Key: Email (jika ada & valid)
- Logic n8n:
- Sheets Read Leads_CRM → Filter: Nomor_WA = current Nomor_WA
- IF (Found): UPDATE baris existing → Update: Kebutuhan (append), Produk_Minat (append), Budget (update jika baru), Timeline (update jika lebih dekat), Intent (update), Score (recalculate), Segment (recalculate), Waktu_Update_Terakhir = NOW, Status = BARU (reset)
- IF (Not Found & Email ada): Filter Email = current Email → IF Found: UPDATE (sama) + tambah Nomor_WA
- IF (Not Found both): INSERT baris baru → Lead_ID auto, Status=BARU, Score calculate, Segment calculate
Nurture Flow Otomatis Berbasis Segment (Cron Jobs)
| Segment | Flow | Frekuensi | Contoh Pesan |
|---|---|---|---|
| HOT | Sales call/WA <1 jam → Proposal → Follow-up 1/3/7 hari | Real-time notif + Cron follow-up | “Kak Budi, Paket Pro untuk tim 10. Saya kirim proposal singkat. Mau call 15 menit hari ini?” |
| WARM | Educational sequence: H+1 (case study), H+3 (demo), H+7 (offer), H+14 (check-in) | Cron H+1, H+3, H+7, H+14 | “Kak, lihat case study tim 10 orang pakai Paket Pro hemat 20 jam/minggu: [Link]. Mau demo?” |
| COLD | Monthly newsletter + Quarterly check-in + Win-back promo | Cron Bulanan + Kuartalan | “Kak, [Brand] punya fitur baru buat tim remote. Mau coba 7 hari gratis? Kode: TRY7” |
Dashboard Lead (Sheets Tab “Leads_Dashboard”)
- Total Lead / Hari, Minggu, Bulan (by Source, by Segment)
- Conversion: Lead → Order (JOIN Leads_CRM → Order_Masuk via Nomor_WA, window 30 hari)
- Conversion Rate by Segment: HOT %, WARM %, COLD %
- Avg Time Lead → Order (hari)
- Lead Velocity: Lead baru / hari (trend 30 hari)
- Sales Performance: Lead assigned, Contacted, Closed Won, Closed Lost per sales
- ROI by Source: Revenue / Lead per Source (WA_Organic, WA_Ads, Referral, dll)
- Stale Leads: Status=BARU/DIHUBUNGI > 7 hari tanpa update → alert sales
Checklist Aktifkan Lead Capture Minggu Ini
- [ ] Setup Sheets Leads_CRM (kolom di atas) + Leads_Dashboard (PIVOT)
- [ ] Setup AI Lead Extractor prompt (copy template di atas)
- [ ] Import workflow: Trigger → Extractor → Validasi → Dedup → Scoring → Sheets Write → Notif
- [ ] Setup Dedup: Primary Nomor_WA, Secondary Email, Update vs Insert logic
- [ ] Setup Auto Scoring: Node Function hitung Score → Segment (HOT/WARM/COLD)
- [ ] Setup Notif Sales Telegram: 🔥 HOT (URGENT), 🟡 WARM, 🟢 COLD
- [ ] Setup Nurture Flow: HOT (call <1 jam), WARM (sequence H+1/3/7/14), COLD (bulanan)
- [ ] Uji: chat lead baru → cek Sheets insert + notif + segment
- [ ] Uji: chat lead existing (nomor sama) → cek update + score recalc
- [ ] Uji: chat FAQ (bukan lead) → cek is_lead=false, tidak masuk Sheets
- [ ] Launch: monitoring 7 hari → cek Lead_Dashboard daily, sales feedback
FAQ: Rekap Data Leads WhatsApp ke Google Sheets
Apakah AI ekstrak lead dari SEMUA chat (termasuk FAQ)?
TIDAK — AI Lead Extractor output `is_lead: false` untuk FAQ murni, chit-chat, spam, existing customer order. Hanya `is_lead: true` yang masuk Sheets. Filter ini di prompt extractor (lihat aturan di atas).
Bagaimana handle lead yang chat berulang (nomor sama, kebutuhan beda)?
Dedup logic: Primary Key = Nomor_WA. Ditemukan existing → UPDATE baris: append kebutuhan/produk baru, update budget/timeline jika lebih spesifik/dekat, recalculate score & segment, reset Status=BARU. Riwayat lead lengkap di satu baris.
Bisakah integrasi ke CRM lain (HubSpot, Pipedrive, Salesforce)?
BISA — n8n punya node HubSpot, Pipedrive, Salesforce, dll. Ganti/ tambah node Sheets Write → CRM Create/Update Contact/Deal. Sheets tetap sebagai backup & dashboard gratis. Template include CRM integration guide.
Bagaimana track ROI per source (Ads vs Organik vs Referral)?
Leads_CRM kolom Source + UTM params. Order_Masuk join via Nomor_WA (window 30 hari). Leads_Dashboard: PIVOT Revenue / Lead count per Source. WA_Ads track via link parameter `?utm_source=facebook&utm_medium=cpc&utm_campaign=promo_jan` → n8n ekstrak UTM dari link pertama user klik.
Template lead capture CRM lengkap sudah jadi di mana?
Ebook + Template n8n — include: AI Lead Extractor prompt, Leads_CRM Sheets 20+ kolom, Dedup logic (Primary/Secondary), Auto Scoring Function, Segmentasi HOT/WARM/COLD, Notif Sales Telegram, Nurture Flow 3 segment (Cron), Leads_Dashboard PIVOT, CRM integration guide (HubSpot/Pipedrive), UTM tracking.
Baca juga: Cara Rekap Data Leads Pembeli dari WhatsApp Langsung ke Google Sheets untuk panduan lengkapnya.
