Comprehensive Guide to Sales Analysis and Reporting

Comprehensive Guide to Sales Analysis and Reporting

Data Overview

The dataset contains sales data for various customers, including order details, sales amounts, product lines, and deal sizes. The dataset has 25 columns and 2823 entries. Key columns include ORDERNUMBER, QUANTITYORDERED, PRICEEACH, SALES, ORDERDATE, STATUS, PRODUCTLINE, CUSTOMERNAME, COUNTRY, DEALSIZE, and others.

Data Preparation

  1. Data Loading:

    • The dataset was loaded from a CSV file using pandas.

    • Data was encoded with 'latin1' to handle special characters.

  2. Date Conversion:

    • The ORDERDATE column was converted to datetime format for time-based analysis.
  1. Sales by Year:

    • 2003: $3,516,979.54

    • 2004: $4,724,162.60

    • 2005: $1,791,486.71

  2. Sales by Quarter:

    • Sales peak in Q4 each year.

    • Highest quarterly sales were in Q4 of 2004 ($2,014,774.92).

  3. Sales by Month:

    • Sales peak in November each year.

    • Highest monthly sales were in November 2003 ($1,029,837.66) and November 2004 ($1,089,048.01).

Top-Selling Products and Categories

  1. Top 10 Products:

    • S18_3232: $288,245.42

    • S10_1949: $191,073.03

  2. Top 10 Categories:

    • Classic Cars: $3,919,615.66

    • Vintage Cars: $1,903,150.84

Customer Segmentation

  1. Customer Sales Data:

    | CUSTOMERNAME | TOTAL_SALES | TOTAL_ORDERS | MOST_COMMON_DEALSIZE | SALES_SEGMENT | ORDER_FREQUENCY_SEGMENT | | --- | --- | --- | --- | --- | --- | | AV Stores, Co. | 157807.81 | 3 | Medium | Very High | Medium | | Alpha Cognac | 70488.44 | 3 | Medium | Medium | Medium | | Amica Models & Co. | 94117.26 | 2 | Small | High | Low | | Anna's Decorations, Ltd | 153996.13 | 4 | Small | Very High | High | | Atelier graphique | 24179.96 | 3 | Medium | Low | Medium |

  2. Customer Segmentation Summary:

    | SALES_SEGMENT | ORDER_FREQUENCY_SEGMENT | MOST_COMMON_DEALSIZE | CUSTOMER_COUNT | AVG_TOTAL_SALES | AVG_TOTAL_ORDERS | | --- | --- | --- | --- | --- | --- | | Low | Low | Medium | 7 | 50956.38 | 2.00 | | Low | Low | Small | 9 | 47269.63 | 1.89 | | Low | Medium | Medium | 5 | 43937.33 | 3.00 | | Low | Medium | Small | 1 | 64591.46 | 3.00 | | Low | High | Medium | 1 | 33440.10 | 4.00 |

Key Insights

  1. Sales Performance:

    • Sales increased significantly from 2003 to 2004 but dropped in 2005.

    • The highest sales were recorded in the fourth quarter and in November of each year.

  2. Top Products and Categories:

    • Classic Cars and Vintage Cars are the top-selling categories.

    • Product S18_3232 is the top-selling product.

  3. Customer Segmentation:

    • Customers are segmented into Low, Medium, High, and Very High based on their total sales and order frequency.

    • Medium and Very High segments are dominant in terms of sales volume.

    • Most common deal sizes are Medium and Small.

Recommendations

  1. Focus on Top-Selling Products and Categories:

    • Increase marketing and stock for top-selling products like S18_3232 and categories like Classic Cars.
  2. Enhance Customer Engagement:

    • Develop targeted marketing strategies for Very High sales segment customers.

    • Offer loyalty programs and discounts to Medium and High segment customers to increase order frequency.

  3. Analyze Sales Drop in 2005:

    • Investigate reasons for the drop in sales in 2005 and implement corrective measures.
  4. Optimize Inventory Management:

    • Maintain optimal stock levels for top-selling products to prevent stockouts.
  5. Improve Shipping and Delivery:

    • Ensure timely shipping to maintain customer satisfaction and encourage repeat purchases.

By leveraging these insights and recommendations, the company can enhance its sales strategies, improve customer satisfaction, and optimize inventory management.

Complete code

Comprehensive Guide to Sales Analysis and Reporting

HNG