Cara Membuat Cohort Analysis dengan SQL untuk Mengukur Retensi Pelanggan

Cohort Analysis merupakan salah satu teknik analisis data yang membantu Data Analyst memahami perilaku pelanggan dari waktu ke waktu. Analisis ini dapat dilakukan menggunakan SQL secara langsung dari data transaksi untuk mengetahui berapa banyak pelanggan yang tetap aktif setelah melakukan pembelian pertama.

Cohort Analysis adalah metode analisis yang mengelompokkan pelanggan berdasarkan karakteristik atau periode tertentu, kemudian mengamati perilaku kelompok tersebut dari waktu ke waktu. Dalam analisis pelanggan, cohort biasanya ditentukan berdasarkan waktu pertama kali pelanggan melakukan transaksi. Misalnya, pelanggan yang pertama kali membeli produk pada Januari 2026 akan masuk cohort Januari 2026 juga.

Setelah cohort terbentuk, aktivitas pelanggan tersebut dapat dibandingkan pada bulan-bulan berikutnya. Dari sini, perusahaan dapat mengetahui tingkat customer retention atau kemampuan mempertahankan pelanggan. Simak lebih lanjut terkait tutorial membuat cohort analysis menggunakan SQL untuk mengukur retensi pelanggan. Ikuti langkahnya satu per satu, yuk, sahabat DQLab!

1. Menentukan Cohort Pelanggan

Langkah pertama adalah mencari tanggal transaksi pertama setiap pelanggan. Sebagai contoh menggunakan PostgreSQL, kita dapat menggunakan MIN() untuk mendapatkan transaksi pertama.

SELECT

customer_id,

MIN(order_date) AS first_purchase_date

FROM orders

GROUP BY customer_id;

Hasilnya akan menunjukkan kapan masing-masing pelanggan pertama kali melakukan pembelian. Selanjutnya, tanggal tersebut dapat diubah menjadi periode bulan menggunakan DATE_TRUNC():

SELECT

customer_id,

DATE_TRUNC('month', MIN(order_date)) AS cohort_month

FROM orders

GROUP BY customer_id;

Hasilnya dapat berupa:

customer_id	cohort_month
C001	2026-01-01
C002	2026-01-01
C003	2026-02-01

Artinya, C001 dan C002 termasuk Cohort Januari, sedangkan C003 termasuk Cohort Februari.

Baca juga: Bootcamp Data Analyst with Python & SQL

2. Menggabungkan Cohort dengan Data Transaksi

Setelah mengetahui cohort setiap pelanggan, langkah berikutnya adalah menggabungkan informasi tersebut dengan seluruh transaksi pelanggan.

WITH customer_cohort AS (

SELECT

customer_id,

DATE_TRUNC('month', MIN(order_date)) AS cohort_month

FROM orders

GROUP BY customer_id

)

SELECT

o.customer_id,

o.order_date,

c.cohort_month

FROM orders o

JOIN customer_cohort c

ON o.customer_id = c.customer_id;

Sekarang setiap transaksi memiliki informasi mengenai cohort pelanggan. Contohnya:

customer_id	order_date	cohort_month
C001	2026-01-05	2026-01-01
C001	2026-02-10	2026-01-01
C001	2026-03-12	2026-01-01
C002	2026-01-15	2026-01-01
C002	2026-03-20	2026-01-01

3. Menghitung Cohort Index

Cohort Index menunjukkan berapa bulan setelah pelanggan pertama kali melakukan transaksi. Misalnya:

• Bulan 0 = bulan pertama pelanggan melakukan transaksi

• Bulan 1 = satu bulan setelah transaksi pertama

• Bulan 2 = dua bulan setelah transaksi pertama dan seterusnya.

Di PostgreSQL, perhitungannya dapat dilakukan menggunakan AGE():

SELECT

customer_id,

cohort_month,

DATE_TRUNC('month', order_date) AS purchase_month, (

EXTRACT(YEAR FROM AGE(

DATE_TRUNC('month', order_date),

cohort_month

)) * 12

+

EXTRACT(MONTH FROM AGE(

DATE_TRUNC('month', order_date),

cohort_month

))

) AS cohort_index

FROM cohort_data;

Hasilnya kurang lebih seperti berikut:

customer_id	cohort_month	purchase_month	cohort_index
C001	Jan	Jan	0
C001	Jan	Feb	1
C001	Jan	Mar	2
C002	Jan	Jan	0
C002	Jan	Mar	2

Baca juga: Data Analyst vs Data Scientist

4. Menghitung Jumlah Pelanggan Aktif

Berikutnya, hitung jumlah pelanggan unik setiap cohort dan periode aktivitas.

SELECT

cohort_month,

cohort_index,

COUNT(DISTINCT customer_id) AS active_customers

FROM cohort_data

GROUP BY

cohort_month,

cohort_index

ORDER BY

cohort_month,

cohort_index;

COUNT(DISTINCT customer_id) penting digunakan agar satu pelanggan yang melakukan beberapa transaksi dalam bulan yang sama tetap dihitung sebagai satu pelanggan aktif.

5. Menghitung Customer Retention

Setelah jumlah pelanggan aktif diperoleh, retenton dapat dihitung dengan membandingkan jumlah pelanggan aktif pada periode tertentu dengan jumlah pelanggan pada cohort awal. Secara sederhana dapat dirumuskan sebagai berikut:

Retention Rate = (Jumlah Pelanggan Aktif pada Periode n / Jumlah Pelanggan pada Bulan 0) × 100%

Dalam SQL, kita dapat menggunakan MAX() dengan conditional aggregation untuk mendapatkan jumlah pelanggan pada bulan 0. Contohnya:

WITH cohort_size AS (

SELECT

cohort_month,

COUNT(DISTINCT customer_id) AS cohort_customers

FROM cohort_data

WHERE cohort_index = 0

GROUP BY cohort_month

)

SELECT

c.cohort_month,

c.cohort_index,

c.active_customers,

ROUND(

c.active_customers * 100.0 / s.cohort_customers,

2

) AS retention_rate

FROM cohort_activity c

JOIN cohort_size s

ON c.cohort_month = s.cohort_month

ORDER BY

c.cohort_month,

c.cohort_index;

Hasilnya dapat berbentuk sebagai berikut:

Cohort	Index	Active Customers	Retenton
Jan 2026	0	100	100%
Jan 2026	1	60	60%
Jan 2026	2	45	45%
Jan 2026	3	35	35%
Feb 2026	0	120	100%
Feb 2026	1	78	65%
Feb 2026	2	58	48,3%

6. Membuat Cohort Retention Table

Agar lebih mudah dibaca, hasil analisis dapat dibuat dalam bentuk matriks. Contohnya:

Cohort	M0	M1	M2	M3
Jan 2026	100%	60%	45%	35%
Feb 2026	100%	65%	48%	-
Mar 2026	100%	70%	-	-

Tabel tersebut memberikan gambaran visual mengenai perubahan retention setiap cohort. Jika angka retention turun tajam dari M0 ke M1. Misalnya, dari 100% dari 40%. Dalam hal ini, perusahaan perlu mengevaluasi mengapa banyak pelanggan tidak kembali melakukan transaksi pada bulan berikutnya.

Cohort Analysis membantu Data Analyst memahami retensi pelanggan berdasarkan waktu sejak pelanggan pertama kali bertransaksi. Proses tersebut dapat dilakukan secara sistematis mulai dari menentukan cohort month, menghitung cohort index, mengidentifikasi pelanggan aktif, hingga menghitung retention rate menggunakan SQL.

Kombinasi SQL dan Cohort Analysis juga membuat data transaksi yang awalnya hanya berupa kumpulan tanggal dan ID pelanggan dapat diubah menjadi insight yang lebih bermakna. Hasilnya dapat digunakan untuk mengevaluasi customer retention, membandingkan performa antar-cohort, dan membantu perusahaan menentukan strategi untuk mempertahankan pelanggan. Bagi Data Analyst, kemampuan melakukan cohort analysis menggunakan SQL menjadi salah satu keterampilan yang berguna karena analisis ini menghubungkan kemampuan teknis mengolah data dengan kebutuhan bisnis untuk memahami perilaku pelanggan.

FAQ

1. Apa itu Cohort Analysis?

Cohort Analysis adalah metode analisis yang mengelompokkan pelanggan berdasarkan periode tertentu, seperti bulan pertama melakukan transaksi, lalu mengamati perilaku mereka dari waktu ke waktu.

2. Mengapa Cohort Analysis digunakan untuk mengukur retensi pelanggan?

Cohort Analysis membantu mengetahui persentase pelanggan yang tetap aktif setelah transaksi pertama serta melihat kapan pelanggan mulai berhenti melakukan transaksi.

3. Apakah Cohort Analysis bisa dilakukan menggunakan SQL?

Bisa. SQL dapat digunakan untuk menentukan cohort, menghitung cohort index, jumlah pelanggan aktif, hingga retention rate.

Kalau kamu tertarik untuk berkarir sebagai Data Analyst yang menguasai teknik Data Cleaning secara handal, ini adalah saat yang tepat! Yuk, segera Sign Up ke DQLab! Di sini, kamu bisa belajar dari dasar hingga tingkat lanjut dengan materi dan tools yang relevan dengan kebutuhan industri, bahkan tanpa latar belakang IT. Belajar kapan saja dan di mana saja dengan fleksibilitas penuh, serta didukung oleh fitur eksklusif Ask AI Chatbot 24 jam!

Tidak cuma itu, DQLab juga sudah menerapkan metode pembelajaran HERO (Hands-On, Experiential Learning & Outcome-based) yang dirancang ramah untuk pemula, dan telah terbukti mencetak talenta unggulan yang sukses berkarier di bidang data. Jadi, mau tunggu apa lagi?

Jadi, tunggu apa lagi? Segera persiapkan diri untuk menguasai keterampilan di bidang data dan teknologi dengan subscribe modul premium, atau ikuti Bootcamp Data Analyst with SQL and Python sekarang juga!

Penulis: Reyvan Maulid

Tags

Belajar Data Science Online DQLab Data Science Free Module DQLab belajar data science