__STYLES__
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.
To identify key customer segments and develop a data-driven strategy for future promotional messaging & targeting.
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.
after importing the CSV files into Excel, I took the following steps to prepare data for analysis…
Checked for missing or duplicate values
Checked for proper formatting and labeling
Performed basic calculations and checked for outliers and data out of the norm**
Identified key metrics, primary and secondary keys
Identified key segments for specific metrics
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.)
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.
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.
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.