The Project

This is my first project about an analysis of the coffee shop's performance. It has three branches in New York City, and the analytics report presents findings on what factors may affect the business' success.

The Objective

Make data-informed decisions to find opportunities to drive sales, and foot traffic, and improve inventory management.

Identify the busiest period, peak sales, and best/worst selling product by visualizing the sales report, operations, and product performance.

Track and reduce pastry wastage, knowing the number of guests arriving and their preference for efficient pastry inventory management.

The Data

The sample dataset was downloaded from Kaggle.com

https://www.kaggle.com/datasets/ylchang/coffee-shop-sample-data-1113/

The sample data contains nine data tables for April 2019 only:

• Sales Receipts
• Pastry Inventory
• Sales Targets
• Customer
• Dates
• Product
• Sales Outlet
• Staff
• Generation

Techniques

Data Preparation - I 'cleaned' the data by removing duplicate rows/data, formatting data types, and deleting missing values but retaining incomplete rows but with useful information.

Data Manipulation - I organized it first with MS Excel and lightly manipulated the data. Then with Power Query, I refined the data by replacing data values, changing data type, and formatting columns for better visualization in Power BI.

Sample:

Raw data

MS Excel lightly cleaned data - proper letter case, indicate proper weight(lb and oz)

Power Query cleaning data

Data Analytics

Product Sales Data

To visualize the sales of the business I used the transaction and product table to create a total sales measure. Then I use that measure to identify**** the top-selling products, sales by product group, and category in charts.

Foot Traffic Data

Using the customer data I created a line graph to know when the foot traffic starts to decline. With this data, I learned that the shop sells better on weekdays.

*Note: there's a significantly large data of customers identified as customer_id 0, there are no records identifying them in customer_data table but they made a lot of transactions based on the transaction data table. Therefore in the age group/generation column, they are identified as Diverse Generation.

Product Performance and Inventory Management (Pastry Wastage)

Beverage(Coffee and Tea) generated the highest sales. Meanwhile, the pastry Ginger Scone has the highest and most costly waste or spoilage among the other pastries.

What else I might have done(if I had additional data to work on this project)

• Dig deeper into what insights I can mine from the 'product promotion' column.
• Weekly sales report
• Customer preference, habits, and total consumption of beverages(liters)