Skip to content

Latest commit

 

History

History
59 lines (43 loc) · 4.91 KB

README.md

File metadata and controls

59 lines (43 loc) · 4.91 KB

The Curious Case of the Missing Orders: A Northwind Customer Behavior and Churn Analysis

This project involves a series of in-depth SQL analyses on the Northwind database to understand customer behavior, purchasing trends, and sales performance. The project focuses on deriving insights from customer order data and explores various factors like purchase frequency, churn, order value, customer segmentation, and acquisition trends. It aims to uncover patterns that can help businesses improve customer retention, increase sales, and optimize marketing efforts.

Logo

Project Overview 📃

In this project, we will perform 12 distinct analyses that provide a comprehensive understanding of customer purchasing behavior, segmenting them based on their activity and identifying strategies to improve business performance.

These analyses span a wide array of business-critical areas, including:

  1. Trends in Order Frequency Over Time: Analyzing how frequently customers place orders and calculating the average time gap between their purchases.
  2. Customer Segmentation by Order Frequency: Categorizing customers as Frequent, Regular, or Infrequent Buyers based on their order frequency.
  3. Average Order Value for Churning and Non-Churning Customers: Comparing the average order value between customers who are at risk of churning and those who are loyal.
  4. Distribution of Order Values: Segmenting customers based on the value of their orders (Low, Medium, High) to understand purchasing behavior across different categories.
  5. Product Preferences Before and After Churn: Analyzing the most frequently purchased product categories by customers who churned versus those who stayed active.
  6. Customer Locations and Churn Rates: Investigating regional patterns in customer churn to identify locations with higher churn rates.
  7. Correlation Between Location and Purchase Behavior: Studying the differences in buying patterns across regions to find trends in customer behavior by location.
  8. Customer Risk Scoring: Assigning risk levels to customers based on purchase frequency, order value, and product categories.
  9. Customer Order Frequency Over the Last 6 Months: Analyzing customers’ order frequency in the past six months to detect changes in behavior.
  10. Identifying Customers with a Decrease in Order Frequency: Spotting customers who have reduced their order frequency and may be at risk of churning.
  11. Customer Lifetime Value (CLTV) Calculation: Estimating the lifetime value of customers based on their order history to prioritize retention efforts.
  12. Customer Acquisition Trends: Analyzing trends in customer acquisition over time and their purchasing behavior to determine the effectiveness of marketing strategies.

Database Used 📀

Northwind Database: A classic database used for simulating real-world business scenarios. It contains data about orders, customers, employees, products, and other business aspects that are crucial for performing sales and customer behavior analysis.

Technologies Used 💻

  • SQL: All analyses were performed using SQL queries to manipulate and aggregate data.
  • MySQL: The relational database management system used to execute the queries and manage the Northwind database.
  • Data Analysis: SQL-based analysis techniques to explore customer data, uncover purchasing patterns, and generate business insights.

Key Features 💡

  • Order Frequency Analysis: Helps identify customer behaviors like repeat purchases, frequent buyers, and those who purchase infrequently.
  • Customer Segmentation: Categorizes customers into meaningful segments for targeted marketing.
  • Churn Analysis: Identifies at-risk customers and the factors leading to churn.
  • Geographic Analysis: Understands how customer behavior varies across different regions and cities.
  • Risk Scoring: Prioritizes customers based on their potential value or risk.
  • Customer Acquisition Insights: Tracks new customer acquisition trends and their impact on sales.

Usage ❗

  1. Clone the repository and import the SQL queries into your SQL client.
  2. Connect the Northwind database to your local MySQL server.
  3. Execute the queries to see the results for each analysis.
  4. Modify the queries based on your specific requirements or dataset changes.

Conclusion 🔹

This project provides a detailed analysis of customer behavior, sales trends, and market segmentation using SQL on the Northwind database. It can be used to gain actionable insights that help businesses optimize their customer engagement, improve retention rates, and boost overall sales performance. The project also serves as a demonstration of how SQL can be used for advanced data analysis and business intelligence.

Author 🎓