Project Goal

Identify key Cafe Rewards customer segments and define offers that should used to target them effectively.

Data Set

Data that simulates the behavior of Cafe Rewards members over a 30-day period, including their transactions and responses to promotional offers.

Data Preparation

The data was prepared in such a way that the flow of each offer sent could be linearly tracked. That is, for each offer sent, identify when it was viewed and when it was completed. In this way, it is even possible to measure how long it takes for customers to view or complete an offer.

To relate the events from offer sent -> offer viewed -> offer completed, logic was applied based on a lookup for the next event where the time was ">=" the time of the received offer and of course the customer id + offer id matched (as long as the completed/viewed event was before the offer expired). The tricky part was handling some cases of customers who had more than one valid offer ( same offer type) at the time of viewing or completing. For these cases, a rule was assumed to associate it with the oldest offer received.

To relate completed offers > to transactions, it was simpler. The logic described in the Challenge requirement was used (associate based on the completion time should be equal to the transaction time).

Taking the time data and dividing it by 24, a Day Activity dimension was prepared to be able to know the 30-day timeline associated with each event.

Data Validation, Findings & Assumptions

1. Customers Missing Data

A group of 2,175 (12.8%) customers were identified with null value for Gender & Income. For Age they had "118" as age value. For these cases, in gender they were included as part of the -Others- group, while income and age were grouped as -Unknown-.

2. Informational Offers and Completion rates

To calculate the completion rate, informational offers are not considered. It was assumed that these do not apply to the reward criteria since they are only for informational purposes for the customer. So completion rate only consider bogo + discount offers. For the general calculation of % Viewed, informational offer was included.

3. Offer Driven Sales Calculation

It was calculated based on the sales of transactions attributed to a completed offer. Some notes to keep in mind:

There were cases of transactions with more than 1 offer completed related. This is ok, but when distributing the amount by type of offer the result will not be the exact sum of both (because they coincide in some transactions).

Of the 33,579 completed offer events, all match with the transactions by time + customer id + offer type, however, +400 events were found with discrepancies since they do not have a previously received offer event that matches the duration of the offer. This can generate 2 versions of the Offer Driven Sales calculation, if these records are included the result is around $617k, while if they are excluded the result is around $608k.

Data Analysis

The analysis was divided into 4 tabs:

1. Campaign Results:

Focuses on providing the general results obtained in the 30 days of testing. The idea is to be able to answer questions such as:

• What was the % of offer completion?
• Are there sales driven by offers?
• What type of offer was most profitable?

2. Demographics:

This tab is intended to provide a diagnostic analysis of how the offers performed based on customer demographic attributes such as:

Gender, Salary, Age, and Years as a member.

3. Customer Segments

Here the idea is to provide the segments that were identified to create marketing target strategies. In total, 3 groups of segments were identified depending on the variable of interest to be analyzed:

• Spenders Group -> Segments that allow us to know which customers spent more than others. A basic percentile statistics method was applied to distribute the data on the amount spent by customer in the 30 days.
• Buying Frequency -> Segments that allow us to know which customers visit the Café more often to transact. A basic percentile statistics method was applied to distribute the data on the number of days with transactions (in the 30 days).
• Offers Interactions -> Segments that allow us to know which customers are more receptive to offers and which are not. The distribution was based on the % of offers completed per customer.

4. Recommendations

The idea of this page is to show the key recommendations focused on 6 aspects:

• Offer configuration.
• Which customers are the most effective target
• What demographic profile do targeted customers have?
• What about customers who did not complete offers (rejecters)
• Demographic Profile of customers who did not complete offers (rejecters)
• Key takeaways