Analiza koszykowa jest badaniem preferencji zakupowych klientów przez pryzmat zawartości ich koszyków. Pozwala uzyskać reguły na zasadzie: jeśli klient kupuje piwo, to kupuje suche przekąski (chipsy, snacki, paluszki) – temat przy okazji mundialu bardzo na czasie. Jak wykonać ją w Excelu i jak analizować ją graficznie?

Opisywany temat jest na styku obu blogów, które prowadzę – SkuteczneRaporty.pl i ExcelBI.pl. Umieściłem go finalnie tutaj, choć będziemy posługiwać się technikami związanymi z modelem danych Power Pivot, który zwykle trafia na blog o dodatkach Power w Excelu. Wynika to z faktu, że kończymy analizę mapą cieplną, która jest jedną z prostszych technik analitycznych w tabeli przestawnej. Niemniej opisywana analiza, która przydaje się np. do planowania rozmieszczenia produktów na półkach lub w gazetkach, ma też identyczny przebieg w Power BI.

Krok 1: Przygotuj model danych w Power Pivot

Poprzez Power Query ładuję do modelu danych tabelę transakcyjną (faktów) Dane, tabelę słownikową Produkty oraz… Produkty po raz drugi pod nazwą Produkty Filtr. Następnie buduję relacje w modelu danych, przy czym relacja z tą drugą ma być nieaktywna, o czym dalej.

Krok 2: Zbuduj miarę w tabeli Dane

Buduję miarę w tabeli Dane:

Sprzedane w tym samym zamówieniu:=CALCULATE (
COUNTROWS ( Dane);
CALCULATETABLE (
SUMMARIZE (
Dane;
Dane[Nr zamówienia]
);
ALL ( Produkty );
USERELATIONSHIP ( Dane[Produkt Id];'Produkty Filtr'[Produkt Id])
)
)

Miara ta liczy, ile transakcji zostało wykonanych w tabeli Dane, ale zanim się policzy, ogranicza się tylko do tych zamówień, które zostały wybrane w tabeli filtrującej Produkty Filtr. Jest to konieczne, gdyż standardowo filtrując tabelę przestawną, widzimy tylko wybrane produkty, a inne znikają. Formuła wymaga włączenia nieaktywnej relacji poprzez formułę języka DAX o nazwie USERELATIONSHIP.

Krok 3: Zbuduj tabelę przestawną

Tabela przestawna pozwoli odpowiedzieć na pytanie: Co kupowali klienci, którzy kupili np. stoły? Pytania te zadajemy poprzez fragmentator, który koniecznie pochodzi z tabeli Produkty Filtr, zaś w tabeli przestawnej umieszczamy w wierszach pola z normalnej tabeli Produkty.

Analiza taka może odbywać się na poziomie Kategorii, Podkategorii czy – częściej – samych Nazw produktów. Mówi nam, że zamówień na stoły było 399, a razem ze stołami klienci najczęściej wkładali do koszyka papier i telefony.

Krok 4: Zbuduj macierz produktową

Gdy lekko zmodyfikujemy naszą miarę, możemy umieścić ją w macierzy produktów, aby sprawdzić, które produkty z całego portfolio są kupowane najczęściej. Ta technika jest już bardziej graficzna.

Sprzedane w tym samym zamówieniu Macierz :=
IF (
MIN(Produkty[Produkt Id])
<> MIN('Produkty Filtr'[Produkt Id]);
[Sprzedane w tym samym zamówieniu]
)

Miara odfiltrowuje wybrany produkt, który jak w kroku 3 nadal jest widoczny i zaburza kolorowanie macierzy.

Mając miarę, wstawiamy tabelę przestawną, w której w wierszach ląduje pole z tabeli Produkty (np. Nazwa produktu lub Kategoria produktu), a w kolumnach analogiczne pole, ale z tabeli Produkty Filtr.

Następnie, posługując się techniką mapy cieplnej, tworzymy obraz zależności. Przykładowy wniosek: klienci kupujący telefony kupują też produkty z podkategorii papier. Białe pola na przekątnej wynikają z tego, że miarą wyłączyliśmy liczenie samych siebie, jeśli chodzi o produkty.

Ta analiza zadziała w identyczny sposób w Power BI, gdzie znajdziemy te same mechanizmy Power Query i Power Pivot oraz formatowanie warunkowe wizualizacji Macierz.

Pobierz plik z analizą koszykową w Excelu

Tu możesz pobrać plik Excel z gotowymi formułami analizy koszykowej.