KHIZHNYAKOVJak zrobić CRM w Excelu: klienci, transakcje i lejek sprzedaży
CRM w Excelu — klienci, transakcje, lejek sprzedaży (szablon) — Zobacz szablon →
Dla jednoosobowej firmy, małego zespołu handlowego czy konsultanta pełnowymiarowy system CRM bywa przerostem formy nad treścią: abonament, wdrożenie, szkolenia, dziesiątki pól, z których używa się trzech. A jednocześnie notatki rozsiane po skrzynce mailowej i telefonie oznaczają, że po kilku tygodniach nikt nie pamięta, komu miał oddzwonić.
Dobry, prosty CRM odpowiada na cztery pytania: kto jest klientem, jaka to transakcja, na jakim jest etapie i kiedy następny kontakt. W Excelu zbudujesz to w jeden wieczór. Poniżej konkretny schemat, formuły i typowe pułapki.
Struktura: cztery tabele zamiast jednej
Największy błąd to jedna ogromna tabela, w której klient pojawia się w kilku wierszach z różnymi wersjami nazwy. Rozdziel dane:
- Klienci: nazwa, osoba kontaktowa, telefon, e-mail, źródło pozyskania, data ostatniego kontaktu.
- Transakcje: klient, nazwa transakcji, etap, kwota, opiekun, planowana data zamknięcia.
- Kontakty: dziennik rozmów, wiadomości i spotkań z datą i krótką notatką.
- Parametry: lista etapów, prawdopodobieństwa, handlowców i źródeł.
Klienta wybierasz w transakcji z listy rozwijanej (Dane, Poprawność danych), a nie wpisujesz ręcznie. Dzięki temu dane się nie rozjadą.
Etapy lejka i prawdopodobieństwo
Zdefiniuj etapy, które odzwierciedlają Twój proces. Przykład dla usług B2B:
| Etap | Prawdopodobieństwo |
|---|---|
| Kontakt | 10% |
| Oferta | 30% |
| Negocjacje | 60% |
| Wygrana | 100% |
| Przegrana | 0% |
To liczby przykładowe, ustaw własne na podstawie historii. Po kilku miesiącach zobaczysz, ile procent ofert faktycznie się zamyka, i skorygujesz tabelę.
Oczekiwany przychód
Oczekiwany przychód to kwota pomnożona przez prawdopodobieństwo etapu. W kolumnie obok etapu użyj funkcji WYSZUKAJ.PIONOWO:
=D2*WYSZUKAJ.PIONOWO(C2;Parametry!$A$2:$B$10;2;FAŁSZ)
Przykład: trzy otwarte transakcje: 20 000 zł na etapie „Negocjacje” (60%), 8 000 zł na etapie „Oferta” (30%) i 45 000 zł na etapie „Kontakt” (10%). Oczekiwany przychód to 12 000 + 2 400 + 4 500 = 18 900 zł. Surowa suma 73 000 zł wygląda lepiej, ale to ta mniejsza liczba jest realna do planowania.
Lejek i konwersja
Lejek to po prostu liczba i kwota transakcji na każdym etapie. Policzysz je funkcjami LICZ.JEŻELI i SUMA.JEŻELI:
- liczba:
=LICZ.JEŻELI(Transakcje!C:C;A2) - kwota:
=SUMA.JEŻELI(Transakcje!C:C;A2;Transakcje!D:D)
Konwersja to udział wygranych w zamkniętych transakcjach. Jeśli z 40 zamkniętych transakcji wygrano 12, konwersja wynosi 12 / 40 = 30%. Średnia wartość wygranej transakcji to suma wygranych podzielona przez ich liczbę. Te dwie liczby pozwalają przewidzieć, ile ofert trzeba wysłać, żeby osiągnąć cel miesięczny.
Przypomnienia o kontaktach
Najcenniejsza funkcja prostego CRM-u to alarm, że klient czeka za długo. Dodaj kolumnę „Dni od kontaktu”: =DZIŚ()-F2, a formatowaniem warunkowym podświetl wiersze, w których wartość przekracza np. 14 dni. Co poniedziałek filtrujesz czerwone wiersze i masz gotową listę telefonów.
Najczęstsze błędy
- Brak dyscypliny: CRM działa tylko wtedy, gdy każdy kontakt trafia do dziennika w dniu, w którym się odbył.
- Zbyt wiele etapów. Pięć, sześć w zupełności wystarczy.
- Wpisywanie klientów ręcznie za każdym razem, co daje dublety.
- Brak daty następnego kontaktu przy otwartej transakcji.
Gotowy szablon: CRM w Excelu
Jeśli wolisz gotowe rozwiązanie, jest szablon CRM na zwykłych formułach. Jest płatny, działa w Excelu 2007 i nowszym oraz w LibreOffice, bez makr, a plik jest po polsku. Poprawki i nowe wersje dostajesz bezpłatnie.
W „Parametrach” ustawiasz menedżerów, źródła, etapy lejka i prawdopodobieństwa. Arkusz „Klienci” mieści do 300 klientów z datą ostatniego kontaktu, „Transakcje” do 500 z etapem, kwotą, oczekiwanym przychodem i statusem kontaktu, a w „Kontaktach” prowadzisz dziennik rozmów. „Lejek” pokazuje transakcje i kwoty według etapów, konwersję i średnią wartość, a „Menedżerowie” wyniki według pracowników. Przeterminowane kontakty są podświetlone. W pakiecie jest pusta tabela i wypełniony przykład z fikcyjnymi danymi. To dobre rozwiązanie dla właścicieli małych firm, handlowców, konsultantów i freelancerów, którzy chcą mieć porządek w klientach bez drogiej subskrypcji.
FAQ
Czy Excel wystarczy jako CRM?
Dla kilku do kilkuset klientów i kilku osób w zespole tak. Granicą jest zwykle praca wielu osób jednocześnie, automatyczne wysyłanie maili czy integracje, a tego Excel nie zastąpi.
Jak uniknąć dubli klientów?
Wybieraj klienta w transakcji z listy rozwijanej i regularnie sprawdzaj powtórzenia (Formatowanie warunkowe, Wyróżnij reguły komórek, Zduplikowane wartości).
Jak często aktualizować CRM?
Najlepiej na bieżąco, po każdej rozmowie. Jeśli to niemożliwe, wyznacz stałą porę, np. koniec dnia, na uzupełnienie dziennika kontaktów.
Czy można policzyć wyniki poszczególnych handlowców?
Tak. Dodaj w transakcjach kolumnę „Opiekun” i użyj SUMA.JEŻELI oraz LICZ.JEŻELI według tej kolumny.
