SQL_2.sql

/*

PROJET 5 : SEGMENTEZ DES CLIENTS D'UN SITE e-COMMERCE

PARTIE 2 : transformez les données


/*

On va se concentrer sur la méthode RFM qui cible :

1- le montant : somme totale dépensée par un client

2- la fréquence : totalité des commandes passées par le client

3- la récence : différence entre la date la plus récente de la base et la date de la dernière commande du client 

*/



1- Montants par client


/* 
création d'une table des commandes qui référe les commandes (dates, prix, type de produit), les clients et les vendeurs associés
*/

select 
    customer_id, 
    order_status, 
    order_purchase_timestamp, 
    price, 
    product_id, 
    seller_id
from orders
left join order_items
using (order_id)
;


 /*
 On va se concentrer uniquement sur les commandes qui ont été livrées (donc payées)
 */


select 
    customer_id, 
    order_status, 
    order_purchase_timestamp, 
    price, 
    product_id, 
    seller_id
from orders
left join order_items
using (order_id)
where order_status='delivered'
;


/* 
Requête finale pour identifier les montants dépensés par les clients 
*/


with commandes_table as (
    select 
        customer_id, 
        order_status, 
        order_purchase_timestamp, 
        price, 
        product_id, 
        seller_id
    from orders
    left join order_items
    using (order_id)
    where order_status='delivered'
    )
   
select 
    customer_id, 
    sum(price) as total_commande
from commandes_table
group by customer_id
order by total_commande desc
;


/* 
On obtient ainsi la liste des clients avec le montant de leurs dépenses 
*/


2- Fréquence des commandes


/*
identifions la liste exaustive des clients, leur nombre de commandes en plus de la somme dépensée
*/


with commandes_table as (
    select 
        customer_id, 
        order_purchase_timestamp, 
        price, 
        product_id, 
        order_id,
        seller_id
    from orders
    left join order_items
    using (order_id)
    where order_status='delivered'
    ),

 customers_table as (  
    select 
        commandes_table.customer_id, 
        order_purchase_timestamp, 
        price, 
        order_id, 
        seller_id, 
        customers.customer_zip_code_prefix
    from commandes_table
    left join customers
    using (customer_id)
    )
    
select 
    customer_id, 
    sum(price) as total_commandes, 
    count(order_id) as nb_commandes 
from customers_table
group by customer_id
order by total_commandes desc
;

/*
on aurait 96 478 clients uniques
plus grosse somme versée : 13 440
plus grand nombre de commandes : 21
plus petite somme versée : 0.85
*/


3- Récence des commandes


with time_table as (
    select 
        max (order_purchase_timestamp) as last_date 
    from orders   
    ), 

commandes_table as (
    select 
        customer_id, 
        order_purchase_timestamp, 
        price, 
        order_id,
        seller_id
    from orders
    left join order_items
    using (order_id)
    ),

 customers_table as (  
    select 
        commandes_table.customer_id, 
        max (order_purchase_timestamp) over (partition by customer_id) as last_sale,
        price, 
        order_id, 
        seller_id, 
        customers.customer_zip_code_prefix
    from commandes_table
    left join customers
    using (customer_id)
    )


select
    customer_id,
    sum(price) as total_commandes, 
    count(order_id) as nb_commandes, 
    (select julianday (last_date) from time_table) - julianday (max (last_sale)) as recency,
    seller_id,
    customer_zip_code_prefix
from customers_table
group by customer_id

;

/*

On obtient une table de référence avec :

- les id_client uniques, 
- le montant de leurs sommes dépensées, 
- le total de leurs commandes passées,
- la récence de leur dernière commande,
- les vendeurs associés
- leur localisation géographique

*/

4- Calcul des scores RFM


/* 
Afin de pouvoir établir une classification des clients en fonction des 3 éléments (récence, fréquence et montant),
on va calculer le score RFM.

En général, on attribue un score de 1 à 5 pour chacune des trois catégories :
- 1 : score faible (montant faible / récence faible / fréquence faible)
- 5 : score le plus élevé (montant élevé / récence forte / fréquence élevée) <=> client

le score RFM pour chaque client est la combinaison finale de ces trois scores

*/



with time_table as (
    select 
        max (order_purchase_timestamp) as last_date 
    from orders   
    ), 

commandes_table as (
    select 
        customer_id, 
        order_purchase_timestamp, 
        price, 
        order_id,
        seller_id
    from orders
    left join order_items
    using (order_id)
    ),

 customers_table as (  
    select 
        commandes_table.customer_id, 
        max (order_purchase_timestamp) over (partition by customer_id) as last_sale,
        price, 
        order_id, 
        seller_id, 
        customers.customer_zip_code_prefix
    from commandes_table
    left join customers
    using (customer_id)
    ),

rfm_table as (
    select
        customer_id,
        sum(price) as total_commandes, 
        count(order_id) as nb_commandes, 
        (select julianday (last_date) from time_table) - julianday (max (last_sale)) as recency,
        seller_id,
        customer_zip_code_prefix
    from customers_table
    group by customer_id
),

rfm_score_table as (
    select 
        customer_id,
        total_commandes,
        nb_commandes,
        recency,
        ntile(5) over (order by recency asc) as R_score,
        ntile(5) over (order by nb_commandes asc) as F_score,
        ntile(5) over (order by total_commandes asc) as M_score
    from rfm_table
)

select 
    customer_id,
    total_commandes,
    nb_commandes,
    recency,
    R_score,
    F_score,
    M_score,
    cast (R_score as text) || cast (F_score as text) || cast (M_score as text) as RFM_Score
from rfm_score_table
order by rfm_score desc
;


/* 
On peut regarder 
1- combien de scores différents il existe dans notre base de données
2- combien de clients sont référencés / score
*/


with time_table as (
    select 
        max (order_purchase_timestamp) as last_date 
    from orders   
    ), 

commandes_table as (
    select 
        customer_id, 
        order_purchase_timestamp, 
        price, 
        order_id,
        seller_id
    from orders
    left join order_items
    using (order_id)
    ),

 customers_table as (  
    select 
        commandes_table.customer_id, 
        max (order_purchase_timestamp) over (partition by customer_id) as last_sale,
        price, 
        order_id, 
        seller_id, 
        customers.customer_zip_code_prefix
    from commandes_table
    left join customers
    using (customer_id)
    ),

rfm_table as (
    select
        customer_id,
        sum(price) as total_commandes, 
        count(order_id) as nb_commandes, 
        (select julianday (last_date) from time_table) - julianday (max (last_sale)) as recency,
        seller_id,
        customer_zip_code_prefix
    from customers_table
    group by customer_id
    ),

rfm_score_table as (
    select 
        customer_id,
        total_commandes,
        nb_commandes,
        recency,
        ntile(5) over (order by recency asc) as R_score,
        ntile(5) over (order by nb_commandes asc) as F_score,
        ntile(5) over (order by total_commandes asc) as M_score
    from rfm_table
    ),

rfm_sum as (
    select 
        customer_id,
        total_commandes,
        nb_commandes,
        recency,
        R_score,
        F_score,
        M_score,
        cast (R_score as text) || cast (F_score as text) || cast (M_score as text) as RFM_score
    from rfm_score_table
    order by RFM_score desc
)

select 
    distinct RFM_Score,
    count (customer_id)
from rfm_sum
group by RFM_Score
order by RFM_Score desc


;


/*

résultats :

555	2815
554	381
553	355
552	291
551	144
545	1180
544	2530
534	661
533	3233
523	383
522	3664
512	111
511	4140
455	2892
454	573
453	383
452	246
451	124
445	1137
444	2473
434	678
433	3267
423	314
422	3932
412	134
411	3735
355	2606
354	501
353	324
352	229
351	117
345	1232
344	2922
334	711
333	3359
323	356
322	3496
312	145
311	3890
255	2803
254	513
253	395
252	220
251	131
245	1303
244	2912
234	834
233	3187
223	366
222	3491
212	106
211	3627
155	2686
154	430
153	363
152	235
151	131
145	1234
144	2965
134	804
133	3154
123	449
122	3437
112	151
111	3850


*/