Thank you for joining me today. I've been working on a comprehensive data analysis, and I'm excited to present the insights and findings I've uncovered. I'll be sharing the results and key takeaways towards the end of this presentation, so please stay tuned for the EDA results.

Regards, Bryan Ronquillo

import pandas as pd import numpy as np import matplotlib.pyplot as plt import plotly.graph_objects as go import plotly.express as px import seaborn as sns

Information

ORDERNUMBER: A unique identifier for each order. QUANTITYORDERED: The quantity of items ordered in each transaction. PRICEEACH: The price of each item in the order. ORDERLINENUMBER: A line number indicating the item's position within the order. SALES: The total sales amount for the order. ORDERDATE: The date on which the order was placed. DAYS_SINCE_LASTORDER: The number of days since the last order was placed. STATUS: The current status of the order (e.g., Shipped, Pending). PRODUCTLINE: The category or line of the product ordered. MSRP: Manufacturer's Suggested Retail Price for the product. PRODUCTCODE: A unique code identifying the product. CUSTOMERNAME: The name of the customer who placed the order. PHONE: The phone number of the customer. ADDRESSLINE1: The primary address line for the customer's location. CITY: The city in which the customer is located. POSTALCODE: The postal code for the customer's address. COUNTRY: The country of the customer. CONTACTLASTNAME: The last name of the contact person for the customer. CONTACTFIRSTNAME: The first name of the contact person for the customer. DEALSIZE: The size of the deal (e.g., Small, Medium, Large).

Importing Data

df = pd.read_csv("Auto_sales.csv") df

from datetime import datetime df["ORDERDATE"] = pd.to_datetime(df["ORDERDATE"], dayfirst=True)

Data

Now we will check how many rows and columns and what type of Dtype we have

df.shape

df.info()

Numerical columns

df_num = df.select_dtypes(include = ["float64","int64"]).drop(columns=["ORDERNUMBER"]) list(df_num.columns)

Categorical columns

df_cat = df.select_dtypes(include="object").drop(columns=["PHONE","ADDRESSLINE1","CONTACTLASTNAME","CONTACTFIRSTNAME"]) list(df_cat.columns)

Descriptive Summary

Descriptive Numerical

display(round(df_num.describe(),2).T)

Descriptive Category

df_cat.describe().T

Cleaning

Missing Values

df.isnull().sum()

Duplicated Values

df.duplicated().sum()

We have observed that there are no missing values or duplicate values in the dataset.

Univariate Analysis

Now, we'll perform univariate analysis to extract insights from each column in our data frame.

Univariate Analysis for Categories

import plotly.express as px import plotly.graph_objects as go from plotly.subplots import make_subplots

def univariateAnalysis_category(cols, data): print("Distribution of", cols) print("_" * 60) # Count plot (bar chart) fig = px.bar(data, x=cols, color=cols, title=f'Distribution of {cols}') fig.update_xaxes(title_text='Categories') fig.update_yaxes(title_text='Count') fig.update_xaxes(categoryorder='total descending') fig.show() # Donut chart (pie chart) value_counts = data[cols].value_counts() percentage = (value_counts / value_counts.sum()) * 100 fig = go.Figure(data=[go.Pie(labels=value_counts.index, values=percentage, hole=0.4)]) fig.update_traces(textinfo='percent+label', pull=[0.05, 0.05], marker=dict(colors=px.colors.qualitative.Set2)) fig.update_layout(title_text=f'{cols}') fig.show() print(" ") cat_columns = df[["STATUS", "PRODUCTLINE", "DEALSIZE"]] for x in cat_columns: univariateAnalysis_category(x, cat_columns)

Univariate Analysis for Numericals

def univariateAnalysis_numeric_plotly(column, nbins): print("\nDescripción de " + column) print("----------------------------------------------------------------------------") print(df[column].describe()) # Create subplots: 1 row, 2 columns fig = make_subplots(rows=1, cols=2, subplot_titles=("Histogram " + column, "Boxplot " + column)) # Histogram hist = go.Histogram(x=df[column], nbinsx=nbins) mean = df[column].mean() sigma = df[column].std() # Adding lines for mean and sigma fig.add_trace(go.Scatter(x=[mean, mean], y=[0, 1], mode="lines", line=dict(color='red', dash='dash')), row=1, col=1) fig.add_trace(go.Scatter(x=[mean + sigma, mean + sigma], y=[0, 1], mode="lines", line=dict(color='green', dash='dash')), row=1, col=1) fig.add_trace(go.Scatter(x=[mean - sigma, mean - sigma], y=[0, 1], mode="lines", line=dict(color='green', dash='dash')), row=1, col=1) fig.add_trace(hist, row=1, col=1) # Boxplot box = go.Box(x=df[column]) fig.add_trace(box, row=1, col=2) fig.update_layout(height=600, width=1000, showlegend=False) fig.show() df_nume = df.select_dtypes(include=[np.number]).columns for x in df_nume: univariateAnalysis_numeric_plotly(x, 20)

country_counts = df['COUNTRY'].value_counts()[:10] fig = px.treemap(country_counts, path=[country_counts.index], values=country_counts.values, title="Top 10 Country Distribution", color=country_counts.values, color_continuous_scale=px.colors.sequential.Blues) fig.update_layout(template='plotly_white') fig.show()

city_counts = df['CITY'].value_counts()[:10] fig = px.treemap(city_counts, path=[city_counts.index], values=city_counts.values, title="Top 10 City Distribution", color=city_counts.values, color_continuous_scale=px.colors.sequential.Blues) fig.update_layout(template='plotly_white') fig.show()

customer_sales = df.groupby('CUSTOMERNAME')['SALES'].sum().sort_values(ascending=False).head(10) fig = px.treemap(customer_sales, path=[customer_sales.index], values=customer_sales.values, title="Top 10 Customers", color=customer_sales.values, color_continuous_scale=px.colors.sequential.Blues) fig.update_layout(template='plotly_white') fig.show()

Bivariate Analysis

Correlation

fig = px.imshow(df_num.corr(), x=df_num.corr().columns, y=df_num.corr().columns) fig.show()

corr = df_num.corr(method='pearson') mask = np.triu(np.ones_like(corr)) fig = plt.subplots(figsize=(8, 5)) sns.heatmap(corr, annot=True,fmt='.2f',mask=mask, cbar=None, linewidth=0.5) plt.show()

Yearly, Quarterly, Monthly, weekly Sales Trend

# Extract year, month, week, and quarter from ORDERDATE df['Year'] = df['ORDERDATE'].dt.year df['Month'] = df['ORDERDATE'].dt.month df['Week'] = df['ORDERDATE'].dt.isocalendar().week df['Quarter'] = df['ORDERDATE'].dt.quarter

by Year

yearly_sales = df.groupby('Year')['SALES'].sum().reset_index() fig = px.line(yearly_sales, x='Year', y='SALES', markers=True, title='Yearly Sales Trend') fig.update_xaxes(title='Year') fig.update_yaxes(title='Sales') fig.update_layout(width=800, height=400) fig.show()

by Month

monthly_data = df.groupby(['Year', 'Month'])['SALES'].sum().reset_index() fig = px.line(monthly_data, x='Month', y='SALES', color='Year', title='Monthly Sales by Year') fig.update_xaxes(title_text='Month') fig.update_yaxes(title_text='Total Sales') fig.show()

monthly_sales = df.groupby('Month')['SALES'].sum().reset_index() fig = px.line(monthly_sales, x='Month', y='SALES', markers=True, title='Monthly Sales Trend') fig.update_xaxes(title='Month') fig.update_yaxes(title='Sales') fig.update_xaxes(type='category') # To treat 'Month' as categorical data fig.update_layout(width=800, height=400) fig.show()

by Quarterly

quarterly_sales = df.groupby(['Year', 'Quarter'])['SALES'].sum().reset_index() quarterly_sales['Quarter'] = 'Q' + quarterly_sales['Quarter'].astype(str) fig = px.bar(quarterly_sales, x='Quarter', y='SALES', title='Quarterly Sales Trend') fig.update_xaxes(title='Quarter') fig.update_yaxes(title='Sales') fig.update_layout(width=800, height=400) fig.show()

by Weekly

rolling_weekly_sales = df.groupby('Week')['SALES'].sum().rolling(window=4).mean().dropna() fig = go.Figure() fig.add_trace(go.Scatter( x=rolling_weekly_sales.index, y=rolling_weekly_sales.values, mode='lines+markers', name='4-Week Rolling Average Sales' )) fig.update_xaxes(title='Week') fig.update_yaxes(title='4-Week Rolling Average Sales') fig.update_layout( title='Weekly Sales Trend (4-Week Rolling Average)', width=800, height=400, xaxis=dict(type='category') ) fig.show()

Results and Insights

Key Statistics: - On average, each order consists of 35 units. - The typical price for one unit is about $101. - The average revenue per order amounts to around $3,553.

Missing Data: - No data is missing or incomplete in any of the dataset columns.

Top-Performing Product Categories: - Classic cars are the top-selling items, with total sales nearing $3.84 million. - Following closely are vintage cars, trucks, and buses.

Sales Patterns Over Time: - Notable increases in sales occurred in November 2018 and November 2019, indicating a possible seasonal pattern.

Best-Selling Products: - The highest-selling product is 'S18_3232,' generating approximately $284,249 in sales. - Other popular items include 'S10_1949,' 'S12_1108,' 'S10_4698,' and 'S18_2238.'

Customer Distribution by Country: - The United States has the most customers, followed by Spain, France, Australia, and the United Kingdom.

Average Sales per Order Line: - Average sales vary based on the order line number, with line 17 having the highest average sales ($3,800.60) and line 18 the lowest ($2,443.72).

Average Unit Price by Product Category: - Classic cars have the highest average price per unit ($115.20), while trains have the lowest average price ($84.11).

Sales Distribution by Deal Size: - Medium-sized deals contribute the most to sales, totaling $5,931,231.47, followed by small and large deals.

Average Days Since Last Order by Country: - The Philippines has the longest average time between orders (2080 days), while Switzerland has the shortest average (1362.94 days).

Number of Unique Customers by Country: - The USA has the highest count of unique customers (32), followed by France (12). Ireland, the Philippines, and Switzerland each have only one unique customer.

Order Status Breakdown: - Most orders are marked as "Shipped" (2541), with "Cancelled" (60) and "Resolved" (47) being less common.

Top Products in Each Category: - For instance, in the classic cars product line, 'S18_3232' is the best-selling item, with sales reaching $284,249.02.

Correlation between Quantity Ordered and Sales: - There is a moderate correlation of 0.55 between the quantity of products ordered and the total sales amount.

Recommendations to the stakeholders

Optimize Sales During Peak Months: Given the observed significant increases in sales in November 2018 and November 2019, it may be beneficial to plan special promotions or marketing campaigns during these months to capitalize on the potential seasonal trend. This can help maximize revenue during the high-demand periods.

Product Focus: Since classic cars are the top-selling product line, consider expanding the product range within this category or promoting related products to further boost sales. Additionally, continue to monitor and analyze the performance of 'S18_3232,' as it's the best-selling product, and explore strategies to maintain or enhance its sales.

Market Expansion: While the United States has the highest number of customers, consider further expanding into Spain, France, Australia, and the United Kingdom, which also have significant customer bases. Targeted marketing efforts and customer engagement strategies in these regions can help increase sales.

Deal Size Strategy: Since medium deals constitute the majority of sales, you might want to explore ways to increase the average deal size. This could involve bundling related products or offering discounts for larger orders to encourage customers to buy more.

Product Pricing: Given the variation in average price per unit across product lines, consider evaluating pricing strategies for products with lower average prices to potentially increase profit margins. Conversely, for high-priced products like classic cars, ensure that pricing remains competitive to maintain sales.

Customer Engagement: Monitor customer engagement and satisfaction, especially in countries with longer average days since the last order, like the Philippines. Implement customer retention strategies to encourage repeat business and reduce the time between orders.

Order Status Analysis: Investigate the reasons behind the relatively high number of "Cancelled" orders (60). Identify common issues leading to cancellations and implement measures to reduce them, such as improved customer support or order fulfillment processes.

Data-Driven Decision-Making: Continue to analyze correlations and trends in your data to make informed decisions. Invest in predictive analytics to forecast future sales and identify potential growth opportunities or areas requiring improvement.

Inventory Management: Given the moderate correlation between quantity ordered and sales, consider optimizing inventory levels to match demand. Overstocking or understocking can impact both revenue and costs.

Data Security and Privacy: Ensure that data security and customer privacy are maintained, especially as you expand into new markets. Comply with local data protection regulations to build trust with customers.