Introduction
This is my another project with Power BI and SQL tools. In this project I analyze sales data of the coffee houses.
I focus on the building KPIs which can be helpful to summarized sales of coffee and other products.
On the other hand, I chose this topic because I am lover of coffee. Also, when I'm in a cafe, I wonder what the income might be and what hours of the day there is the most work.
For this purpose, I used sample training data from kaggle.com to practice analytical thinking and the use of the above-mentioned tools.
Dashboard overview
The dashboard below presented data for coffee houses sales from January to June of 2023 year. Dataset includes information about three locations: Hell's Kitchen, Astoria and Lower Manhattan.
KPIs - helpful tool to keep eye on the business growth
Total Revenue: This KPI shines a spotlight on the café's financial success. With a grand total of $700.78K the café's sales. It's a powerful metric that offers a quick glance at the overall health of the business, capturing the combined result of all coffee, tea, and other sales over the period (the first half of 2023 year).
Total Transactions: In a quick overview, the KPI - Total Transactions summarizes all the work of the three cafes over a common period. In total it was over 149K transactions.
Average Transaction Value: This metric shows the average order value. This allows us to better illustrate how much money customers spend on average in cafes.
Average Unit Price of Coffee: Priced at $3.03 on average, this KPI shows on the café's pricing strategy for its coffee offerings. This metric is useful to compare coffee unit price with competition.
Average Unit Price of Tea: Unlike a cup of coffee, which is the basic product of every cafe, it is also worth taking a look at the price of tea. Tea is often the second element in a café's offer, but it is often treated as an additional product without in-depth analysis. It is also worth knowing the price for a cup of tea.
Insights
Analyzing the above dashboard, several observations and trends can be distinguished:
- The cafe 'Hell's Kitchen' earned the highest revenue in the analyzed period. This value was estimated on $238K.
- Coffee sales accounts for approximately 39% of total revenues for all three locations.
- Tea sales are also important and are responsible for as much as 28% of total revenues.
- The upward trend in the number of transactions for three locations in the analyzed period also deserves mention. May and June are particular months with a significant increase.
- Peak hours for all locations are 7:00 AM - 10:00 AM. However, it is worth noting that in 'Hell's Kitchen' and 'Lower Manhattan' there is a decline in traffic during afternoon work hours. On the other hand, Astoria is seeing slight growth to compare with two other locations.
Prescriptive Analysis
In order to increase the number of transactions, and therefore total revenues, it is worth combining the collected information.
Customers usually buy coffee products in the morning until 10 a.m., which is illustrated by the chart of the number of transactions during the day. However, cafés offer also other products such as bakery products and drinking chocolate. Together, these two product categories account for approximately 22% of total revenues. This accounts for as much as 1/5 of total revenues, so this is a lot. Additionally, also such products can be ordered in the afternoon.
Therefore, in a future marketing campaign, drinking chocolate and confectionery products may be combined into one set at a promotional price in order to encourage customers to order more of such products. It is worth introducing promotions, especially in the afternoon, so that they do not conflict with drinking standard coffee in the morning.
Technical background
The raw dataset was cleaned and combined to there separate tables that I combined into one diagram in Power Bi for data integrity. The relationship between tables presents schema below:
DAX functions
For the building dashboard and KPIs I used DAX functions such as:
- Total Revenue:
Total Revenue = SUMX( Transactions, Transactions[transaction_qty] * RELATED('Product Detail'[unit_price]))
- Total Transaction
Total Transaction = COUNT('Transactions'[transaction_id])
- Average Transaction Value
Avg Transaction Value = [Total Revenue]/ [Total Transaction]
- Average Price For Coffee
AveragePriceForCoffee = CALCULATE(AVERAGE('Product Detail'[unit_price]), 'Product Detail'[product_category] = "Coffee")
- Average Price For Tea
AveragePriceForTea = CALCULATE(AVERAGE('Product Detail'[unit_price]), 'Product Detail'[product_category] IN {"Tea", "Loose Tea"})
SQL queries
In order to practice the analytical functions which were used in DAX, I also analyzed them using SQL language.
Below presents KPIs analyzed in Microsoft SQL Server Management Studio:
Summary
This project allowed me to learn in practice what KPIs are and how to analyze sales data. Additionally, my goal was to combine two analytical tools: PowerBi and SQL.
Although in this particular project I used strictly training data, but I was very focused on professional analysis of sales data as if it could be applied to a specific coffee houses chain.
However, the most interesting element of the project was finding insights and coming up with a way for cafes to develop further.
On the other hand, I noticed that the data could have been analyzed in a different way for example these three cafes could have been compared as competitive points but not the one web.
References
Source of dataset: https://www.kaggle.com/datasets/ahmedabbas757/coffee-sales
The theme image was generated by an AI tool.
Add comment
Comments