Situation:

You're a franchise owner for the coffee chain Maven Roasters. The franchise has 3 locations in New York City. You want to analyze the data to understand purchase behavior and streamline operations.

Tasks:

• Profile and prepare raw data for analysis
• Explore the data with pivot tables
• Create visuals to understand dashboards and trends

For this project I used Google Sheets instead of Excel. First, because it's free. Second, because I have a Mac, and there can be functionality issues with Excel.

Preparing for analysis:

• I created calculated fields (seen in blue) to get the revenue for all transactions (quantity x unit price).
• I used MONTH and WEEKDAY functions to extract month and day of the week from "transaction date."
• To get the month and day names, I used text functions such as =text(B7,"mmm").

Exploring the data with pivot tables:

• I created pivot tables to slice and dice the data.
• First, I created a pivot table to show revenue by month.
• Then, I created ones that showed transactions by day of week and hour of day.
• After, I created another table to show transactions by product category.
• I also added a slicer to filter by store location.

Slicer in use (showing results for Astoria):

Uh oh, Google Sheets!

This project was originally intended for Excel users, and that's where I hit a snag. One step asked users to create a pivot table of the top 15 product types sold (individual products vs broad categories). But if you try this in Google Sheets, it'll crash.

So I set out to find a way to get the same results, using a different method.

I decided to use the UNIQUE function to get the name of each of the 30 products in the product type column. Next to it, I used a COUNT IF function to find the number of transaction with those products.

I then sorted Z to A to have the top products appear at the top. I then shrunk the list down to the top 15. You can see the results on the next page.

Visualizing the data

Then, it was time to visualize the data to provide insights. I used the pivot tables to create charts, and used best practices like eliminating grid lines and borders to clean up the visuals. I then used the visuals to draw key insights for the business.

If the goal is to increase sales during non-peak hours, maybe consider a promotion.

Transactions stay pretty steady overall (1 is Sunday).

Coffee and tea should remain well-stocked. Packaged chocolate? Not so much. We might be able to let that one go to focus on best-selling products.