RFMC Analysis Paper

In response to your e-mail regarding customer health, I have gathered data and ran an RFMC Analysis to compare customers to each other. This shows the recency-how recent the customer purchased, frequency-how often the customer purchases, monetary-how much the customer has spent in total, and coupons-how much a customer saves by using coupons. This model will segment our customers into groups that will help determine which ones have made the most recent purchases, most purchases, have the highest spend, and use coupons compared to those who are in the opposite segments. To follow are a plan, explanation of output, and recommendations for next steps.

Business Problem Statement The business problem is to determining which customers to target
The variables included are customer_id, transaction_date, total amount paid ($), and total coupon amount used ($). My initial assessment is that with such a large amount of data we will have reliable blocks (groupings) of data. We should be able to look at a customer’s rankings and see its lifestyle,(e.g. is the customer spending more or less over time). We should also be able to tell if a customer is or isn’t using coupons and how it is affecting their monetary ranking, and are they spending more often or less often over time.
Analysis Plan
To follow are the steps I will take when running the analysis:
1. Get to know the data so I understand what the data represents, what each field means
2. Check data for missing values and fill in with ‘average’ of other rows in that field
3. Calculate an RFMC field for each row of data for comparing each customers data o Recency - based on most recent date of purchase
Next, I ran analysis one year at a time in order to see MRFC year over year. Year 2018 was only 2 months’ worth of data so it was not an apple to apple compare with 2016 and 2017. I kept the output for 2018 to show that it was part of the analysis and what the trend was over the first two months.

DESCRIPTIVE ANALYSIS FOR ALL YEARS: Over a 2.2 year span, the best customers, on average, have purchased in the last 14 days, 215 times, spent $8,399.00, and saved $64.00 by using coupons. The worst customers, on average, have purchased in the last 112 days, 17 times, spent $397.00, and saved $1.412.00 by using coupons.
YEAR_ALL
RECENCY FREQUENCY

The next page shows descriptive analysis for years 2016 – 2018 separately. The Frequency looks high as I ran the data with a recent date of 22Jan2018. Subtracting ~365 days for each represents a frequency of approximately 25 for year 2016 and 30 days for year 2017. For the best customers, the average frequency in purchases for 2016, 2017 is 112 and 107 days respectively. The average amount of money spent per customer for 2016, 2017 is $4,500.00 and $4,100.00 respectively. The average savings from coupons per customer for 2016 and 2017 are $24.00 and $25.00 respectively. Running the analysis for each year separately shows customers on average are purchasing approximately once a month compared