Jak liczyć retencję klientów i LTV w arkuszu kalkulacyjnym krok po kroku

Zrozumiesz, jak poprawnie zorganizować dane zakupowe w kohorty, obliczyć retencję procentową, Net Dollar Retention oraz Customer Lifetime Value.

Jedynym miarodajnym sposobem pomiaru retencji oraz LTV jest analiza kohortowa powtarzalnych zakupów w czasie.

▶ Kliknij czas, aby przeskoczyć do tego miejsca w filmie

1. 00:20Zasada działania tabeli kohortowej

   Autor wyjaśnia ideę grupowania klientów według miesiąca pierwszego zakupu oraz monitorowania ich aktywności w kolejnych okresach.

   • Dane są organizowane według miesiąca pierwszego zakupu jako punktu wyjścia relacji.
   • Każda kohorta śledzi strumień kolejnych transakcji przypisanych do tego samego zbioru klientów.
   • Suma zamówień w danym miesiącu kalendarzowym wymaga odczytu po przekątnej tabeli.
2. 04:00Formatowanie danych transakcyjnych w Excelu

   Omówienie technicznych aspektów pobierania danych z bazy SQL i przygotowania struktury siatki kohortowej.

   • W startupach dane wyciąga data scientist za pomocą SQL bezpośrednio z bazy.
   • Nieuwzględnione miesiące z przyszłości przyjmują wartości zerowe na przekątnych.
3. 04:12Kalkulacja procentowej retencji klientów

   Wprowadzenie formuły dzielącej liczbę powracających klientów przez wielkość pierwotnej kohorty z zablokowaną kolumną.

   • Formuła dzieli bieżący miesiąc przez miesiąc zerowy kohorty z blokadą kolumny.
   • Miesiąc zerowy zawsze reprezentuje 100 procent bazy wyjściowej danej grupy.
   • W przykładzie kohorty styczniowej po 12 miesiącach pozostało 26 procent klientów.
4. 07:30Analiza Net Dollar Retention

   Prezentacja przychodów generowanych przez kohorty i wyliczanie retencji netto wartości dolara z uwzględnieniem dosprzedaży.

   • Przychód jest ważniejszy niż liczba osób, bo klienci generują zmienne koszyki zakupowe.
   • Wskaźnik NDR może przekraczać wartość 100 procent dzięki ekspansji przychodów.
   • W najlepszych firmach subskrypcyjnych retencja NDR przekracza 150 procent.
5. 10:20Kumulacyjny przychód z kohorty

   Budowa tabeli sumującej narastająco wszystkie wpływy pieniężne uzyskane od danej grupy klientów w czasie.

   • Formuła dodaje przychód bieżący do sumy poprzednich miesięcy kohorty.
   • Kohorta styczniowa wygenerowała łącznie 14200 dolarów przychodu w 11 miesięcy.
   • Dane kumulacyjne pozwalają przewidzieć przyszły przychód z nowych klientów.
6. 12:00Wyliczenie LTV i porównanie z CACKluczowy moment

   Przeliczenie kumulacyjnego przychodu na pojedynczego klienta, uwzględnienie marży brutto i ocena opłacalności akwizycji.

   • Średni przychód na klienta z kohorty wyniósł 406 dolarów po roku.
   • Mnożenie przychodu przez marżę daje rzeczywiste Customer Lifetime Value.
   • Zwrot z wydatku na CAC nastąpił w czwartym miesiącu relacji z klientem.

1. 01
   Zbuduj tabelę transakcji według kohort

   KtoAnalityk danych

   Jak to zrobić

   • Zgrupuj klientów w wierszach według miesiąca ich pierwszego zakupu.
   • Wpisz kolejne miesiące relacji w kolumnach od zera wzwyż.
   • Wprowadź liczbę aktywnych klientów kupujących w każdym kolejnym okresie.

   Po co

   Uporządkowanie danych transakcyjnych w jednolitym układzie kohortowym.

   Umożliwia to śledzenie zachowań konkretnych grup klientów bez mieszania ich ze świeżo pozyskanymi użytkownikami.

   ▶ 01:15 w filmie
2. 02
   Oblicz procentową retencję bazy

   KtoMenedżer e-commerce

   Jak to zrobić

   • Podziel liczbę klientów z danego miesiąca przez wartość z miesiąca zerowego.
   • Zablokuj kolumnę miesiąca zerowego za pomocą znaku dolara w formule.
   • Przeciągnij formułę na pozostałe komórki z wyłączeniem przyszłych okresów.

   Po co

   Poznanie tempa utraty kupujących w kolejnych miesiącach od akwizycji.

   Pozwala ocenić stabilność bazy klientów oraz jakość poszczególnych roczników zakupowych.

   ▶ 04:15 w filmie
3. 03
   Przelicz Net Dollar Retention

   KtoMenedżer finansowy

   Jak to zrobić

   • Wypełnij siatkę wartością sprzedaży wygenerowaną przez poszczególne kohorty.
   • Zastosuj formułę retencji do przychodów odnosząc każdy miesiąc do miesiąca zerowego.
   • Zidentyfikuj wartości powyżej 100 procent świadczące o dosprzedaży.

   Po co

   Monitorowanie finansowej retencji bazy klientów.

   Przychód jest ważniejszy niż liczba użytkowników, ponieważ aktywni klienci mogą z czasem wydawać więcej.

   ▶ 08:32 w filmie
4. 04
   Wyznacz LTV w oparciu o marżę brutto

   KtoDyrektor finansowy

   Jak to zrobić

   • Zsumuj kumulacyjny przychód kohorty dodając kolejne miesiące narastająco.
   • Podziel sumę przez liczbę klientów początkowych uzyskując przychód na klienta.
   • Pomnóż wynik przez marżę brutto w celu uzyskania rzeczywistego LTV.

   Po co

   Ustalenie realnego zysku brutto przypadającego na pozyskanego klienta.

   Umożliwia rzetelne zestawienie wartości życiowej klienta z kosztem CAC i wyznaczenie momentu zwrotu z marketingu.

   ▶ 12:05 w filmie