Dataset of $200$ customers as well as their information $($Customer ID$,$ spending for $2020,2021,$ number of orders, days since last shopping$).$

Instructions:

Data Preparation:

Download the dataset into Excel.

Calculating RFM Metrics:

Recency $($R$)$: Calculate the number of days since each customer's last purchase. Use a specific reference date $($e$.$g$.,$ the date right after the end of the dataset period$)$ to determine how recent each customer's last purchase was.

Frequency $($F$)$: Count the total number of transactions for each customer during the dataset period.

Monetary $($M$)$: Sum up the total purchase amount for each customer during the dataset period.

RFM Segmentation:

Assign a rank or score from $1$ to $5$ to each customer for each RFM metric, with $5$ being the highest $($most recent purchase, most frequent purchases, highest monetary value$)$ and $1$ being the lowest.

Create a combined RFM score by concatenating the individual R$,$ F$,$ and M scores $($e$.$g$.,$ a customer with scores of $4$ for recency, $3$ for frequency, and $5$ for monetary value would have a combined RFM score of $435).$

Analysis and Strategy Development: Use the data provided, show the following in the excel file:

$($a$)$ Create a RFM data for all customers.

$($b$)$ Create a tiered membership system where different services and rewards are offered to customers depending on hoe much they spent in $2021.$ Assign bronze membership status to customers who spent less than $$250,$ silver membership status to those who spent $$250$ or more but less than $$1000,$ and the gold membership status to those who spent $$1000$ or more.

$($c$)$ Show a year to year difference in spending between $2020$ and $2021$

d$)$ Percentage difference between customers' $2020$ and $2021$ spending

e$)$ Who are your top $10$ customers based on RFM rating? Derive show and explain

f$)$ Who are your top customers based on RFM analysis. Derive, Show and explain