Zapytania kohortowe i lejkowe
Uzyskiwanie odpowiedzi na pytania o retencję
Zapytania kohortowe i lejkowe to bezpłatna lekcja Digital Marketing Academy na CoddyKit. To lekcja 4 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 Digital Marketing Academy, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs Digital Marketing Academy zawiera 4 lekcji w sumie.
Czym jest kohorta?
Kohorta to grupa użytkowników, których łączy zdarzenie początkowe, zazwyczaj miesiąc rejestracji. Śledzenie każdej kohorty w czasie pokazuje rzeczywistą retencję.
Zagregowane metryki ukrywają odpływ użytkowników, natomiast kohorty pokazują, czy nowi użytkownicy rzeczywiście pozostają aktywni.
SELECT user_id,
DATE_TRUNC('month', signup_date) AS cohort_month
FROM users;Definiowanie kohorty
Pierwszym krokiem jest przypisanie każdemu użytkownikowi jego kohorty. DATE_TRUNC zaokrągla daty rejestracji w dół do początku miesiąca, dzięki czemu użytkownicy są poprawnie grupowani.
Warto zapisać ten element jako wielokrotnie wykorzystywany składnik wszystkich kolejnych zapytań.
SELECT DATE_TRUNC('month', signup_date) AS cohort_month,
COUNT(*) AS cohort_size
FROM users
GROUP BY 1
ORDER BY 1;Okresy aktywności
Retencja mierzy aktywność w kolejnych miesiącach od rejestracji. Okres należy obliczyć jako różnicę między miesiącem zamówienia a miesiącem kohorty.
Okres 0 oznacza miesiąc rejestracji, okres 1 — następny miesiąc i tak dalej.
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;Budowanie macierzy retencji
Należy połączyć miesiąc kohorty i okres, a następnie policzyć liczbę unikalnych aktywnych użytkowników w każdej komórce. Wynikiem jest klasyczny trójkąt retencji.
Każdy wiersz to kohorta, a każda kolumna oznacza liczbę miesięcy od rejestracji.
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;Wskaźnik retencji
Surowe liczby trudno porównywać między kohortami o różnej wielkości. Liczbę aktywnych użytkowników w każdym okresie należy podzielić przez początkową wielkość kohorty.
Funkcja okna przenosi wielkość z okresu 0 do każdego wiersza, umożliwiając obliczenie ilorazu.
SELECT cohort, period,
active * 1.0 / FIRST_VALUE(active) OVER (
PARTITION BY cohort ORDER BY period
) AS retention
FROM cohort_activity;Czym jest lejek?
Lejek śledzi użytkowników przechodzących przez uporządkowane etapy: wizytę, rejestrację, dodanie do koszyka i zakup. Spadek między etapami pokazuje, gdzie tracą Państwo użytkowników.
Analiza lejka zamienia ogólne skargi na „niską konwersję” w konkretny etap, na którym występuje problem.
SELECT step, COUNT(DISTINCT user_id) AS users
FROM events
WHERE step IN ('visit', 'signup', 'cart', 'purchase')
GROUP BY step;Zliczanie poszczególnych etapów
Agregacja warunkowa zlicza użytkowników na każdym etapie w jednym przebiegu. FILTER zlicza unikalnych użytkowników, którzy dotarli do poszczególnych etapów.
Jedno zapytanie, jeden wiersz i cały lejek widoczny od razu.
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;Współczynniki konwersji etapów
Najważniejszą wartością jest iloraz kolejnych etapów. Dzielenie liczby użytkowników na każdym etapie przez liczbę użytkowników na poprzednim etapie ujawnia największy spadek.
Należy rzutować wartości na typ dziesiętny, aby dzielenie całkowitoliczbowe nie sprowadziło współczynnika do zera.
SELECT signups * 1.0 / NULLIF(visits, 0) AS visit_to_signup,
buyers * 1.0 / NULLIF(signups, 0) AS signup_to_buy
FROM funnel_counts;Uporządkowane lejki ze znacznikami czasu
Ścisły lejek wymaga, aby etapy następowały po sobie w odpowiedniej kolejności. Każdy etap należy połączyć z kolejnym tylko wtedy, gdy jego znacznik czasu jest późniejszy.
Zapobiega to zliczeniu zakupu, który nastąpił przed odpowiadającą mu rejestracją.
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';Lejek według kanału
Podział lejka według kanału pozyskania pokazuje, które źródła przyciągają użytkowników rzeczywiście dokonujących konwersji, a nie tylko klikających.
Należy grupować warunkowe zliczenia według kanału, aby porównać jakość poszczególnych źródeł.
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;Od wniosków do działania
Kohorty pokazują, czy retencja poprawia się z kolejnymi wydaniami, a lejki wskazują, który etap należy naprawić w pierwszej kolejności.
Razem przenoszą marketing od raportowania tego, co się wydarzyło, do diagnozowania przyczyn i określania, gdzie należy inwestować w dalszej kolejności.
Szybkie sprawdzenie
Lejek zlicza użytkowników na etapach wizyty, rejestracji i zakupu. Co pokazują ilorazy kolejnych etapów?
Podsumowanie
Kohorty grupują użytkowników według miesiąca rozpoczęcia i śledzą retencję w kolejnych okresach za pomocą DATE_TRUNC i AGE. Lejki zliczają unikalnych użytkowników na każdym uporządkowanym etapie i porównują ilorazy kolejnych etapów.
Mają już Państwo zaawansowany zestaw narzędzi do diagnozowania retencji i konwersji wyłącznie w SQL.
Ucz się Digital Marketing Academy dzięki korepetycjom AI — za darmo
Pisz i uruchamiaj kod w przeglądarce, otrzymuj natychmiastową pomoc od korepetytora AI dostępnego 24/7 i kontynuuj naukę w sieci lub w aplikacji.
- Kursy
- 63
- Lekcje
- 239
Często zadawane pytania
Czy lekcja „Zapytania kohortowe i lejkowe” jest bezpłatna?
Tak — pełny tekst „Zapytania kohortowe i lejkowe” 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 Digital Marketing Academy, przejdź na CoddyKit PRO. Kurs Digital Marketing Academy zawiera 4 lekcji w sumie.
Co nauczysz się w „Zapytania kohortowe i lejkowe”?
Uzyskiwanie odpowiedzi na pytania o retencję Ćwiczysz Digital Marketing Academy 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ąć Digital Marketing Academy?
Nie wymagamy żadnego doświadczenia. Digital Marketing Academy 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 4 z 4.
Ile czasu zajmuje lekcja „Zapytania kohortowe i lejkowe”?
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 Digital Marketing Academy?
Tak. Każda lekcja Digital Marketing Academy 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
- Dlaczego marketerzy uczą się SQL
- SELECT, WHERE, GROUP BY
- Łączenie tabel marketingowych
- Zapytania kohortowe i lejkowe