CAFE REWARDS CHALLENGE- IDENTIFY KEY CUSTOMER SEGMENTS & DEVELOP A DATA DRIVEN STRATEGY FOR FUTURE PROMOTIONAL TARGETING

About this project

Challenge Objective

For the Maven Rewards Challenge, I played the role of a Senior Marketing Analyst at Maven Cafe.

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

The results need to be summarised in a report that will be presented to the Chief Marketing Officer.

Customers receive offers once every few days and have a limited time to redeem them. These can be informational offers (simple advertisement of a product), discount offers, or "buy one, get one" (BOGO) offers. Each customer receives a different mix of offers, attempting to maximise their probability of making a purchase.

Every customer purchase during the period is marked as a 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.

About The Data Set

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.

Analysing the Data

The first step is to put the data in an appropriate shape by cleaning the data to eliminate the fields or aspects of the raw data that's not needed for my analysis and report preparation.

Many customers didn’t provide information on gender. I replaced the empty values in the gender column with “NO GENDER”. Some customers didn’t provide information on Income. I replaced empty values in the Income column with 0.

This is to enable easy analysis and to prevent blank values appearing in Pivot Tables.

Secondly, I identified the main customer segments; these are Males, Females, Others and No Gender (“No Gender” are customers who did not indicate their sex in the customer database).

Thirdly, I analysed the data by identifying the different customer segments such as Age Groups and Income Brackets.

Customer Age has been grouped into Age Groups as described below:

AGE RANGE: 18 – 30, 31 – 40, 41 – 50, 51 – 60, 61+

AGE GROUP: YOUNG ADULTS ADULTS SENIORS ELDERS AGED

Customer Income has been grouped into Income Brackets as described below:

INCOME RANGE: 0 – 30,000, 31,000 – 60,000, 61,000 – 90,000, 91,000+

INCOME BRACKET: LOWER INCOME MIDDLE INCOME HIGHER INCOME WEALTHY

I realised the need to create 4 fact tables out of the original EVENTS table using POWER QUERY in Excel. I created an EVENT_OFFER_RECEIVED table, EVENT_OFFER_VIEWED table, EVENT_OFFER_COMPLETED table and EVENT_TRANSACTION table.****

I ended up with 4 FACT tables and 2 DIMENSION tables.

The fourth step is to load the data into the data model in Microsoft Excel 2019. I identified the relationship between the FACT tables and the DIMENSION tables and created the relationship between the tables.

undefined

IDENTIFICATION OF CUSTOMER SEGMENTS

Number of customers by Age Group and Income Bracket:

The SENIORS, ELDERS and AGED form 81% of total customer base whilst the ADULTS and YOUNG ADULTS form only 19% of total customer base.

MIDDLE INCOME & HIGHER INCOME Brackets form 74% of total Customer base whilst LOWER INCOME & the WEALTHY form only 26%.

Customers that Received Offer by Age Group & Income Bracket:

The SENIORS, ELDERS and AGED form 81% of total customers that received an Offer whilst the ADULTS and YOUNG ADULTS form only 19%.

MIDDLE INCOME & HIGHER INCOME Brackets form 74% of customers that received an Offer whilst LOWER INCOME & the WEALTHY form only 26%.

IDENTIFICATION OF CUSTOMER SEGMENTS & COMPARISON OF OFFER VIEWED / OFFER COMPLETED

Offer Viewed by Age Group and Income Bracket:

The SENIORS, ELDERS and AGED form 82% of total customers that viewed offers whilst ADULTS and YOUNG ADULTS form only 18%.

MIDDLE INCOME & HIGHER INCOME Brackets form 73% of total Customers that viewed offers whilst LOWER INCOME & the WEALTHY form only 27%.

Offer Completed by Age Group & Income Bracket:

The SENIORS, ELDERS and AGED form 82% of total customers that Completed an Offer whilst the ADULTS and YOUNG ADULTS form only 18%.

MIDDLE INCOME & HIGHER INCOME Brackets form 78% of customers that Completed an Offer whilst LOWER INCOME & the WEALTHY form only 22%.

COMPARISON OF COUNT AND TOTAL AMOUNT OF TRANSACTIONS BY AGE GROUP & INCOME BRACKET

Count of Transactions by Age Group and Income Bracket:

The SENIORS, ELDERS and AGED add up to 76% of total Count of Transactions whilst ADULTS and YOUNG ADULTS add up to only 24%.

MIDDLE INCOME & HIGHER INCOME Brackets add up to 79% of total Count of Transactions whilst LOWER INCOME & the WEALTHY add up to only 21%.

Amount Received for Transactions by Age Group & Income Bracket:

The SENIORS, ELDERS and AGED add up to 82.68% of total Amount of Transactions whilst the ADULTS and YOUNG ADULTS add up to only 17.32%.

MIDDLE INCOME & HIGHER INCOME Brackets add up to 74.66% of Amount received whilst LOWER INCOME & the WEALTHY add up to only 25.34%.

ANALYSIS OF OFFER TYPES AND CHANNELS USED IN COMMUNICATING OFFERS TO CUSTOMERS

OFFER RECEIVED:

The SENIORS, ELDERS & AGED received 80% of all Offers whilst ADULTS and YOUNG ADULTS received 20% of all Offers. Our BOGO and DISCOUNT Offer Types reached a larger number of customers when sent via the Channels: [Web, Email, Mobile, Social].

RECOMMENDATION: Direct future Promotional Offers via same channels to SENIORS, ELDERS & AGED.

OFFER VIEWED:

The SENIORS, ELDERS & AGED viewed 82% of all Offers whilst ADULTS and YOUNG ADULTS viewed 18% of all Offers. Our BOGO and DISCOUNT Offer Types are viewed by a larger number of customers when sent via the Channels: [Web, Email, Mobile, Social].

RECOMMENDATION: Direct future Promotional Offers via same channels to SENIORS, ELDERS & AGED.

OFFER COMPLETED:

The SENIORS, ELDERS & AGED completed 82% of all Offers received. YOUNG ADULTS and ADULTS completed 18% of all Offers received. Our BOGO and DISCOUNT Offer Types are completed by a larger number of customers when sent via the Channels: [Web, Email, Mobile, Social].

RECOMMENDATIONS:

Direct future Promotional Offers via the Channels: [Web, Email, Mobile, Social] to SENIORS, ELDERS & AGED. Customers in MIDDLE & HIGER INCOME BRACKETS complete more Offers than Customers in LOWER INCOME BRACKET and the WEALTHY.

SUM OF REWARDS FOR OFFERS COMPLETED:

The SENIORS, ELDERS & AGED received 82% of the REWARDS. YOUNG ADULTS and ADULTS received 18% of the REWARDS. Our BOGO and DISCOUNT Offer Types generates a higher number of rewards when sent via the Channels: [Web, Email, Mobile, Social].

RECOMMENDATIONS:

Direct future Promotional Offers via the Channels: [Web, Email, Mobile, Social] to SENIORS, ELDERS & AGED. Give more REWARDS to Customers in MIDDLE and HIGHER-INCOME BRACKETS than Customers in LOWER INCOME BRACKET and the WEALTHY.