Tutorial Retention Analysis Menggunakan SQL dari Transaksi ke Retention Rate

Retention analysis merupakan salah satu metode yang digunakan untuk mengetahui seberapa banyak pengguna atau pelanggan yang kembali menggunakan produk dalam periode tertentu. Bagi perusahaan, informasi ini penting karena jumlah pengguna baru saja tidak cukup untuk menggambarkan pertumbuhan bisnis. Perusahaan juga perlu mengetahui apakah pengguna tersebut tetap aktif setelah melakukan transaksi pertama.

SQL dapat membantu proses tersebut dengan mengolah data transaksi menjadi informasi retention yang lebih mudah dianalisis. Melalui beberapa query, data transaksi dapat digunakan untuk menentukan tanggal transaksi pertama, mengelompokkan pengguna berdasarkan periode hingga menghitung retention rate. Artikel ini akan membahas tutorial retention analysis menggunakan SQL secara bertahap, mulai dari menyiapkan data transaksi hingga mendapatkan retention rate. Simak langkah-langkahnya berikut ini sahabat DQLab!

1. Data yang Dibutuhkan untuk Retention Analysis

Sebelum membuat query SQL, siapkan data transaksi yang memiliki setidaknya tiga informasi utama meliputi:

user_id	transaction_date	transaction_id
U001	2026-01-05	TR001
U001	2026-02-10	TR015
U002	2026-01-12	TR002
U002	2026-03-01	TR030
U003	2026-02-15	TR010

Keterangan:

• user_id digunakan untuk mengidentifikasi pengguna

• transaction_date merupakan tanggal transaksi

• transaction_id merupakan identitas transaksi

Misalnya tabel tersebut bernama transactions.

Baca Juga: Bootcamp Data Analyst with Python & SQL

2. Menentukan Transaksi Pertama Setiap User

Tahap pertama dalam retention analysis adalah mencari kapan masing-masing pengguna pertama kali melakukan transaksi. Informasi ini nantinya digunakan untuk menentukan cohort month, yaitu bulan ketika pengguna pertama kali melakukan transaksi. Query SQL:

SELECT

user_id,

MIN(transaction_date) AS first_transaction_date

FROM transactions

GROUP BY user_id;

Fungsi MIN() digunakan untuk mengambil tanggal transaksi paling awal dari setiap user_id. Hasilnya dapat berupa:

user_id	first_transaction_date
U001	2026-01-05
U002	2026-01-12
U003	2026-02-15

Dari data tersebut dapat diketahui bahwa U001 dan U002 merupakan pengguna yang pertama kali bertransaksi pada Januari, sedangkan U003 masuk pada Februari.

3. Menentukan Cohort Month

Selanjutnya, tanggal transaksi pertama perlu diubah menjadi periode bulan agar pengguna dapat dikelompokkan berdasarkan cohort. Pada PostgreSQL, salah satu cara yang dapat digunakan adalah DATE_TRUNC().

SELECT

user_id,

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

FROM transactions

GROUP BY user_id;

DATE_TRUNC(‘month’,.........) akan mengubah tanggal menjadi awal bulan.

Contohnya:

2026-01-05 → 2026-01-01

2026-02-15 → 2026-02-01

4. Menghubungkan Cohort dengan Seluruh Transaksi

Setelah cohort setiap pengguna diketahui, informasi tersebut dapat digabungkan kembali dengan tabel transaksi. Contoh query:

WITH user_cohort AS (

SELECT

user_id,

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

FROM transactions

GROUP BY user_id

)

SELECT

t.user_id,

t.transaction_date,

uc.cohort_month

FROM transactions t

JOIN user_cohort uc

ON t.user_id = uc.user_id;

Hasilnya akan memberikan informasi mengenai setiap transaksi sekaligus cohort pengguna.

user_id	transaction_date	cohort_month
U001	2026-01-05	2026-01-01
U001	2026-02-10	2026-01-01
U002	2026-01-12	2026-01-01
U002	2026-03-01	2026-02-01
U003	2026-02-15	2026-02-01

Baca Juga: Data Analyst vs Data Scientist

5. Menghitung Periode Retention

Selanjutnya, tentukan berapa bulan jarak antara cohort pengguna dan bulan transaksi. Pada PostgreSQL, perhitungan tersebut dapat dilakukan menggunakan AGE(). Contohnya:

DATE_PART(

'month',

AGE(

DATE_TRUNC('month', t.transaction_date),

uc.cohort_month

)

) AS month_number

Jika pengguna pertama kali melakukan transaksi pada Januari dan kembali bertransaksi pada Februari, maka:

• Januari = Month 0

• Februari = Month 1

• Maret = Month 2

Konsep ini penting karena retention analysis biasanya tidak hanya melihat apakah pengguna kembali, tetapi juga kapan pengguna tersebut kembali.

6. Menghitung Jumlah User yang Retained

Setelah periode retention diperoleh, hitung jumlah pengguna unik pada setiap cohort dan periode.

WITH user_cohort AS (

SELECT

user_id,

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

FROM transactions

GROUP BY user_id

),

user_activity AS (

SELECT DISTINCT

t.user_id,

uc.cohort_month,

DATE_TRUNC('month', t.transaction_date) AS activity_month

FROM transactions t

JOIN user_cohort uc

ON t.user_id = uc.user_id

)

SELECT

cohort_month,

activity_month,

COUNT(DISTINCT user_id) AS retained_users

FROM user_activity

GROUP BY

cohort_month,

activity_month

ORDER BY

cohort_month,

activity_month;

Penggunaan COUNT(DISTINCT user_id) penting agar satu pengguna yang melakukan banyak transaksi dalam bulan yang sama tetap dihitung sebagai satu user, bukan beberapa user.

7. Menghitung Retention Rate

Jumlah pengguna yang kembali saja belum cukup. Agar dapat dibandingkan antar-cohort, kita perlu menghitung retention rate. Rumus sederhananya yakni:

Retention Rate = Jumlah User yang Kembali / Jumlah User pada Cohort × 100%

Misalnya:

Cohort Januari terdiri dari 100 pengguna. Pada Februari, terdapat 60 pengguna yang kembali. Maka Retention Rate = 60 / 100 × 100% = 60%

Retention analysis menggunakan SQL dapat membantu mengubah data transaksi menjadi insight mengenai perilaku pengguna. Prosesnya dimulai dengan menentukan transaksi pertama setiap user, membuat cohort berdasarkan bulan, mengidentifikasi aktivitas pengguna pada periode berikutnya lalu menghitung jumlah user yang kembali dan retention rate.

Konsep utamanya sebenarnya cukup sederhana:

Data transaksi → Transaksi pertama → Cohort → Aktivitas berikutnya → Retained Users → Retention Rate

Melalui pemahaman alur tersebut, SQL tidak hanya digunakan untuk mengambil data, tetapi juga dapat menjadi alat untuk menjawab pertanyaan bisnis seperti seberapa banyak pengguna yang bertahan, kapan mereka mulai berhenti aktif, dan cohort mana yang memiliki performa terbaik.

FAQ

1. Apa itu retention analysis menggunakan SQL?

Retention analysis menggunakan SQL adalah proses menganalisis data untuk mengetahui seberapa banyak pengguna yang kembali aktif atau melakukan transaksi setelah periode pertama.

2. Apa fungsi cohort dalam retention analysis?

Cohort digunakan untuk mengelompokkan pengguna berdasarkan periode ketika mereka pertama kali melakukan aktivitas, sehingga tingkat retensi antar kelompok dapat dibandingkan.

3. Bagaimana cara menghitung retention rate?

Retention rate dihitung dengan membagi jumlah pengguna yang kembali pada periode tertentu dengan jumlah pengguna dalam cohort, kemudian dikalikan 100%.

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