Digital Marketing Academy · Pelajaran

Kueri Kohort dan Corong

Jawab pertanyaan tentang retensi

Pelajaran 4 dari 413 langkah

Kueri Kohort dan Corong adalah pelajaran Digital Marketing Academy gratis di CoddyKit. Ini adalah pelajaran 4 dari 4. Kamu bisa membaca pelajaran lengkapnya di bawah secara gratis — lalu praktikkan langsung di browser dengan editor kode bawaan dan tutor AI 24/7. Ini adalah bagian dari jalur belajar Digital Marketing Academy, dan progresmu tersinkronisasi di web dan aplikasi CoddyKit. Kursus Digital Marketing Academy mencakup 4 pelajaran total.

Apa Itu Kohort?

Kohort adalah kelompok pengguna yang memiliki peristiwa awal yang sama, biasanya bulan pendaftaran mereka. Memantau setiap kohort dari waktu ke waktu menunjukkan retensi yang sebenarnya.

Metrik agregat menyembunyikan pelanggan yang berhenti; kohort menunjukkan apakah pengguna baru benar-benar bertahan.

SELECT user_id,
       DATE_TRUNC('month', signup_date) AS cohort_month
FROM users;

Menentukan Kohort

Langkah pertama adalah memberi setiap pengguna label kohort. DATE_TRUNC membulatkan tanggal pendaftaran ke awal bulan agar pengguna dapat dikelompokkan dengan rapi.

Simpan hasil ini sebagai blok pembangun yang dapat digunakan kembali untuk semua langkah berikutnya.

SELECT DATE_TRUNC('month', signup_date) AS cohort_month,
       COUNT(*) AS cohort_size
FROM users
GROUP BY 1
ORDER BY 1;

Periode Aktivitas

Retensi mengukur aktivitas pada bulan-bulan setelah pendaftaran. Hitung periode sebagai selisih antara bulan pesanan dan bulan kohort.

Periode 0 adalah bulan pendaftaran, periode 1 adalah bulan berikutnya, dan seterusnya.

SELECT o.user_id,
       (DATE_PART('year', o.order_date) - DATE_PART('year', u.signup_date)) * 12
       + (DATE_PART('month', o.order_date) - DATE_PART('month', u.signup_date)) AS period
FROM orders o
JOIN users u ON u.user_id = o.user_id;

Membangun Matriks Retensi

Gabungkan bulan kohort dan periode, lalu hitung pengguna aktif unik pada setiap sel. Hasilnya adalah segitiga retensi klasik.

Setiap baris adalah sebuah kohort; setiap kolom menunjukkan jumlah bulan sejak pendaftaran.

SELECT DATE_TRUNC('month', u.signup_date) AS cohort,
       DATE_PART('month', AGE(o.order_date, u.signup_date)) AS period,
       COUNT(DISTINCT o.user_id) AS active
FROM users u
JOIN orders o ON o.user_id = u.user_id
GROUP BY 1, 2;

Tingkat Retensi

Jumlah mentah sulit dibandingkan di antara kohort dengan ukuran berbeda. Bagi jumlah pengguna aktif pada setiap periode dengan ukuran awal kohort.

Fungsi jendela membawa ukuran periode 0 ke setiap baris untuk menghitung rasio tersebut.

SELECT cohort, period,
       active * 1.0 / FIRST_VALUE(active) OVER (
         PARTITION BY cohort ORDER BY period
       ) AS retention
FROM cohort_activity;

Apa Itu Corong?

Corong melacak pengguna melalui tahapan berurutan: mengunjungi, mendaftar, menambahkan ke keranjang, lalu membeli. Penurunan di antara tahapan menunjukkan di mana Anda kehilangan pengguna.

Analisis corong mengubah keluhan samar tentang "konversi rendah" menjadi tahap spesifik yang mengalami kebocoran.

SELECT step, COUNT(DISTINCT user_id) AS users
FROM events
WHERE step IN ('visit', 'signup', 'cart', 'purchase')
GROUP BY step;

Menghitung Setiap Tahap

Agregasi bersyarat menghitung pengguna pada setiap tahap dalam satu kali proses. FILTER menghitung pengguna unik yang mencapai setiap tahap.

Satu kueri, satu baris, dan seluruh corong dapat dilihat sekilas.

SELECT
  COUNT(DISTINCT user_id) FILTER (WHERE step = 'visit') AS visits,
  COUNT(DISTINCT user_id) FILTER (WHERE step = 'signup') AS signups,
  COUNT(DISTINCT user_id) FILTER (WHERE step = 'purchase') AS buyers
FROM events;

Rasio Konversi Antar-Tahap

Angka yang menarik adalah rasio antara tahapan yang berurutan. Membagi setiap tahap dengan tahap sebelumnya menunjukkan penurunan terbesar.

Ubah hasilnya menjadi desimal agar pembagian bilangan bulat tidak membuat rasio Anda menjadi nol.

SELECT signups * 1.0 / NULLIF(visits, 0) AS visit_to_signup,
       buyers * 1.0 / NULLIF(signups, 0) AS signup_to_buy
FROM funnel_counts;

Corong Berurutan dengan Stempel Waktu

Corong ketat mengharuskan tahapan terjadi secara berurutan. Gabungkan setiap tahap dengan tahap berikutnya hanya jika stempel waktunya lebih kemudian.

Ini mencegah penghitungan pembelian yang terjadi sebelum pendaftaran yang cocok.

SELECT COUNT(DISTINCT v.user_id) AS visited,
       COUNT(DISTINCT p.user_id) AS purchased
FROM events v
LEFT JOIN events p
  ON p.user_id = v.user_id
  AND p.step = 'purchase'
  AND p.event_time > v.event_time
WHERE v.step = 'visit';

Corong Berdasarkan Kanal

Membagi corong berdasarkan kanal akuisisi menunjukkan sumber mana yang mendatangkan pengguna yang benar-benar berkonversi, bukan hanya mengeklik.

Kelompokkan hitungan bersyarat berdasarkan kanal untuk membandingkan kualitas di antara berbagai sumber.

SELECT channel,
       COUNT(DISTINCT user_id) FILTER (WHERE step = 'visit') AS visits,
       COUNT(DISTINCT user_id) FILTER (WHERE step = 'purchase') AS buyers
FROM events
GROUP BY channel;

Dari Wawasan ke Tindakan

Kohort memberi tahu Anda apakah retensi meningkat dari satu rilis ke rilis berikutnya; corong memberi tahu Anda tahap mana yang harus diperbaiki terlebih dahulu.

Bersama-sama, keduanya mengubah pemasaran dari sekadar melaporkan apa yang terjadi menjadi mendiagnosis alasannya dan menentukan tempat berinvestasi berikutnya.

Pemeriksaan Singkat

Corong Anda menghitung pengguna pada tahap kunjungan, pendaftaran, dan pembelian. Apa yang ditunjukkan oleh rasio tahapan yang berurutan?

Jawab di pelajaran interaktif

Rangkuman

Kohort mengelompokkan pengguna berdasarkan bulan mulai dan melacak retensi sepanjang periode menggunakan DATE_TRUNC dan AGE. Corong menghitung pengguna unik pada setiap tahap berurutan dan membandingkan rasio yang berurutan.

Anda kini memiliki perangkat lanjutan untuk mendiagnosis retensi dan konversi sepenuhnya dalam SQL.

Gratis untuk memulai

Belajar Digital Marketing Academy dengan tutor AI — gratis

Tulis dan jalankan kode asli di browser kamu, dapatkan bantuan instan dari tutor AI 24/7, dan lanjutkan di mana kamu tinggalkan di web atau aplikasi.

Kursus: 63
Pelajaran: 239

Mulai belajar gratis Lihat kursus lengkapnya

Pertanyaan yang Sering Diajukan

Apakah pelajaran “Kueri Kohort dan Corong” gratis?

Ya — teks lengkap “Kueri Kohort dan Corong” gratis dibaca di sini di web. Untuk praktiknya secara interaktif (editor kode bawaan dan tutor AI 24/7) dan buka sisa kursus Digital Marketing Academy, upgrade ke CoddyKit PRO. Kursus Digital Marketing Academy mencakup 4 pelajaran total.

Apa yang akan aku pelajari di “Kueri Kohort dan Corong”?

Jawab pertanyaan tentang retensi Kamu berlatih Digital Marketing Academy dengan kode praktik yang langsung kamu jalankan di browser, dan tutor AI 24/7 menjawab pertanyaanmu saat kamu mengerjakan pelajaran ini.

Apakah aku perlu pengalaman untuk memulai Digital Marketing Academy?

Tidak diperlukan pengalaman sebelumnya. Digital Marketing Academy di CoddyKit dirancang untuk pemula hingga pelajar tingkat lanjut, jadi kamu bisa memulai di sini atau dari awal dan belajar sesuai kecepatan kamu sendiri. Ini adalah pelajaran 4 dari 4.

Berapa lama pelajaran “Kueri Kohort dan Corong” memakan waktu?

Sebagian besar pelajaran CoddyKit memakan waktu sekitar 5–10 menit. Setiap pelajaran ringkas dan interaktif, jadi kamu membuat kemajuan stabil dan melanjutkan dari tempat kamu tinggalkan di web dan aplikasi.

Bisakah aku menulis dan menjalankan kode dalam pelajaran Digital Marketing Academy ini?

Ya. Setiap pelajaran Digital Marketing Academy menyertakan editor kode bawaan, jadi kamu menulis dan menjalankan kode nyata langsung di browser dan mendapatkan umpan balik AI instan — tidak diperlukan penyiapan lokal.

Semua pelajaran dalam kursus ini

← Kembali ke Digital Marketing Academy