Author: Hang Vo Thuy Nguyen
π 3. Analysis
π¨ 4. Tableau Dashboard
-
Open Sale Analysis.sql file in the SQL server or your SQL development kit.
-
Add the Dataset 'stores.xlsx' to the database and run the code.
![image](https://private-user-images.githubusercontent.com/102349995/253848460-0cdd4cbc-bcdf-4087-a4de-3f1cc269796e.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjQ4MDMwODIsIm5iZiI6MTcyNDgwMjc4MiwicGF0aCI6Ii8xMDIzNDk5OTUvMjUzODQ4NDYwLTBjZGQ0Y2JjLWJjZGYtNDA4Ny1hNGRlLTNmMWNjMjY5Nzk2ZS5wbmc_WC1BbXotQWxnb3JpdGhtPUFXUzQtSE1BQy1TSEEyNTYmWC1BbXotQ3JlZGVudGlhbD1BS0lBVkNPRFlMU0E1M1BRSzRaQSUyRjIwMjQwODI3JTJGdXMtZWFzdC0xJTJGczMlMkZhd3M0X3JlcXVlc3QmWC1BbXotRGF0ZT0yMDI0MDgyN1QyMzUzMDJaJlgtQW16LUV4cGlyZXM9MzAwJlgtQW16LVNpZ25hdHVyZT03OTM5OTMzYTMzNWVlMzE1OTRkYjg3ODFlOTNmZmE5NzQzYmJkMjNjZmZkZWZlYmViN2QxOGEwYzczMTA4ZWEwJlgtQW16LVNpZ25lZEhlYWRlcnM9aG9zdCZhY3Rvcl9pZD0wJmtleV9pZD0wJnJlcG9faWQ9MCJ9.bLbaJY-lyxyCUn9kp27gykv7Gs-k1NfdY9GFlzhdwT4)
To utilize SQL analysis for conducting sales data analysis, follow these steps:
-
Launch your preferred SQL client and connect with the database where you have imported the sales data.
-
Familiarize yourself with the SQL scripts available in the repository. These scripts encompass different facets of sales data analysis, including data cleansing, segmentation, and RFM analysis.
-
Employ the SQL queries within your SQL client to carry out the desired analysis.
-
Evaluate the outcomes and extract valuable insights from the sales data.
Here are some analyses I used in this repository:
- Items have not been ordered
select * from products P
left join orderdetails O
on P.productCode=O.productCode
where O.productCode is null
![image](https://private-user-images.githubusercontent.com/102349995/253849919-0a340308-2ea6-4763-9013-e6e905c2b3f8.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjQ4MDMwODIsIm5iZiI6MTcyNDgwMjc4MiwicGF0aCI6Ii8xMDIzNDk5OTUvMjUzODQ5OTE5LTBhMzQwMzA4LTJlYTYtNDc2My05MDEzLWU2ZTkwNWMyYjNmOC5wbmc_WC1BbXotQWxnb3JpdGhtPUFXUzQtSE1BQy1TSEEyNTYmWC1BbXotQ3JlZGVudGlhbD1BS0lBVkNPRFlMU0E1M1BRSzRaQSUyRjIwMjQwODI3JTJGdXMtZWFzdC0xJTJGczMlMkZhd3M0X3JlcXVlc3QmWC1BbXotRGF0ZT0yMDI0MDgyN1QyMzUzMDJaJlgtQW16LUV4cGlyZXM9MzAwJlgtQW16LVNpZ25hdHVyZT00MmIwNGI1OGU1NGRiYWZhMWZlNTM0NTQwZTc5OTQ5OTJhZjU2MmIzMDY4YWQwYWM0NjM3Yzk2MzBmMGNmYzcxJlgtQW16LVNpZ25lZEhlYWRlcnM9aG9zdCZhY3Rvcl9pZD0wJmtleV9pZD0wJnJlcG9faWQ9MCJ9.ac_Cyci73gs9InMrIbI0K9ij8uJfju1NBvojfmG272c)
- Items ordered at least once
select distinct P.productCode, P.productName
from products P inner join orderdetails O
on P.productcode=O.productCode
![image](https://private-user-images.githubusercontent.com/102349995/253849765-87efc58b-7d4f-4fd6-8937-b8c24d601cff.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjQ4MDMwODIsIm5iZiI6MTcyNDgwMjc4MiwicGF0aCI6Ii8xMDIzNDk5OTUvMjUzODQ5NzY1LTg3ZWZjNThiLTdkNGYtNGZkNi04OTM3LWI4YzI0ZDYwMWNmZi5wbmc_WC1BbXotQWxnb3JpdGhtPUFXUzQtSE1BQy1TSEEyNTYmWC1BbXotQ3JlZGVudGlhbD1BS0lBVkNPRFlMU0E1M1BRSzRaQSUyRjIwMjQwODI3JTJGdXMtZWFzdC0xJTJGczMlMkZhd3M0X3JlcXVlc3QmWC1BbXotRGF0ZT0yMDI0MDgyN1QyMzUzMDJaJlgtQW16LUV4cGlyZXM9MzAwJlgtQW16LVNpZ25hdHVyZT1lNGQxYzIwZGE1ZTEwOTZiZWFkMzRkOGQ2ODliYzc1ZjkzNTJiN2JiZDRhYTRjOGVlMGM5ZDJlNTZmMzU3NjAzJlgtQW16LVNpZ25lZEhlYWRlcnM9aG9zdCZhY3Rvcl9pZD0wJmtleV9pZD0wJnJlcG9faWQ9MCJ9.dCignaE3vpcPHYGRGaadtRVpLyH7s7qpf-548TriVpU)
- Order quantity and Revenue by countries
select c.country, sum(od.quantityOrdered) as TotalQuantityOrderd, sum(od.quantityOrdered*od.priceEach) as Revenue
from customers c inner join orders o
on c.customerNumber=o.customerNumber
inner join orderdetails od
on o.orderNumber=od.orderNumber
group by c.country
order by TotalQuantityOrderd desc
![image](https://private-user-images.githubusercontent.com/102349995/253849641-43b52df5-7a44-45ba-8278-5fe4213b2f04.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjQ4MDMwODIsIm5iZiI6MTcyNDgwMjc4MiwicGF0aCI6Ii8xMDIzNDk5OTUvMjUzODQ5NjQxLTQzYjUyZGY1LTdhNDQtNDViYS04Mjc4LTVmZTQyMTNiMmYwNC5wbmc_WC1BbXotQWxnb3JpdGhtPUFXUzQtSE1BQy1TSEEyNTYmWC1BbXotQ3JlZGVudGlhbD1BS0lBVkNPRFlMU0E1M1BRSzRaQSUyRjIwMjQwODI3JTJGdXMtZWFzdC0xJTJGczMlMkZhd3M0X3JlcXVlc3QmWC1BbXotRGF0ZT0yMDI0MDgyN1QyMzUzMDJaJlgtQW16LUV4cGlyZXM9MzAwJlgtQW16LVNpZ25hdHVyZT01ZjhkMTdmMTUyNzA0Zjc2YTdiYWM0M2U0ZjcyZDA5NjgzYTJjMGM1NjdjYmY0ZDdiMmNhZDJjNGUyNmEyZWU3JlgtQW16LVNpZ25lZEhlYWRlcnM9aG9zdCZhY3Rvcl9pZD0wJmtleV9pZD0wJnJlcG9faWQ9MCJ9.iMUUEj_tToRGQXHktWGtqLZhnV_eWOcK9lhxG1hlo6I)
- Total orders and Revenue per status
select o.status, count(distinct o.orderNumber) as OrderCount, sum(od.quantityOrdered * od.priceEach) AS Revenue
from orders o
inner join orderdetails od
on o.orderNumber = od.orderNumber
group by o.status
order by Revenue desc
![image](https://private-user-images.githubusercontent.com/102349995/253849437-c0c6ef90-47fe-4f77-980e-8c0c7e7eae60.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjQ4MDMwODIsIm5iZiI6MTcyNDgwMjc4MiwicGF0aCI6Ii8xMDIzNDk5OTUvMjUzODQ5NDM3LWMwYzZlZjkwLTQ3ZmUtNGY3Ny05ODBlLThjMGM3ZTdlYWU2MC5wbmc_WC1BbXotQWxnb3JpdGhtPUFXUzQtSE1BQy1TSEEyNTYmWC1BbXotQ3JlZGVudGlhbD1BS0lBVkNPRFlMU0E1M1BRSzRaQSUyRjIwMjQwODI3JTJGdXMtZWFzdC0xJTJGczMlMkZhd3M0X3JlcXVlc3QmWC1BbXotRGF0ZT0yMDI0MDgyN1QyMzUzMDJaJlgtQW16LUV4cGlyZXM9MzAwJlgtQW16LVNpZ25hdHVyZT1iZjg4MjhhOTE1M2FmOTM1N2Y5ZDQ5NGYzNzVjMTIzZjliZGFmNWZmYjgxMzI5OTE5YjRkMWFiZWM1OTRlMGQxJlgtQW16LVNpZ25lZEhlYWRlcnM9aG9zdCZhY3Rvcl9pZD0wJmtleV9pZD0wJnJlcG9faWQ9MCJ9.6afpktOPOwsFcYc4-tZRPzGnajYZnngvqUJMaFZUJU0)
- Revenue by Product
select P.productCode, P.productName, sum(od.quantityOrdered * od.priceEach) as Revenue
from products P inner join orderdetails od
on P.productCode=od.productCode
group by P.productCode, P.productName
order by Revenue desc
![image](https://private-user-images.githubusercontent.com/102349995/253849310-aed0275c-fa00-48a7-8bc3-1a95e8c422b2.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjQ4MDMwODIsIm5iZiI6MTcyNDgwMjc4MiwicGF0aCI6Ii8xMDIzNDk5OTUvMjUzODQ5MzEwLWFlZDAyNzVjLWZhMDAtNDhhNy04YmMzLTFhOTVlOGM0MjJiMi5wbmc_WC1BbXotQWxnb3JpdGhtPUFXUzQtSE1BQy1TSEEyNTYmWC1BbXotQ3JlZGVudGlhbD1BS0lBVkNPRFlMU0E1M1BRSzRaQSUyRjIwMjQwODI3JTJGdXMtZWFzdC0xJTJGczMlMkZhd3M0X3JlcXVlc3QmWC1BbXotRGF0ZT0yMDI0MDgyN1QyMzUzMDJaJlgtQW16LUV4cGlyZXM9MzAwJlgtQW16LVNpZ25hdHVyZT1mMTEyNDQyMjhhNzNmZDQ3MjQwNDFiM2E0ZWI5OTk1ZWExMDBkOTQxMDQyNWNiMzNjNDU2MTdlZWQ5MzFiMjFlJlgtQW16LVNpZ25lZEhlYWRlcnM9aG9zdCZhY3Rvcl9pZD0wJmtleV9pZD0wJnJlcG9faWQ9MCJ9.tzlPq6xlw9dljnX5mi4LMXiP8Eaq455KP8MAkke-XAg)
- Revenue by product line
select p.productLine, sum(od.quantityOrdered * od.priceEach) AS Revenue
from products p
inner join orderdetails od
on p.productCode = od.productCode
group by p.productLine
order by Revenue desc
![image](https://private-user-images.githubusercontent.com/102349995/253849056-1864580a-9d9e-4542-a81f-822b5626de6a.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjQ4MDMwODIsIm5iZiI6MTcyNDgwMjc4MiwicGF0aCI6Ii8xMDIzNDk5OTUvMjUzODQ5MDU2LTE4NjQ1ODBhLTlkOWUtNDU0Mi1hODFmLTgyMmI1NjI2ZGU2YS5wbmc_WC1BbXotQWxnb3JpdGhtPUFXUzQtSE1BQy1TSEEyNTYmWC1BbXotQ3JlZGVudGlhbD1BS0lBVkNPRFlMU0E1M1BRSzRaQSUyRjIwMjQwODI3JTJGdXMtZWFzdC0xJTJGczMlMkZhd3M0X3JlcXVlc3QmWC1BbXotRGF0ZT0yMDI0MDgyN1QyMzUzMDJaJlgtQW16LUV4cGlyZXM9MzAwJlgtQW16LVNpZ25hdHVyZT1hZGFiOTgxYjIxMTMyMDA4YmU3NTM1ZTJjYjM4MTc4ZjdjMjhiOTdlYzgwYmIxYTQwYTRlMGUxOWIyY2YyZTU0JlgtQW16LVNpZ25lZEhlYWRlcnM9aG9zdCZhY3Rvcl9pZD0wJmtleV9pZD0wJnJlcG9faWQ9MCJ9.4IFugxUGTybMcQBlXg8PXIEK146SyJ2pQZU3BnWpseQ)
- Who is the best customer? (Using RFM analysis)
with rfm as (
select
o.customerNumber,
max(o.orderDate) as last_order_date,
count(o.orderNumber) as Frequency,
sum(od.quantityOrdered * od.priceEach) as MonetaryValue,
sum(od.quantityOrdered * od.priceEach) / count(o.orderNumber) as AvgMonetaryValue,
(select max(orderDate) from orders as max_order_date) as max_order_date,
datediff(dd, max(o.orderDate), (select max(orderDate) from orders)) as Recency
from orders o
inner join orderdetails od on o.orderNumber = od.orderNumber
group by o.customerNumber
),
rfm_calc as (
select
r.*,
ntile(4) over (order by last_order_date) as rfm_recency,
ntile(4) over (order by Frequency) as rfm_frequency,
ntile(4) over (order by MonetaryValue) as rfm_monetary
from rfm r
)
select
c.customerName, rfm.*,
(case
when rfm_recency = 4 and rfm_frequency >= 3 and rfm_monetary >= 3 then 'Loyal Customers'
when rfm_recency >= 3 and rfm_frequency >= 3 and rfm_monetary >= 2 then 'Active' --(Customers who buy often & recently, but at low price points)
when rfm_recency >= 2 and rfm_frequency >= 1 and rfm_monetary >= 2 then 'Potential Customers'
when rfm_recency >= 3 and rfm_frequency >= 1 and rfm_monetary = 1 then 'New Customers'
when rfm_recency <= 2 and rfm_frequency >= 1 and rfm_monetary >=1 then 'Lost Customers'
end) as rfm_segment
from rfm_calc rfm
inner join customers c
on rfm.customerNumber=c.customerNumber
order by MonetaryValue desc
![image](https://private-user-images.githubusercontent.com/102349995/253788863-5326abec-6945-4380-b7e8-b683c673e4b8.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjQ4MDMwODIsIm5iZiI6MTcyNDgwMjc4MiwicGF0aCI6Ii8xMDIzNDk5OTUvMjUzNzg4ODYzLTUzMjZhYmVjLTY5NDUtNDM4MC1iN2U4LWI2ODNjNjczZTRiOC5wbmc_WC1BbXotQWxnb3JpdGhtPUFXUzQtSE1BQy1TSEEyNTYmWC1BbXotQ3JlZGVudGlhbD1BS0lBVkNPRFlMU0E1M1BRSzRaQSUyRjIwMjQwODI3JTJGdXMtZWFzdC0xJTJGczMlMkZhd3M0X3JlcXVlc3QmWC1BbXotRGF0ZT0yMDI0MDgyN1QyMzUzMDJaJlgtQW16LUV4cGlyZXM9MzAwJlgtQW16LVNpZ25hdHVyZT1jOGZlMDE5ZDkyMjlhMTlkMzgxMWZiZDM3MjM3OWQyMTk4ZjVjZWFkZGVmMGVmZTc1Nzc1YzA0MDllNzM0Nzk2JlgtQW16LVNpZ25lZEhlYWRlcnM9aG9zdCZhY3Rvcl9pZD0wJmtleV9pZD0wJnJlcG9faWQ9MCJ9._g4eljmwUZdkwsbS27JQAwcEohx1J4iDDn8jwTrHkFI)
Here is a preview of the interactive dashboard created using Tableau:
![image](https://private-user-images.githubusercontent.com/102349995/253789482-e8df1d63-b4bc-4c5a-b586-aa459e9cece9.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjQ4MDMwODIsIm5iZiI6MTcyNDgwMjc4MiwicGF0aCI6Ii8xMDIzNDk5OTUvMjUzNzg5NDgyLWU4ZGYxZDYzLWI0YmMtNGM1YS1iNTg2LWFhNDU5ZTljZWNlOS5wbmc_WC1BbXotQWxnb3JpdGhtPUFXUzQtSE1BQy1TSEEyNTYmWC1BbXotQ3JlZGVudGlhbD1BS0lBVkNPRFlMU0E1M1BRSzRaQSUyRjIwMjQwODI3JTJGdXMtZWFzdC0xJTJGczMlMkZhd3M0X3JlcXVlc3QmWC1BbXotRGF0ZT0yMDI0MDgyN1QyMzUzMDJaJlgtQW16LUV4cGlyZXM9MzAwJlgtQW16LVNpZ25hdHVyZT01NWZhYjNkNmQzYWRkYzQ4OTk3NWNlYWQ3NDdlYTJlMDc4NzNlZjAwOTJmNmQxNTcwNDkyNWY5MDE5ZTk5NTlhJlgtQW16LVNpZ25lZEhlYWRlcnM9aG9zdCZhY3Rvcl9pZD0wJmtleV9pZD0wJnJlcG9faWQ9MCJ9.8-_VhytDd3UNbp2eAOU8zdUqnmCLt7KjkKXCQQ_JWJY)