Coffee Shop Sales

In this project, I analyzed transaction records for Maven Roasters, a fictitious coffee shop operating out of three NYC locations.

Dataset Summary

Dataset includes the transaction date, timestamp and location, along with product-level details. The dataset can be found here!

Recommended Analysis

  1. How have Maven Roasters sales trended over time?

  2. Which days of the week tend to be busiest, and why do you think that's the case?

  3. Which products are sold most and least often? Which drive the most revenue for the business?

Data Cleansing and Transformation

To enhance the value of this dataset, several calculated fields (highlighted in blue) have been added to the transactions sheet:

  • Column L – Revenue: Calculated by multiplying the transaction quantity (Column D) by the unit price (Column H).

  • Columns M–Q – Date and Time Parsing: Extracted detailed components from the transaction dates and times:

    • M – Month: Extracted using Month(transaction_date).

    • N – Full Month Name: Retrieved using TEXT(transaction_date, "mmmm").

    • O – Weekday Number: Determined using WEEKDAY(transaction_date, 2).

    • P – Weekday Name: Extracted using TEXT(transaction_date, "dddd").

    • Q – Transaction Hour: Parsed using HOUR(transaction_time).

Pivot Tables

*

Pivot Tables *

Following the transformation of the data columns, the dataset was enhanced with pivot tables and pivot charts to address key business objectives:

  • Monthly Revenue: Analyzed to determine the company's revenue by month.

  • Transaction Counts: Evaluated based on various dimensions, including:

    • Month

    • Hour

    • Product Category

    • Product Type

Insights Gained

The objective of reviewing this data was to answer several key questions about Maven Roasters' performance. Through analysis, we were able to answer these questions and uncover additional insights.

Key Insights

Maven Roasters sees its busiest day on Mondays, with 7,150 monthly transactions, driven by the workweek's start. Sales show a steady upward trend, rebounding from a February dip below $30,000 to $55,000 by June. Coffee leads sales with 20,000 transactions over six months, reflecting its core focus, while packaged chocolate underperforms, likely due to placement, preferences, or inventory issues. Coffee and tea dominate revenue, with nearly 35,000 transactions in six months.

Recommendations for Future Strategy

These insights highlight opportunities for Maven Roasters' growth. The steady upward trend affirms the effectiveness of the current business model, while Sunday sales could improve with targeted promotions or events. For packaged chocolate, low performance suggests reconsidering its offerings, improving placement, or introducing new flavors.

Conclusion

Maven Roasters can leverage data-driven insights to boost profitability and customer satisfaction. Steady sales growth and strong coffee and tea performance provide a solid foundation for success. By prioritizing these strengths, the business can maintain its competitive edge and enhance customer loyalty.

Addressing weaker areas, such as low packaged chocolate sales, offers opportunities to refine the product lineup, improve inventory, or introduce new marketing strategies. Targeting slow days like Sunday with promotions or events could also maximize revenue.

In a competitive market, adapting to trends and customer needs positions Maven Roasters for sustained growth and long-term success.