Building a Customer Segmentation Model in Google Sheets

Image by Editor | Midjourney
 

Customer segmentation is an essential technique that helps businesses understand and categorize their customers based on shared characteristics such as spending habits, spending amount, geographics, age, and purchase frequency. It helps with targeted marketing, and personalized customer interactions to improve services. In this article, we will build a customer segmentation model in Google Sheets.

Step 1: Prepare Your Data in Google Sheets

• Open Google Sheets and create a new spreadsheet.
• Name your first sheet “Raw Data” then import your data if you are loading it from other sources.
• If necessary, clean and format your data for better analysis.
• Our sample data contains the following information.
  • Transaction ID
  • Customer ID
  • Purchase Date
  • Product Category
  • Items Quantity
  • Unit Price
  • Total Amount
  • Payment Method
  • Shipping Country
  • Marketing Channel
  • Customer Type

 Step 2: Create a Customer Summary

Pivot tables offer dynamic summarization which helps to segment data easily.

• Click Insert >> select Pivot Table.
• In the Create pivot table box >> select New Worksheet >> click Create.

• From the Pivot table editor:
• In the Rows field:
  • Add Customer ID.
  • Add Payment Method.
  • Add Marketing Channel.
• In the Columns field: Add Shipping Country.
• In the Values field:
  • Add Purchase Date and select COUNT (for total purchase).
  • Add Total Amount and select SUM (for total spend).

Rename the columns:

• COUNT of Purchase Date >> Total Purchase.
• SUM of Amount >> Total Amount.

Step 3: Calculating RFM Scores

To calculate the RFM score (Recency, Frequency, Monetary) you can use a pivot table to get the recency, frequency, and monetary in Google Sheets.

1. Create a Pivot Table

• Go to the Insert tab >> select Pivot Table.
• In the Create pivot table box >> select New Worksheet >> click Create.
• From the Pivot table editor:
• In the Rows field: Add Customer ID.
• In the Values field:
  • Add the Purchase Date and select MAX (for the last purchase).
  • Add Transaction ID and select COUNT (for purchase frequency).
  • Add Total Amount and select SUM (for total spend).

Rename the columns:

• MAX of Purchase Date >> Last Purchase.
• COUNT of Transaction ID >>Total Purchases.
• SUM of Amount >>Total Amount.

2: Create an RFM Calculation Sheet

• You can either use the Pivot table directly or you can copy the pivot table values in another sheet.
• Add these column headers after the existing pivot table columns:
  • Recency (Days Since Last Purchase)
  • R Score
  • F Score
  • M Score
  • RFM Score
  • Segment

1. Calculate Recency Score (R Score)

Calculate Recency (Days Since Last Purchase):

• Select cell E2 in the Recency (Days Since Last Purchase) column.
• Insert the following formula.

Formula:

=TODAY() - B2

This formula calculates the most recent purchase per customer.

Calculate R Score:

• Select cell F2 in the R Score column.
• Insert the following formula.

Formula:

=IF(F2 <= PERCENTILE(F:F, 0.2), 5,
IF(F2 <= PERCENTILE(F:F, 0.4), 4,
IF(F2 <= PERCENTILE(F:F, 0.6), 3,
IF(F2 <= PERCENTILE(F:F, 0.8), 2, 1))))

OR

=IF(F2 <= 30, 5,
IF(F2 <= 60, 4,
IF(F2 <= 90, 3,
IF(F2 <= 180, 2, 1))))

This formula calculates the recency score based on the last purchase days. Here, recent buyers get higher scores.

2. Calculate Frequency Score (F Score)

• Select cell G2 in the F Score column.
• Insert the following formula.

Formula:

=IF(C2 >= PERCENTILE($C$2:$C$28, 0.8), 5,
IF(C2 >= PERCENTILE($C$2:$C$28, 0.6), 4,
IF(C2 >= PERCENTILE($C$2:$C$28, 0.4), 3,
IF(C2 >= PERCENTILE($C$2:$C$28, 0.2), 2, 1))))

OR

=IF(F2 >= 5, 5,
IF(F2 >= 4, 4,
IF(F2 >= 3, 3,
IF(F2 >= 2, 1))))

This formula calculates the frequency score based on the purchase numbers. Here, frequent buyers get higher scores.

3. Calculate Monetary Score (M Score)

• Select cell H2 in the M Score column.
• Insert the following formula.

Formula:

=IF(D2 >= PERCENTILE($D$2:$D$11, 0.8), 5,
IF(D2 >= PERCENTILE($D$2:$D$11, 0.6), 4,
IF(D2 >= PERCENTILE($D$2:$D$11, 0.4), 3,
IF(D2 >= PERCENTILE($D$2:$D$11, 0.2), 2, 1))))

This formula calculates the monetary score based on the total spend. Here, higher spenders get higher scores.

4. Combine RFM Scores

• Select cell I2 in the RFM Score column.
• Insert the following formula.

Formula:

=VALUE(CONCATENATE(F2, G2, H2))

This formula concatenates R, F, and M scores.

5: Assign Segments

• Select cell J2 in the Segment column.
• Insert the following formula.

Formula:

=IF(I2 >= 444, "Champions",
IF(I2 >= 434, "Loyal Customers",
IF(I2 >= 311, "Active Customers",
IF(I2 >= 211, "At Risk",
"Lost Customers"))))

This formula assigns segments based on the RFM score of a customer.

• Champions: High RFM scores across all metrics. Your best customers.
• Loyal Customers: Good recency and frequency. These are regular, valuable customers.
• Active Customers: Medium RFM score, they are occasional buyers.
• At Risk: Low recency but good history but shows declining engagement.
• Lost: Poor scores across metrics. Haven’t purchased it in a while.

Output:

Note: If percentile-based scoring returns a similar score due to a lack of variation, instead of using percentiles, you can manually assign scores based on logical cutoffs.

 Step 4: Create the Dashboard

1. Create Summary Statistics

• Create a new sheet named Dashboard.
• Create a pivot table from the RFM Analysis sheet:
• In the Rows field: Add Segment.
• In Values field:
  • Add Customer ID (COUNT)
  • Add Total Amount (SUM)
  • Add Total Purchases (AVERAGE)

Add Segment Metrics Table:

You can create a summary table with these columns:

• Segment
• Number of Customers
• Total Revenue
• Average Order Value
• % of Total Revenue

Average Order Value:

Formula:

=D2/B2

This formula divides the Total Amount by the Number of Customers.

Percentage of Revenue:

Formula:

=D2 /SUM(D2:D5)

This formula divides Total Amount by SUM($Total Amount) then select Percentage or multiplies it with 100.

2. Insert Chart for Visualizations

Create a pie chart for customer distribution:

• Click Insert >> select Chart.
• Select the Segment and Customer # column.
• Select Pie chart.

Create a bar chart for revenue by segment:

• Click Insert >> select Chart.
• Select the Segment and Total Amount column.
• Select Column chart.

Final Output:

Based on the segment analysis you can develop targeted marketing campaigns for each segment such as:

• Champions: VIP programs and early access.
• Loyal Customers: Cross-sell and upsell opportunities.
• Active Customers: Engagement-boosting promotions.
• At Risk: Re-engagement campaigns.
• Lost Customers: Win-back promotions.

 Conclusion

By following the step-by-step guide, you can build a customer segmentation model in Google Sheets. RFM calculations and analysis help identify loyal and at-risk customers. The Pivot Tables & Charts visualize the data more efficiently. data visualization Remember to regularly update your data and adjust thresholds based on your business needs. You can explore other metrics to make your model more dynamic. Your business success will lie in consistent monitoring and refinement of your segments and marketing strategies.