RFM ANALYSIS - MySQL & PYTHON¶

# Loading libraries
import pandas as pd
import time
import sqlalchemy
from sqlalchemy import create_engine
import pymysql

# Loading the connection information
%load_ext sql
%sql mysql+pymysql://***:***@localhost:3306/brazilecom          

%%sql
SELECT
    CAST(ord.order_purchase_timestamp AS DATE) AS orderDate,
    opay.payment_value AS amount,
    ord.order_id AS oid,
    cur.customer_unique_id AS cid,
    (SELECT 
            CAST(MAX(order_purchase_timestamp) AS DATE)
        FROM
            orders) AS maxDate
FROM
    orders AS ord
        JOIN
    customers AS cur ON ord.customer_id = cur.customer_id
        JOIN
    order_payments AS opay ON ord.order_id = opay.order_id
GROUP BY oid
LIMIT 5

%%sql
-- STEP 1
with temp as (
        SELECT 
        CAST(ord.order_purchase_timestamp AS DATE) AS orderDate,
        opay.payment_value AS amount,
        ord.order_id AS oid,
        cur.customer_unique_id AS cid,
        (SELECT 
                CAST(MAX(order_purchase_timestamp) AS DATE)
            FROM
                orders) AS maxDate
    FROM
        orders AS ord
            JOIN
        customers AS cur ON ord.customer_id = cur.customer_id
            JOIN
        order_payments AS opay ON ord.order_id = opay.order_id
    GROUP BY oid
        )

-- STEP 2
-- This 'rfm' table will run baseline r-f-m calculations per customer. 
-- Note: we only include customers with a spend above $100.
    SELECT
        datediff(maxDate, max(orderDate)) as Recency,
        count(cid) as Frequency,
        sum(amount) as Monetary,
        cid
    FROM temp
    GROUP BY cid
    HAVING monetary > 100
    ORDER BY monetary desc
limit 5

%%sql
-- STEP 1
with temp as (
        SELECT 
        CAST(ord.order_purchase_timestamp AS DATE) AS orderDate,
        opay.payment_value AS amount,
        ord.order_id AS oid,
        cur.customer_unique_id AS cid,
        (SELECT 
                CAST(MAX(order_purchase_timestamp) AS DATE)
            FROM
                orders) AS maxDate
    FROM
        orders AS ord
            JOIN
        customers AS cur ON ord.customer_id = cur.customer_id
            JOIN
        order_payments AS opay ON ord.order_id = opay.order_id
    GROUP BY oid
        ),

-- STEP 2
    -- This 'rfm' table will run baseline r-f-m calculations per customer. 
    -- Note: we only include customers with a spend above $100.
        rfm as (
        SELECT
            datediff(maxDate, max(orderDate)) as Recency,
            count(cid) as Frequency,
            round(sum(amount),2) as Monetary,
            cid
        FROM temp
        GROUP BY cid
        HAVING monetary > 100
        ORDER BY monetary desc
        )

-- STEP 3
-- This 'rfmRanking' table will give us an ordered ranking per each evaluation metric (r-f-m)
-- this is one area that can be tweaked for optimized groups. (10-decile, 20-twentile, etc)
SELECT *,
    (rRank + fRank + mRank) AS score
FROM (
    SELECT 
        Recency as R,
        Frequency as F,
        Monetary as M,
        NTILE(100) over(ORDER BY Recency DESC) as rRank,
        NTILE(100) over(ORDER BY Frequency) as fRank,
        NTILE(100) over(ORDER BY Monetary) as mRank,
        cid
    FROM rfm
    ) as sub1
ORDER BY score DESC
LIMIT 5

%%sql
-- STEP 1
with temp as (
        SELECT 
        CAST(ord.order_purchase_timestamp AS DATE) AS orderDate,
        opay.payment_value AS amount,
        ord.order_id AS oid,
        cur.customer_unique_id AS cid,
        (SELECT 
                CAST(MAX(order_purchase_timestamp) AS DATE)
            FROM
                orders) AS maxDate
    FROM
        orders AS ord
            JOIN
        customers AS cur ON ord.customer_id = cur.customer_id
            JOIN
        order_payments AS opay ON ord.order_id = opay.order_id
    GROUP BY oid
        ),

    -- STEP 2
    -- This 'rfm' table will run baseline r-f-m calculations per customer. 
    -- Note: we only include customers with a spend above $100.
        rfm as (
        SELECT
            maxDate,
            max(orderDate) as mostRecent,
            datediff(maxDate, max(orderDate)) as Recency,
            count(cid) as Frequency,
            round(sum(amount),2) as Monetary,
            cid
        FROM temp
        GROUP BY cid
        HAVING monetary > 100
        ORDER BY monetary desc
        ),

    -- STEP 3    
    -- This 'rfmRanking' table will give us an ordered ranking per each evaluation metric (r-f-m)
    -- this is one area that can be tweaked for optimized groups. (10-decile, 20-twentile, etc)
    rfmRanking as (
    SELECT *,
        (rRank + fRank + mRank) AS score
    FROM (
        SELECT 
            Recency as R,
            Frequency as F,
            Monetary as M,
            NTILE(100) over(ORDER BY Recency DESC) as rRank,
            NTILE(100) over(ORDER BY Frequency) as fRank,
            NTILE(100) over(ORDER BY Monetary) as mRank,
            cid
        FROM rfm
        ) as sub1
    ORDER BY score DESC
        )
    
-- STEP 4
-- This final query gives us the customer ranking/grouping by decreasing value and importance. 
-- 'GroupRank' = 1 will be our most valuable customers, while 10 is the least valuable.
-- This query gives us the average per each evaluation metric, but also a list to send to marketing.
-- The NTILE function allows us to tweaked and optimized groups (decile, twentile, etc),
-- so this query can be continually reassessed if need. It's also possible to further segment each
-- 'GroupRank' if needed, so we can split the top group to find the 'absolute' best customers.

select *,
    CASE 
    WHEN GroupRank >= 90 THEN 'Vip'
    WHEN GroupRank >= 75 THEN 'Loyal'
    WHEN GroupRank >= 50 THEN 'Insider'
    ELSE 'Starter'
    END AS Segment
FROM (SELECT *,
    NTILE(100) OVER(ORDER by score) as GroupRank
From rfmRanking) AS sub1
ORDER BY score DESC
limit 5

%%sql
-- STEP 1
with temp as (
        SELECT 
        CAST(ord.order_purchase_timestamp AS DATE) AS orderDate,
        opay.payment_value AS amount,
        ord.order_id AS oid,
        cur.customer_unique_id AS cid,
        (SELECT 
                CAST(MAX(order_purchase_timestamp) AS DATE)
            FROM
                orders) AS maxDate
    FROM
        orders AS ord
            JOIN
        customers AS cur ON ord.customer_id = cur.customer_id
            JOIN
        order_payments AS opay ON ord.order_id = opay.order_id
    GROUP BY oid
        ),

    -- STEP 2
    -- This 'rfm' table will run baseline r-f-m calculations per customer. 
    -- Note: we only include customers with a spend above $100.
        rfm as (
        SELECT
            maxDate,
            max(orderDate) as mostRecent,
            datediff(maxDate, max(orderDate)) as Recency,
            count(cid) as Frequency,
            sum(amount) as Monetary,
            cid
        FROM temp
        GROUP BY cid
        HAVING monetary > 100
        ORDER BY monetary desc
        ),

    -- STEP 3    
    -- This 'rfmRanking' table will give us an ordered ranking per each evaluation metric (r-f-m)
    -- this is one area that can be tweaked for optimized groups. (10-decile, 20-twentile, etc)
    rfmRanking as (
    SELECT *,
        (rRank + fRank + mRank) AS score
    FROM (
        SELECT 
            Recency as R,
            Frequency as F,
            Monetary as M,
            NTILE(100) over(ORDER BY Recency DESC) as rRank,
            NTILE(100) over(ORDER BY Frequency) as fRank,
            NTILE(100) over(ORDER BY Monetary) as mRank,
            cid
        FROM rfm
        ) as sub1
    ORDER BY score DESC
        ),
    
-- STEP 4
-- This final query gives us the customer ranking/grouping by decreasing value and importance. 
-- 'GroupRank' = 1 will be our most valuable customers, while 10 is the least valuable.
-- This query gives us the average per each evaluation metric, but also a list to send to marketing.
-- The NTILE function allows us to tweaked and optimized groups (decile, twentile, etc),
-- so this query can be continually reassessed if need. It's also possible to further segment each
-- 'GroupRank' if needed, so we can split the top group to find the 'absolute' best customers.
    RFMFinalTable as (
    select *,
        CASE 
        WHEN GroupRank >= 90 THEN 'Vip'
        WHEN GroupRank >= 75 THEN 'Loyal'
        WHEN GroupRank >= 50 THEN 'Insider'
        ELSE 'Starter'
        END AS Segment
    FROM (SELECT *,
        NTILE(100) OVER(ORDER by score) as GroupRank
    From rfmRanking) AS sub1
    ORDER BY score DESC)

SELECT Segment,
        round(avg(r)) as RecAvg,
        round(avg(r) / sum(avg(r)) over()*100,1) as RecPerc,
        round(avg(f), 2) as FreqAvg,
        round(avg(f) / sum(avg(f)) over()*100,1) as FreqPerc,
        round(avg(m)) as MonAvg,
        round(avg(m) / sum(avg(m)) over()*100,1) as MonPerc,
        count(cid) as CustCount,
        round(count(cid) / sum(count(cid)) over()*100,1) as CustPerc
--        ,group_concat(cid SEPARATOR ", ") CustomerList
FROM RFMFinalTable
GROUP BY Segment
ORDER BY GroupRank DESC

# Restructure dataframe for plotting
plotData = df.set_index('Segment').copy()
plotData = plotData[['RecPerc', 'FreqPerc', 'MonPerc']]
plotData.columns = ['Recency', 'Frequency', 'Monetary']
plotData

# Import libraries and plot
import matplotlib.pyplot as plt
import seaborn as sns
plt.style.use('ggplot')
sns.set_palette("Blues_d")

# Plot Visuals
fig, axes = plt.subplots(1,3, sharey=True, figsize=(12,8))
# fig.suptitle('Plot')

sns.barplot(x=plotData.index, y=plotData['Recency'], ax=axes[0])
axes[0].set_title('Recency - Lower is better')

sns.barplot(x=plotData.index, y=plotData['Frequency'], ax=axes[1])
axes[1].set_title('Frequency - Higher is better')

sns.barplot(x=plotData.index, y=plotData['Monetary'], ax=axes[2])
axes[2].set_title('Monetary - Higher is better')

plt.show()