Calculate RFM values

Calculate Recency, Frequency and Monetary values for the online dataset we have used before - it has been loaded for you with recent 12 months of data. There's a TotalSum column in the online dataset which has been calculated by multiplying Quantity and UnitPrice: online['Quantity'] * online['UnitPrice'].

Also, we have created a snapshot_date variable that you can use to calculate recency. Feel free to print the online dataset and the snapshot_date into the Console. The pandas library is loaded as pd, and datetime as dt.

This exercise is part of the course

Customer Segmentation in Python

View Course

Exercise instructions

Calculate Recency, Frequency and Monetary value (in this order) for each customer.
For Recency - calculate the difference between snapshot_date value and the max of the InvoiceDate.
Rename the columns as Recency, Frequency and MonetaryValue.
Print the header with the top 5 rows of the datamart.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

# Calculate Recency, Frequency and Monetary value for each customer 
datamart = online.groupby(['CustomerID']).agg({
    'InvoiceDate': lambda x: (snapshot_date - x.____()).days,
    'InvoiceNo': '____',
    'TotalSum': '____'})

# Rename the columns 
datamart.rename(columns={'InvoiceDate': '____',
                         'InvoiceNo': '____',
                         'TotalSum': '____'}, inplace=True)

# Print top 5 rows
print(____.head())

Edit and Run Code