Analyzing Cutomers Purchasing Transactions Behavior Using Analytical SQL

A report uses customers transactions data to gain insights into customer behavior and optimize sales, revenue, customer retention, and churn

Problem Statement:

The purpose is to analyze customer purchasing transactions and gain insight into customer behavior to efficiently and proactively target customers, with the goal of increasing sales/revenue, improving customer retention, and reducing churn.

Datasets:

• Customers Data folder contains all datasets used in this analysis.

• OnlineRetail: An OnlineRetail dataset contains 12858 rows of retail transactions data. Each row represents a purchase made by a customer and includes information such as the invoice number, stock code, quantity, invoice date, price, customer ID, and country.

• DailyCustomers: The DailyCustomers dataset contains 574396 rows of daily purchasing transactions data for customers. Each row represents a purchase made by a customer and includes information such as the customer ID, purchasing date, and the amount.

Project Steps:

1- Exploring the OnlineReatail Dataset by applying some business meaningful analytical queries which help with understanding the data, and applying different analyses.

2- Implementing the RFM Segmentation model to separate a group of customers into subgroups of customers according to their behavior for product purchasing.

3- Calculating the maximum number of consecutive days a customer made purchases.

4- Calculating the number of days or transactions it takes each customer to reach a spent threshold of 250 LE.

5- Creating visuals and charts that help reading and to understand the datasets, analysis, and changes on it.

Project Files:

• Customer Data, which contains the used datasets
• Customers Transactions Analysis Report, which contains the business story, analyzing queries business meaning, data charts, and conclusions.
• Output_Data, which contains all data that have been exported from the different analysis methods.
• Queries Explanation which contains the SQL queries used to perform the analysis, the description for each query, and how it works.
• SQL Queries which is .sql file that contains the queries with clear comments.

Tools and Technologies:

• SQL.
• Analytical SQL Functions.
• CTEs.
• Window Functions.
• Toad.
• Power BI.