Churn Rate With SQL

Membangun sebuah startup memang tidaklah mudah. Bukan hanya mendapatkan pelanggan baru yang susah, tetapi mempertahankan pelanggan yang lama lebih susah lagi.

Kenyataannya, rata-rata usaha aplikasi di handphone kehilangan 77% dari pengguna hariannya (Daily Application User/DAU) dalam waktu hanya 3 hari setelah pemasangan. Dan dalam waktu 90 hari, jumlah pelanggan yang hilang sudah mencapai 95%. Kehilangan pelanggan terjadi di semua tipe bisnis.

Mengantisipasi hilangnya pelanggan merupakan hal yang penting bagi pengusaha untuk mengambil keputusan. Untuk itu, metrik yang digunakan adalah Churn Rate.

Churn Rate sendiri terbagi 2, yaitu: Costumer Churn Rate dan Revenue Churn Rate. Costumer Churn Rate digunakan untuk menghitung persentase hilangnya pelanggan, sedangkan Revenue Churn Rate digunakan untuk menghitung persentase hilangnya revenue pada perusahaan. Pada artikel ini kita akan membahas tentang Costumer Churn Rate.

Costumer Churn Rate adalah metrik yang digunakan untuk mengukur persentase berhentinya pelanggan pada suatu layanan ataupun produk yang disediakan dalam jangka waktu tertentu. Biasanya, Churn Rate menggunakan periode bulanan. Untuk menghitung Churn Rate, bisa menggunakan rumus:

Pada studi kasus kali ini kita akan menggunakan SQL untuk menghitung churn ratenya. Berikut skema dari tabel yang digunakan. Tabel yang digunakan untuk menghitung churn rate yaitu tabel subscriptions. Terdiri atas 3 atribut dan type datanya, seperti pada gambar dibawah ini.

select * from  subscriptions limit 10

Pada studi kasus kali ini kita akan mencari churn rate selama bulan januari tahun 2017. Yang pertama dilakukan adalan mencari jumlah kustomer yang keluar selama bulan januari. Query yang digunakan ntuk mencari jumlah kustomer yang keluar selama bulan desember sebagai berikut :

SELECT COUNT(*) AS cancelled
FROM subscriptions
WHERE subscription_start < '2017-01-01'
AND (
subscription_end
BETWEEN '2017-01-01'
AND '2017-01-31'
)

Penjelasan query diatas adalah mencari jumlahnya cusomer dengan menggunakan perintah count(*) dengan kondisi si kustomer telah menggunakan layanan jauh jauhari (subscriptions_start) sebelum bulan januari 2017 namun karena kita akan mencari jumlah kustomer yang berhenti layanan selama bulan januari 2017 makan perlu ditambahkan kondisi subscriptions_end selama bulan januari.

Berdasarkan query diatas dihasilkan kustomer yang berhenti menggunakan layanan selama bulan januari berjumlah 35 orang.

Selanjutnya menghitung jumlah kustomer keseluruhan sebelum bulan januari. Berikut query yang digunakan

SELECT COUNT(*) AS total_customer
FROM subscriptions
WHERE subscription_start < '2017-01-01'
AND (
(subscription_end >= '2017-01-01')
OR (subscription_end IS NULL)
)

Berdasarakan query diatas dihasilkan jumlah customer sebelum bulan januari 2017 berjumlah 277 customer.

Secara perhitungan manual maka untuk churn rate selama bulan januari 2017 yaitu 35 /277 =0.126353790613718 atau 12 %

SELECT 1.0 *
(
SELECT COUNT(*)
FROM subscriptions
WHERE subscription_start < '2017-01-01'
AND (
subscription_end
BETWEEN '2017-01-01'
AND '2017-01-31'
)
) / (
SELECT COUNT(*)
FROM subscriptions
WHERE subscription_start < '2017-01-01'
AND (
(subscription_end >= '2017-01-01')
OR (subscription_end IS NULL)
)
)
AS result;

Cara lain dalam mencari churn rate menggunakan perintah WITH dan CASE pada SQL.

WITH enrollments AS
(SELECT *
FROM subscriptions
WHERE subscription_start < '2017-01-01'
AND (
(subscription_end >= '2017-01-01')
OR (subscription_end IS NULL)
)),
status AS
(SELECT
CASE
WHEN (subscription_end > '2017-01-31')
OR (subscription_end IS NULL) THEN 0
ELSE 1
END as is_canceled,
CASE
WHEN (subscription_start < '2017-01-01')
AND (
(subscription_end >= '2017-01-01')
OR (subscription_end IS NULL)
) THEN 1
ELSE 0
END as is_active
FROM enrollments
)

SELECT 1.0 * SUM(is_canceled)/SUM(is_active) FROM status;

Berdasarkan query SQL diatas , didapatkan Churn Rate sebesar 12% yang mana 12 % merupakan angka yang tidak ideal bagi perusahaan karena berdasarkan benchmark dari Software as a Service (SaaS) seperti Oracle dan Microsoft, Churn Rate ideal adalah 5–7 persen.

Multiple Month: Churn Rate Calculation

Pada studi kasus diatas kita hanya mencari churn rate pada bulan januari 2017.Berikut ini kita akan mencari churn rate untuk bulan januari , februari, dan maret.

WITH months AS
(SELECT
'2017-01-01' as first_day,
'2017-01-31' as last_day
UNION
SELECT
'2017-02-01' as first_day,
'2017-02-28' as last_day
UNION
SELECT
'2017-03-01' as first_day,
'2017-03-31' as last_day
),
cross_join AS
(SELECT *
FROM subscriptions
CROSS JOIN months),
status AS
(SELECT id, first_day as month,
CASE
WHEN (subscription_start < first_day)
AND (
subscription_end > first_day
OR subscription_end IS NULL
) THEN 1
ELSE 0
END as is_active,
CASE
WHEN subscription_end BETWEEN first_day AND last_day THEN 1
ELSE 0
END as is_canceled
FROM cross_join),
status_aggregate AS
(SELECT
month,
SUM(is_active) as active,
SUM(is_canceled) as canceled
FROM status
GROUP BY month)
SELECT *
FROM status_aggregate;

WITH months AS (
SELECT
'2017-01-01' AS first_day,
'2017-01-31' AS last_day
UNION
SELECT
'2017-02-01' AS first_day,
'2017-02-28' AS last_day
UNION
SELECT
'2017-03-01' AS first_day,
'2017-03-31' AS last_day
),
cross_join AS (
SELECT *
FROM subscriptions
CROSS JOIN months
),
status AS (
SELECT
id,
first_day AS month,
CASE
WHEN (subscription_start < first_day)
AND (
subscription_end > first_day
OR subscription_end IS NULL
) THEN 1
ELSE 0
END AS is_active,
CASE
WHEN subscription_end BETWEEN first_day AND last_day THEN 1
ELSE 0
END AS is_canceled
FROM cross_join
),
status_aggregate AS (
SELECT
month,
SUM(is_active) AS active,
SUM(is_canceled) AS canceled
FROM status
GROUP BY month
)
SELECT
month,
1.0 * canceled / active AS churn_rate
FROM status_aggregate;

Ini adalah hal yang harus diperhatikan oleh perusahaan karena Churn Rate merupakan hambatan bagi pertumbuhan. Selain dari menghambat pertumbuhan, kehilangan pelanggan juga berarti kehilangan pemasukan atau Revenue Churn Rate.