__STYLES__

Maven Cafe Reward Challenge

Tools used in this project
Maven Cafe Reward Challenge

Maven Cafe Reward Dashboard

About this project

Objective:

As a Senior Marketing Analyst, the goal is to use the sample data to identify key customer segments and provide recommendation on promotion strategies.

Key steps taken in preparation:

  1. In Power Query, performing data cleaning by removing duplicates, splitting column, extracting value and formatting.
  2. Identify key metrics of customer base and offers.
  3. Data modelling between fact and dimension tables.
  4. List down key questions to answer about customer segments and offer/channels.
  5. Build calculated columns and measures for intended visuals.

Key Assumptions:

In some cases, completed offers don't have a corresponding previously viewed offer. It is 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.

Findings:

On Customer Segments:

  • 17000 existing customers and 16994 of them received offer(s).

  • Offers received generates high engagement: 99.7% was viewed and 75.2% result in completion.

  • Mature adults (age 36 - 65), moderate income population, and customers became members between 2016-2017, have been cafe's main target audience' attributes.

  • Mature adults made most purchase and sales, followed by senior group (age 66 - 94), then adults (age 18 - 35), indicating a strong appealing to mature age customers, and opportunity to explore younger demographic.

  • Low-income customers is dominant (50.7%), suggesting possible high price sensitivity in their purchase behaviour.

  • On average, it takes 91.97 hours/3.8 days for customers to view, and 61.27 hours/2.6 days to make purchase in response to the offers received.

  • Majority of customers view the offer within 24 hours after receiving it, and 17.1% views are expired, which means customer offer was viewed after offer expiry date and cannot be converted to transaction.

  • Regardless of offer expiry days (duration), majority of customers make purchase within 2-5 days after receiving it.

  • The Adult group tends to spend less time reviewing offers but takes longer to make a purchase compared to the Mature Adult group, indicating that the offers might be less appealing to them.

  • Some offers were completed and rewarded after expiration, suggesting a loophole that allows customers to redeem expired offers.

On offers and channels:

  • Offer is a significant booster for transaction and sales: offer drove up transaction volume by 22.0% of and sales amount by 34.7%.

  • Large amount of offers received is a key driver for transaction, however, at 336-hour mark, despite higher offer amount, its transaction and sales generated is less than 500-hour mark. Further information will be needed to understand the discrepancy, as there might be factors such as seasonality, demographic shift, change in competitors, etc that may influence the pattern.

  • Discount offer is favoured by customers over Bogo (Buy one get one). Discount offers generate less rewards which prevents potential reduce in sales in the future.

  • Promoting offer using all 4 channels (web, email, mobile, social) is the most effective in increasing sales.

  • By filtering customer segment, Low-income segment, Adults, and Elderly age group have higher rewards to sales ratio, suggesting they are likely to be influenced by rewards and make deliberate purchase rather than out of habit.

Recommendations:

  • Continue to send offers on regular basis as it has been proven to be an effective approach to drive sales.
  • Expand customer base to extend offers to larger audience.
  • Explore younger demographic by sending offers that appeals to their lifestyle, and implement more social media promotion as younger group is more exposed to it and less sensitive to traditional promotion approach like loyalty program.
  • Promote more discount than Bogo offer as it generates less rewards.
  • Offers could be tailored to incentivise different customer segments. eg.
    • Offers can focus less on rewards and more on convenience and quality for Mature Adult and High-Income group as they seems less driven by rewards.
    • Encourage early response from Adult group by sending offers with shorter duration/expiry days to reduce their purchase lag, and allow longer duration/expiry days for Senior or Elderly groups.
    • Provide additional incentives for recent members to encourage more active purchase and loyalty, such as "Exclusive rewards for new member".

Additional project images

Discussion and feedback(16 comments)
comment-1691-avatar
Olorunfemi Tunde-Adedipe
Olorunfemi Tunde-Adedipe
14 days ago
Hi Lin, you've done a really good job. If I may ask, how did you get the transaction and sales with and without offer? I don't understand the logic behind both. Could you please explain it?

comment-1693-avatar
Muhammad Ovais
Muhammad Ovais
14 days ago
Great Work Zhu, I am studying your dashboard and trying to learn the way you did your Analysis. I would like to connect with you to resolve the issues i am facing.

comment-1704-avatar
Bui Minh Duc
Bui Minh Duc
12 days ago
Hi Lin, your dashboard so beautiful. If you don't mind, you can send me your report to email: bduc0608@gmail.com. I want to refer to understand the logic behind your dashboard

comment-1705-avatar
Romina Stefan
Romina Stefan
12 days ago
Hi Lin ! Very nice dashboard, I love the structure and the questions followed by charts 🌸🙏😊 I think the "events" file is not completed, meaning it's a missing piece of data for the transactions. It should be an extra column for "transaction" event with the offer_id related to that transaction , or in the"value" column where we see now the amount like this {'amount': 2.8} it should be like this : {'amount': 2.8, 'offer_id': '2906b810c7d4411798c6938adc9daaa5'} . We don't know sure if a transaction is exactly related to a certain type of offer even if it occurred simultaneously because I saw at the same time many transactions with different customer_id and just one offer completed . Hope this makes sense! 🙏

comment-1720-avatar
Paul Emmanuel UDOH
Paul Emmanuel UDOH
10 days ago
This is lovely, simple and interactive. Wish to get the data to replicate this for learning purpose. Good job

comment-1753-avatar
Steven Jeromi P
Steven Jeromi P
5 days ago
Visually appealing dashboard. Great work

comment-1773-avatar
Mariam Abdalgaed
Mariam Abdalgaed
1 day ago
Great Work

comment-1776-avatar
Thạch Quyên Dương
about 5 hours ago
Hi Lin Zhu! I'm really impressed with your dashboard and logic. I would like to learn more about how you present and solve the problem so can you send me the PBI of this case study? If possible, you send via email:duongthachquyen@gmail.com. I really thank you and hope that soon get feedback from you
2000 characters remaining
Cookie SettingsWe use cookies to enhance your experience, analyze site traffic and deliver personalized content. Read our Privacy Policy.