Coffee Shop Sales Dashboard (Power BI)

The primary goal of this project is to assess coffee shop sales for three coffee shop locations over a span of 6 months commencing in January and concluding in June using Power BI.

Revenue and transactions

• Monthly sales have shown a steady increase from the second month (February), with the highest revenue recorded in the sixth month (June) for all stores.
• 72% of revenue is generated during weekdays for all stores
• On weekdays, Hell's Kitchen experiences its highest transaction volume on Friday (7489) and Tuesday (7472), signifying these as the busiest days. Similarly, Astoria's busiest weekdays are Thursday (7427) and Monday (7403). Lower Manhattan's busiest weekday is Monday (7136).

Busiest days of the week for each store

• On weekdays, Hell's Kitchen experiences its highest transaction volume on Friday (7489) and Tuesday (7472), signifying these as the busiest days. Similarly, Astoria's busiest weekdays are Thursday (7427) and Monday (7403). Lower Manhattan's busiest weekday is Monday (7136).

Top 3 product categories across all 3 stores.

a) Coffee

• Coffee sales, contributing 39% to the combined revenue of all three stores, amount to $269,952, making it the top-selling product category across all locations.

• Best selling coffee products:

  • Astoria: Gourmet brewed coffee (Ethiopia Small) selling 1619 units (although Barista espresso generates a higher revenue)
  • Hells Kitchen: Barista Espresso (Latte) selling 1500 units
  • Lower Manhattan: Barista Espresso (Latte) selling 1541 units

• Coffee transactions in Astoria and Hell's Kitchen are nearly identical, with only a 1k difference in revenue.

• Lower Manhattan lags, with a 1k difference from Astoria and 2k from Hell's Kitchen.

• Beverage preferences (that is sizes and prices of beverages) in different locations drive revenue variations.

• Due to limited data, we cannot determine which products determine a greater profit.

b) Tea

• Tea sales contribute 28% to the combined revenue of all three stores, totaling $196,406, establishing it as the second best-selling product category across all locations
• Best selling tea products:
  • Astoria: Brewed Chai Tea (Spicy Eye Opener Chai, Large) selling 1634 units
  • Hells Kitchen: Brewed Chai Tea (Morning Sunrise Chai, Regular) selling 1589 units
  • Lower Manhattan: Brewed Chai Tea (Morning Sunrise Chai, Regular) selling 1509 units
• Astoria significantly outperforms Hell's Kitchen and Lower Manhattan in tea transactions, demonstrating strong performance in that store.
• Despite Lower Manhattan having the fewest tea transactions, the revenue difference from Hell's Kitchen is only 1k.

c) Bakery

• Bakery sales, contributing 12% to the combined revenue of all three stores, amount to $82,316, making it the third best-selling product category across all locations.
• Lower Manhattan leads in bakery transactions, surpassing both Astoria and Hell's Kitchen.
• Astoria and Hell's Kitchen have equal bakery transactions, with a 1k revenue difference compared to Lower Manhattan.

Store hours

• Hells kitchen and lower Manhattan operating hours are from 6h00-20h00, whereas Astoria's hours are from 7h00-19h00.
• Busiest hours for Hell's Kitchen and Lower Manhattan: 6 am - 10 am (4 hours).
• Astoria's busiest hours: 7 am - 10 am (3 hours), consistent on both weekdays and weekends.
• The busiest hours collectively generate $341,970, constituting 49% of total revenue.
• Non-peak hours, from 11 am to 8 pm (9 hours), generate $356,843, comprising 51% of total revenue.

Conclusion

In conclusion, the analysis of monthly sales, transaction trends, and product categories across three stores provides valuable insights into the business performance. Interestingly, Lower Manhattan, while making the least in revenue, makes the most during peak hours compared to the other stores. Astoria makes the most during non-peak hours compared to the other stores despite reduced operational hours. All stores are close to each other in terms revenue generated. There is room to understand more of the customer base between these two stores to understand these differences. This comprehensive analysis equips the business with actionable insights for optimizing operations and maximizing revenue across all three locations.

Actions performed in Power BI

I performed transformations on the date column to extract information from it.

I extracted the month name in order to create a month name column. (Shown in the transactions by month visualisation)

I extracted the month number in order to create a month number column which was used to order months in the correct order. (Shown in the transactions by month visualisation)

I extracted the name of day in the week. (Shown in the transactions by each day of the week visualisation)

I extracted the day number in order to create a day number column which was used to order the days of the week. (Shown in the transactions by each day of the week visualisation)

I performed transformations on the time column to extract information from it.

I extracted the hour from the time column in order to create the hour column. (Shown in the transactions per hour visualization)

Calculated column

I created a calculated column called total price. I used the unit price multiplied by the transaction quantity column in order to create this column. (This was used in the total revenue card and revenue generated by store location visualization)

Measure

I created a measure called Percentage which was used to generate a percentage based on the revenue generated by transactions. (Shown in % of revenue generated card)