Introduction
This week for Workout Wednesday we are performing RFM (recency, frequency and monetary value) analysis. RFM Analysis is a marketing technique used to quantitatively rank and group customers based on the recency, frequency and monetary total of their recent transactions.
A score of 1 to 5 is assigned for each of the three main factors. The collection of three scores for each customer is called an RFM cell. In a simple system, organizations average these values together, then sort customers from highest to lowest to find the most valuable customers. Some businesses, instead of simply averaging the three values, weigh the values differently.
Requirements
1. Use the data provided for this exercise on Data.World.
2. Create a measure that averages the [Sum] column and converts that value to an integer.
3. Create a measure called Customer Count that is a distinct count of the UserID column.
4. Create a scatter chart that shows the RFM customer segmentation. Use the [Characteristics R+FM v2] as the legend and direct label the segments in the visual. Plot the frequency and monetary score, the [FM] field, on the y-axis and the recency score, the [R] column on the x-axis. Place borders around the segment areas to separate them from other segments. Use the Customer Count measure set the size of the marker in the plot.
5. Show the Average Sum and Customer Count measures in the tooltip.
Dataset
This week’s data is found on Data.World. You will need to log in to access the data, but accounts are free.
To access the dataset, go to https://data.world/mlongoria/pbiwow2023wk46.
You can connect directly to Data.World from Power BI Desktop, or you can download the Excel file to your local machine and connect to that.
Share
After you finish your workout, share on Twitter using the hashtags #WOW2023 and #PowerBI, and tag @MMarie, @shan_gsd, @KerryKolosko. Also make sure to fill out the Submission Tracker so that we can count you as a participant this week in order to track our participation throughout the year.
Solution
Solution File available for download via Data Stories Gallery.