Exploratory Data Analysis in SQL

Main Takeaways at the Bottom

Getting an Idea of what is included in the data frame.

SELECT * FROM updated_data.csv

column0int64

InvoiceNoobject

536365

536366

536367

SELECT InvoiceNO, StockCode AS SKU, Description, Quantity, UnitPrice, CustomerID, country FROM updated_data.csv

InvoiceNoobject

SKUobject

536365

85123A

536365

71053

536365

84406B

536365

84029G

536365

84029E

536365

22752

536365

21730

536366

22633

536366

22632

536367

84879

Customer Analysis

What are the countries to which our customers reside?

SELECT DISTINCT(country) FROM 'updated_data.csv'

Countryobject

United Kingdom2.6%

France2.6%

36 others94.7%

United Kingdom

France

Australia

Netherlands

Germany

Norway

EIRE

Switzerland

Spain

Poland

How many transactions in each country?

SELECT country, COUNT(DISTINCT(InvoiceNo)) as Count_of_Sales FROM 'updated_data.csv' GROUP BY country;

Countryobject

United Kingdom2.6%

Germany2.6%

36 others94.7%

Count_of_Salesint64

1 - 23494

United Kingdom

23494

Germany

603

France

461

Spain

105

Sweden

EIRE

360

Netherlands

101

Portugal

Switzerland

Channel Islands

How many customers are there per country?

SELECT country, COUNT(DISTINCT(CustomerID)) FROM 'updated_data.csv' GROUP BY country

Countryobject

United Kingdom2.6%

France2.6%

36 others94.7%

count(DISTINCT "CustomerID")int64

1 - 3951

United Kingdom

3951

France

Australia

Netherlands

Germany

Norway

EIRE

Switzerland

Spain

Poland

How many customers do we have in total?

SELECT count(DISTINCT(CustomerID)) FROM 'updated_data.csv'

count(DISTINCT "CustomerID")int64

4373

Top 10 Customers with the most orders

SELECT DISTINCT CustomerID, SUM(Quantity) as Total_Quantity_Bought, Country FROM updated_data.csv GROUP BY CustomerID, Country LIMIT 10

CustomerIDfloat64

12583.0 - 17850.0

Total_Quantity_Boughtint64

58 - 37720

17850.0

1693

13047.0

1355

12583.0

5009

13748.0

439

15100.0

15291.0

2074

14688.0

3222

17809.0

2015

15311.0

37720

14527.0

2049

--Top Customer by Sales Revenue SELECT CustomerID,

What is the top 10 products for customer 17850?

SELECT StockCode as SKU, Description,SUM(Quantity) as Quantity_Purchased FROM updated_data.csv WHERE CustomerID = 17850.0 GROUP BY SKU, Description ORDER BY Quantity_Purchased DESC LIMIT 10

SKUobject

85123A10%

7105310%

8 others80%

Descriptionobject

WHITE HANGING HEART T-LIGHT HOLDER10%

WHITE METAL LANTERN10%

8 others80%

85123A

WHITE HANGING HEART T-LIGHT HOLDER

71053

WHITE METAL LANTERN

84029G

KNITTED UNION FLAG HOT WATER BOTTLE

84406B

CREAM CUPID HEARTS COAT HANGER

22633

HAND WARMER UNION JACK

22632

HAND WARMER RED POLKA DOT

84029E

RED WOOLLY HOTTIE WHITE HEART.

21730

GLASS STAR FROSTED T-LIGHT HOLDER

37370

RETRO COFFEE MUGS ASSORTED

21068

VINTAGE BILLBOARD LOVE/HATE MUG

Gross Sales to Each Customer (Top 10)

SELECT CustomerID, ROUND(SUM(Quantity * UnitPrice), 2) AS Gross_Revenue FROM updated_data.csv WHERE CustomerID <> 'nan' GROUP BY CustomerID ORDER BY Gross_Revenue DESC

CustomerIDfloat64

12346.0 - 18287.0

Gross_Revenuefloat64

-4287.63 - 279489.02

14646.0

279489.02

18102.0

256438.49

17450.0

187482.17

14911.0

132572.62

12415.0

123725.45

14156.0

113384.14

17511.0

88125.38

16684.0

65892.08

13694.0

62653.1

15311.0

59419.34

Product Analysis

Average Order Price? Average Unit Price? Average Order Quantity?

Gross Sales. Gross Sales by SKU

--Gross Sales SELECT ROUND(SUM(Quantity * UnitPrice), 2) AS Gross_Revenue FROM updated_data.csv

Gross_Revenuefloat64

9747747.93

--Gross Sales by SKU SELECT StockCode as SKU, Description, ROUND(SUM(Quantity * UnitPrice),2) AS Gross_Sales FROM updated_data.csv WHERE SKU <> 'DOT' GROUP BY SKU, Description ORDER BY Gross_Sales DESC LIMIT 10 --Took out DOT as it is just the postage for each order. Same applies to the block below.

SKUobject

2242310%

4756610%

8 others80%

Descriptionobject

REGENCY CAKESTAND 3 TIER10%

PARTY BUNTING10%

8 others80%

22423

REGENCY CAKESTAND 3 TIER

47566

PARTY BUNTING

85123A

WHITE HANGING HEART T-LIGHT HOLDER

85099B

JUMBO BAG RED RETROSPOT

23084

RABBIT NIGHT LIGHT

POST

POSTAGE

22086

PAPER CHAIN KIT 50'S CHRISTMAS

84879

ASSORTED COLOUR BIRD ORNAMENT

79321

CHILLI LIGHTS

23298

SPOTTY BUNTING

What are the top 10 selling products? In UK?

SELECT StockCode as SKU, Description, ROUND(SUM(Quantity * UnitPrice),2) AS Gross_Sales FROM updated_data.csv WHERE Country = 'United Kingdom' AND SKU <> 'DOT' GROUP BY SKU, Description ORDER by Gross_Sales DESC LIMIT 10

SKUobject

2242310%

4756610%

8 others80%

Descriptionobject

REGENCY CAKESTAND 3 TIER10%

PARTY BUNTING10%

8 others80%

22423

REGENCY CAKESTAND 3 TIER

47566

PARTY BUNTING

85123A

WHITE HANGING HEART T-LIGHT HOLDER

85099B

JUMBO BAG RED RETROSPOT

22086

PAPER CHAIN KIT 50'S CHRISTMAS

84879

ASSORTED COLOUR BIRD ORNAMENT

79321

CHILLI LIGHTS

22502

PICNIC BASKET WICKER 60 PIECES

21137

BLACK RECORD COVER FRAME

23084

RABBIT NIGHT LIGHT

Average Price Per SKU?

SELECT StockCode as SKU, ROUND(AVG(UnitPrice), 2) as Average_Price FROM updated_data.csv GROUP BY SKU

SKUobject

85123A0%

710530%

4068 others100%

Average_Pricefloat64

-3687.35 - 7324.78

85123A

3.1

71053

4.78

84406B

4.25

84029G

5.18

84029E

5.07

22752

9.77

21730

5.32

22633

2.19

22632

2.2

84879

1.72

Average Order Price

SELECT ROUND(AVG(UnitPrice * Quantity), 2) as Order_Price FROM updated_data.csv

Order_Pricefloat64

17.99

Average Unit Price

-- Average Unit Price SELECT ROUND(AVG(UnitPrice), 2) as Price FROM updated_data.csv

Pricefloat64

4.61

Average Order Quantity

-- Average Quantity Amount SELECT ROUND(AVG(Quantity), 2) as Quantity FROM updated_data.csv

Quantityfloat64

9.55

SELECT DISTINCT(StockCode) as SKU, Description, ROUND(AVG(UnitPrice), 2) as Average_Price FROM updated_data.csv GROUP BY SKU, Description ORDER BY Average_Price DESC

SKUobject

207130.2%

230840.1%

4068 others99.7%

Descriptionobject

check2.5%

4222 others80.8%

Missing16.7%

AMAZONFEE

AMAZON FEE

22502

PICNIC BASKET WICKER 60 PIECES

CRUK

CRUK Commission

Manual

DOT

DOTCOM POSTAGE

BANK CHARGES

Bank Charges

22828

REGENCY MIRROR WITH SHUTTERS

22827

RUSTIC SEVENTEEN DRAWER SIDEBOARD

22655

VINTAGE RED KITCHEN CABINET

22656

VINTAGE BLUE KITCHEN CABINET

The block above includes some records that I do not want to factor in, therefore I will exclude them in the next block to get a true sense of the highest average priced items.

SELECT DISTINCT(StockCode) as SKU, Description, ROUND(AVG(UnitPrice), 2) as Average_Price FROM updated_data.csv WHERE SKU NOT IN ('AMAZONFEE', 22502, 'CRUK', 'M', 'DOT', 'BANK CHARGES') GROUP BY SKU, Description ORDER BY Average_Price DESC LIMIT 10

SKUobject

2282810%

2282710%

8 others80%

Descriptionobject

REGENCY MIRROR WITH SHUTTERS10%

RUSTIC SEVENTEEN DRAWER SIDEBOARD10%

8 others80%

22828

REGENCY MIRROR WITH SHUTTERS

22827

RUSTIC SEVENTEEN DRAWER SIDEBOARD

22655

VINTAGE RED KITCHEN CABINET

22656

VINTAGE BLUE KITCHEN CABINET

22823

CHEST NATURAL WOOD 20 DRAWERS

22826

LOVE SEAT ANTIQUE WHITE METAL

22016

Dotcomgiftshop Gift Voucher 100.00

Discount

21769

VINTAGE POST OFFICE CABINET

22929

SCHOOL DESK AND CHAIR

Time Series Analysis

Firstly we need to clean the data to be in the correct format and we need to extract information from the InvoiceDate column.

--Allows me to see the format of the Date. SELECT InvoiceDate FROM updated_data.csv LIMIT 1 --I will need to work around this format to get the month and the actual day

InvoiceDateobject

12/1/2010 8:26

Used python to add the day, month, year columns to our data frame. Will use SQL below to analyze.

import pandas as pd df = pd.read_csv('updated_data.csv') df['InvoiceDate'] = pd.to_datetime(df['InvoiceDate'], format='%m/%d/%Y %H:%M') df['Month'] = df['InvoiceDate'].dt.month df['Day'] = df['InvoiceDate'].dt.day df['Year'] = df['InvoiceDate'].dt.year df.to_csv('updated_data_timeseries.csv', index=False) df.head()

Unnamed: 0int64

InvoiceNoobject

536365

-- Viewing a sample of the new dataframe. SELECT * FROM updated_data_timeseries.csv LIMIT 10

Unnamed: 0int64

0 - 9

InvoiceNoobject

53636570%

53636620%

53636710%

536365

536366

536367

-- Shows us the distinct months, days, and years as needed. Used this for data validation. --SELECT DISTINCT (month) --FROM updated_data_timeseries.csv --SELECT DISTINCT (year) --FROM updated_data_timeseries.csv --SELECT DISTINCT (day) --FROM updated_data_timeseries.csv

Yearint64

2010

2011

Time EDA

Sales per Year

SELECT DISTINCT(year) as Year, COUNT(DISTINCT(InvoiceNo)) FROM updated_data_timeseries.csv GROUP BY Year ORDER BY Year ASC -- Interesting discrepancy in the data as far as the difference in Invoices between the two years. -- Will need to investigate it further.

Yearint64

count(DISTINCT "InvoiceNo")int64

2010

2025

2011

23875

--Investigating further in the invoice discrepancy SELECT DISTINCT month FROM updated_data_timeseries.csv WHERE year = 2010 -- There is only December for 2010. We will need to exclude or sperate it.

Monthint64

Gross Revenue per Year

SELECT DISTINCT(year) as Year, ROUND(SUM(UnitPrice * Quantity), 2) AS Gross_Sales FROM updated_data_timeseries.csv WHERE year = 2011 GROUP BY Year ORDER BY Year ASC -- Only looking at 2011 as there is only one month of data for 2010.

Yearint64

Gross_Salesfloat64

2011

8998790.91

Gross Revenue per Month over the two years.

SELECT DISTINCT(month) as Month, ROUND(SUM(UnitPrice * Quantity), 2) AS Gross_Revenue FROM updated_data_timeseries.csv WHERE year = 2011 GROUP BY Month ORDER BY Gross_Revenue DESC -- Looks like November is best month for revenue in 2011.

Monthint64

1 - 12

Gross_Revenuefloat64

433668.01 - 1461756.25

1461756.25

1070704.67

1019687.62

723333.51

691123.12

683267.08

682680.51

681300.11

560000.26

498062.65

Main Takeaways

- We sell to 38 countries - UK has the highest count of customers at 3,951 out of 4,373 total customers. - While Customer 17850 has ordered the most product by quantity, customer 14646 has contributed to the most amount of revenue at $279,489.02 out of a total of $9,747,747.93 in the 13 months observed. - Our average order price is $17.99, the average unit price is $4.61, and the average quantity ordered is 9.55 units. - For 2011, the best month by revenue was November at $1,461,756.25.

.css-ytumd6{-webkit-text-decoration:none;text-decoration:none;}Exploratory Data Analysis in SQL

Customer Analysis

Product Analysis

Time Series Analysis

Main Takeaways

Exploratory Data Analysis in SQL