Project Description

For the Maven Cafe a 30 day test period in which different offers have been sent to existing rewards members shall be analyzed to identify the most important customer groups and derive a strategy for future offer campaigns.

Objectives

1. Which are the Cafe's key customer segments?
2. Is it profitable for the Cafe to run Offer campaigns?
3. Which offers are most beneficial to the Cafe?
4. How can the visibility and completion rate of offers be maximized?

Data preparation

All my data preparation was done in Power BI (Power Query). After importing all 3 tables and picking the best data type per column, I created a few calculated columns, such as age and income groups, Time of Membership and a more readable offer name. Especially the events table required some preparation, to separate the key-value-pairs in the value column needed to be separated in multiple columns through splitting, unpivoting and pivoting.

I also created an additional table that contained only transactions that were offer driven and merged the offer_id and rewards amount. At that point I already noticed, that a customer could use multiple offers on one transaction, which made it difficult to provide clean statistics on revenue generated by offer.

In addition, I created a custom data table, which was not really necessary as there was only one date field. But since it is good practice and provides more freedom for date breakdowns, I included it anyways.

Lastly, I created a measure table to contain all measures I created throughout the development of the dashboards.

Insights on Customers

• The largest group of customers which also accounts for the most revenue is between the age of 50 - 69
• Most customers have either a low or medium income (under 80k). The AVG amount spent per transactation as well as the AVG rewards collected increase with the income of the customer
• There are more men than women signed up for the rewards program, but women generate more revenue in total and in AVG, they also have a higher ratio of offer driven transactions
• The Cafe managed to grow the number of their registered rewards members over time, with the most transactions coming from newer rewards members.

Insights on Transactions and Offers

• Sending new offers to the rewards customers has an immediate positive effect on the amount of transactions made and revenue generated.
• With a higher offer frequency, total revenue, revenue - rewards and even non-offer-driven revenue increase.
• Revenue - rewards indicates the profitability of the offers, since the Rewards reduce the total profits and can be seen as the cost of the offer strategy. Sending offers every 3-4 days generates the best results on this KPI.
• Even the non-offer-driven revenue increases with shorter offer intervals, which can either indicate a social proof effect, or a positive impact of the informational offers, which can't directly put into relationship with any transactions.
• The highest visibility of offers is achievedf by using the combination of web, email, mobile and social media for distribution
• High visibility and longer offer duration lead to a higher completion rate
• The average Revenue - Rewards is higher for every offer than it is for transactions without offers, but Discounts are most profitable.

Recommendations

1. Target middle aged customers with medium to high income as they generate most revenue.
2. Run a campaign to figure out if there are any products missing for the male customers, since there are significantly more men registered, but women generate more revenue.
3. Continue sending offers to registered rewards customers, as they have a positive impact on the Cafes Revenue, even after substracting the rewards.
4. Send out new offers every 3-4 days for the highest effect.
5. Provide more Discount offers, because they have the best cost to generated revenue ratio.
6. For best visibility, distribute all offers through web, email, mobile & social media.