📊 Kohorty i retencja w SQL – analiza zachowań klientów krok po kroku

Jak napisać zapytanie SQL do analizy kohort i retencji klientów?

Zapytanie do analizy kohort i retencji powinno robić trzy rzeczy: przypisać każdego klienta do kohorty, wyznaczyć kolejne okresy jego aktywności oraz policzyć, jaki odsetek klientów z danej kohorty wrócił w miesiącu 1, 2, 3 i dalej. Najczęściej kohortę definiuje się jako miesiąc pierwszego zakupu lub pierwszej aktywności, a retencję mierzy się w kolejnych pełnych miesiącach od tego momentu.

W praktyce potrzebujesz tabeli zdarzeń zawierającej co najmniej identyfikator klienta oraz datę aktywności, na przykład zamówienia. Najpierw wyznaczasz dla każdego klienta datę pierwszej aktywności przez MIN(event_date). Następnie sprowadzasz tę datę do poziomu miesiąca, aby otrzymać miesiąc kohorty. W kolejnym kroku łączysz tę informację z wszystkimi aktywnościami klienta i obliczasz numer okresu retencji, czyli różnicę między miesiącem zdarzenia a miesiącem kohorty. Dzięki temu klient aktywny w miesiącu pozyskania ma okres 0, w następnym miesiącu 1, itd.

Typowy schemat wygląda tak:

WITH first_activity AS ( SELECT customer_id, DATE_TRUNC('month', MIN(event_date)) AS cohort_month FROM events GROUP BY customer_id ), activity AS ( SELECT DISTINCT e.customer_id, f.cohort_month, DATE_TRUNC('month', e.event_date) AS activity_month FROM events e JOIN first_activity f ON e.customer_id = f.customer_id ), retention AS ( SELECT cohort_month, activity_month, EXTRACT(YEAR FROM age(activity_month, cohort_month)) * 12 + EXTRACT(MONTH FROM age(activity_month, cohort_month)) AS period_number, COUNT(DISTINCT customer_id) AS active_customers FROM activity GROUP BY cohort_month, activity_month ), cohort_size AS ( SELECT cohort_month, COUNT(DISTINCT customer_id) AS cohort_customers FROM first_activity GROUP BY cohort_month ) SELECT r.cohort_month, r.period_number, r.active_customers, c.cohort_customers, ROUND(1.0 * r.active_customers / c.cohort_customers, 4) AS retention_rate FROM retention r JOIN cohort_size c ON r.cohort_month = c.cohort_month ORDER BY r.cohort_month, r.period_number;

Najważniejsze jest poprawne zdefiniowanie jednostki czasu i aktywności. Jeśli analizujesz zakupy cykliczne, zwykle stosujesz miesiące. Jeśli produkt ma częste użycie, sensowniejszy może być tydzień. Równie ważne jest, co uznajesz za „powrót”: zakup, logowanie, odnowienie subskrypcji albo dowolne zdarzenie biznesowo istotne. To właśnie ta definicja wpływa na wynik retencji bardziej niż sama konstrukcja SQL.

Warto też pilnować dwóch technicznych kwestii. Po pierwsze, w części z aktywnością dobrze użyć DISTINCT na poziomie klient plus okres, aby wielokrotne zdarzenia w tym samym miesiącu nie zawyżały retencji. Po drugie, denominator, czyli liczebność kohorty, powinien być stały dla całej kohorty i liczony tylko raz na podstawie pierwszej aktywności. Dopiero wtedy wskaźnik retencji jest poprawny i porównywalny między kohortami.

Końcowy wynik zwykle zawiera: miesiąc kohorty, numer okresu retencji, liczbę aktywnych klientów oraz procent retencji. Taka struktura pozwala później łatwo zbudować macierz kohortową albo wykres pokazujący, jak szybko użytkownicy odpadają po pierwszym kontakcie.

💡 Zanim policzysz retencję, upewnij się, że deduplikujesz aktywności do poziomu klient + okres, bo wiele zdarzeń w tym samym miesiącu może sztucznie zawyżyć wynik. Dobrą praktyką jest też przetestowanie kilku definicji „powrotu” i jednostek czasu, bo to one najczęściej najmocniej zmieniają obraz retencji.

Najczęściej zadawane pytania i odpowiedzi odnośnie Kohorty i retencja w SQL: zapytanie, które pokazuje prawdziwe zachowanie klientów

Jak działa analiza kohort i retencji klientów w SQL?

Analiza kohort i retencji w SQL polega na przypisaniu klienta do momentu startowego i sprawdzeniu, czy wracał w kolejnych okresach. Najczęściej kohortą jest miesiąc pierwszej aktywności, a retencję liczy się w następnych miesiącach. Dzięki temu widać nie tylko liczbę użytkowników, ale też tempo ich odpływu po pierwszym zakupie lub innym ważnym zdarzeniu.

Jak zdefiniować kohortę klienta w zapytaniu SQL?

Kohortę klienta najczęściej definiuje się jako miesiąc jego pierwszej aktywności. W praktyce wyznacza się minimalną datę zdarzenia dla każdego klienta za pomocą MIN(event_date), a potem sprowadza ją do wybranego poziomu czasu, na przykład miesiąca. Taki sposób daje stały punkt odniesienia do porównywania zachowania klientów pozyskanych w różnych okresach.

Co oznacza period_number w analizie retencji?

Period_number oznacza numer okresu od momentu wejścia klienta do kohorty. Wartość 0 odnosi się do miesiąca pozyskania, 1 do kolejnego miesiąca, a następne liczby pokazują dalsze okresy aktywności. Ten wskaźnik pozwala ułożyć wynik w logiczną oś czasu i łatwo porównać retencję między różnymi kohortami.

Dlaczego w analizie retencji trzeba deduplikować aktywności klienta?

Deduplikacja jest potrzebna, aby jeden klient nie został policzony kilka razy w tym samym okresie. Jeśli użytkownik wykona wiele zdarzeń w jednym miesiącu, a dane nie zostaną ograniczone do poziomu klient plus okres, retencja będzie zawyżona. Właśnie dlatego w części z aktywnością często stosuje się DISTINCT przed dalszym grupowaniem.

Jaką jednostkę czasu wybrać do liczenia retencji w SQL?

Jednostkę czasu należy dobrać do naturalnego rytmu korzystania z produktu lub usługi. W artykule wskazano, że dla zakupów cyklicznych zwykle używa się miesięcy, a dla częstego użycia lepszy może być tydzień. Dobra jednostka czasu pomaga odróżnić realny brak powrotu od zwykłej specyfiki zachowania użytkownika.

Miesiąc sprawdza się przy zakupach i dłuższych cyklach decyzyjnych.
Tydzień bywa lepszy przy częstej aktywności w produkcie.

Co można uznać za powrót klienta w analizie retencji?

Powrotem klienta jest każde zdarzenie, które ma biznesowe znaczenie w danym modelu analizy. Nie musi to być wyłącznie zakup. Artykuł wskazuje, że za powrót można uznać także logowanie, odnowienie subskrypcji lub inne istotne działanie. Ta definicja mocno wpływa na wynik, dlatego powinna być ustalona świadomie przed pisaniem końcowego raportu.

Jakie są najczęstsze błędy przy liczeniu retencji klientów w SQL?

Najczęstsze błędy dotyczą złej definicji aktywności, braku deduplikacji i niepoprawnego mianownika kohorty. Jeśli liczebność kohorty nie jest stała albo zdarzenia są liczone wielokrotnie w tym samym okresie, wynik przestaje być porównywalny. Problemem bywa też użycie niewłaściwej jednostki czasu, która zniekształca realny obraz powrotów klientów.

Liczenie wielu zdarzeń tego samego klienta w jednym okresie.
Zmiennej wielkości kohorty zamiast stałego denominatora.
Niedopasowanie okresu analizy do sposobu używania produktu.

Jak wygląda końcowy wynik analizy kohortowej i retencji?

Końcowy wynik zwykle pokazuje miesiąc kohorty, numer okresu retencji, liczbę aktywnych klientów i wskaźnik retencji. Taka tabela jest wygodna do dalszej wizualizacji, na przykład w formie macierzy kohortowej lub wykresu. Dzięki niej można szybko zobaczyć, które grupy klientów wracają regularnie, a które odpadają już po pierwszym kontakcie.