Promotion Success: Data-Driven Strategies for Smarter Campaigns

INTRODUCTION & CHALLENGE OBJECTIVE

I have taken the role of a Sr. Marketing Analyst at Maven Cafe and present the data to the CMO, below being the objective.

You've just run a test by sending different combinations of promotional offers to existing rewards members. Now that the 30-day period for the test has concluded, the task is to identify key customer segments and develop a data-driven strategy for future promotional messaging & targeting.

ABOUT THE DATASET

Data that simulates the behavior of Cafe Rewards members over a 30-day period, including their transactions and responses to promotional offers. The data is contained in three files: one with details on each offer, another with demographic information on each customer, and a third with the activity for each customer during the period. The activities are divided into offer received, offer viewed, offer accepted, and transaction. For a transaction to be attributed to an offer, it must occur at the same time as when the offer was "completed" by the customer.

TOOLS USED

Power BI

DATA PREPARATION & MODELLING

Data Cleaning / Preparation :

The data was pretty much clean and the key changes made were with regard to "removing the 118 age" as it felt like an outlier, I thought it might compromise the result and hence I decided to remove it.

For a transaction to be attributed to an offer, it was essential to duplicate and merge 2 tables.

The Offer IDs felt a bit longer and hence meaningful names were given to it like clubbing the name of the Offer type - Min Spend - Reward - Duration.

Age-wise, Membership-wise, and Income-wise categorizations were made for better understanding.

** The Conversion Rate is calculated from viewing to completion, I thought this was important in order to know the engagement with the Offers, which will be important to study about the Offer Engagements.

Dashboard Designing:

I went on to create a 4-page dashboard excluding the Home Page where I was keen on presenting data that doesn't overwhelm the User with tons of Visuals on a single page, yet provides the necessary information. This time I decided to have Insights & Recommendations in the Dashboard itself.

INSIGHTS & RECOMMENDATIONS

KPIs:

No. Customers - 14825

No. Customers who received Offers - 14820

No. Customers who viewed Offers - 14675

No. Customers who completed Offers - 11986

No. Transactions - 123.96K

No. Transactions driven by Offers - 32.44K

Total Sales - $1.73M

Sales driven by Offers - $675.78K

View Rate (%) - 74.98%

Completion Rate (%) - 48.79%

Average Days to View - 3

Average Days to Complete - 4.5

Conversion Rate (view to completion) - 65.07%

Insights:

1. Channel Effectiveness :

"W-E-M-S" (combination of Web, Email, Mobile, Social) has driven the highest number of offer views and sales, contributing significantly to the success of the offers. Other combinations like W-E-M and E-M-S have also shown promising engagement.

While W-E-M-S leads in views and sales, the completion rate across channels varies, with W-E showing the highest completion rate at over 50% maybe it indicates the effectiveness of Email being the efficient Channel.

2. Offer Type Effectiveness :

Despite "BOGO (Buy One Get One)" offer showing significant engagement, and "Discount" Offers showing comparatively lower view counts, the discount offers generated higher sales compared to other types.

3. Offers (Transaction & Sales) :

Offers account for 26.17% of total transactions and contribute to 38.95% of the total sales, indicating that a large portion of revenue is driven by promotional activities.

4. Customer Engagement & Offer Performance by Demographics :

The "Mid-range Income" earners who also fall under the "Committed Members" category dominates the customer base as well as in the generation of Sales. The "Mature adults" are the most active groups across Customer engagement, Offer Performance & Sales.

5. Offer Types leading in Sales Generation :

"Discount" Offers once again takes the lead, with 10-day offer duration generating $95K in Sales. Other offers consistently perform well but lag a bit behind the Discount Offers.

6. Sales across 30 days (In Hours) :

Sales and offer completions show spikes at specific points in time, coinciding with key promotional campaigns. Also noticeable trend is the Sales peak around 500 - 610 hours window.

7. How quickly was a offer viewed and completed?

Offers viewed instantly and within 24 hours amounts to a significant portion of views and the effective channel was W-E-M-S.

Most of the Offers are completed either within a day or on an average within 2-5 days, however the offer with a minimum spend of $20 are completed only after 5 days (on an average).

8. Rewards / Min Spend / Duration Analysis :

Offers with $5 reward has higher conversion rate and generate high sales followed by $2 and $3.

Offers with higher minimum spends show significant lower completion rates and $5 and $7 proves promising.

Offers with 7-day durations perform best, with a 62.4% completion rate and a high sales impact ($333.17K).

Offers with 10-day durations have slightly lower completion rates but still perform well in terms of overall engagement.

Recommendations:

1. Focus on High performing Channel :

Invest more resources into W-E-M-S by prioritizing it for upcoming offers, boosting its reach, and refining the offers sent through this channel.

Revisit strategies for underperforming channels (E-M-S and W-E). It may be necessary to either reduce investment in these channels or explore retargeting strategies to improve engagement and completion rates.

2. Leveraging Discount Offers :

Prioritize discount offers in your campaigns, as they drive higher sales while maintaining strong completion rates.

Experiment with mixing low-reward and moderate-reward discounts to create a balanced offering, as the highest sales tend to come from mid-range rewards ($2-$5).

3. Targeting Key Customer Demographics for maximum ROI :

Segment campaigns by income, age, and membership, with a particular focus on mid-range income and older customer groups. These groups have the highest purchasing power and are most responsive to offers.

Retain committed members by providing personalized offers, loyalty programs, or exclusive deals, as they form the backbone of your sales engine.

4. Optimizing Offer Duration :

Keep offer durations between 7 to 10 days, as this is the optimal timeframe for driving both completions and sales.

5. Increasing Offer Views :

Target non-viewers with retargeting campaigns & Implement time-sensitive messaging in W-E-M-S to encourage immediate viewing (such as limited-time offers or instant rewards) to increase the percentage of instant views.

6. Aligning Promotions with Sales Trend & prioritize Rewards and Minimum Spend :

Optimize promotional timing around key periods (e.g., end of the month or key shopping seasons) to ensure continuous sales momentum.

Stick to mid-range rewards ($2-$5) & Consider offering tiered rewards that start low and increase based on customer engagement or spending thresholds to encourage continued participation.

For mass-market customers (e.g., mid-range income and younger segments), keep the minimum spend threshold low to increase completions. For highly engaged customers, set higher minimum spend thresholds to boost sales, particularly for premium or exclusive products.

CONCLUSION

This project was a great opportunity in terms of learning to decide what is required and what to leave out. Also, calculations for certain things were tricky and it was an amazing learning experience. Also, though I did not explicitly include RFM analysis here, I came to know about it and I'm currently learning to perform that as well.