1Problem i cel projektu

Sama wartość sprzedaży nie pokazuje, jak zachowują się klienci ani które grupy mają największe znaczenie dla biznesu.

Celem projektu było odpowiedzenie m.in. na pytania:

• ilu klientów dokonuje kolejnych zakupów,
• jak szybko wracają po poprzednim zamówieniu,
• jak koncentruje się wartość zakupów,
• które segmenty RFM są najliczniejsze i generują najwyższą wartość dodatnich zakupów,
• jak zmienia się retencja kolejnych kohort,
• którzy klienci są aktywni, zagrożeni utratą lub nieaktywni,
• jak często klienci wracają po długiej przerwie,
• jak anulowania wpływają na ocenę wartości klienta,
• czy w danych występują powtarzalne wzorce sezonowe.

2Dane

Zbiór Online Retail II zawiera rzeczywiste transakcje brytyjskiego sklepu internetowego sprzedającego głównie artykuły upominkowe. Wielu klientów firmy to hurtownicy.

Źródło danych: Online Retail II – UCI Machine Learning Repository

Dane obejmują:

Jeden rekord nie oznacza całego zamówienia, dlatego przed analizą klienta dane zostały zagregowane z poziomu pozycji faktury do poziomu zamówienia.

3Kontrola jakości i przygotowanie danych

Przed rozpoczęciem analizy przeprowadziłam kontrolę kompletności, duplikatów, zakresów dat i nietypowych operacji.

Najważniejsze problemy jakości danych:

Brak customer_id uniemożliwia wykorzystanie tych rekordów w analizie RFM, kohort, retencji i historii konkretnego klienta.

Dwa źródła nakładały się w okresie od 1 do 9 grudnia 2010 r. Do usunięcia powtórzeń wynikających z nakładania wykorzystałam EXCEPT ALL, zachowując jednocześnie dane źródłowe w warstwie raw.

Następnie przygotowałam warstwę staging, w której rekordy zostały sklasyfikowane jako:

• sale,
• cancellation,
• stock_adjustment,
• zero_price,
• bad_debt_adjustment,
• manual_correction.

4Warstwa analityczna

Do analiz klientów wykorzystałam wyłącznie dodatnie, płatne transakcje posiadające identyfikator klienta.

Po zagregowaniu pozycji faktur do poziomu zamówień otrzymałam:

Widok zamówień klientów stał się podstawą dalszych analiz zachowań, wartości klienta, RFM, kohort, retencji i cyklu życia.

5Zachowania klientów

Analiza pokazała, że większość klientów w dostępnej historii dokonała więcej niż jednego zakupu.

Klienci powracający odpowiadali za około 95,61% wszystkich zamówień.

Średni czas pomiędzy pierwszym i drugim zaobserwowanym zamówieniem wyniósł 97,53 dnia, natomiast średni odstęp pomiędzy wszystkimi kolejnymi zamówieniami wyniósł 51,69 dnia.

6Wartość klientów i koncentracja

Dla każdego klienta obliczyłam liczbę zamówień, łączną wartość dodatnich zakupów oraz średnią wartość zamówienia.

Analiza koncentracji wykazała, że:

1 354 klientów, czyli 23,04% bazy, odpowiadało za 80,01% wartości dodatnich zakupów.

Oznacza to silną koncentrację wartości wśród części klientów, choć wynik nie odpowiada dokładnie klasycznej relacji 80/20.

7Segmentacja RFM

Dla każdego klienta obliczyłam:

• Recency – liczbę dni od ostatniego zamówienia,
• Frequency – liczbę zamówień,
• Monetary – łączną wartość dodatnich zakupów.

Wstępnie przetestowałam NTILE(4), jednak metoda dzieliła klientów o identycznej liczbie zamówień pomiędzy różne grupy. W finalnym rozwiązaniu zastosowałam PERCENT_RANK(), dzięki czemu klienci o tych samych wartościach metryk otrzymywali ten sam percentyl.

with rfm_percentiles_CTE as
(
    select
        *
        ,percent_rank() over (order by recency_days desc) as r_percentile
        ,percent_rank() over (order by frequency) as f_percentile
        ,percent_rank() over (order by monetary) as m_percentile
    from analytics.customer_rfm
)
select
    *
    ,case
        when frequency = 1 and r_percentile >= 0.75 then 'new_customer'
        when r_percentile >= 0.75 and f_percentile >= 0.75 and m_percentile >= 0.75 then 'champion'
        when r_percentile >= 0.50 and f_percentile >= 0.50 then 'loyal_customer'
        when r_percentile >= 0.50 and f_percentile < 0.50 then 'potential_loyalist'
        when r_percentile < 0.50 and (f_percentile >= 0.50 or m_percentile >= 0.75) then 'at_risk'
        else 'hibernating'
    end as rfm_segment
from rfm_percentiles_CTE;

Wyniki segmentacji

* new_customer oznacza klienta z jednym niedawnym zamówieniem w dostępnej historii danych, a nie potwierdzonego nowego klienta firmy.

Segment champion stanowił tylko 10,39% klientów, ale odpowiadał za ponad połowę wartości dodatnich zakupów.

8Analiza kohortowa i retencja

Klientów przypisałam do kohort na podstawie miesiąca pierwszego zamówienia zaobserwowanego w dostępnych danych.

Retencję obliczyłam jako udział klientów aktywnych w danym miesiącu względem początkowej liczebności kohorty.

Wybrane wyniki

Retencja nie spadała liniowo – klient mógł nie kupować w jednym miesiącu i wrócić w kolejnym.

W analizie rozróżniłam również:

• NULL – brak wystarczającego okresu obserwacji,
• 0% – okres był obserwowany, ale żaden klient nie wrócił.

Ważona retencja została wykorzystana jako bardziej reprezentatywna dla ogólnego zachowania klientów, ponieważ uwzględnia różną wielkość kohort.

9Cykl życia klienta i reaktywacja

Progi aktywności nie zostały przyjęte arbitralnie. Wyznaczyłam je na podstawie percentyli rzeczywistych odstępów pomiędzy zakupami.

Przyjęte statusy:

Za reaktywację uznałam zakup wykonany po przerwie dłuższej niż 147 dni.

Co najmniej jedną reaktywację miało 2 174 klientów, czyli 36,99% całej bazy.

Szczególnie interesująca okazała się grupa at_risk – 53,08% klientów z tego segmentu było już wcześniej reaktywowanych, co wskazuje na powtarzający się wzorzec długich przerw w zakupach.

10Anulowania i ich wpływ na wartość klienta

Anulowania zostały przeanalizowane jako osobny obszar, zamiast łączyć je bezpośrednio z wcześniejszymi analizami dodatnich zachowań zakupowych.

Analiza ujawniła przypadki, w których ranking oparty wyłącznie na dodatnich zakupach był mylący.

Przykład wpływu anulowań na ranking

Przypadek klienta 16446 pokazuje, że bardzo wysoka wartość dodatnich zakupów może zostać praktycznie całkowicie zneutralizowana przez anulowania.

11Sezonowość

Do porównania sezonowości wykorzystałam te same miesiące 2010 i 2011 r., wykluczając niepełny grudzień 2011.

Najbardziej powtarzalny wzorzec wystąpił jesienią.

W obu latach aktywność rosła od września, a listopad był miesiącem o najwyższej liczbie zamówień i najwyższej wartości zakupów.

Ze względu na dostępność tylko dwóch porównywalnych cykli rocznych wynik należy traktować jako sygnał sezonowości, a nie jej jednoznaczne potwierdzenie.

12Najważniejsze wnioski biznesowe

Analiza wskazuje, że:

• działalność w dużym stopniu opiera się na klientach dokonujących kolejnych zakupów,
• wartość zakupów jest silnie skoncentrowana wśród części klientów,
• segment champion ma bardzo duży udział w wartości dodatnich zakupów mimo niewielkiego udziału w bazie,
• istotna część klientów przechodzi w długie okresy nieaktywności,
• grupa at_risk często zawiera klientów, którzy już wcześniej wracali po długich przerwach,
• anulowania mogą istotnie zmienić ocenę wartości pojedynczych klientów,
• jesienią widoczny jest powtarzalny wzrost aktywności zakupowej.

13Ograniczenia analizy

Brak customer_id

22,77% rekordów nie posiada identyfikatora klienta. Rekordy te nie mogły zostać wykorzystane w analizie RFM, kohort, retencji ani historii pojedynczego klienta.

Początek historii klienta

Dane rozpoczynają się w grudniu 2009 r., dlatego pierwszy zakup klienta widoczny w zbiorze nie musi być jego pierwszym zakupem w firmie.

W związku z tym:

• first_purchase_date należy interpretować jako pierwszą zaobserwowaną datę zakupu,
• kohorty są kohortami pierwszego zaobserwowanego zakupu, a nie potwierdzonymi kohortami pozyskania,
• RFM, lifecycle i częstotliwość opisują zachowanie klienta w dostępnej historii, a nie jego pełną relację z firmą.

Anulowania

Segmentacja RFM została obliczona na podstawie dodatnich zamówień. Opisuje więc aktywność, częstotliwość i wartość dodatnich zakupów, a nie pełną wartość netto klienta po anulowaniach.

Niepełny ostatni miesiąc

Dane kończą się 9 grudnia 2011 r., dlatego miesiąc ten nie został wykorzystany do porównań sezonowych z pełnymi miesiącami.

14Dalszy rozwój projektu

W środowisku produkcyjnym analizę warto byłoby rozszerzyć o:

• pełniejszą historię transakcji sprzed grudnia 2009,
• rzeczywistą datę pozyskania lub rejestracji klienta,
• uzupełnienie brakujących customer_id z systemu źródłowego,
• jednoznaczne powiązanie anulowania z pierwotną transakcją,
• przeliczenie komponentu Monetary i segmentacji RFM na podstawie wartości netto,
• porównanie segmentacji brutto i netto,
• dłuższy okres danych do dokładniejszej oceny sezonowości.

15Czego się nauczyłam

Projekt pozwolił mi przećwiczyć nie tylko składnię SQL, ale przede wszystkim prowadzenie analizy od surowych danych do interpretacji biznesowej.

Wykorzystałam m.in.:

• strukturę raw → staging → analytics,
• kontrolę jakości i deduplikację,
• EXCEPT ALL,
• wieloetapowe CTE,
• FILTER,
• CASE,
• COALESCE,
• NULLIF,
• ROW_NUMBER(),
• LAG(),
• DENSE_RANK(),
• PERCENT_RANK(),
• FIRST_VALUE(),
• PERCENTILE_CONT(),
• analizę RFM,
• kohorty i retencję,
• analizę cyklu życia i reaktywacji.

Najważniejszym elementem projektu było dla mnie rozróżnienie pomiędzy wynikiem obliczenia a jego poprawną interpretacją. Analiza anulowań i ograniczonej historii klienta pokazała, że nawet poprawne technicznie zapytanie SQL może prowadzić do błędnego wniosku, jeśli nie uwzględni się kontekstu i ograniczeń danych.

16Repozytorium

Struktura skryptów:

01_database_setup_and_import.sql
02_data_quality.sql
03_data_preparation.sql
04_customer_behavior.sql
05_customer_value.sql
06_rfm_segmentation.sql
07_retention_and_cohort_analysis.sql
08_cancellations_and_seasonality.sql

Zobacz na GitHubie

1Business problem and project goal

Sales value alone does not show how customers behave or which groups have the greatest significance for the business.

The project aimed to answer questions including:

• how many customers make repeat purchases,
• how quickly they return after a previous order,
• how purchase value is concentrated,
• which RFM segments are the largest and generate the highest gross purchase value,
• how retention changes across successive cohorts,
• which customers are active, at risk of churn or inactive,
• how often customers return after a long gap,
• how cancellations affect customer value assessment,
• whether repeatable seasonal patterns are present in the data.

2Data

The Online Retail II dataset contains real transactions from a UK online retailer selling mainly gift items. Many of the company's customers are wholesalers.

Data source: Online Retail II – UCI Machine Learning Repository

The data includes:

One record does not represent an entire order, so before customer analysis the data was aggregated from invoice-line level to order level.

3Data quality and preparation

Before starting the analysis, I checked completeness, duplicates, date ranges and unusual operations.

The main data quality issues were:

Missing customer_id prevents these records from being used in RFM, cohort, retention and individual customer history analysis.

The two sources overlapped from 1 to 9 December 2010. To remove repetitions caused by the overlap, I used EXCEPT ALL while preserving the source data in the raw layer.

I then prepared the staging layer, where records were classified as:

• sale,
• cancellation,
• stock_adjustment,
• zero_price,
• bad_debt_adjustment,
• manual_correction.

4Analytics layer

For customer analysis, I used only positive, paid transactions with a customer identifier.

After aggregating invoice lines to the order level, I obtained:

The customer order view became the basis for further analysis of customer behavior, customer value, RFM, cohorts, retention and lifecycle.

5Customer behavior

The analysis showed that most customers made more than one purchase within the available history.

Returning customers accounted for approximately 95.61% of all orders.

The average time between the first and second observed order was 97.53 days, while the average gap between all consecutive orders was 51.69 days.

6Customer value and concentration

For each customer, I calculated the number of orders, total gross purchase value and average order value.

The concentration analysis showed that:

1,354 customers, representing 23.04% of the customer base, accounted for 80.01% of gross purchase value.

This shows a strong concentration of value among part of the customer base, although the result does not exactly match the classic 80/20 relationship.

7RFM segmentation

For each customer, I calculated:

• Recency – the number of days since the last order,
• Frequency – the number of orders,
• Monetary – the total gross purchase value.

I initially tested NTILE(4), but the method split customers with identical order counts across different groups. In the final solution, I used PERCENT_RANK(), so customers with the same metric values received the same percentile.

with rfm_percentiles_CTE as
(
    select
        *
        ,percent_rank() over (order by recency_days desc) as r_percentile
        ,percent_rank() over (order by frequency) as f_percentile
        ,percent_rank() over (order by monetary) as m_percentile
    from analytics.customer_rfm
)
select
    *
    ,case
        when frequency = 1 and r_percentile >= 0.75 then 'new_customer'
        when r_percentile >= 0.75 and f_percentile >= 0.75 and m_percentile >= 0.75 then 'champion'
        when r_percentile >= 0.50 and f_percentile >= 0.50 then 'loyal_customer'
        when r_percentile >= 0.50 and f_percentile < 0.50 then 'potential_loyalist'
        when r_percentile < 0.50 and (f_percentile >= 0.50 or m_percentile >= 0.75) then 'at_risk'
        else 'hibernating'
    end as rfm_segment
from rfm_percentiles_CTE;

Segmentation results

* new_customer means a customer with one recent order in the available data history, not a confirmed new customer of the company.

The champion segment represented only 10.39% of customers but accounted for more than half of gross purchase value.

8Cohort analysis and retention

I assigned customers to cohorts based on the month of the first order observed in the available data.

I calculated retention as the share of customers active in a given month relative to the initial cohort size.

Selected results

Retention did not decrease linearly – a customer could make no purchase in one month and return in a later month.

I also distinguished between:

• NULL – insufficient observation period,
• 0% – the period was observed, but no customer returned.

Weighted retention was used as a more representative measure of overall customer behavior because it accounts for differences in cohort size.

9Customer lifecycle and reactivation

The activity thresholds were not chosen arbitrarily. I derived them from percentiles of the actual gaps between purchases.

The adopted statuses were:

I defined reactivation as a purchase made after a gap longer than 147 days.

At least one reactivation was observed for 2,174 customers, representing 36.99% of the entire customer base.

The at_risk group was particularly interesting – 53.08% of customers in this segment had already been reactivated before, indicating a recurring pattern of long gaps between purchases.

10Cancellations and their impact on customer value

Cancellations were analyzed as a separate area rather than being combined directly with the earlier analyses of positive purchase behavior.

The analysis revealed cases where a ranking based only on positive purchases was misleading.

Example of the impact of cancellations on customer ranking

Customer 16446 shows that a very high gross purchase value can be almost completely neutralized by cancellations.

11Seasonality

To compare seasonality, I used the same months in 2010 and 2011, excluding incomplete December 2011.

The most repeatable pattern appeared in autumn.

In both years, activity increased from September, and November was the month with the highest number of orders and the highest purchase value.

Because only two comparable yearly cycles are available, the result should be treated as a signal of seasonality rather than definitive confirmation.

12Key business conclusions

The analysis indicates that:

• the business relies to a large extent on customers making repeat purchases,
• purchase value is strongly concentrated among part of the customer base,
• the champion segment has a very large share of gross purchase value despite representing a relatively small share of customers,
• a significant share of customers moves into long periods of inactivity,
• the at_risk group often includes customers who have previously returned after long gaps,
• cancellations can materially change the assessment of individual customer value,
• a repeatable increase in purchase activity is visible in autumn.

13Analysis limitations

Missing customer_id

22.77% of records do not have a customer identifier. These records could not be used in RFM, cohort, retention or individual customer history analysis.

Beginning of customer history

The data begins in December 2009, so the first purchase visible for a customer in the dataset does not have to be their first purchase from the company.

Therefore:

• first_purchase_date should be interpreted as the first observed purchase date,
• cohorts are based on the first observed purchase, not confirmed acquisition cohorts,
• RFM, lifecycle and frequency describe customer behavior within the available history, not the customer's full relationship with the company.

Cancellations

RFM segmentation was calculated using positive orders. It therefore describes activity, frequency and gross purchase value rather than the customer's full net value after cancellations.

Incomplete final month

The data ends on 9 December 2011, so this month was not used in seasonal comparisons with complete months.

14Further development

In a production environment, the analysis could be extended with:

• a more complete transaction history before December 2009,
• the actual customer acquisition or registration date,
• enrichment of missing customer_id values from the source system,
• unambiguous linking of a cancellation to the original transaction,
• recalculation of the Monetary component and RFM segmentation using net value,
• comparison of gross and net segmentation,
• a longer data period for a more reliable assessment of seasonality.

15What I learned

The project allowed me to practice not only SQL syntax but, above all, the process of conducting an analysis from raw data to business interpretation.

I used, among other things:

• the raw → staging → analytics structure,
• data quality checks and deduplication,
• EXCEPT ALL,
• multi-step CTEs,
• FILTER,
• CASE,
• COALESCE,
• NULLIF,
• ROW_NUMBER(),
• LAG(),
• DENSE_RANK(),
• PERCENT_RANK(),
• FIRST_VALUE(),
• PERCENTILE_CONT(),
• RFM analysis,
• cohorts and retention,
• customer lifecycle and reactivation analysis.

The most important part of the project for me was distinguishing between a calculation result and its correct interpretation. The analysis of cancellations and limited customer history showed that even a technically correct SQL query can lead to an incorrect conclusion if the context and limitations of the data are not taken into account.

16Repository

Script structure:

01_database_setup_and_import.sql
02_data_quality.sql
03_data_preparation.sql
04_customer_behavior.sql
05_customer_value.sql
06_rfm_segmentation.sql
07_retention_and_cohort_analysis.sql
08_cancellations_and_seasonality.sql

View on GitHub