Cafe Promotional Offer Analysis Dashboard

1. Introduction

This report analyzes customer segmentation and offer performance at a café, focusing on how different demographic groups and offer types impact customer behavior, sales, and engagement. Key customer segments include gender, age groups, income brackets, and membership types. The report also examines offer duration, completion rates, and the channels used for offer distribution.

2. Customer Segments Analysis

Key Customer Segments

• Gender Distribution:
  • Male (M) customers form the largest segment at 57.23%, followed by Female (F) at 41.34%, and Other (O) at 1.43%.
• Membership Groups:
  • Established Members represent the largest segment with 9,995 customers.
  • Recent Members comprise 4,198, while Early Members account for 2,807 customers.
• Age Groups:
  • Senior Citizens (56-85 years) are the most dominant group, with 6,829 customers. This segment generates the most sales and shows the highest offer completion rate (51.7%).
  • Mid-Age Adults (36-55 years) follow, making a significant contribution to sales and offer completions (49% completion).
  • Adults (18-35 years) have the lowest completion rate (39.1%) and the lowest average sale per transaction.
• Income Groups:
  • Low-Income customers make up the majority (39.8%) of the base, indicating high price sensitivity.
  • Middle-Income customers follow closely at 35.3%, while High-Income customers make up 13%.

Customer Growth by Membership Year

• Highest Growth: Customer growth peaked between 2016-2017, with an 83.47% increase in 2017.
• Decline in New Members: 2018 saw a 35.11% decline in new members, signaling a need for retention strategies or new customer acquisition efforts.

3. Insights Discovery:

• Advanced calculations were made to determine completion rates and how different factors (such as duration and reward levels) influenced customer behavior.
• Data visualization techniques in Power BI allowed us to uncover trends, such as the optimal timing for offer completion and the impact of multi-channel marketing strategies.
• For example, we identified that Senior Citizens have the highest average transaction values and completion rates, while Adults underperformed, suggesting potential for more targeted strategies.

4. Segmentation & Performance Metrics:

• We calculated key performance indicators (KPIs) such as offer completion rates, sales contributions, and average sale per transaction across different customer segments.
• Metrics such as the reward-to-sale ratio and time to completion were crucial in identifying high-performing segments and offer effectiveness.

5. Offer Performance and Analysis

Offer Completion and Sales Performance

• Duration Impact:
  • 7-day offers have the highest completion rate (56.2%) and generate $301,899 in sales.
  • 10-day offers have the highest average sale per transaction ($20.52) and a 57.2% completion rate, driving $160,712 in sales.
  • 5-day offers show the lowest completion rate and generated $154,013 in sales, with a $21.34 average sale per transaction.

Income Impact on Offers

• Low-Income Group: This segment is the largest but tends to focus more on offers with rewards, showing a higher reward-to-sale ratio. Targeting this group with discount offers may further boost engagement.

Sales and Reward Efficiency:

• Senior Citizens are the most profitable segment, generating $322,539 in sales with a 51.7% completion rate.
• Mid-Age Adults follow closely, contributing $204,689 in sales with a 49% completion rate.
• Adults generated modest sales of $55,480 with a 39.1% completion rate, indicating a potential for targeted offers to improve engagement.
• Elderly Customers have the lowest sales of $33,915, suggesting they may require tailored, lower-difficulty offers.

6. Channels and Offer Types

Offer Channels:

• Multi-Channel Promotions: Offers promoted through a combination of web, email, mobile, and social media are the most effective in driving customer engagement and boosting sales.

Offer Types:

• Discount Offers: These offers are more popular and generate higher sales compared to Bogo (Buy One Get One) offers. Discounts drive efficient rewards and sales, with a lower risk of cannibalizing future revenue.

7. Behavioral Insights

• Response Time: Most customers view offers within 24 hours of receiving them, and typically make purchases within 2-5 days. However, 17.1% of views occur after the offer expiry, indicating a need for better communication or urgency in offer terms.

• Senior Citizens and Mid-Age Adults respond more quickly to offers, especially those with shorter expiration times. They seem to prefer offers that give them less time to act.

• Adults take longer to make purchases, suggesting that they may need more attractive or personalized offers to increase their engagement.

8. Recommendations:

• Focus on Senior Citizens and Mid-Age Adults: These groups generate the highest sales and complete the most offers. Keep targeting them with personalized and relevant offers.
• Improve Engagement for Adults: Since Adults take longer to respond and show lower interest, try offering them deals with shorter timeframes, more personalized rewards, or offers that match their specific preferences.
• Use More Discount Offers: Discount offers are more popular and result in higher sales than "Buy One, Get One Free" (BOGO) offers. Continue to use more discounts in future promotions.
• Better Communication on Offer Expiration: Many offers are being viewed after they expire. Ensure that messaging about offer deadlines is clear and creates urgency, so customers are more likely to act before the offer ends.
• Maximize Reach Across All Channels: Use web, email, mobile, and social media together for promoting offers. This will help you reach more customers, especially younger ones.
• Target Low-Income Customers: Since most customers fall into the low-income category, focus on offering easier-to-complete deals and discounts to boost their engagement and increase offer completion.
• Retention Strategies for Existing Members: With fewer new members joining in 2018, focus on keeping current customers loyal by offering exclusive rewards or personalized deals. This will help maintain their engagement.