Skip to content

Latest commit

 

History

History
45 lines (28 loc) · 2.36 KB

README.md

File metadata and controls

45 lines (28 loc) · 2.36 KB

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.