๐๐๐ฎ ๐๐๐ก๐๐ช๐ก๐๐ฉ๐ ๐ง๐๐ฅ๐๐๐ฉ ๐๐ช๐จ๐ฉ๐ค๐ข๐๐ง๐จ?
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
Important Notice for college students
If youโre a college student and have skills in programming languages, Want to earn through blogging? Mail us at geekycomail@gmail.com
For more Programming related blogs Visit Us Geekycodes. Follow us on Instagram.
Please subscribe to our Youtube Channel by clicking on the youtube icon given below