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

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

Customer Analysis

What are the countries to which our customers reside?

SELECT DISTINCT(country) FROM 'updated_data.csv'

How many transactions in each country?

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

How many customers are there per country?

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

How many customers do we have in total?

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

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

--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

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

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 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.

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

Average Price Per SKU?

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

Average Order Price

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

Average Unit Price

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

Average Order Quantity

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

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

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

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

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()

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

-- 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

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.

--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.

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.

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.

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.