For this project, I play the role of a Sr. Marketing Analyst at Maven Café who just got the results from running a marketing test. This test sent out different combinations of promotional offers to existing rewards members.

OBJECTIVE

To identify key customer segments and develop a data-driven strategy for future promotional messaging & targeting.

DATA

Data collected for this project consists of a set of 3 CSV files including 1 table in each. The data stimulates customer behavior of rewards members over a 30-day period.

Table 1: Offers - Details on each offer

Table 2: Customers - Demographic information on each customer

Table 3: Events - Activity for each customer during the 30-day period

Data is sourced from Kaggle, via Udacity with a Public Domain and downloaded for this project from Maven Analytics on 8/7/24.

DATA PREP

after importing the CSV files into Excel, I took the following steps to prepare data for analysis…

1. Checked for missing or duplicate values

2. Checked for proper formatting and labeling

3. Performed basic calculations and checked for outliers and data out of the norm**

4. Identified key metrics, primary and secondary keys

5. Identified key segments for specific metrics

6. Added columns with new segments, calculations and measures needed for visuals

** 2175 customer IDs had “118” for their age. Not only is it very rare anyone would live that long, but extremely rare that many people would be that age. I first could assume that they meant to enter “18” and accidentally pressed the “1” twice. However, there are already 70 entries for the age of 18. Adding 2175 entries to that age would seem like WAY too many 18-year-olds compared to total counts in other ages. For this reason, I chose to treat this like an outlier and made all the “118” ages into null (or empty) values. This means that “101” is the oldest age in the table, which is a lot more reasonable. (These customer IDs are also missing gender and income values, but this shouldn’t be an issue for analysis. I would suggest that the company investigate these memberships and see if there are any issues with them or the data being collected.)

VISUALS

For my visual presentation, I split my analysis into four cohesive dashboards: “Customers, Performance, Behavior and Channels”.

I was inspired by the classic feel of the Café chalkboard menu and wanted to try to mimic that in the overall design.

FINDINGS

Customers:

1. 16,994 customers received offers out of the total 17,000.
2. Out of the 16,994 received offers, 99.1% (or 16,834) customers viewed them.
3. 12,774 customers completed the offers, which was 75.9% of the total views.
4. Males account for a slight majority, with 57% over 41% female and 1% of customers identified as “other”.
5. The average age of customers is 54, with the 55-64 age group accounting for the majority.
6. The average membership length is 1 year and 5 months long, with 58% of all memberships being 1-2 years. The longest membership in the data is 5 years old.
7. The average estimated income is $65,405. Most customers estimate their income between $50K and $70K, and landing in the Lower-middle to Middle income group.

By examining the offers that were completed by each demographic, I suggest targeting female members with a 1 to 2-year long membership, ages 55-64 with a mid-high income, as these members completed the most number of offers.

Performance:

1. There were 306,534 total events over the 30-day period.
2. Of the 76,277 offers received, only 33,579 (about 44%) were completed.
3. 138,953 (or 44%) of all events were transactions.
4. The total revenue (amount spent by the customers) was $1,775,427.
5. With a conversion rate (CR) of 59%, Discount offers are more effective than BOGO offers. Informational offers show no conversion at all, as they don’t have any required purchase or reward.
6. Over half of customers responded to offers with a 7 Day duration. Customers are less likely to complete offers within a 3 to 4-day duration.
7. The number of views and completed offers start out strong at the time the offer is received and then taper off through the duration of the offer. Sales increase over time, peaking near the end of the month.

Behaviors:

1. Most sales from transactions fell below $50, with 53% under $10.
2. Total sales with transactions under $10 stayed consistent over time. The higher the amount spent; the more sporadic transactions are over time.
3. The average sale from a transaction is $12.78, with the lowest sale of $0.05 and the highest at $1,062.
4. Older age groups spent more on average compared to younger groups.
5. Transactions happen in waves, correlated with the time the offer was viewed. Once transactions are made, the number of completed offers drops over time.
6. 33,579 rewards were earned by customers, with a total of $740,693 in rewards.
7. The average reward earned by a single customer is about $5, which makes up 36% of completed offers.
8. BOGO offers rewarded customers with more money over Discounts.

Channels:

1. Web and Email offers were completed with only a 7% difference from other offer types.
2. Females completed a lot more than males over all channels.
3. Customers over age 55 have a higher conversion rate (CR) and continue to increase with age, with 85+ having the highest rate across all channels.
4. Longer memberships have higher CRs across all channels.
5. High incomes have the highest CRs across all channels.

Customer segment and channel conversion rates (CR) suggest targeting Discount offers to females age 55+ with a more mature memberships (2-3 years) and an estimated high income.