AnalitikMenengah9 menit baca

Cara Melacak Regresi Tahap di Sales Pipeline CRM

Deteksi deal pipeline yang mundur, tandai setiap regresi di Google Sheets, dan buat ringkasan mingguan yang bisa ditindaklanjuti sales director Anda.

Panduan ini menunjukkan cara mendeteksi regresi tahap di Google Sheets menggunakan ekspor riwayat tahap dari CRM Anda, menandai setiap pergerakan mundur dengan formula, dan merangkumnya menjadi dashboard yang bisa dibaca sales director Anda saat standup hari Senin. Ketika sebuah deal mundur dari Proposal kembali ke Discovery, itu adalah sinyal yang perlu ditindaklanjuti, dan sebagian besar dashboard CRM tidak akan menampilkannya untuk Anda.

Yang Anda Perlukan

Ekspor riwayat tahap dari CRM (HubSpot Pipeline Activity, Salesforce OpportunityFieldHistory, atau yang setara) dengan kolom minimal: Deal ID, Stage Name, Stage Changed Date, dan kolom Rep/Owner
Google Sheets dengan data ekspor tersebut sudah dimuat, biasanya 5.000 hingga 80.000 baris tergantung seberapa panjang riwayat Anda dan seberapa aktif tim Anda
Familiar dengan VLOOKUP, QUERY, dan pengurutan rentang secara manual
Daftar tahap pipeline yang sudah didefinisikan dalam urutan yang benar (jika sales rep menggunakan lima nama berbeda untuk "Discovery," perbaiki itu terlebih dahulu)

Panduan Langkah demi Langkah

Ekspor Data yang Tepat dari CRM Anda

Laporan deal CRM standar memberikan satu baris per deal, menampilkan posisi setiap deal saat ini. Itu tidak berguna di sini. Yang Anda butuhkan adalah satu baris per perubahan tahap, yaitu log riwayat yang menunjukkan setiap kali deal bergerak, ke arah mana, dan kapan. Keduanya adalah ekspor yang sangat berbeda, dan perlu dipastikan bahwa Anda memiliki yang tepat sebelum mulai membangun apapun.

Di HubSpot, ini ada di Reports > Sales > Deal Stage History. Di Salesforce, query objek OpportunityFieldHistory yang difilter ke Field = 'StageName'. Di Pipedrive, ekspor "Pipeline change log" di bawah Reports. Setiap CRM besar memiliki data ini, tetapi jalur untuk mengaksesnya berbeda-beda.

Unduh sebagai CSV dengan kolom minimal berikut: deal ID, stage name, tanggal perubahan tahap, dan deal owner
Sertakan nilai deal jika tersedia, karena regresi pada deal senilai Rp 2,7 miliar adalah diskusi yang berbeda dibandingkan regresi pada deal senilai Rp 45 juta
Ekspor minimal 6 bulan riwayat untuk analisis tren; satu minggu saja hampir tidak memberikan informasi tentang pola apapun
Perkirakan 15.000 hingga 60.000 baris untuk tim dengan 10 atau lebih sales rep dan data 6 bulan

Pro Tip

Jika ekspor Anda memberikan kolom "From Stage" dan "To Stage" secara terpisah, gunakan format tersebut. Ini akan melewati dua langkah di bawah. Jika Anda hanya mendapatkan tahap saat ini per event, Anda akan menghitung tahap sebelumnya sendiri di Langkah 4.

Audit dan Bersihkan Nama Tahap

Sebelum membangun apapun, buat pivot kolom Stage Anda dan periksa setiap nilai yang berbeda. Di sinilah Anda akan menemukan bahwa "Demo," "Product Demo," dan "Demo/Presentation" sebenarnya adalah tahap yang sama tetapi dimasukkan secara berbeda oleh sales rep yang berbeda, dan sekarang setiap formula perbandingan akan memperlakukannya sebagai tiga tahap terpisah sehingga melewatkan dua pertiga regresi Anda.

Buat sheet pembersihan dua kolom dengan "Raw Name" di kolom A dan "Canonical Name" di kolom B. Kemudian tambahkan kolom pembersihan di sebelah data mentah Anda:

=IFERROR(VLOOKUP(TRIM(C2), Cleanup!$A:$B, 2, FALSE), C2) - IFERROR akan meneruskan nama apapun yang sudah cocok dengan benar
TRIM adalah keharusan; ekspor CRM secara rutin membawa spasi di awal atau akhir yang tidak terlihat di sel tetapi akan merusak setiap pencocokan tepat
Gunakan =UNIQUE(C2:C) pada kolom kosong untuk menarik semua nama tahap yang berbeda sebelum membangun tabel pembersihan
Setelah kolom pembersihan terlihat benar, salin dan tempelkan dengan Paste Special > Values Only ke kolom Stage Anda, lalu hapus kolom pembantu

Buat Tabel Pencarian Urutan Tahap

Regresi tahap hanya masuk akal setelah Anda mendefinisikan apa arti "maju" secara numerik. Buat sheet baru bernama StageOrder dengan dua kolom: Stage dan Order. Daftarkan setiap nama tahap kanonik dan berikan masing-masing sebuah bilangan bulat. Arah angka-angka inilah yang akan diuji oleh logika regresi Anda.

Stage	Order
Prospecting	1
Qualification	2
Discovery	3
Demo	4
Proposal	5
Negotiation	6
Closed Won	7
Closed Lost	99

Closed Lost mendapat nilai 99, bukan 8. Menetapkannya angka 8 akan menandai deal yang bergerak dari Closed Lost kembali ke Negotiation sebagai regresi, padahal itu sebenarnya adalah re-open, yaitu hal yang berbeda dan perlu dilacak secara terpisah.

Gunakan bilangan bulat saja, tanpa desimal, agar perbandingan tidak ambigu
Jika pipeline Anda memiliki jalur bercabang (misalnya, "Technical Evaluation" dapat berjalan bersamaan dengan "Proposal"), berikan nomor urut yang sama untuk tahap paralel dan putuskan bersama tim apakah pergerakan lintas jalur dihitung sebagai regresi
Tambahkan kolom "Status" ke StageOrder (Active/Deprecated) untuk menangani tahap yang diganti namanya di tengah tahun tanpa kehilangan data historis
Perbarui tabel ini setiap kali admin CRM menambahkan tahap baru. Tahap yang tidak ada akan menghasilkan error VLOOKUP yang ditangkap oleh IFERROR, yang menandakan ada sesuatu yang perlu diperbaiki

Pro Tip

Kunci sheet StageOrder agar sales rep atau admin CRM tidak secara tidak sengaja mengedit angka di tengah analisis. Proteksi melalui Data > Protect sheets and ranges.

Urutkan Data dan Tambahkan Nomor Urut Tahap

Inilah titik tumpu dari seluruh proses ini. Anda perlu mengurutkan baris berdasarkan Deal_ID secara menaik, kemudian Changed_Date secara menaik. Ini menempatkan event setiap deal dalam urutan kronologis, sehingga Anda bisa membandingkan setiap baris dengan baris sebelumnya dalam deal yang sama. Jika pengurutan salah, setiap tanda regresi akan menjadi tidak valid.

Di Google Sheets: Data > Sort range > Sort by Deal_ID (A-Z), kemudian tambahkan level pengurutan kedua untuk Changed_Date (A-Z). Pada 40.000 baris, ini membutuhkan sekitar 10-15 detik.

Sekarang tambahkan tiga kolom pembantu ke data Anda:

Kolom G (Stage_Order) - urutan numerik untuk tahap setiap baris:

=IFERROR(VLOOKUP(C2, StageOrder!$A:$B, 2, FALSE), 0)

Kolom H (Prev_Stage_Name) - nama tahap dari baris di atasnya, tetapi hanya jika termasuk dalam deal yang sama:

=IF(A2=A1, C1, "")

Kolom I (Prev_Stage_Order) - pengecekan deal yang sama untuk urutan numerik:

=IF(A2=A1, G1, "")

Seret ketiga formula tersebut dari baris 2 hingga baris data terakhir Anda
Baris di mana Deal_ID berubah (event pertama untuk deal baru) akan mengembalikan nilai kosong di H dan I, yang merupakan hasil yang benar karena tidak ada tahap sebelumnya untuk dibandingkan
Setiap baris di mana G mengembalikan 0 berarti nama tahap tidak dikenali; filter untuk nilai nol dan periksa tabel pembersihan Anda dari Langkah 2

Pro Tip

Pada 50.000 baris atau lebih, kolom pembantu ini membutuhkan 20-40 detik untuk menghitung ulang setiap kali ada perubahan. Setelah pengaturan awal, salin kolom G hingga I dan tempelkan dengan Paste Special > Values Only untuk membekukannya. Jalankan ulang setiap minggu saat Anda memuat data baru.

Tandai Event Regresi

Dengan data yang sudah diurutkan dan nomor urut tahap yang tersedia, tanda regresi bergantung pada dua perbandingan: apakah ada tahap sebelumnya untuk dibandingkan (artinya kolom I tidak kosong), dan apakah nomor urut saat ini lebih rendah dari sebelumnya? Nomor urut yang lebih rendah berarti deal bergerak mundur.

Kolom J (Regression_Flag):

=IF(I2="", "First Entry", IF(G2<I2, "Regression", IF(G2=I2, "No Change", "Forward")))

Kolom K (Regression_Path) - transisi tahap yang tepat, mudah dibaca untuk tabel direktur:

=IF(J2="Regression", H2&" → "&C2, "")

Ini menghasilkan entri seperti "Proposal → Discovery" dan "Negotiation → Demo", yaitu transisi yang ingin ditelusuri lebih lanjut oleh VP of Sales Anda.

Filter kolom J untuk "Regression" dan periksa 10-15 baris secara acak sebelum mempercayai hasilnya pada skala penuh
Entri "No Change" muncul ketika sebuah deal disimpan di CRM tanpa perubahan tahap (umum terjadi saat sales rep mengedit field lain seperti tanggal penutupan atau nilai deal); ini adalah noise, bukan regresi
Perhatikan deal yang melewati tahap yang sama dua kali. Ini muncul sebagai urutan "Forward" kemudian "No Change" kemudian "Regression", yang sering menandakan masalah entri data yang perlu dilaporkan ke admin CRM
Baris "First Entry" secara otomatis dikecualikan dari analisis karena kolom I kosong; tidak diperlukan IFERROR di query downstream Anda

Buat Ringkasan Regresi dengan QUERY

Dengan setiap regresi yang sudah ditandai di kolom J, agregasi yang penting untuk pelaporan operasional adalah: sales rep mana yang memiliki regresi terbanyak, transisi tahap mana yang paling sering terjadi, dan apakah tren bulanan membaik atau memburuk. QUERY menangani lebih dari 80.000 baris dalam waktu kurang dari 2 detik untuk agregasi ini. Jangan gunakan COUNTIFS di sini karena akan melambat di atas 30.000 baris pada sheet dengan kalkulasi lain yang berjalan.

Buat sheet bernama Regression_Summary. Mulai dengan tiga query berikut:

Regresi per sales rep:

=QUERY(Data!A:K, "SELECT E, COUNT(A) WHERE J='Regression' GROUP BY E ORDER BY COUNT(A) DESC LABEL E 'Rep', COUNT(A) 'Regression Count'", 1)

Jalur regresi yang paling umum:

=QUERY(Data!A:K, "SELECT K, COUNT(A) WHERE J='Regression' GROUP BY K ORDER BY COUNT(A) DESC LIMIT 10 LABEL K 'Regression Path', COUNT(A) 'Count'", 1)

Tren bulanan:

=QUERY(Data!A:K, "SELECT YEAR(D), MONTH(D), COUNT(A) WHERE J='Regression' GROUP BY YEAR(D), MONTH(D) ORDER BY YEAR(D) DESC, MONTH(D) DESC LABEL YEAR(D) 'Year', MONTH(D) 'Month', COUNT(A) 'Regressions'", 1)

Ganti Data!A:K dengan nama sheet dan tab aktual Anda
Fungsi tanggal QUERY seperti YEAR() dan MONTH() memerlukan kolom tanggal Anda berupa nilai tanggal Google Sheets yang sebenarnya, bukan string teks; jika tanggal diimpor sebagai teks (umum terjadi dari ekspor Salesforce), gunakan =DATEVALUE(D2) untuk mengonversinya sebelum menjalankan query ini
Tambahkan kolom tingkat regresi di sebelah tabel sales rep. Regresi dibagi total event tahap per sales rep memberikan gambaran yang lebih jujur dibandingkan jumlah mentah (sales rep dengan 200 deal dan 20 regresi memiliki tingkat 10% yang sama dengan yang memiliki 30 deal dan 3 regresi, tetapi jumlah mentahnya terlihat sangat berbeda)
Jika kolom Changed_Date Anda memiliki format campuran seperti "2024-01-15" dan "1/15/24" dan "15 Jan 2024" yang berdampingan dalam kolom yang sama (hal ini terjadi ketika ekspor berasal dari beberapa region CRM), Anda perlu melakukan normalisasi tanggal sebelum QUERY dapat memfilter berdasarkan tanggal sama sekali

Pro Tip

Format tanggal campuran adalah masalah pembersihan tersendiri. Kombinasi IFERROR, DATEVALUE, dan REGEXEXTRACT dapat mem-parsing sebagian besar format, tetapi siapkan waktu 30-60 menit pertama kali Anda menghadapi kolom dengan campuran format yang parah.

Buat Tab Dashboard Direktur

Sheet ringkasan digunakan untuk analisis. Tab dashboard adalah yang diproyeksikan pada Senin pagi. Batasi hingga 3 panel: angka utama, rincian per sales rep, dan jalur regresi teratas. Apapun yang melebihi itu akan membuat direktur Anda berhenti melihatnya.

Dashboard yang diperbarui secara otomatis memerlukan formula QUERY untuk menarik dari data langsung, jadi jangan bekukan nilai di sini seperti yang Anda lakukan dengan kolom pembantu di Langkah 4.

Panel 1 (angka utama):** Dua hitungan QUERY dengan batas tanggal, satu untuk minggu ini dan satu untuk minggu lalu, ditambah sel pengurangan sederhana yang menampilkan selisihnya. Pemformatan bersyarat merah jika regresi meningkat, hijau jika berkurang.
Panel 2 (tabel sales rep kuartal ini):** Gunakan QUERY sales rep dari Langkah 6, difilter ke tanggal awal kuartal saat ini menggunakan DATE(YEAR(TODAY()), MONTH(TODAY())-MOD(MONTH(TODAY())-1, 3), 1) sebagai batas bawah. Batasi hingga 5 baris dengan LIMIT 5 agar tabel tetap berukuran tetap.
Panel 3 (jalur regresi teratas):** QUERY jalur yang dibatasi hingga 5 teratas, dengan catatan tentang persentase total regresi yang diwakili setiap jalur. Kolom persentase ini dihitung secara manual tetapi layak ditambahkan.
Tambahkan sel "Last Refreshed" dengan =TEXT(NOW(), "MMM D, YYYY") agar siapapun yang membuka file mengetahui apakah data masih segar atau sudah dua minggu tidak diperbarui
Kunci tab dashboard dari pengeditan melalui Data > Protect sheets and ranges. Satu penekanan tombol yang tidak disengaja pada sel yang salah dapat merusak formula QUERY dan tidak akan jelas mengapa hingga seseorang menyadari angka-angka berhenti diperbarui

Kesimpulan

Yang telah Anda bangun adalah lapisan deteksi regresi yang bekerja pada ekspor CRM apapun: pemetaan urutan tahap, perbandingan baris per baris yang dijaga oleh pemeriksaan Deal_ID, dan agregasi QUERY yang dapat menangani lebih dari 80.000 baris tanpa melambat. Dashboard direktur memberikan jawaban yang dapat dipertahankan untuk pertanyaan "apakah kesehatan pipeline membaik?" alih-alih hanya mengangkat bahu dan berjanji untuk menyelidikinya.

Pertanyaan berikutnya yang paling sering ditanyakan tim setelah menjalankan ini selama sebulan adalah apakah mereka bisa mengotomatisasi pembaruan data mingguan. Di sinilah proses manual mencapai batasnya karena logika deteksinya sudah solid, tetapi seseorang masih harus mengunduh, membersihkan, mengurutkan, dan memuat ulang ekspor setiap minggu. Coba ModelMonkey gratis selama 14 hari, tersedia untuk Google Sheets dan Excel.

Pertanyaan Umum

Apa perbedaan antara regresi tahap dan re-open deal?

Regresi tahap adalah deal yang bergerak mundur dalam pipeline yang aktif (dari Proposal ke Discovery). Re-open adalah deal yang ditandai Closed Lost yang bergerak kembali ke tahap aktif manapun. Menetapkan nilai 99 untuk Closed Lost di tabel StageOrder mencegah re-open muncul sebagai regresi, karena setiap tahap aktif (urutan 1-6) secara numerik lebih rendah dari 99, yang dibaca oleh formula sebagai "Forward" bukan "Regression." Lacak re-open secara terpisah dengan memfilter baris di mana Prev_Stage_Name sama dengan "Closed Lost."

CRM saya hanya mengekspor tahap deal saat ini, bukan riwayat tahap. Apakah saya masih bisa mendeteksi regresi?

Ya, tetapi Anda memerlukan 2 snapshot yang diambil pada waktu yang berbeda. Ekspor daftar deal lengkap minggu ini dan lagi minggu depan. VLOOKUP tahap minggu lalu terhadap ekspor minggu ini berdasarkan Deal ID. Di mana urutan tahap saat ini lebih rendah dari urutan snapshot sebelumnya, itulah regresi. Kelemahannya adalah Anda hanya akan menangkap regresi yang terjadi di antara dua tanggal ekspor Anda, dan beberapa pergerakan mundur dalam minggu yang sama akan tergabung menjadi satu sinyal.

Bagaimana formula menangani deal yang melompati tahap ke depan dan kemudian regresi?

Perbandingan baris per baris di Langkah 5 menangani ini dengan benar karena membandingkan setiap event dengan event yang langsung mendahuluinya untuk deal tersebut, bukan dengan event pertama asli. Deal yang melewati Prospecting, kemudian Demo, kemudian Proposal, kemudian kembali ke Discovery akan menandai pergerakan terakhir sebagai regresi dari Proposal (urutan 5) ke Discovery (urutan 3), yang memang tepat.

Mengapa QUERY dan bukan COUNTIFS untuk tabel ringkasan?

Pada 5.000 baris, COUNTIFS masih baik. Di atas 30.000 baris, COUNTIFS dengan beberapa kriteria mengevaluasi setiap kombinasi sel pada setiap penghitungan ulang. Pada sheet dengan formula lain yang berjalan, ini mendorong waktu penghitungan ulang melewati 60 detik, kadang jauh lebih lama. QUERY menggunakan mesin mirip SQL yang mengagregasi jauh lebih cepat. Rincian per sales rep pada 80.000 baris berjalan dalam waktu kurang dari 3 detik. Jika sheet Anda mulai lambat, pendekatan COUNTIFS biasanya menjadi penyebabnya.

Apa yang terjadi ketika admin CRM menambahkan tahap pipeline baru?

Tambahkan tahap baru dan nomor urutnya ke tabel StageOrder segera. Semua baris historis dengan nama tahap tersebut sebelum Anda menambahkannya akan mengembalikan 0 dari VLOOKUP (ditangkap oleh IFERROR dan muncul sebagai tanda). Setelah Anda menambahkan baris ke StageOrder, nilai nol tersebut akan terselesaikan dengan benar pada penghitungan ulang berikutnya. Kasus yang lebih sulit adalah ketika sebuah tahap disisipkan di antara dua tahap yang sudah ada, misalnya menambahkan "Technical Evaluation" antara Demo (urutan 4) dan Proposal (urutan 5), karena setiap tahap di atasnya perlu diubah nomornya untuk menjaga urutan relatif yang benar.