2019 Sales Overview (SQL and Excel)

This was a dataset with 12 tables, one for each month of the year for 2019. Tools used for this project include MySQL and Excel. The original dataset was fairly clean with 6 columns in each table, all the same.

Shortcomings with this dataset would be the only customer information provided being an address. However, it appears to be B2C transactions, so identifying top customers is not going to be as crucial as it would be to understand and nurture relationships with top customers in a B2B setting.

This dataset was downloaded from Kaggle: https://www.kaggle.com/datasets/knightbearr/sales-product-data

MySQL:

I used MySQL for initial exploration and analysis of the dataset to quickly identify which months were producing the most sales and which products were bringing in the most sales revenue. I also ended up using queries to change the table and column names to allow for easier reference when joining tables. When finished with exploratory analysis, I joined all 12 tables (185,686 records) and exported to Excel. Screenshots of some queries and results are available for viewing.

Excel:

Once the table was combined with a union query, I brought it to Excel for further analysis. While in Excel, I split columns like purchase_address and purchase_date to evaluate these on a more detailed level. I then used pivot tables to explore the data in different ways and identify more trends, bridging off the exploration done with SQL.

Once I had found insights I deemed important for stakeholders, I began to create a dynamic dashboard with PivotCharts. I used a timeline which is connected to the two visualizations on top to explore results by down to a single month. The line chart is helpful for identifying shorter term trends, but is not useful when evaluating a single month at a time.

I included a table showing the top 5 individual days for sales revenue and the bottom chart shows which 5 cities generated the most revenue.

Lastly, I was able to modify the format of the dashboard by setting a print area to fit nicely onto one sheet of paper in case a stakeholder prefers to have a physical copy.

Findings for Stakeholders:

Interesting trends shown from the line chart indicate a spike in sales revenue near the end of Q1 followed by a steady decline until the holiday shopping season (October through December). It would be interesting to further explore the company's marketing data to identify successful and failed campaigns to boost sales in the summer months.

Further exploration also showed the 5 days which generated the most sales revenue were all in December. In fact, the top 10 all fell in December. This was surprising given the influence Black Friday has and the volume of sales typically shown within the week.

The product which produced the greatest sales revenue for 2019 was the Macbook Pro Laptop, with just over $8 million in sales revenue. The Macbook Pro also has the highest cost per unit. The next two products with the most sales were the iPhone and ThinkPad Laptop, respectively.

Recommended Action:

I would find it important to evaluate different marketing campaigns and how they correlate with sales numbers over the year. This could help identify trends or opportunities to boost sales numbers in the summer months.

Another opportunity would be to use Black Friday as a way to boost sales numbers in the holiday season. This could give us a variety of top 10 sales revenue days for 2020 and future years.