Brewing Up a Cup of Success with Maven Brewers Coffee Shop

Brewing Up a Cup of Success - A Coffee Shop Analysis

For this project I was acting as a franchise owner at Maven Roasters, a coffee shop chain with three locations in New York City. The assignment was to transform data into a dynamic dashboard that franchise owners can use to identify patterns, trends, and opportunities for the business. The main goal is to understand purchase behavior and streamline operations.

The data used was downloaded into Excel from Maven Analytics and includes transaction records from a fictitious coffee shop in New York City. The Excel file has a single table including 149,116 records and 11 fields.

I first explored the coffee shop dataset, conducted some basic data QA and profiling, and added calculated date and time fields.

Findings:

There are 149,116 transactions over the span of 6 months: JAN 1, 2023 to JUNE 30, 2023.

The products and items sold include the following categories: Bakery, Branded, Coffee, Coffee Beans, Drinking Chocolates, Flavours, Loose Tea, Packaged Chocolate and Tea

Process:

I added columns to the dataset using basic calculations and formulas for Revenue, Weekday and Hour of Day and formatted columns appropriately.

I created a couple Pivot Tables and then visualized the data with Pivot Charts and designed an interactive dashboard, and then identified insights and recommendations for the coffee shop. I also created a Maven Roasters logo for this project.

Interesting patterns/trends and suggestions for company improvement:

1. Revenue each month from January 1st, 2023 - June 30th, 2023 shows an upward trend into the Spring and Summer months across all locations with a drop in February. I suggest they target any promotional items in January-February to boost revenue during the winter months (perhaps a special flavor of Hot Chocolate?)

2. Most transactions occur during the week with Monday as a common day for sales. However, Hell's Kitchen seems to be very popular on Sunday instead of Monday. Saturday seems to be the least popular across all stores. Running weekend promotions would increase revenue for weekends for Astoria and Lower Manhattan. I suggest running a special on Saturdays in all stores to get more customers in the door.

3. 8am - 10pm are the busiest times of the day across all locations. Astoria has a higher transaction rate throughout the day. I recommend making sure staffing is appropriate for the morning rush. I suggest having a larger number of afternoon/evening employees in Astoria to improve operations. I also noticed Astoria has a lack of transactions from 8pm-7am. I would find out if they are closing or opening their location at the correct times. If they are, then perhaps they can either promote early bird specials to increase revenue at 6am or open the store a little later than other locations to save on payroll.

4. Coffee and Tea are the most popular selling categories across all locations. Bakery follows as another popular category. Perhaps running a Coffee or Tea with a Bakery item combo or BOGO deal can increase revenue.

5. Barista Espresso brought in the most revenue, followed by Gourmet brewed coffee and Brewed Chai tea. We can increase revenue even more by creating variations of these popular Product Types, like new flavors or iced versions.