Persediaan Temu Duga SQL · Pelajaran

Menentukan Kohort Mengikut Tindakan Pertama

Menetapkan setiap pengguna kepada kohort berdasarkan tarikh peristiwa pertama mereka.

Pelajaran 1 daripada 413 langkah

Menentukan Kohort Mengikut Tindakan Pertama ialah pelajaran Persediaan Temu Duga SQL percuma di CoddyKit. Ini ialah pelajaran 1 daripada 4. Anda boleh membaca keseluruhan pelajaran di bawah secara percuma — kemudian berlatih secara praktikal dalam pelayar menggunakan penyunting kod terbina dalam dan tutor kecerdasan buatan 24/7. Pelajaran ini merupakan sebahagian daripada laluan pembelajaran Persediaan Temu Duga SQL, dan kemajuan anda disegerakkan merentas web serta aplikasi CoddyKit. Kursus Persediaan Temu Duga SQL merangkumi sejumlah 4 pelajaran.

Mengapa Kohort Muncul dalam Temu Duga

Apabila penemuduga analitik produk berkata "bina kohort", mereka sedang menguji sama ada anda boleh menetapkan setiap pengguna kepada satu kumpulan berdasarkan masa mereka melakukan sesuatu buat kali pertama, kemudian menjejaki kumpulan itu dari semasa ke semasa.

Kohort ialah sekumpulan pengguna yang berkongsi peristiwa permulaan dalam tempoh yang sama, biasanya pembelian, pendaftaran atau log masuk pertama mereka. Kekuatan kohort ialah ia membolehkan anda membandingkan pengguna secara adil: semua orang dalam kohort Januari diukur bermula daripada permulaan Januari mereka sendiri.

Kemahiran asas pertama, dan kemahiran yang dilatih dalam pelajaran ini, ialah mengira tarikh tindakan pertama setiap pengguna dengan tepat.

Jadual Sumber

Hampir semua soalan kohort bermula daripada jadual peristiwa: satu baris bagi setiap tindakan pengguna berserta cap masa. Bayangkan sebuah jadual events:

user_id — siapa yang melakukan tindakan
event_type — perkara yang dilakukan
event_at — masa tindakan itu, sebagai cap masa

Dalam temu duga, jelaskan tahap perincian secara lisan: "Adakah ini satu baris bagi setiap peristiwa, dan bolehkah seorang pengguna muncul berkali-kali?" Jawapannya hampir selalu ya, dan itulah sebabnya anda memerlukan pengagregatan untuk meringkaskannya kepada satu tindakan pertama bagi setiap pengguna.

CREATE TABLE events (
  user_id    INT,
  event_type VARCHAR(50),
  event_at   TIMESTAMP
);

Tindakan Pertama = MIN bagi Cap Masa

Langkah utama adalah mudah: kumpulkan mengikut user_id dan ambil MIN(event_at). Nilai minimum itu ialah tindakan pertama pengguna, iaitu saat yang menentukan kohort mereka.

Inilah jawapan yang ingin didengar oleh penemuduga terlebih dahulu, sebelum sebarang penggunaan fungsi tetingkap yang rumit. GROUP BY biasa adalah tepat, mudah dibaca dan pantas.

SELECT
  user_id,
  MIN(event_at) AS first_action_at
FROM events
GROUP BY user_id;

Menapis kepada Peristiwa Penentu

Selalunya kohort ditentukan oleh tindakan tertentu, bukan sebarang peristiwa. "Kumpulkan pengguna mengikut pembelian pertama mereka" bermaksud anda mesti menapis baris pembelian sebelum mengambil nilai minimum.

Letakkan penapis dalam WHERE supaya MIN hanya melihat baris yang memenuhi syarat. Perangkap biasa dalam temu duga ialah mengambil MIN bagi semua peristiwa kemudian menapis selepas itu, yang akan menetapkan tarikh permulaan yang salah kepada sesiapa yang melayari dahulu sebelum membeli.

SELECT
  user_id,
  MIN(event_at) AS first_purchase_at
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

Mengumpulkan kepada Tempoh Kohort

Kohort biasanya ialah satu tempoh, bukan cap masa tepat: "kohort 2024-03" atau "minggu 2024-03-04". Bundarkan tarikh tindakan pertama ke bawah mengikut tahap perincian tempoh tersebut.

Dalam Postgres, gunakan DATE_TRUNC('month', ...). Dalam MySQL, anda boleh menggunakan DATE_FORMAT(d, '%Y-%m-01'); dalam SQL Server, gunakan DATETRUNC(month, d) atau nilai hari pertama bulan yang dikira. Nyatakan dialek anda dalam temu duga supaya pilihan sintaks itu kelihatan disengajakan.

SELECT
  user_id,
  DATE_TRUNC('month', MIN(event_at)) AS cohort_month
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

Membungkusnya dalam CTE

Penetapan kohort bagi setiap pengguna ialah komponen asas yang akan anda gunakan semula dalam pertanyaan pengekalan, jadi bungkusnya dalam CTE dengan nama yang jelas. Ini memastikan langkah seterusnya mudah dibaca dan menunjukkan bahawa anda berfikir menggunakan komponen yang boleh digabungkan.

Mulai dari sini, setiap pertanyaan seterusnya boleh digabungkan semula dengan user_cohort untuk mengetahui kumpulan pengguna tersebut.

WITH user_cohort AS (
  SELECT
    user_id,
    DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events
  WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT * FROM user_cohort;

Saiz Kohort: Mengira Ahli

Semakan kewajaran pertama yang dijangka oleh penemuduga ialah saiz kohort: berapa ramai pengguna yang tergolong dalam setiap kohort. Kumpulkan CTE penetapan mengikut cohort_month dan kira pengguna yang berbeza.

Gunakan COUNT(DISTINCT user_id) sebagai langkah berjaga-jaga walaupun CTE itu sudah mempunyai satu baris bagi setiap pengguna; ini menunjukkan bahawa anda mengambil kira tahap perincian data. Kiraan ini menjadi penyebut bagi setiap peratusan pengekalan selepas ini.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT
  cohort_month,
  COUNT(DISTINCT user_id) AS cohort_size
FROM user_cohort
GROUP BY cohort_month
ORDER BY cohort_month;

Alternatif Fungsi Tetingkap

Penemuduga kadangkala meminta label kohort dilampirkan pada setiap baris peristiwa, bukan jadual yang telah diringkaskan. Dalam keadaan ini, fungsi tetingkap amat sesuai: MIN(event_at) OVER (PARTITION BY user_id) mengira tindakan pertama tanpa membuang baris.

Ini berguna apabila anda memerlukan kedua-dua peristiwa terperinci dan tanda kohort dalam satu laluan, yang menjadi persediaan untuk mengira pengekalan.

SELECT
  user_id,
  event_at,
  DATE_TRUNC('month',
    MIN(event_at) OVER (PARTITION BY user_id)
  ) AS cohort_month
FROM events
WHERE event_type = 'purchase';

Perangkap Ikatan dan Pendua

Bagaimana jika seorang pengguna mempunyai dua peristiwa pada cap masa terawal yang sama tepat? MIN mengendalikannya dengan kemas: ia mengembalikan satu nilai minimum itu tanpa mengira bilangan baris yang mempunyai nilai sama, jadi penetapan kohort kekal satu bagi setiap pengguna.

Bandingkan dengan pendekatan ROW_NUMBER() ... ORDER BY event_at, yang memutuskan ikatan secara sewenang-wenangnya. Anda mesti menambah pemecah seri yang menentukan hasil secara konsisten, seperti event_id, untuk mendapatkan hasil yang stabil. Menyebut pertukaran ini tanpa diminta menunjukkan kematangan anda.

SELECT user_id, event_at,
  ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY event_at, event_id
  ) AS rn
FROM events
WHERE event_type = 'purchase';

Zon Waktu dan Sempadan Hari

Soalan temu duga yang halus: pembelian pada 11:30 PM di New York ialah hari berikutnya dalam UTC. Jika kohort dikelompokkan mengikut hari kalendar, zon waktu menentukan kohort yang disertai oleh pengguna.

Jawapan yang selamat: simpan cap masa dalam UTC, kemudian tukarkannya kepada zon waktu perniagaan sebelum memotongnya. Nyatakan dengan jelas zon yang mentakrifkan "hari" bagi metrik tersebut, kerana satu keputusan ini sahaja boleh mengalihkan ribuan pengguna antara kohort.

SELECT
  user_id,
  DATE_TRUNC('day',
    MIN(event_at AT TIME ZONE 'America/New_York')
  ) AS cohort_day
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

Mengecualikan Pengguna Sebelum Tetingkap

Analisis sebenar mengehadkan kohort kepada julat tarikh, contohnya "kohort yang bermula pada Q1". Tapis berdasarkan tarikh tindakan pertama yang telah diagregatkan, iaitu dengan klausa HAVING atau penapis luar pada CTE, bukan WHERE pada peristiwa mentah.

Menapis peristiwa mentah mengikut tarikh akan tersilap membenarkan pengguna yang mula-mula membeli pada bulan Disember tetapi turut melakukan tindakan pada Q1 masuk ke dalam kohort Q1. Sentiasa hadkan berdasarkan tindakan pertama yang telah dikira.

WITH user_cohort AS (
  SELECT user_id, MIN(event_at) AS first_at
  FROM events WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT user_id, DATE_TRUNC('month', first_at) AS cohort_month
FROM user_cohort
WHERE first_at >= DATE '2024-01-01'
  AND first_at <  DATE '2024-04-01';

Semakan Ringkas

Penemuduga bertanya: "Kumpulkan setiap pengguna mengikut bulan pembelian pertama mereka. Pengguna mungkin melayari sebelum membeli." Pendekatan manakah yang betul?

Jawab dalam pelajaran interaktif

Imbas Kembali: Menentukan Kohort

Perkara penting bagi soalan temu duga tentang penentuan kohort:

Kohort mengumpulkan pengguna berdasarkan tindakan pertama yang memenuhi syarat.
Kiranya dengan MIN(event_at) selepas menapis kepada peristiwa penentu dalam WHERE.
Kumpulkan kepada satu tempoh menggunakan DATE_TRUNC atau padanan mengikut dialek.
Bungkus penetapan itu dalam CTE untuk digunakan semula; COUNT(DISTINCT user_id) memberikan saiz kohort.
Perhatikan sempadan hari mengikut zon waktu dan hadkan julat tarikh berdasarkan tindakan pertama yang telah dikira, bukan peristiwa mentah.

Kuasai perkara ini dan matriks pengekalan dalam pelajaran seterusnya hanya memerlukan satu gabungan.

Percuma untuk bermula

Pelajari SQL dengan tutor kecerdasan buatan — percuma

Tulis dan jalankan kod sebenar dalam pelayar anda, dapatkan bantuan segera daripada tutor kecerdasan buatan yang tersedia 24/7, dan sambung semula dari tempat anda berhenti di web atau dalam aplikasi.

Kursus: 30
Pelajaran: 120

Mula belajar secara percuma Lihat kursus penuh

Soalan Lazim

Adakah pelajaran “Menentukan Kohort Mengikut Tindakan Pertama” percuma?

Ya — teks penuh “Menentukan Kohort Mengikut Tindakan Pertama” boleh dibaca secara percuma di web ini. Untuk berlatih secara interaktif menggunakan penyunting kod terbina dalam dan tutor kecerdasan buatan 24/7, serta membuka kunci baki kursus Persediaan Temu Duga SQL, tingkat taraf kepada CoddyKit PRO. Kursus Persediaan Temu Duga SQL merangkumi sejumlah 4 pelajaran.

Apakah yang akan saya pelajari dalam “Menentukan Kohort Mengikut Tindakan Pertama”?

Menetapkan setiap pengguna kepada kohort berdasarkan tarikh peristiwa pertama mereka. Anda berlatih Persediaan Temu Duga SQL menggunakan kod praktikal yang dijalankan terus dalam pelayar, manakala tutor kecerdasan buatan 24/7 menjawab soalan anda semasa anda mengikuti pelajaran.

Adakah saya memerlukan pengalaman untuk memulakan Persediaan Temu Duga SQL?

Tiada pengalaman terdahulu diperlukan. Pembelajaran Persediaan Temu Duga SQL di CoddyKit disusun untuk pelajar daripada peringkat pemula hingga lanjutan, jadi anda boleh bermula di sini atau dari awal dan belajar mengikut kadar anda sendiri. Ini ialah pelajaran 1 daripada 4.

Berapa lamakah pelajaran “Menentukan Kohort Mengikut Tindakan Pertama” diambil?

Kebanyakan pelajaran CoddyKit mengambil masa kira-kira 5–10 minit. Setiap pelajaran ringkas dan interaktif, jadi anda boleh membuat kemajuan secara berterusan dan menyambung tepat dari tempat anda berhenti di web atau aplikasi.

Bolehkah saya menulis dan menjalankan kod dalam pelajaran Persediaan Temu Duga SQL ini?

Ya. Setiap pelajaran Persediaan Temu Duga SQL menyertakan penyunting kod terbina dalam, jadi anda boleh menulis dan menjalankan kod sebenar terus dalam pelayar serta menerima maklum balas kecerdasan buatan serta-merta — tanpa memerlukan persediaan setempat.

Semua pelajaran dalam kursus ini

Menentukan Kohort Mengikut Tindakan Pertama
Membina Matriks Pengekalan
Pengekalan Hari-N dan Bergulir
Pertanyaan Penyisihan dan Pengaktifan Semula

← Kembali ke Persediaan Temu Duga SQL