Coffee Shop Dashboard

Business Task

Transform data into a interactive dashboard for franchise owners to use to make decisions for their branch of the coffee shop.

Data Sources Used

For this project, I used the Coffee Shop Sales data from Maven Analytics. The data is organized in an excel spreadsheet.

Column Titles

transaction_id, transaction_date, transaction_time, transaction_qty, store_id, store_location, product_id, unit_price, product_category, product_type.

Cleaning and Organizing the Data

The first thing that I did was to check for missing and duplicate data. I then checked to make sure that the date format was consistent and there were no extra spaces in the data. Next I added a column to calculate the revenue found by multiplying the price by the quantity.

After calculating the revenue, I added new columns to calculate the month, day of the week, and hour that a transaction occurred.

Pivot Tables and Dashboard

I started out creating different pivot tables for revenue such as revenue by month, by day of the week and by hour. I then looked at the number of transactions by product category. Finally, I created a pivot table for the revenue and transaction quantity for the top 15 products.

After creating my pivot tables, I started to work on my dashboard to create visualizations for the different pivot tables along with a slicer so franchise owners could focus on their own store when looking at the dashboard.