Brew-haha Beans & Banter Customer Behavior Analysis

My goal is to transform the data into a dynamic dashboard that franchise owners can use to identify patterns, trends and opportunities for the business. I will achieve this by:

• profilling and preparing the raw data for analysis
• exploring the data with excel pivot tables
• building a dynamic dashboard to visualize patterns and trends

The data for this project was sourced from Maven Analytics for educational purposes. The following columns make up the original dataset:

• transaction_id — each transaction has a unique id
• transaction_date — date transaction took place
• transaction_time — date transaction took place
• transaction_qty — number of items purchased during transaction
• store_id — specific store in which transaction occurred
• store_location — location of store in which transaction occurred
• product_id — id of the product
• unit_price — unit price of product purchased
• product_category — the category the product purchased falls under
• product_type — type of product purchased
• product_detail — extra detail of product

I conducted an exploratory data analysis and cleaned the date. After cleaning, data transformation was next. New columns created :

• time_of_day : {6am — 12pm : morning; 12pm-5pm : afternoon; after 5pm : evening}
• total_amt : total amount of money spent during transaction
• day_of_month: the day in the month transaction occurred
• time_of_day — the time in the day transaction occurred
• day_of_week — the day in the week transaction occurred

Pivot tables were created to summarize the data and expedite chart creation. I mostly use bar, column and line charts to visualize the data.

These were my findings:

• Hell’s Kitchen is our best performing branch. Followed closely by Astoria. Lower Manhattan is consistently lagging behind in patronage.

• Already made Coffee and Tea are our best sellers which aligns with a cafe.

• Loose Tea, Branded and Packaged Chocolate are the worst performing products. People can acquire these items at retail chains while purchasing household items.

• High volume days are Friday and Thursday, when the workweek is ending. While weekends are low volume days as more people sleep in and enjoy leisure time, not needing coffee to stay alert.

• Mornings are naturally when we get the most patronage.

• As we establish our presence in the various locations, we are building a steady client base as shown with the consistent increase in revenue over our first 6 months.

Finally, these were my recommendations:

• More data is required to investigate what is causing Lower Manhattan’s bad performance. We could possibly conduct customer surveys, and investigate the location’s proximity to other cafes.
• We could invest in stocking high quality artisanal loose tea, branded and packaged chocolate, specifically those hard to come by regular marts and supermarkets to attract coffee and tea connoisseurs.