The purpose of this project is to provide high level analysis of sales and insights into KPIs for the month of April 2019 for a fictional coffee shop. The data set used for this project can be found here and can also be accessed on Kaggle, which is where I found the data. For this initial high level analysis I limited the scope of what questions I wanted to answer, and thus will only be using the Transactions, Product, and Sales_target tables. The KPIs and Business Questions I will address are:
I first examined the tables to familiarize myself with the various fields and do any necessary cleaning. While there was no cleaning required in these specific tables as the initial data set was fairly clean, I will point out a few key observations:
While the label transaction_id would suggest that field is a unique key for the table, there are actually many duplicates in that field. As seen below, transaction_ids start over numbering at 1 on each day at each store location.
Throughout my analysis, I will concatenate the transaction_id, transaction_date, and sales_outlet_id fields to get unique transactions. Note that even this concatenated key can be duplicated into multiple line items in the transaction table if more than one product was purchased on the transaction, however I will not be analyzing by line item in this project, so I did not use product_id as part of the unique key.
For the sales targets table, I faced a few issues.
I am including analysis of actual vs targets here to demonstrate an understanding of this key metric and how to calculate it, however because of the lack of clarity in the source data, any conclusions for this part of the analysis should taken with a grain of salt.
3. The sales_targets table includes targest for 10 store locations, while the transactions table only lists sale for locations 3, 5, and 8. Througout this analysis, when I refer to sales or targets company-wide, I will be referring to these three stores only, and when comparing actual vs targets, I will either use inner joins or specifically filter to these stores in order to exclude targets for other locations.
While total dollars sold peaks in week 16 (third week of the month), profit, transactions, and units sold continue to increase into week 17. This indicates that higher margin items are being sold in the final week.
Coffee and Tea beverages account for the largest portion of all KPIs. While Bakery items and Drinking Chocolate also account for large dollars and items sold, they are much less profitable. These items certainly attract customers and should remain on the menu, however coffee and tea should still be the primary focus of inventory and marketing teams to drive profit.
The company exceded target in beverages by 20%, but missed targets in other groups. However, because of the previous observations that beverages are the most profitable and drive the business, this is overall a positive, and the shortfalls in other groups should merely be used to adjust inventory planning going forward.
Store 8 is the leader in all KPIs. Store 5 has the next highest transactions, but performs worse than store 3 in other KPIs. This indicates that store 5 serves more customers and potentially could generate more profit if store staff were trained to push more profitable items and deemphasize lower margin items.
Stores 3 and 8 far exceded beverage goals, hence driving high profit, while store 5 only slightly exceded beverage goal. As prevoisuly observed, if store 5 began to target even higher beverage goals and train staff accordingly, this store could see increase in profit.
Beverages such as coffee and tea drive the profit of the business, and stores that place emphasis on these items are getting more bang for their buck. Marketing teams and store managers should place emphasis on promotions and training that emphasize drinks.
Customer info is available in the data set. Analysis should be performed into customer demographics such as age and gender and correlations between the demographics and purchasing patterns , ie which types of customers patronize the coffee shop more frequently, which groups tend to buy more items on each visit, and which groups buy the most profitable items. This information could be used by marketing teams to drive promotional decisions.
Additionally, store staff experience levels should be analyzed to see if more experienced staff tend to drive more profitable sales.