Budowanie macierzy retencji
Zliczanie aktywnych użytkowników według kohorty i przesunięcia okresu w celu utworzenia tabeli retencji.
Budowanie macierzy retencji to bezpłatna lekcja Coding Interview Prep na CoddyKit. To lekcja 2 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej Coding Interview Prep, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs Coding Interview Prep zawiera 4 lekcji w sumie.
Czym jest macierz retencji
Następnym krokiem po zdefiniowaniu kohorty jest słynna macierz retencji: wiersze odpowiadają kohortom, kolumny przesunięciom okresów (miesiąc 0, 1, 2, ...), a każda komórka zawiera liczbę użytkowników z danej kohorty, którzy nadal byli aktywni przy danym przesunięciu.
Osoby prowadzące rozmowy kwalifikacyjne lubią to zadanie, ponieważ wymaga połączenia przypisania do kohorty, złączenia z powrotem z aktywnością, obliczenia różnicy okresów i wykonania przestawienia danych. To najbardziej reprezentatywne zapytanie w analityce produktu.
Dwa źródła danych
Potrzebne są dwie rzeczy: okres kohorty każdego użytkownika (z poprzedniej lekcji) oraz zapis każdego okresu aktywności danego użytkownika. Aktywność pochodzi z tej samej tabeli zdarzeń, zredukowanej do poziomu okresu.
Zapytanie należy więc zbudować następująco: CTE kohorty, następnie CTE aktywności zawierające listę miesięcy, w których każdy użytkownik był aktywny, a na końcu złączenie obu zbiorów.
WITH user_cohort AS (
SELECT user_id,
DATE_TRUNC('month', MIN(event_at)) AS cohort_month
FROM events
GROUP BY user_id
)
SELECT * FROM user_cohort;Wyszczególnianie aktywnych okresów
CTE aktywności odpowiada na pytanie: „W których miesiącach każdy użytkownik był aktywny?”. Każde zdarzenie należy obciąć do miesiąca, a następnie usunąć duplikaty za pomocą DISTINCT lub GROUP BY, aby użytkownik aktywny 40 razy w marcu dał jeden wiersz oznaczający marzec.
Ta lista aktywności na poziomie użytkownika i miesiąca jest zbiorem, który należy połączyć z kohortą, aby zmierzyć utrzymanie aktywności w kolejnych przesunięciach.
WITH activity AS (
SELECT DISTINCT
user_id,
DATE_TRUNC('month', event_at) AS active_month
FROM events
)
SELECT * FROM activity;Obliczanie przesunięcia okresu
Sercem macierzy jest numer okresu: ile miesięcy po rozpoczęciu kohorty przypadła dana aktywność? Należy odjąć miesiąc kohorty od miesiąca aktywności.
W Postgresie można w przejrzysty sposób policzyć pełne miesiące między dwiema datami. Przenośna formuła mnoży różnicę lat przez 12 i dodaje różnicę miesięcy; wiele silników oferuje również funkcje pomocnicze. Przesunięcie 0 oznacza początkowy miesiąc danej kohorty.
-- months between two month-truncated dates (Postgres)
SELECT
(EXTRACT(YEAR FROM active_month) - EXTRACT(YEAR FROM cohort_month)) * 12
+ (EXTRACT(MONTH FROM active_month) - EXTRACT(MONTH FROM cohort_month))
AS period_number;Dołączanie kohorty do aktywności
Połącz CTE kohorty z CTE aktywności po user_id. Każdy wiersz wyniku oznacza: ten użytkownik, należący do kohorty X, był aktywny przy przesunięciu N. Zliczenie unikalnych użytkowników dla każdej pary (kohorta, przesunięcie) tworzy macierz w długim formacie.
Ponieważ każdy członek kohorty jest aktywny w swoim początkowym miesiącu, przesunięcie 0 powinno być równe rozmiarowi kohorty — to wbudowany test poprawności.
WITH user_cohort AS (
SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
FROM events GROUP BY user_id
),
activity AS (
SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
FROM events
)
SELECT c.cohort_month, a.active_month, c.user_id
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id;Tabela retencji w długim formacie
Dodaj obliczenie przesunięcia i agregację. Otrzymujesz teraz uporządkowany wynik w długim formacie: jeden wiersz dla każdej kohorty i każdego przesunięcia, zawierający liczbę użytkowników, którzy pozostali aktywni. Wielu rekruterów zaakceptuje taki wynik bezpośrednio, ponieważ pivotowanie jest tylko kwestią prezentacji.
Zauważ, że wyrażenie obliczające przesunięcie występuje zarówno w SELECT, jak i w GROUP BY, ponieważ jest wyliczane, a nie przechowywane w kolumnie.
WITH user_cohort AS (
SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
FROM events GROUP BY user_id
),
activity AS (
SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
FROM events
)
SELECT
c.cohort_month,
(EXTRACT(YEAR FROM a.active_month)-EXTRACT(YEAR FROM c.cohort_month))*12
+(EXTRACT(MONTH FROM a.active_month)-EXTRACT(MONTH FROM c.cohort_month)) AS period_number,
COUNT(DISTINCT c.user_id) AS retained_users
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id
GROUP BY c.cohort_month, period_number
ORDER BY c.cohort_month, period_number;Przekształcanie na szerokie kolumny
Aby uzyskać klasyczną siatkę, przekształć przesunięcia w kolumny za pomocą agregacji warunkowej: sumy wyrażenia CASE dla każdego przesunięcia. Ten przenośny wzorzec działa w każdym dialekcie bez specjalnej składni PIVOT.
Każde wyrażenie CASE zwraca 1, gdy wartość period_number wiersza odpowiada danej kolumnie, więc SUM zlicza użytkowników, którzy pozostali aktywni przy tym przesunięciu.
SELECT
cohort_month,
COUNT(DISTINCT CASE WHEN period_number = 0 THEN user_id END) AS m0,
COUNT(DISTINCT CASE WHEN period_number = 1 THEN user_id END) AS m1,
COUNT(DISTINCT CASE WHEN period_number = 2 THEN user_id END) AS m2,
COUNT(DISTINCT CASE WHEN period_number = 3 THEN user_id END) AS m3
FROM retention_long
GROUP BY cohort_month
ORDER BY cohort_month;Od zliczeń do wskaźników retencji
Rekruterzy zwykle oczekują procentów, a nie surowych zliczeń. Podziel liczbę użytkowników utrzymanych przy danym przesunięciu przez rozmiar kohorty (przesunięcie 0). Rzutuj wynik na typ zmiennoprzecinkowy lub pomnóż go przez 1.0, aby uniknąć dzielenia całkowitego — najczęstszego cichego błędu w tym zadaniu.
Otrzymujesz krzywą retencji: 100% w miesiącu 0, opadającą w kierunku poziomu stabilizacji. To właśnie ten poziom interesuje interesariuszy.
SELECT
cohort_month,
period_number,
retained_users,
ROUND(
100.0 * retained_users
/ MAX(retained_users) OVER (PARTITION BY cohort_month),
1
) AS retention_pct
FROM retention_long
ORDER BY cohort_month, period_number;Pułapka dzielenia całkowitego
Częsta pułapka rekrutacyjna: w większości silników wyrażenie 120 / 500 daje 0, a nie 0.24, ponieważ oba operandy są liczbami całkowitymi. Wskaźniki retencji po cichu wychodzą wtedy równe zero.
Napraw to, nadając jednej stronie typ numeryczny: pomnóż przez 100.0, rzutuj jeden operand na NUMERIC albo dziel przez NULLIF(size, 0), aby dodatkowo zabezpieczyć się przed pustą kohortą. Stwierdzenie „NULLIF zapobiega dzieleniu przez zero” zapewnia dodatkowe punkty.
SELECT
retained_users,
cohort_size,
100.0 * retained_users / NULLIF(cohort_size, 0) AS pct
FROM retention_long;Uzupełnianie brakujących przesunięć zerami
Jeśli kohorta miała zero użytkowników utrzymanych przy przesunięciu 2, JOIN nie zwróci wiersza, pozostawiając lukę w macierzy. Aby pokazać jawne 0, wygeneruj pełną siatkę kombinacji (kohorta, przesunięcie), a następnie użyj LEFT JOIN, aby dołączyć do niej zliczenia.
Utwórz siatkę, łącząc za pomocą CROSS JOIN kohorty z listą liczb lub przesunięć, a następnie użyj COALESCE, aby brakujące zliczenia zastąpić zerami. Rekruterzy docenią zauważenie tej luki.
WITH offsets AS (SELECT generate_series(0, 6) AS period_number),
cohorts AS (SELECT DISTINCT cohort_month FROM retention_long)
SELECT
c.cohort_month, o.period_number,
COALESCE(r.retained_users, 0) AS retained_users
FROM cohorts c
CROSS JOIN offsets o
LEFT JOIN retention_long r
ON r.cohort_month = c.cohort_month
AND r.period_number = o.period_number
ORDER BY c.cohort_month, o.period_number;Trójkątny kształt i efekt świeżości
Warto wspomnieć jeszcze o jednym szczególe: macierz ma trójkątny kształt. Kohorta, która rozpoczęła aktywność w zeszłym miesiącu, nie może mieć jeszcze wartości dla miesiąca 3, więc późniejsze przesunięcia obejmują mniej kohort.
Średnia wartości kolumny obliczona dla kohort jest więc obciążona na korzyść starszych kohort. Należy wspomnieć, że można albo uczciwie pokazać trójkąt, albo ograniczyć porównania do przesunięć, które osiągnęły wszystkie kohorty. Ta świadomość odróżnia analityków od osób piszących zapytania.
Szybkie sprawdzenie
Zapytanie dotyczące retencji dzieli liczbę użytkowników utrzymanych przez rozmiar kohorty, ale każdy procent poza miesiącem 0 wynosi 0. Jaka jest najbardziej prawdopodobna przyczyna?
Podsumowanie: macierz retencji
Aby zbudować macierz retencji podczas rozmowy rekrutacyjnej:
- Przypisz każdemu użytkownikowi okres kohorty, a następnie wypisz jego okresy aktywności i usuń duplikaty.
- Połącz te dane i oblicz przesunięcie okresu (liczbę miesięcy między kohortą a aktywnością).
- Agreguj dane do długiego formatu za pomocą
COUNT(DISTINCT user_id); jeśli potrzebna jest siatka, wykonaj pivotowanie za pomocąCASE. - Ostrożnie przekształcaj zliczenia w wskaźniki, unikając dzielenia całkowitego oraz dzielenia przez zero dzięki
100.0iNULLIF. - Użyj wygenerowanej siatki i LEFT JOIN, aby uzupełnić komórki zerami, pamiętając, że macierz ma trójkątny kształt.
Często zadawane pytania
Czy lekcja „Budowanie macierzy retencji” jest bezpłatna?
Tak — pełny tekst „Budowanie macierzy retencji” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu Coding Interview Prep, przejdź na CoddyKit PRO. Kurs Coding Interview Prep zawiera 4 lekcji w sumie.
Co nauczysz się w „Budowanie macierzy retencji”?
Zliczanie aktywnych użytkowników według kohorty i przesunięcia okresu w celu utworzenia tabeli retencji. Ćwiczysz Coding Interview Prep z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.
Czy potrzebuję doświadczenia, aby zacząć Coding Interview Prep?
Nie wymagamy żadnego doświadczenia. Coding Interview Prep w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 2 z 4.
Ile czasu zajmuje lekcja „Budowanie macierzy retencji”?
Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.
Czy mogę pisać i uruchamiać kod w tej lekcji Coding Interview Prep?
Tak. Każda lekcja Coding Interview Prep zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.
Wszystkie lekcje w tym kursie
- Definiowanie kohorty na podstawie pierwszego działania
- Budowanie macierzy retencji
- Retencja dnia N i retencja krocząca
- Zapytania dotyczące odpływu i powrotów użytkowników