𝙒𝙝𝙮 𝙘𝙖𝙡𝙘𝙪𝙡𝙖𝙩𝙚 𝙧𝙚𝙥𝙚𝙖𝙩 𝙘𝙪𝙨𝙩𝙤𝙢𝙚𝙧𝙨?
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.
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).
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 firstname.lastname@example.org
Please subscribe to our Youtube Channel by clicking on the youtube icon given below