How to do customer segmentation based on their orders in different years using SQL

Question

I want to make customer segments based on their orders in different years (number of days before last order) using a SQL query. Below are the segments that I want:

New Customer = Made an order for the very first time in whole data
Repeat Customer L1 = Made an order at least once in last one year (365days)
Repeat Customer L2 = Made an order at least once in last two years (but not in last one year)
Repeat Customer L3 = Made an order at least once in last three years (but not in last two years)
Unspecified = Any order that does not have any of the above conditions

This is the table that I have: (Small example, the data that I have is too huge)

Date(yyyymmdd)	Customer_id
20180403	abc123
20180711	def456
20180625	mno789
20181123	abc123
20190130	ghi123
20190321	def456
20190909	ghi123
20191225	jkl456
20200205	abc123
20200617	ghi123
20200817	hij123
20210307	mno789
20211009	xyz345

This is the output I am trying to get through SQL (I am new to SQL):

Year	Customer_id	Segment	Transactions
2018	abc123	New Customer	2
2018	def456	New Customer	1
2018	mno789	New Customer	1
2019	ghi123	New Customer	2
2019	def456	Repeat Customer L1	1
2019	jkl456	New Customer	1
2020	abc123	Repeat Customer L2	1
2020	ghi123	Repeat Customer L1	1
2020	hij123	New Customer	1
2021	mno789	Repeat Customer L3	1
2021	xyz345	New Customer	1

Your help would be really appreciated.

user_16929358 · Answer

With the data that you shared being:

WITH input as(
SELECT '20180403' as DATE,  'abc123' as Customer_Id UNION ALL
SELECT '20180711',  'def456'UNION ALL
SELECT '20180625',  'mno789'UNION ALL
SELECT '20181123',  'abc123'UNION ALL
SELECT '20190130',  'ghi123'UNION ALL
SELECT '20190321',  'def456'UNION ALL
SELECT '20190909',  'ghi123'UNION ALL
SELECT '20191225',  'jkl456'UNION ALL
SELECT '20200205',  'abc123'UNION ALL
SELECT '20200617',  'ghi123'UNION ALL
SELECT '20200817',  'hij123'UNION ALL
SELECT '20210307',  'mno789'UNION ALL
SELECT '20211009',  'xyz345'
)

I used 3 common_table_expresion, I used row_number to bring the transaction_column that has the same Year and the same customer_Id and I use a CASE expression to bring the Segment column according to the values that you shared that depends on the year column.

with YearExtr as (
 SELECT SUBSTR(DATE, 1,4) as Year,input.Customer_Id FROM input
), transactcOL as(
SELECT FORMAT_DATE("%Y",PARSE_DATE("%Y", Year))as Real_Year, Customer_Id, MAX(transaction_col) as transaction_col,Cast(FORMAT_DATE("%Y",PARSE_DATE("%Y", Year))as INT)as Formated_Year FROM
(SELECT * ,
 ROW_NUMBER() OVER(PARTITION BY YearExtr.Year, YearExtr.Customer_Id
                   ORDER BY YearExtr.Year DESC) as transaction_col
  from YearExtr
order by YearExtr.Year  )
GROUP BY Year, Customer_Id
), lastexp as(
SELECT DISTINCT tcol.Real_Year, tcol.Customer_Id,
CASE
WHEN tcol.Formated_Year>=t2.Formated_Year AND ((tcol.Formated_Year)-(t2.Formated_Year))=1 THEN 'Repeat Customer L1'
WHEN tcol.Formated_Year>=t2.Formated_Year AND ((tcol.Formated_Year)-(t2.Formated_Year))=2 THEN 'Repeat Customer L2'
WHEN tcol.Formated_Year>=t2.Formated_Year AND ((tcol.Formated_Year)-(t2.Formated_Year))=3 THEN 'Repeat Customer L3'
ELSE 'New Customer'
END as Segment, tcol.transaction_col,
ROW_NUMBER() OVER(PARTITION BY tcol.Real_Year, tcol.Customer_Id
                   ORDER BY tcol.Formated_Year DESC) as rown
FROM transactcOL tcol left join transactcOL t2
USING (Customer_Id)
GROUP BY tcol.Real_Year, tcol.Customer_Id, tcol.Formated_Year, t2.Formated_Year, tcol.transaction_col
)
SELECT lastexp.Real_Year, lastexp.Customer_Id, lastexp.Segment, lastexp.transaction_col
FROM lastexp WHERE rown=1

The Output is:

How to do customer segmentation based on their orders in different years using SQL

1 Answers