Gmail CRM: Push Pipeline Data ke Google Sheets

Jika peluang senilai Rp 1.800.000.000 berpindah dari Discovery ke Proposal lalu Legal, log aktivitas dapat menghitungnya tiga kali. CRM mungkin mencatat pipeline terbuka Rp 63 miliar, sedangkan spreadsheet menunjukkan Rp 87 miliar. Masalahnya biasanya bukan kapasitas Google Sheets, melainkan tingkat data yang digunakan.

Google menyatakan bahwa spreadsheet dapat menampung hingga “10 juta sel” dalam dokumentasi batas ukuran file. Untuk pipeline, kontrol duplikasi dan definisi kolom biasanya lebih penting daripada kapasitas workbook.

Alur Gmail CRM ke Google Sheets

Gunakan alur berikut untuk laporan forecast mingguan atau laporan investor triwulanan:

1. Gmail menyimpan email dan percakapan dengan prospek.
2. CRM yang terhubung dengan Gmail, seperti Streak, Copper, atau NetHunt, mengubah percakapan menjadi deal dengan stage, owner, amount, dan close date.
3. ModelMonkey mengekstrak atau mengklasifikasikan informasi dari catatan deal, misalnya tipe pelanggan, risiko closing, dan kategori industri.
4. Google Sheets menyimpan ekspor mentah, memilih versi terbaru setiap deal, menerapkan aturan forecast, dan merekonsiliasi total dengan CRM.

Nama perusahaan bukan kunci yang aman. Gunakan Deal ID dari CRM. Satu perusahaan dapat memiliki beberapa deal aktif pada waktu yang sama.

Struktur spreadsheet yang bisa langsung disalin

Buat tab berikut:

Kolom CRM_Export

Sinkronkan atau ekspor kolom berikut dari CRM:

Simpan Amount sebagai angka, bukan teks dengan awalan Rp. Format mata uang dapat diterapkan setelahnya. Dengan begitu, SUM, SUMIFS, dan SUMPRODUCT tetap bekerja dengan benar.

Jangan mengubah CRM_Export. Tab ini adalah bukti sumber. Jika sinkronisasi menambahkan baris baru untuk perubahan stage, biarkan semua baris tetap tersimpan.

Langkah 1: Dorong data CRM Gmail ke Google Sheets

Untuk ekspor manual, gunakan CSV dari CRM dan masukkan hasilnya ke CRM_Export. Streak menyediakan panduan ekspor resmi, sedangkan Copper menyediakan panduan ekspor records.

Untuk forecast mingguan, gunakan sinkronisasi terjadwal tanpa kode. Atur agar sinkronisasi:

• mengambil seluruh deal yang aktif;
• mempertahankan Deal ID dan Updated At;
• menambahkan Exported At pada setiap refresh;
• tidak menimpa baris historis;
• mencatat waktu refresh di CRM_Controls!B3.

Jika CRM menyediakan API dan volume data sudah besar, gunakan penarikan delta berdasarkan Updated At. Untuk sebagian besar tim FP&A, ekspor terjadwal sudah cukup selama kontrol berikut dijalankan.

Langkah 2: Buat satu baris terbaru per deal

Urutkan data berdasarkan Updated At dari yang terbaru ke yang terlama. Jika CRM_Export memiliki Deal ID di kolom A dan data sampai kolom I, gunakan formula berikut di Deals_Current!A2:

=ARRAYFORMULA(
  VLOOKUP(
    UNIQUE(FILTER(CRM_Export!A2:A,CRM_Export!A2:A<>"")),
    SORT(CRM_Export!A2:I,7,FALSE),
    {1,2,3,4,5,6,7,8,9},
    FALSE
  )
)

Formula ini mengambil baris pertama untuk setiap Deal ID setelah data diurutkan berdasarkan Updated At terbaru. Jika CRM memiliki 186 deal aktif, Deals_Current seharusnya memiliki 186 baris, bukan 247 baris akibat riwayat perubahan stage.

Langkah 3: Lengkapi kolom final Deals_Current

Gunakan struktur berikut agar setiap formula memiliki posisi yang jelas:

Di J2, ambil flag dari Stage_Map:

=ARRAYFORMULA(
  IF(C2:C="","",
    IFNA(VLOOKUP(C2:C,Stage_Map!A:D,2,FALSE),FALSE)
  )
)

Di K2:

=ARRAYFORMULA(
  IF(C2:C="","",
    IFNA(VLOOKUP(C2:C,Stage_Map!A:D,3,FALSE),"")
  )
)

Di L2:

=ARRAYFORMULA(
  IF(C2:C="","",
    IFNA(VLOOKUP(C2:C,Stage_Map!A:D,4,FALSE),"Unmapped")
  )
)

Di M2:

=ARRAYFORMULA(
  IF(E2:E="","",
    IFERROR(E2:E*K2:K,0)
  )
)

Di N2, dengan waktu refresh terakhir di CRM_Controls!B3:

=ARRAYFORMULA(
  IF(G2:G="","",
    (CRM_Controls!$B$3-G2:G)*24
  )
)

Langkah 4: Pisahkan aturan stage di Stage_Map

Buat tabel berikut:

Aturan forecast berada di Stage_Map, bukan di formula dashboard. Jika sales mengubah label “Proposal” menjadi “Proposal Sent”, Anda cukup memperbarui tabel kontrol tanpa menulis ulang seluruh model.

Google mendeskripsikan QUERY sebagai fungsi untuk “menjalankan kueri atas data menggunakan bahasa kueri Google Visualization API” dalam referensi Google Sheets. Simpan logika ringkasan di formula agar hasilnya dapat diperiksa dan diulang.

Langkah 5: Validasi data sebelum membuat dashboard

Tambahkan formula berikut ke Deals_Current.

Periksa duplikasi pada data mentah

Di CRM_Export!J2:

=ARRAYFORMULA(
  IF(A2:A="","",
    COUNTIF(A2:A,A2:A)
  )
)

Nilai lebih dari 1 menunjukkan bahwa deal memiliki beberapa baris. Hal ini tidak selalu salah karena bisa berupa riwayat perubahan, tetapi harus dipastikan sudah ditangani oleh Deals_Current.

Untuk menandai duplikasi yang tidak diharapkan:

=ARRAYFORMULA(
  IF(A2:A="","",
    IF(COUNTIFS(A2:A,A2:A,G2:G,G2:G)>1,"DUPLICATE","OK")
  )
)

Periksa amount kosong atau tidak valid

Di Deals_Current!P2:

=ARRAYFORMULA(
  IF(A2:A="","",
    IF((E2:E="")+(NOT(ISNUMBER(E2:E))),"AMOUNT ERROR","OK")
  )
)

Periksa deal tanpa stage map

Di Deals_Current!Q2:

=ARRAYFORMULA(
  IF(A2:A="","",
    IF(K2:K="","STAGE UNMAPPED","OK")
  )
)

Periksa refresh yang terlalu lama

Misalnya, data dianggap kedaluwarsa jika lebih dari 48 jam:

=ARRAYFORMULA(
  IF(A2:A="","",
    IF(N2:N>48,"STALE","OK")
  )
)

Gabungkan status validasi

Di Deals_Current!O2:

=ARRAYFORMULA(
  IF(A2:A="","",
    IF(
      (P2:P<>"OK")+(Q2:Q<>"OK")+(R2:R<>"OK"),
      "CHECK",
      "OK"
    )
  )
)

Sesuaikan referensi kolom jika Anda menempatkan pemeriksaan tambahan di lokasi lain.

Langkah 6: Hitung pipeline dan weighted pipeline

Di Pipeline_Dashboard, gunakan formula berikut untuk merangkum pipeline terbuka berdasarkan stage:

=QUERY(
  {Deals_Current!C2:C,Deals_Current!E2:E,Deals_Current!J2:J},
  "select Col1, sum(Col2)
   where Col3 = TRUE
   group by Col1
   label sum(Col2) 'Open Pipeline'",
  0
)

Weighted pipeline dihitung dari kolom yang sudah didefinisikan:

=SUMIFS(
  Deals_Current!M:M,
  Deals_Current!J:J,TRUE,
  Deals_Current!O:O,"OK"
)

Pipeline terbuka dihitung sebagai berikut:

=SUMIFS(
  Deals_Current!E:E,
  Deals_Current!J:J,TRUE,
  Deals_Current!O:O,"OK"
)

Jangan memasukkan Closed Won ke pipeline terbuka. Bookings dan pendapatan aktual sebaiknya berasal dari tabel transaksi atau P&L dengan tanggal transaksi yang jelas, bukan dari total peluang CRM.

Langkah 7: Rekonsiliasi dengan CRM

Masukkan angka pipeline terbuka dari CRM ke CRM_Controls!B2, lalu masukkan jumlah deal aktif ke CRM_Controls!B4.

Rekonsiliasi nilai dalam Rupiah:

=IF(
  ABS(
    SUMIFS(Deals_Current!E:E,Deals_Current!J:J,TRUE)
    -CRM_Controls!$B$2
  )<0.01,
  "TIES",
  "BREAK: "&TEXT(
    SUMIFS(Deals_Current!E:E,Deals_Current!J:J,TRUE)
    -CRM_Controls!$B$2,
    "Rp #,##0"
  )
)

Rekonsiliasi jumlah deal:

=IF(
  COUNTIFS(Deals_Current!J:J,TRUE)
  =CRM_Controls!$B$4,
  "COUNT TIES",
  "COUNT BREAK"
)

Periksa juga perbedaan waktu refresh:

=IF(
  ABS((CRM_Controls!$B$3-CRM_Controls!$B$5)*24)<1,
  "REFRESH ALIGNED",
  "REFRESH MISMATCH"
)

B3 adalah waktu refresh Sheets, sedangkan B5 adalah waktu snapshot CRM. Rekonsiliasi nilai tidak bermakna jika CRM dan Sheets mengambil data pada waktu yang berbeda secara signifikan.

Contoh hasil untuk meeting Senin

Misalkan CRM menunjukkan:

• 186 deal aktif;
• pipeline terbuka Rp 63.000.000.000;
• weighted pipeline Rp 24.000.000.000.

Dashboard harus menunjukkan angka yang sama setelah baris duplikat, amount kosong, stage yang tidak dipetakan, dan data kedaluwarsa dikeluarkan. Jika statusnya TIES, COUNT TIES, dan REFRESH ALIGNED, angka tersebut siap digunakan dalam pembahasan forecast.

Jika pipeline Sheets mencapai Rp 87.000.000.000, jangan langsung memperbaiki angka secara manual. Periksa terlebih dahulu apakah ada:

• Deal ID yang muncul lebih dari sekali;
• deal Closed Won yang masih ditandai sebagai pipeline terbuka;
• amount dalam bentuk teks;
• stage tanpa pasangan di Stage_Map;
• snapshot CRM dan ekspor Sheets dengan timestamp berbeda.

Kapan memakai sinkronisasi tanpa kode atau API?

Gunakan sinkronisasi terjadwal tanpa kode jika forecast diperbarui mingguan, jumlah deal aktif kurang dari sekitar 5.000, dan field utama relatif stabil.

Gunakan API atau warehouse feed jika Anda memerlukan snapshot harian, riwayat stage lengkap, atau ekspor sekitar 80.000 baris yang membuat workbook lambat. Google menjelaskan bahwa kuota Apps Script berlaku berdasarkan “quota limits” pada dokumentasi resmi Apps Script. Namun, memenuhi kuota tidak otomatis membuat model forecast andal. Anda tetap membutuhkan kunci deal, histori terpisah, validasi, dan rekonsiliasi.

Simpan dua dataset:

• Deals_Current untuk forecast saat ini;
• Pipeline_History untuk conversion, aging, dan akurasi forecast.

Jangan mencampurkan histori perubahan dengan tabel current-state yang digunakan dashboard.

FAQ Gmail CRM ke Google Sheets

Apakah Gmail dapat langsung menjadi pipeline forecast?

Tidak sebaiknya. Gmail berisi percakapan dan konteks, sedangkan pipeline membutuhkan field terstruktur seperti Deal ID, stage, amount, owner, dan close date. Gunakan CRM sebagai sumber status deal.

Apakah saya harus mengimpor semua email ke Google Sheets?

Tidak. Impor hasil yang sudah diringkas di CRM. Email mentah terlalu rinci untuk dashboard dan dapat menimbulkan duplikasi jika setiap aktivitas diperlakukan sebagai deal.

Mengapa Deal ID lebih baik daripada nama perusahaan?

Satu perusahaan dapat memiliki beberapa peluang, perpanjangan kontrak, atau proyek berbeda. Deal ID membedakan setiap peluang secara konsisten dan memungkinkan deduplikasi yang dapat diaudit.

Berapa sering pipeline harus disinkronkan?

Untuk forecast mingguan, sinkronisasi harian atau sebelum meeting sudah memadai. Tambahkan timestamp refresh dan gunakan validasi STALE agar data yang terlalu lama tidak masuk ke laporan.

Apa yang harus dilakukan jika statusnya BREAK?

Jangan mengubah total secara manual. Bandingkan populasi deal, filter stage, timestamp refresh, amount kosong, dan Deal ID duplikat antara CRM dan Deals_Current.

Dorong data pipeline Gmail CRM ke Google Sheets dengan kontrol yang dapat diaudit

Alur yang dapat diandalkan adalah Gmail ke CRM, CRM ke CRM_Export, CRM_Export ke Deals_Current, lalu Stage_Map ke dashboard forecast. Pisahkan data mentah dari data current-state, gunakan Deal ID sebagai kunci, dan tampilkan kegagalan validasi dengan status yang jelas.

Dengan struktur ini, pipeline Rp 63 miliar tetap menjadi pipeline, bukan pendapatan yang masuk ke model secara tidak sengaja. Coba ModelMonkey gratis selama 14 hari, tersedia di Google Sheets dan Excel.