SQL_Customer_Behavior_Analysis.sql

----------------------------------------------------------------------------------------------------------------------------
--Business Insights Questions
----------------------------------------------------------------------------------------------------------------------------

--1. Which category generates highest revenue?

SELECT 
	category,
	ROUND(SUM(purchase_amount),2) AS highest_revenue
FROM customer_behaviour_analysis
GROUP BY category
ORDER BY highest_revenue DESC

-- ❓ Business Problem :- Company does not know which category contributes most to revenue.


-- 🚀 Impact :- 
			 -- Helps prioritize high-performing categories
			 -- Optimizes inventory planning
			 -- Improves marketing ROI

----------------------------------------------------------------------------------------------------------------------------

--2. Are discounts actually increasing purchase value?

SELECT * FROM customer_behaviour_analysis

SELECT 
	discount_applied,
	ROUND(SUM(purchase_amount),2) AS total_revenue,
	ROUND(AVG(purchase_amount),2) AS avg_purchase
FROM customer_behaviour_analysis
GROUP BY discount_applied

--❓ Business Problem :- Discounts may reduce profit without increasing sales.


-- 🚀 Impact :- 
				--Identify effectiveness of discounts
				--Reduce unnecessary discount costs
				--Improve profit margins

----------------------------------------------------------------------------------------------------------------------------
-- 3. What is the total revenue generated by male and female customer ? 
SELECT * FROM customer_behaviour_analysis

SELECT 
	gender,
	ROUND(SUM(purchase_amount),2) AS highest_revenue
FROM customer_behaviour_analysis
GROUP BY gender
ORDER BY highest_revenue DESC


--❓ Business Problem :- Lack of understanding of revenue contribution by gender segments.

--🚀 Impact:- 
			--Helps design targeted marketing campaigns
			--Improves customer segmentation strategy
			--Enhances personalization efforts

----------------------------------------------------------------------------------------------------------------------------

--4. Which customer used a discount but still spent more then the average purchase amount ?

SELECT * FROM customer_behaviour_analysis

SELECT TOP 10
	customer_id,
	purchase_amount,
	discount_applied
FROM customer_behaviour_analysis
WHERE discount_applied= 'Yes' AND purchase_amount > (SELECT AVG(purchase_amount) FROM customer_behaviour_analysis)

--❓ Business Problem:- Company cannot identify high-spending customers who are also discount users.


--🚀 Impact:- 
			--Identifies premium discount-sensitive customers
			--Enables targeted discount campaigns
			--Improves customer retention and revenue

----------------------------------------------------------------------------------------------------------------------------

--5. Which are the top/bottom 5 products with the highest average review rating. 
SELECT * FROM customer_behaviour_analysis

SELECT TOP 5
	item_purchased,
	ROUND(AVG(review_rating),2) AS Avg_ratings
FROM customer_behaviour_analysis
GROUP BY item_purchased
ORDER BY Avg_ratings DESC

SELECT TOP 5
	item_purchased,
	ROUND(AVG(review_rating),2) AS Avg_ratings
FROM customer_behaviour_analysis
GROUP BY item_purchased
ORDER BY Avg_ratings ASC

--❓ Business Problem :- No visibility into product performance based on customer satisfaction.

--🚀 Impact :- 
				--Promotes high-performing products
				--Improves low-rated products
				--Enhances customer experience

----------------------------------------------------------------------------------------------------------------------------

--6. Average purchase: Standard vs Express shipping

SELECT * FROM customer_behaviour_analysis

SELECT 
	shipping_type,
	COUNT(DISTINCT customer_id) as order_placed,
	ROUND(AVG(purchase_amount),2)  as avg_purchase,
	ROUND(SUM(purchase_amount),2) AS revenue
FROM customer_behaviour_analysis
--WHERE shipping_type IN ('Standard','Express')
GROUP BY shipping_type
ORDER BY revenue DESC

--❓ Business Problem :- Unclear if faster shipping leads to higher spending.


--🚀 Impact
			--Helps optimize shipping pricing strategy
			--Encourages premium shipping adoption
			--Increases average order value

----------------------------------------------------------------------------------------------------------------------------

--7. Do subscribed customers spend more ? Compare average speed and total revenue between subscribers and non-subscribers.

SELECT * FROM customer_behaviour_analysis


SELECT 
	subscription_status,
	COUNT(customer_id) as users,
	ROUND(AVG(purchase_amount),2) as avg_revenue,
	ROUND(SUM(purchase_amount),2) as total_revenue
FROM customer_behaviour_analysis
GROUP BY subscription_status
ORDER BY total_revenue DESC

--❓ Business Problem :- The effectiveness of subscription programs is unknown.

--🚀 Impact
			--Validates subscription model performance
			--Improves customer loyalty programs
			--Increases customer lifetime value (CLV)

----------------------------------------------------------------------------------------------------------------------------
--8. Top 5 products with highest discount usage %

SELECT * FROM customer_behaviour_analysis

SELECT TOP 5
	item_purchased,
	COUNT(item_purchased) AS total_number_of_times_sold,
	COUNT(CASE WHEN discount_applied='Yes' THEN 1 END) AS  Number_of_times_sold_when_disocunt_applied,
	COUNT(CASE WHEN discount_applied='Yes' THEN 1 END) *100.0 / COUNT(*) AS discount_percent
FROM customer_behaviour_analysis
GROUP BY item_purchased
ORDER BY discount_percent DESC

-- ❓ Business Problem :- Some products may be overly dependent on discounts.

--🚀 Impact:- 
			--Identifies discount-driven products
			--Helps optimize pricing strategy
			--Reduces profit margin loss


----------------------------------------------------------------------------------------------------------------------------

--9. Segment customer into new, returning and loyal based on their total number of previous purchase, 
--and show the count of each segment.

SELECT * FROM customer_behaviour_analysis

SELECT 
	CASE 
		WHEN previous_purchases= '0' THEN 'New Customer'
		WHEN previous_purchases BETWEEN 1 AND 15  THEN 'Returning Customer'
	ELSE 'Loyal Customers'
END AS customer_segment,
COUNT(*) AS customer_count
FROM customer_behaviour_analysis
GROUP BY CASE 
		WHEN previous_purchases= '0' THEN 'New Customer'
		WHEN previous_purchases BETWEEN 1 AND 15  THEN 'Returning Customer'
	ELSE 'Loyal Customers'
END

--❓ Business Problem :- Lack of customer segmentation leads to generic strategies.


--🚀 Impact:- 
			--Enables personalized marketing
			--Improves retention strategies
			--Increases conversion rates

----------------------------------------------------------------------------------------------------------------------------

--10. What are the top 3 most purchased products within each category ?

SELECT * FROM customer_behaviour_analysis

;WITH CTE AS 
(
SELECT 
	category,
	item_purchased,
	COUNT(item_purchased) AS most_purchased,
	RANK() OVER (PARTITION BY category ORDER BY COUNT(item_purchased) DESC) AS RNK
FROM customer_behaviour_analysis
GROUP BY category,item_purchased
)
SELECT * FROM CTE
WHERE RNK <=3

--❓ Business Problem:- The company doesn’t know top-performing products within categories.


--🚀 Impact
			--Improves product placement & recommendations
			--Helps inventory optimization
			--Increases sales through best-sellers

----------------------------------------------------------------------------------------------------------------------------

--11. Are customers who are repeat buyers (more than 5 previous purchases) also likely to subscribe ?

SELECT * FROM customer_behaviour_analysis

SELECT
	CASE 
		WHEN previous_purchases > 5 THEN 'Repeat Buyers'
		ELSE 'Normal Buyers'
	END AS customer_type,
	subscription_status,
	COUNT(*) AS customer_count,
	COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY CASE 
										WHEN previous_purchases > 5 THEN 'Repeat Buyers'
										ELSE 'Normal Buyers'
									END
									) AS percents

FROM customer_behaviour_analysis
GROUP BY CASE 
		WHEN previous_purchases > 5 THEN 'Repeat Buyers'
		ELSE 'Normal Buyers'
	END,subscription_status
--1047

--❓ Business Problem :- Unclear relationship between loyalty and subscription.

--🚀 Impact:- 
			--Improves subscription targeting
			--Increases conversion to paid programs
			--increase customer retention strategy

----------------------------------------------------------------------------------------------------------------------------

--12. What is the revenue contribution of each age group ? 

SELECT * FROM customer_behaviour_analysis

SELECT 
	CASE 
		WHEN age BETWEEN 18 AND 25 THEN '18-25'
		WHEN Age BETWEEN 26 AND 35 THEN '26-35'
		WHEN Age BETWEEN 36 AND 50 THEN '35-50'
		ELSE '51+'
	END AS age_group,
	ROUND(SUM(purchase_amount),2) AS total_revenue
FROM customer_behaviour_analysis
GROUP BY CASE 
		WHEN age BETWEEN 18 AND 25 THEN '18-25'
		WHEN Age BETWEEN 26 AND 35 THEN '26-35'
		WHEN Age BETWEEN 36 AND 50 THEN '35-50'
		ELSE '51+'
	END
	ORDER BY total_revenue DESC

--❓ Business Problem:- No visibility into which age group contributes most to revenue.

--🚀 Impact:-
			--Enables age-based targeting
			--Enhances marketing efficiency