__STYLES__

Unlocking Customer Loyalty: A Data-Driven Strategy for Targeted Rewards

Tools used in this project
Unlocking Customer Loyalty: A Data-Driven Strategy for Targeted Rewards

Power BI Maven Cafe Rewards Challenge Report

About this project

For this project I am the Senior Marketing Analyst at Maven Cafe

I've been asked to build a report that summarizes the results of the 30-day test of promotional offers that will be presented to the CMO.

We need to identify the key customer segments and develop a strategy for future promotional message and targeting using the information provided in this report.

The data was provided in a set of 3 csv files for event information, customer information and promotion information

The report comprises of:

Home Page - A page to introduce the report and it's purpose.

Summary Page - A summary page showing key information from the data set.

Customer Page - A page showing key customer information.

Offers Page - An analysis of the offers sent to customers.

Recommendations Page - A Page with recommendations for future promotion campaigns.

Home Page

undefinedThis is the report welcome screen, click on an images to go to the selected page.

Summary Page

undefinedThis page contains text summaries and key visuals from pages within the report.

16,994 customers received an offer of which 16,834 offers were viewed giving a 99.1% offer view rate.

Males are the largest group of our rewards membership program with 49.9% or 8,464 of membership. The 50K-79K: Medium Income group has the most members with 7,327 members. Most of our members are in the 65-188: Seniors age group with 6,441 members. 2017 was the year we had the most members join our rewards membership scheme where we gained 6,469 new members.

Discount just slightly edged out Bogo (Buy-one-get-one) as the most received offer with just over 40% of received rewards being of this offer type. Web was the channel with the most completed offers with a completion rate of 49%. Informational was the offer type that was most completed as a customer just had to read the offer.

Customer Page

undefined

At 84.4%The 50-64: Pre-Retirement age group had the highest completion rate completing 4,347 offers out of of 5,148 offers received. The 80K+: High Income group had the highest completion rate at 93.3% where 3,467 offers were completed out of 3,716 offers received.

Our largest income group of our members is the 50K-79K: Medium Income group.

The key insights for this page are that encounters generated $101 million in revenue which is on average a 10% year on year increase. Procedures generated $105 million in revenue. The discrepancy is due to procedure cost being calculated on base cost. Whilst encounter revenue is calculated on total claim cost. one encounter can have multiple procedures. The total procedure base cost may not be shown total the claim cost and would require discussions with stakeholders for further clarification.

Urgent care encounters raised $23M in revenue or 22.7% of total encounter revenue. The number of encounters where insurance contributed to the cost was 14,173 or 51.22% of encounters. White people visited the hospital the most with 19,315 encounters or 69.8% of the total encounters.

Encounters peaked in 2014 with $12 million in revenue raised and 2020 had a lower peak with $10.3 million in revenue.

Offers Page

undefinedThe Bogo offer type was the type of offer that the 65-118: Seniors age group received the most of with 5,684 Bogo offers being received. We found that the 50-64: Pre-Retirement age group completed the most offers.

Across all the age groups with between 46.2% and 47.9% of the members of each age group viewing their offer after 10 days.

Bogo was the offer type most received by the 50K-79K: Medium Income group, just beating Discount. With 6,449 Bogo offers being received.

Females were most likely to complete an offer with a completion rate of 88% compared to just 75.6% for Males.

Overall, only 44% of all offers were completed. Members who joined in 2016 had the highest offer completion rate with 89.4% of offers being completed.

Recommendations Page

undefinedIt was noted that offers with higher difficulty rates had higher completion rates. The 80K+: High Income group had the highest completion rate of 87%. It was noted that the 80K+: High Income group preferred the offer with a $2 reward with a completion rate of 84.1%.

The Web channel had the highest completion rate with a rate of 49%

Additional project images

Discussion and feedback(0 comments)
2000 characters remaining
Cookie SettingsWe use cookies to enhance your experience, analyze site traffic and deliver personalized content. Read our Privacy Policy.