Business Case

For this project, I've been tasked to analyze a sales dataset from Maven's Cafe. The business owner is interested to learn some key insights from the data to make data driven decisions.

The dataset contains 149,116 transaction records from January to June 2023 from three store locations, the foundation for creating an interactive dashboard in Excel.

Work Methodology

The data was loaded into Excel following the ETL principle (Extract, Transform and Load):

Extract

The dataset was converted into a table and then loaded into Power Query for data transformation. This was done with the purpose of having all the transformation steps pre-established and ready for new data input.

Transform

For the transformation process, five steps were applied in Power Query for this data set:

1. A custom column named "Revenue calc" was created. It will calculate the revenue by multiplying the transaction quantity times the unit price.

= Table.AddColumn(Source, "Revenue", each [transaction_qty]*[unit_price]

2. A second column was added into the process. Its purpose is to extract the names of the months from the date column. This would return the full name of the month in the column, instead of having that outcome, a function like "LEFT" from Excel was included to return the name of the month abbreviated into three letters.

= Table.AddColumn(#"Revenue calc", "Month Name", each Text.Start(Date.MonthName([transaction_date], "en-US"), 3), type text)

3. Another column, like the previous one, was added. Its purpose is to extract the names of the days of the week. This step replicates the previous step, with some slight adjustments within the M code.

= Table.AddColumn(#"Inserted Month Name", "Day Name", each Text.Start(Date.DayOfWeekName([transaction_date], "en-US"), 3), type text)

4. The default change type step that Power Query creates was rearranged as a fourth step for just having a single change type step in the process. I've proceeded to change the data type of the columns that I considered necessary.

= Table.TransformColumnTypes(#"Inserted Day Name",{{"transaction_id", Int64.Type}, {"transaction_date", type datetime}, {"transaction_time", type time}, {"transaction_qty", Int64.Type}, {"store_id", Int64.Type}, {"store_location", type text}, {"product_id", Int64.Type}, {"unit_price", Currency.Type}, {"product_category", type text}, {"product_type", type text}, {"product_detail", type text}, {"Revenue", Currency.Type}})

5. A fourth and final column was added. It extracts the hour from the column that contains the transaction time. For data presentation purpose, I've included in the code a text function that displays the hour from an integer to an "hour o'clock" format.

= Table.AddColumn(#"Changed Type", "Hour", each Time.ToText(Time.From([transaction_time]), "HH:00"), type text)

Load

Once I was done implementing transformation steps into the query, I proceeded to load it as a connection. From said connection, I proceeded to create several pivot tables that would provide the following information:

• The total revenue by month.
• The total of transactions done per day of the week.
• The total of transactions done per hour of the day.
• The total of transactions by product category.
• The top fifteen product types with their respective total revenue and transaction count, sorted by the total transactions in descending order.

From said pivot tables, I proceeded to create the visualization materials that were required for the dashboard:

• A line graph that shows the revenue by month.
• Two column charts that shows the total transactions by day of week and by hour of day.
• A bar chart that shows the total transactions by product category.
• The pivot table that was created earlier that shows the top fifteen product types with their respective total revenue and transaction count.
• A data slicer connected to all the pivot tables created, filtering the data by store locations.

Finally, I proceeded to adjust the dashboard design and format for a presentation purposes, using some of Maven's Cafe colors.

Insights

• As of June 2023, June was the best month yet in sales, reporting ta total revenue of 166,485.88 among the three store locations, while February was the lowest with 76,145.19.
• There's sales trend over 21.5K sales done on Monday, Thursday, and Friday.
• There's a tendency of high transactional volume from 7:00 a.m. to 10:00 a.m., being 10:00 a.m. the "peak hour" for all store locations. This could be due to the beginning of work shift for most customers that work near the stores that would like to have some coffee or tea before starting to work.
• It has been observed that there is a noticeable drop on sales from the late hours, from 19:00 to 20:00 on Lower Manhattan and on 20:00 on Hell's Kitchen. For the aforementioned locations, this could imply in losses due to low demand within that time of the night. The only exception noticed is Astoria, which maintains a steady transactional volume during night hours.
• Coffee and tea are the most sold product category among all locations, while branded merchandise and packaged chocolates are the least sold. If we consider this with the trends that were noticed from 7:00 to 10:00, we can safely say that people are more interested in buying coffee or tea as a booster before starting their shift at work.

Recommendations

• Reconsider operating hours, close the stores at19:00. Operating past 19:00 would affect business profitability due to low demand at within that hour of the night, there are few potential customers with graveyard shift that would approach the stores and buy products.
• Ensure that there are enough personnel available during peak hours, to ensure customer satisfaction and sales.
• Ensure proper stock availability due to the peak hours and high demand of two key products: Brewed chai tea and Gourmet brewed coffee.
• In the case of the chocolates that are the least sold and their shelf life vary depending on its packaging and preparation, consider employing promotional sales (for the purchase of X coffee, get for 25% off for Y chocolate) in order to minimize the potential risk of stock spoilage.