Maven Cafe Reward Challenge

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".