𝗡𝗲𝘄 𝗮𝗻𝗱 𝗥𝗲𝗽𝗲𝗮𝘁 𝗖𝘂𝘀𝘁𝗼𝗺𝗲𝗿𝘀 𝗹𝗼𝗴𝗶𝗰 𝗶𝗻 𝗦𝗤𝗟

𝙒𝙝𝙮 𝙘𝙖𝙡𝙘𝙪𝙡𝙖𝙩𝙚 𝙧𝙚𝙥𝙚𝙖𝙩 𝙘𝙪𝙨𝙩𝙤𝙢𝙚𝙧𝙨?
1. Repeat Customers spend more on each purchase
2. Repeat Customers are more likely to shop with you again and again
3. Repeat Customers are our business promoters
4. Businesses spend more on your key times like festive rush

𝙒𝙝𝙮 𝙣𝙚𝙬 𝙘𝙪𝙨𝙩𝙤𝙢𝙚𝙧𝙨 𝙖𝙧𝙚 𝙞𝙢𝙥𝙤𝙧𝙩𝙖𝙣𝙩?
1. Customer Acquisition Cost (CAC) is an important metric. The lesser the CAC, the better the business is.
2. Helps businesses to identify if their model is a viable one.
3. Every business must understand their CAC as it helps them in investing wisely and scale their business.
4. They get an idea of how much revenue is needed for better sustenance and growth.

Since we have used CTEs in this example, it will be easy to understand the logic and also the query performance will be better.

𝗡𝗼𝘁𝗲: The timeframe for calculating the new & repeat customers can be changed by introducing a where clause in the CTEs.

REQUIREMENT:

The orders placed by consumers in a shop on a daily basis are listed in the
table. We must keep track of each day’s totals for both the number of

customers who were new to the store and those who had previously visited

(new customers and repeat customers).

Expected Results

Query

 with as First_Visit

(

 select Customer_ID, min (Order_date) as First_Visit_Date

 from Orders

 group by Customer_ID

),

Visit_Flag as

(

 select o.*,F.First_Visit_Date,

 O.Order_Date=F.First_Visit_Date then 1 else 0 end as

First_Visit_Flag,

case when O.Order_Date<>F.First_Visit_Date then  1 else  0 end as 
Repeat_Visit_Flag

 from Orders O

 inner join First_Visit F on  O.Customer_ID= F.Customer_ID

)

 Select Order_Date , sum (First_Visit_Flag) as New_Customers,

sum (Repeat_Visit_Flag) as  Repeated_Customers,

count(Customer_ID) as Total_Customers

 Visit_Flag

 Order_Date