Maven Cafe Offer Report

About This Project

Objective:

As a Senior Marketing Analyst, the goal is to leverage sample data to identify key customer segments and provide actionable recommendations for promotion strategies.

Key Steps Taken:

Data Cleaning: In Power Query, removed duplicates, split columns, extracted values, and formatted data.

Metric Identification: Determined key metrics related to the customer base and offers.

Data Modeling: Established relationships between fact and dimension tables.

Question Development: Listed key questions to answer regarding customer segments, offers, and channels.

❓Which customer segments (age, gender, income) generate the highest revenue, have the highest offer completion rates, and show the most loyalty and retention?

❓Which types of offers (discounts, BOGO, etc.) perform best in terms of completion rates, revenue generation, and customer engagement across different segments?

❓What are the most effective marketing channels (web, email, mobile, social) for delivering offers and driving customer engagement and sales?

❓When is the best time (week, month) to send offers to maximize customer interaction and revenue, and how can offer timing be optimized for underperforming periods?

❓What is the impact of offers on total revenue, and how do transactions with and without offers compare in terms of average transaction value?

❓What strategies can be implemented to improve customer retention, reduce churn, and re-engage at-risk segments?

Measure Creation: Built calculated columns and measures to support the intended visualizations.

Key Assumptions:

• In some cases, completed offers don’t have a corresponding previously viewed offer. It’s assumed that the customer made the transaction without being aware of the offer. Due to the low frequency of such instances, these transactions are categorized as offer-driven, even though customers might have completed them regardless.
• Customer interaction is considered only if the customer viewed the offer messages. In some cases, completed offers don’t have a corresponding previously viewed offer. It’s assumed that the customer made the transaction without being aware of the offer.

Insights:

👉 The total revenue without offers is $1.16M, whereas with offers, it's $616.62K. This shows that while offers contribute a significant portion.

👉 The total number of transactions is 139K, with 31K of those involving offers.

👉 Male customers generated $0.56M without offers and $0.29M with offers where as Female customers generated $0.55M without offers and $0.31M with offers. This suggests both genders respond positively to offers, with a slight preference among females.

👉 Discount offers have the highest conversion rate (58.64%) and have generated substantial revenue across all channels. It is especially popular among the 51-70 age group and customers with an income of $60K-$80K. Where as BOGO offers also perform well but have a slightly lower conversion rate (51.38%) compared to discounts.

👉 Offers sent during Week 3 had the highest engagement, with 72.94% of offers viewed and 45.22% completed. This suggests that the timing of the offer may impact its success.

👉 From the cohorts based on “Membership Date”, customers who became member in 2015 and 2016 show strong offer completion rates, particularly in 2015 where it reaches up to 84.67%. Whereas customers who became member starting in late 2017, there’s a notable drop in offer completion rates as low as 32.51% in 2018. Though the revenue generated by this group is quite good, it suggests that while customers may view the offers, they are not finding them compelling enough to complete.

👉 The average transaction value is relatively high in the 2015 and 2016 cohorts, indicating that these members are valuable customers. The average transaction value declines slightly in the later cohorts, with a noticeable drop in 2018. This suggests that not only are fewer members engaging, but those who do are spending less.

👉 While the number of customers acquired in 2017 and 2018 is high, the engagement and retention of these customers are much lower than in previous years. This suggests that the focus may have shifted towards quantity over quality, leading to a larger but less engaged customer base.

👉$5 as Rewards offer the highest total revenue ($226,113.77) and have a high completion rate (85.61%). Despite having a lower view percentage (61.52%), $10 as Rewards have the second-highest total revenue ($149,324.70), but with a completion rate of just 50.09%, it suggests that customers find it harder to complete offers in this tier. $2 Rewards and $3 Rewards have high completion rates (81.56% and 70.27% respectively) and decent revenues ($161,246.97 and $79,938.24 respectively), indicating these rewards are attainable for customers and still profitable.

👉 Offers with a $10 minimum spend generate the highest revenue despite a lower completion rate, while $20 minimum spend offers have the highest completion rate but contribute the least revenue. $5 and $7 minimum spend offers maintain moderate to strong completion rates and provide steady revenue, appealing to value-conscious customers.

👉 Minimum Spend of $5 has a high completion rate (75.64%) but generates less revenue ($143,132.26) compared to the higher spend levels, which suggests this tier is appealing but less profitable.

Recommendation:

👉 Implement gender-specific messaging to enhance engagement by tailoring discounts and promotions to products favored by each gender.

👉 Prioritize promotional offers via social media and mobile, supplemented by web and email channels, to maximize customer reach.

👉 Focus on Discounts offer as the primary offer type, leveraging top-performing channels. Promote Discount offer for higher-income customers focusing on premium products, and use BOGO offers to target value-driven segments especially Younger or Budget-Conscious customers.

👉 Increase the frequency of promotional offers in Week 3 and Week 4 to boost engagement and sales. Also Focus on Week 1 and Week 2 with new offers to attract the customers like Welcome Offers, Educational Campaigns, Limited-Time Flash etc.

👉 Continue nurturing high-value customers from 2015-2016 with exclusive promotions and tiered loyalty rewards.

👉 Launch targeted campaigns within the first 90 days post-membership to boost engagement for customers who joined in 2017-2018.

👉 Personalize onboarding with tailored offers based on new members' interests or purchase history.

👉 Develop targeted re-engagement campaigns for 2017-2018 cohorts with special offers and engagement incentives.

👉 Shift communication to emphasize the value of membership, focusing on non-monetary benefits such as access to exclusive content, events, or community forums that enhance the overall customer experience.

👉 Introduce rewards for non-purchase engagement activities to keep members involved.

👉 Promote the $5 Rewards tier aggressively, highlight $2 and $3 Rewards tiers, and reassess the 10 Rewards tier for better completion.

👉 Promote $10 minimum spend offers for maximum revenue, monitor the high completion rate of $20 offers for targeted high-value customers, and maintain $5 and $7 offers for broader customer inclusivity.