rfm_analysis.sql

SELECT * FROM public.sales_data_sample

--Identifying unique values--

SELECT DISTINCT status FROM public.sales_data_sample
SELECT DISTINCT year_id FROM public.sales_data_sample
SELECT DISTINCT product_line FROM public.sales_data_sample
SELECT DISTINCT country FROM public.sales_data_sample
SELECT DISTINCT deal_size FROM public.sales_data_sample
SELECT DISTINCT territory FROM public.sales_data_sample

--ANALYSIS--

-- Grouping sales by product line
-- exp: to see total revenue generated by each product line
SELECT product_line, sum(sales) as revenue
FROM public.sales_data_sample
GROUP BY product_line
ORDER BY 2 DESC

-- Grouping sales across years
-- exp: to see total revenue per year
SELECT year_id, sum(sales) as revenue
FROM public.sales_data_sample
GROUP BY year_id
ORDER BY 2 DESC

-- Grouping Sales Operations within the year
-- exp: to see months of operations in the year 
SELECT DISTINCT month_id 
FROM public.sales_data_sample
WHERE year_id = '2005' -- change year_id


-- Grouping sales by deal size
-- exp: to see total revenue per deal size
SELECT deal_size, sum(sales) as revenue
FROM public.sales_data_sample
GROUP BY deal_size
ORDER BY 2 DESC


-- What was the best month for sales in a specific year?
-- How much was earned in that month?

SELECT month_id, sum(sales) as revenue, Count(order_number) as frequency
FROM public.sales_data_sample
WHERE year_id = '2003' -- change year_id to see performance of other years
GROUP BY month_id
ORDER BY 2 DESC

-- Why is November the best performing month across years?
-- What product is sold in November?
-- What product perfrorms best in November

--exp: to see revenue & frequency of purchase of product lines in a given month & year 
SELECT month_id, product_line, sum(sales) as revenue, count(order_number) as frequency
FROM public.sales_data_sample
WHERE year_id ='2003' and month_id = '11' -- change ids to see performance
GROUP BY month_id, product_line
ORDER BY 3 desc


-- Who are our customers?

SELECT
		Customer_name,
		sum(sales) as monetary_value, --How much do they bring in?
		avg(sales) as avgmonetaryvalue,-- On average, how much do they bring in?
		count(order_number) as frequency, --how often do they purchase from us?
		max(order_date) as last_order_date --when was the last time they purchased from us
FROM public.sales_data_sample
GROUP BY customer_name

-- Customers in terms of recency using DATEDIFF
SELECT
		Customer_name,
		sum(sales) as monetary_value,
		avg(sales) as avgmonetaryvalue,
		count(order_number) as frequency,
		max(order_date) as last_order_date, -- per customer
		(SELECT max(order_date) FROM public.sales_data_sample) as max_order_date, -- For the store
		DATEDIFF(DD, max(order_date), (SELECT max(order_date) FROM public.sales_data_sample)) as recency
FROM public.sales_data_sample
GROUP BY customer_name

-- Customers in terms of recency using minus sign in pgadmin
--exp: to see how many days have passed since the customers purchased from us
SELECT
    customer_name,
    SUM(sales) AS monetary_value,
    AVG(sales) AS avgmonetaryvalue,
    COUNT(order_number) AS frequency,
    MAX(order_date) AS last_order_date, -- per customer
    (SELECT MAX(order_date) FROM public.sales_data_sample) AS max_order_date, -- store-wide
    (SELECT MAX(order_date) FROM public.sales_data_sample) - MAX(order_date) AS recency
FROM public.sales_data_sample
GROUP BY customer_name
ORDER BY 7 DESC



--- CTE of customers in terms of recency, frequency and monetary value
With rfm as
(SELECT
    customer_name,
    SUM(sales) AS monetary_value,
    AVG(sales) AS avgmonetaryvalue,
    COUNT(order_number) AS frequency,
    MAX(order_date) AS last_order_date, -- per customer
    (SELECT MAX(order_date) FROM public.sales_data_sample) AS max_order_date, -- store-wide
    (SELECT MAX(order_date) FROM public.sales_data_sample) - MAX(order_date) AS recency
FROM public.sales_data_sample
GROUP BY customer_name),

rfm_calc as(
SELECT *,
		NTILE(4) OVER (ORDER BY recency desc) as rfm_recency, -- 4 categories of recency
		NTILE(4) OVER (ORDER BY frequency) as rfm_frequency, -- 4 categories of frequency
		NTILE(4) OVER (ORDER BY avgmonetaryvalue) as rfm_monetary -- 4 categories of monetary value
FROM rfm)

SELECT * FROM rfm_calc


-- Concatenating rfm values

With rfm as
(SELECT
    customer_name,
    SUM(sales) AS monetary_value,
    AVG(sales) AS avgmonetaryvalue,
    COUNT(order_number) AS frequency,
    MAX(order_date) AS last_order_date, -- per customer
    (SELECT MAX(order_date) FROM public.sales_data_sample) AS max_order_date, -- store-wide
    (SELECT MAX(order_date) FROM public.sales_data_sample) - MAX(order_date) AS recency
FROM public.sales_data_sample
GROUP BY customer_name),

rfm_calc as(
SELECT *,
		NTILE(4) OVER (ORDER BY recency desc) as rfm_recency,
		NTILE(4) OVER (ORDER BY frequency) as rfm_frequency,
		NTILE(4) OVER (ORDER BY avgmonetaryvalue) as rfm_monetary
FROM rfm)

SELECT *,
		rfm_recency + rfm_frequency + rfm_monetary as rfm_concat,
		cast(rfm_recency as varchar) + cast(rfm_frequency as varchar) + cast(rfm_monetary as varchar) as rfm_concat_string -- casting ad
FROM rfm_calc


-- casting as string in pgadmin

With rfm as
(SELECT
    customer_name,
    SUM(sales) AS monetary_value,
    AVG(sales) AS avgmonetaryvalue,
    COUNT(order_number) AS frequency,
    MAX(order_date) AS last_order_date, -- per customer
    (SELECT MAX(order_date) FROM public.sales_data_sample) AS max_order_date, -- store-wide
    (SELECT MAX(order_date) FROM public.sales_data_sample) - MAX(order_date) AS recency
FROM public.sales_data_sample
GROUP BY customer_name),

rfm_calc as(
SELECT *,
		NTILE(4) OVER (ORDER BY recency desc) as rfm_recency,
		NTILE(4) OVER (ORDER BY frequency) as rfm_frequency,
		NTILE(4) OVER (ORDER BY avgmonetaryvalue) as rfm_monetary
FROM rfm)

SELECT *,
		rfm_recency + rfm_frequency + rfm_monetary as rfm_concat,
		cast(rfm_recency as varchar) -- changing the data type from numeric to varchar and concatenating it with other columns simultaneously
		|| cast(rfm_frequency as varchar)
		|| cast(rfm_monetary as varchar) as rfm_concat_string
FROM rfm_calc




-- Creating a temporary table (Drop table in PGadmin)

DROP TABLE IF EXISTS rfm;
CREATE TEMP TABLE rfm AS
With rfm_base as
(SELECT
    customer_name,
    SUM(sales) AS monetary_value,
    AVG(sales) AS avgmonetaryvalue,
    COUNT(order_number) AS frequency,
    MAX(order_date) AS last_order_date, -- per customer
    (SELECT MAX(order_date) FROM public.sales_data_sample) AS max_order_date, -- store-wide
    (SELECT MAX(order_date) FROM public.sales_data_sample) - MAX(order_date) AS recency
FROM public.sales_data_sample
GROUP BY customer_name),

rfm_calc as(
SELECT *,
		NTILE(4) OVER (ORDER BY recency desc) as rfm_recency,
		NTILE(4) OVER (ORDER BY frequency) as rfm_frequency,
		NTILE(4) OVER (ORDER BY avgmonetaryvalue) as rfm_monetary
FROM rfm_base)

SELECT *,
		rfm_recency + rfm_frequency + rfm_monetary as rfm_concat,
		cast(rfm_recency as varchar)
		|| cast(rfm_frequency as varchar)
		|| cast(rfm_monetary as varchar) as rfm_concat_string
FROM rfm_calc



-- Using the created RFM table (quote the numbers in pgadmin)

SELECT * FROM rfm


SELECT customer_name, rfm_recency, rfm_frequency, rfm_monetary,
	CASE
		WHEN rfm_concat_string IN ('111','112','121','122','123','132','211','212','114','141') THEN 'inactive_customers'
		WHEN rfm_concat_string IN ('133','134','143','244','334','343','344') THEN 'retention_required'
		WHEN rfm_concat_string IN ('311','411','331') THEN 'new_customers'
		WHEN rfm_concat_string IN ('222','223','233','322') THEN 'potential_churners'
		WHEN rfm_concat_string IN ('323','333','321','422') THEN 'active'
		WHEN rfm_concat_string IN ('433','434','443','444') THEN 'loyal'
		ELSE 'unclassified'
end as rfm_segment
from rfm