Purpose
The purpose of this project was to perform exploratory data analysis of coffee shop transactions for a coffee chain to be able to uncover gaps for opportunities based on the results obtained.
Results / Stakeholder Suggestions
​​​
The key determination found was that while product popularity was generally constant, and coffee and tea were the most popular products, transaction volume varied greatly depending on the store.
The Manhattan store had close to no sales in the last two hours of the day, in comparison to other stores which also experienced higher sales in the morning rush hour, but remained constant throughout the day until closing.
A possible solution here would be to close the store earlier to be able to minimise costs since the Manhattan area requires a different operation to the other stores.
​
​
Process (Calculations and Formatting)
When opening the data in Excel I first applied a row filter to be able to visualise the different entries and determine what sort of transformation could be made.
I began by adding column for revenue and used a multiplication function with amount sold and transactions to be able to calculate it.
To be able to read the data more clearly I added used the =TEXT and MONTH etc functions columns to format how months, weekdays would be shown, so that they were more clear.
I also extract the hour from the transaction time field in column B.
Process (Pivot Tables)
To hone in on the data an get a closer look to be able to create relevant charts, I added pivot tables to show total revenue by month, as well transactions by day of week, and hour of day.
This gave a time frame reference to the data and allowed for comparison and analysis on different levels to be able to find out what was occurring with the sales.
When creating the tables I selected the relevant fields and determined the values, if by sum or count depending on revenue or transaction amounts.
Process (Visualisation/Pivot Charts)
Finally, in order to visualise the tables and switch between store locations, I created pivot charts and inserted a splicer to be able to toggle between location, and see the values returned for each store.
I selected different types of charts based on the values used.
For revenue by month I opted for a line graph and it's more clear to visualise the sales over time.
For others such as transactions by day and hour I chose a bar chart as it's easier to view and compare the days and times this way.
