Jak obliczyć wartość życiową klienta z pliku CSV (krok po kroku)
Aby obliczyć wartość życiową klienta (customer lifetime value) z pliku CSV z transakcjami, należy obliczyć średnią wartość zamówienia, częstotliwość zakupów i długość życia klienta. Pomnóż te trzy wartości przez siebie, a następnie uwzględnij marżę brutto. Arytmetyka jest prosta. To decyzje uznaniowe — których klientów uznać za utraconych (churned) i czy patrzeć wstecz, czy w przyszłość — decydują o tym, czy wynik będzie użyteczny.
Dwa zespoły mogą zastosować ten sam wzór na tym samym pliku i otrzymać dwa wyniki różniące się trzykrotnie. Żaden z nich nie jest błędny. Po prostu odpowiedziały na różne pytania, nawet tego nie zauważając.
Ten przewodnik opisuje potrzebne dane wejściowe, ręczne obliczenia w trzech etapach oraz moment, w którym arkusz kalkulacyjny przestaje wystarczać.
Co jest potrzebne przed rozpoczęciem
Potrzebujesz eksportu na poziomie transakcji: jeden wiersz na zamówienie, zawierający identyfikator klienta, datę zamówienia i wartość zamówienia. Podsumowanie miesięczne nie zadziała, ponieważ wartość życiowa klienta opiera się zasadniczo na tym, jak poszczególni klienci zachowują się w czasie.
Przed zastosowaniem jakiegokolwiek wzoru należy podjąć dwie decyzje, które wpływają na wynik bardziej niż same obliczenia.
Historyczna czy predykcyjna. Historyczna wartość życiowa klienta sumuje to, co klienci faktycznie wydali do tej pory. Predykcyjna szacuje, ile wydadzą w przyszłości. Wariant historyczny jest łatwy do obrony i zawsze zaniża wynik; wariant predykcyjny jest bardziej przydatny do budżetowania i zależy całkowicie od przyjętych założeń dotyczących retencji.
Co uznaje się za odejście (churn). W przypadku subskrypcji jest to proste — decyduje data rezygnacji. W biznesach transakcyjnych nie ma zdarzenia rezygnacji, więc potrzebujesz reguły. Na przykład klient, który nie dokonał zakupu od dwunastu miesięcy, jest traktowany jako utracony. Wybierz przedział czasowy i go zapisz.
Dobry wynik wygląda następująco. Otrzymujesz tabelę dla każdego klienta z łącznymi wydatkami, liczbą zamówień, datą pierwszego i ostatniego zamówienia oraz etykietą segmentu. Obok znajduje się główny wskaźnik wraz z podanym założeniem dotyczącym marży.
Jak to zrobić ręcznie
Opcja 1: Oblicz średnią wartość zamówienia i częstotliwość zakupów
Pogrupuj plik transakcji według identyfikatora klienta. Dla każdego klienta zsumuj wartości zamówień i policz zamówienia. Średnia wartość zamówienia to całkowity przychód podzielony przez łączną liczbę zamówień w całej bazie. Częstotliwość zakupów to łączna liczba zamówień podzielona przez liczbę unikalnych klientów w danym okresie.
Pułapką jest tutaj okres. Częstotliwość mierzona w ciągu dwunastu miesięcy i częstotliwość mierzona w całej historii to różne liczby, a połączenie ich w jednym wzorze daje bezużyteczny wynik. Najpierw ustal przedział czasowy i stosuj go konsekwentnie.
Opcja 2: Dodaj krzywą retencji
Długość życia klienta to parametr, który większość osób zgaduje. Wersja poparta twardymi danymi pochodzi z Twoich własnych informacji: pogrupuj klientów według miesiąca pozyskania, a następnie policz, ilu z nich było aktywnych w każdym kolejnym miesiącu.
To daje krzywą retencji. Średnia długość życia to w przybliżeniu obszar pod nią — w arkuszu kalkulacyjnym jest to suma miesięcznych wskaźników retencji. Jeśli 100 klientów utrzymuje się na poziomie 80%, 65% i 55% w ciągu trzech miesięcy, kumulujesz długość życia miesiąc po miesiącu, zamiast zakładać zaokrągloną liczbę. Nasz przewodnik po tworzeniu wykresu retencji kohortowej omawia kwestie wizualne, a artykuł o tym, czym jest analiza kohortowa, wyjaśnia samą koncepcję.
Opcja 3: Przekształć to w LTV i wskaźnik LTV:CAC
Pomnóż średnią wartość zamówienia przez częstotliwość zakupów i długość życia klienta, a następnie pomnóż przez marżę brutto. Wartość życiowa oparta na przychodach zawyża ten wskaźnik, czasem bardzo mocno, ponieważ traktuje produkt o niskiej marży tak samo jak produkt o wysokiej marży.
Następnie podziel wynik przez koszt pozyskania klienta (CAC). To właśnie do wyznaczenia tego stosunku służy ta liczba. Sama wartość życiowa klienta to tylko ciekawostka; wartość życiowa zestawiona z kosztem jej pozyskania to podstawa do podjęcia decyzji.
Gdzie metoda ręczna przestaje się sprawdzać
Segmentacja to moment, w którym wszystko się sypie. Pojedynczy, uśredniony wskaźnik łączy najlepsze i najgorsze kohorty w jedną, nic niemówiącą liczbę. Użyteczna wersja to podział na kanały, plany lub miesiące pozyskania. Każdy segment oznacza konieczność przebudowania całego łańcucha wzorów dla przefiltrowanego podzbioru danych. Zazwyczaj segmentów wartych sprawdzenia jest więcej, niż ktokolwiek ma cierpliwości analizować, więc większość pozostaje niezbadana.
Drugim problemem jest ponowne uruchamianie obliczeń. Plik z kolejnego kwartału ma więcej wierszy i często inaczej nazwane kolumny. Siatka retencji, której zbudowanie zajęło całe popołudnie, wymaga rozszerzenia zakresów i ponownego sprawdzenia formuł. Pominięty zakres jest niewidoczny w wynikach, co czyni go niebezpiecznym.
Ponadto same założenia są niewidoczne. Pół roku później plik pokazuje liczbę i nikt nie pamięta, czy opierała się ona na przychodach, czy na marży, ani jaki przedział czasowy dla odejść (churn) został zastosowany. W ten sposób nieaktualne dane trafiają do prezentacji dla zarządu.
Istnieje też subtelniejszy problem. Ponieważ odbudowanie modelu jest kosztowne, zazwyczaj uruchamia się go raz, a potem cytuje przez rok. Wartość życiowa klienta nie jest stałą. Zmienia się, gdy zmieniają się ceny, gdy struktura pozyskiwania przesuwa się w stronę tańszego kanału oraz gdy zmiana w produkcie wpływa na retencję. Wynik obliczony zeszłej wiosny może opisywać biznes, który już nie istnieje.
Jak obliczyć wartość życiową klienta za pomocą Powerdrill Bloom
Krok 1: Prześlij swoje dane
Prześlij plik CSV z transakcjami bezpośrednio do programu. Powerdrill Bloom profiluje kolumny, dzięki czemu możesz potwierdzić, że masz użyteczny identyfikator klienta i czystą kolumnę z datami, zanim dojdzie do jakiegokolwiek grupowania.
Krok 2: Opisz obliczenia w języku naturalnym
Przedstaw reguły tak, jakbyś tłumaczył je koledze z pracy. Potraktuj klientów bez zakupu od dwunastu miesięcy jako utraconych, zastosuj określony procent marży brutto i oblicz wartość życiową klienta według kanału pozyskania. Następnie zadaj kluczowe pytania uzupełniające — jak wynik różni się między najwyższym a najniższym kwartylem lub jak zmieniał się w poszczególnych kohortach pozyskania.
Krok 3: Wyeksportuj wykres, raport lub prezentację
Pobierz tabelę z podziałem na segmenty, powiązaną z nią krzywą retencji lub pisemne podsumowanie z założeniami zapisanymi obok wyniku.
Dlaczego to rozwiązanie jest lepsze niż odbudowywanie modelu co kwartał
| Tradycyjny arkusz kalkulacyjny | Powerdrill Bloom | |
|---|---|---|
| Segmentacja według kanału | Odbudowanie łańcucha formuł dla każdego segmentu | Dodanie tego do zapytania |
| Zmiana przedziału czasowego dla odejść (churn) | Przebudowanie siatki retencji | Zadanie pytania ponownie z nowym przedziałem |
| Zapisywanie założeń | Ręczna notatka, często pomijana | Określone bezpośrednio w zapytaniu |
| Plik z kolejnego kwartału | Rozszerzenie zakresów, sprawdzenie formuł | Przesłanie pliku i ponowne zadanie pytania |
Drugi wiersz to ten, który zmienia to, czego się dowiadujesz. Dwunastomiesięczny i sześciomiesięczny przedział czasowy dla odejść mogą dać skrajnie różne wartości życiowe klienta. Rzetelnym sposobem na poradzenie sobie z tym jest przetestowanie obu wariantów i sprawdzenie, czy Twoje wnioski pozostają aktualne. W arkuszu kalkulacyjnym nikt nie robi tego dwukrotnie.
Najczęstsze błędy
Używanie przychodu zamiast marży. Wartość życiowa oparta na przychodach sztucznie upiększa wyniki każdego biznesu i mocno zniekształca wszelkie porównania z kosztem pozyskania.
Łączenie wszystkich klientów w jeden wskaźnik. Średnia dla bazy o bardzo zróżnicowanych segmentach nie opisuje żadnego rzeczywistego klienta. Dokonaj segmentacji przed wyciągnięciem wniosków.
Zgadywanie długości życia klienta. „Około dwóch lat” to najczęstsza wartość wejściowa i zarazem najtrudniejsza do obrony. Zamiast tego wyznacz ją na podstawie własnej krzywej retencji, nawet jeśli jest ona krótka.
Ignorowanie efektu przeżywalności (survivor effect). Historyczna wartość życiowa uwzględnia tylko tych klientów, którzy już istnieli, co faworyzuje tych z długim stażem. Obliczenia oparte na kohortach pozwalają tego uniknąć.
Raportowanie bez uwzględnienia CAC. Wartość życiowa na poziomie $900 nic nie znaczy, dopóki nie wiesz, czy pozyskanie kosztowało $90, czy $800.
Traktowanie wskaźnika jako trwałego. Przeliczaj go za każdym razem, gdy znacząco zmieniają się ceny, produkt lub struktura pozyskiwania klientów. Liczba przeniesiona z zeszłego roku to tylko zgadywanie ubrane w miejsca po przecinku.
Podsumowanie
Obliczanie wartości życiowej klienta z pliku CSV składa się z trzech etapów. Średnia wartość zamówienia i częstotliwość, krzywa retencji z własnych kohort, a następnie marża i stosunek do kosztu pozyskania. Robienie tego w arkuszu kalkulacyjnym działa — dopóki nie potrzebujesz segmentacji lub przetestowania innego przedziału czasowego dla odejść.
Jeśli model jest odbudowywany tylko wtedy, gdy ktoś o to poprosi na spotkaniu, wypróbuj Powerdrill Bloom na swoim eksporcie transakcji. Nasza strona poświęcona asystentowi AI do plików CSV opisuje szerszy proces pracy, a artykuł o zamianie pliku CSV w wykres przedstawia kwestie wizualizacji danych.
Najczęściej zadawane pytania
Jaki jest wzór na wartość życiową klienta?
Pomnóż średnią wartość zamówienia przez częstotliwość zakupów i średnią długość życia klienta, a następnie pomnóż przez marżę brutto. Użycie przychodu zamiast marży zawyża wynik i sprawia, że jakiekolwiek porównanie z kosztem pozyskania staje się niewiarygodne.
Jak obliczyć wartość życiową klienta w Excelu?
Pogrupuj plik transakcji według identyfikatora klienta, aby uzyskać liczbę zamówień i sumy. Zbuduj siatkę retencji kohortowej, aby określić długość życia klienta, a następnie połącz te trzy parametry wejściowe. Tworzenie siatki retencji to najbardziej pracochłonna część, którą trzeba powtarzać przy każdej zmianie przedziału czasowego.
Jaka jest różnica między historycznym a predykcyjnym LTV?
Historyczny wskaźnik sumuje to, co klienci już wydali, więc zawsze zaniża wynik i jest łatwy do obrony. Predykcyjny szacuje przyszłe wydatki na podstawie założeń dotyczących retencji, co czyni go bardziej przydatnym do planowania, ale też bardziej podatnym na błędy.
Jaki jest dobry stosunek LTV do CAC?
Powszechnie wskazywany cel to trzy do jednego, ale zależy to od struktury marży i okresu zwrotu. Bardziej przydatnym pytaniem jest to, ile miesięcy zajmuje klientowi zwrot kosztów jego pozyskania.
Jak postępować z klientami, którzy nie dokonali ostatnio żadnego zakupu?
Zdefiniuj przedział czasowy dla odejść (churn window) i stosuj go konsekwentnie — dwanaście miesięcy bez zakupu to powszechna reguła w biznesach transakcyjnych. Zapisz ten przedział obok wyniku, ponieważ jego zmiana może znacząco wpłynąć na wartość życiową klienta.