Project Scope and Purpose

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:

KPIS Tracked:

• Number of Transactions
• Total Sales in Dollars
• Total Profit in Dollars
• Total Items Sold

Additional Business Questions Answered:

1. What are the KPI values company wide?
2. What is the percentage change in KPI values week over week throughout the month?
3. What are the KPI values for individual product_category?
4. Did the company meet its Units Sold goal for each product group for the month?
5. What are the KPI values for individual store locations?
6. Did each store meet its Units Sold goal for each product group for the month?

Exploratory analysis

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:

Transactions Table unique key fields:

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.

Sales_Targets Table:

For the sales targets table, I faced a few issues.

1. There is no documentation in data source web site or the kaggle clarifying if these goals are for dollars sold, profit dollars, or units sold. The product group labels in this table also do not align precisely with those in the product group are product category fields in the product table. Because of this lack of clarity, I have assumed that the goals are by product group, with the group of "beans" aligning with the product group of "Whole Beans/Teas" from the product table, and I have assumed the goals are for Units sold, as the scope of the goal numbers aligns most closely with the actuals for units sold.

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.

2. The sales_targets table is in the form of a pivot table:In order to perform joins and effectively analyze the actual vs targets, I performed a lateral join on this table to unpivot it, and added that table to the the database schema. Here are the new table and code used for the transformation.

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.

Business Questions:

1. What are the KPI values company wide?

2. What is the week over week change in KPIs throughout the month?

Takeaway:

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.

Query Code:

3. What are the KPI values for each individual product_category?

Takeaway:

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.

Query Code:

4. Did the company meet its units sold targets in each product group?

Takeaway:

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.

Query Code:

5. What are the KPI values for individual store locations?

Takeaway:

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.

Query Code:

6. Did each store meet its units sold targets?

Takeaway:

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.

Query Code:

Summary

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.

Next Steps of Analysis:

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.