Customer Segmentation Use Case Part 4: Data Analysis and Reporting

Introduction

In this blog post we will apply our Recency, Frequency, and Monetary (RFM) customer segmentation model to our data and visualize the results.

In part 1, we described the Customer Segmentation use case, introduced the RFM segmentation framework, and articulated how we can use that segmentation method to provide value for our fictional organization, Nexacore.  Click here to review that blog post.

In part 2, we described how to get started with Infoworks AI by connecting to Nexacore’s data, creating a knowledge base, and creating a project.  Click here to review that blog post.

In part 3, we began to explore our Nexacore data set through profiling and exploring the metadata.  We also saw how we are able to use Infoworks AI to cleanse our data by deduplicating and formatting values to meet our use case.  Click here to review that blog post.

Data Analysis

RFM Model Review

We discussed the RFM model in Part 1 of this blog series, but as a refresher, RFM is a customer segmentation tool that scores customers based on how recently a customer made a purchase, how frequently they purchase, and how much money they have spent with the organization.  Based on these three measures, customers are divided into quartiles and a composite score is generated.

Applying the RFM Model

We’re going to apply the RFM model to our Nexacore data set.  At this point we’ve already looked at the metadata of the orders table and determined that it contains all of the information we need to calculate an RFM score.  The order_date column will allow us to calculate recency, the order_id column will allow us to calculate the frequency, and the total_amount column will allow us to calculate the monetary score.

All we need to do is ask Infoworks AI to calculate the RFM score for us, by submitting the prompt below in the ‘Start conversation here…’ box.

‘do a recency, frequency, and monetary analysis of our customers; segment the customers using quartile for each metric with the first quartile representing the most recent, most frequent, and most monetary; concatenate the 3 values into a single RFM score’

The AI model is able to determine how to 1) calculate the RFM score, 2) generate a SQL query to carry out the task, and 3) validate the syntax of the query issued.  Let’s run that query to see the result by selecting the ‘Edit’ button below the query and selecting the ‘Run’ button in the SQL editor pane.

Notice the results contain the customer_id and the corresponding RFM score.  Based on the RFM score, we can categorize customers.  These categories are defined in our business glossary (see Part 2 of this series for more information on the business glossary).  These categories are below.  More categories can be added if desired.

Category	RFM Score	Notes
Best Customers	111	The customers who purchased recently, frequently, and have higher monetary spending.
High Spending New Customers	141, 142	The customers who have purchased recently, but infrequently, and have higher monetary spending.
Lowest Spending Active Loyal Customers	113, 114	The customers who have purchased recently and frequently, but have lower monetary spending.
Churned Best Customers	411,412, 421, 422	The customers who have not purchased recently, but have purchased frequently and have higher monetary spending.

Since this data is in our business glossary, we simply need to submit another prompt like the one below.  We can also add customer name and contact information to our result set at the same time.

‘categorize customers based on RFM score and add customer name and contact information’

Submitting this prompt provides us a query that contains all of the information we need.  As a last step, we’ll visualize the data before passing it back to our marketing team.

Reporting

In this last step, we’re going to report on and visualize the results and pass them onto the Nexacore Marketing team.

First, let’s see how we can export our results.  Infoworks AI allows us to export the results as .csv so that they can be further analyzed in other tools.  Simply click the ‘Export to CSV’ link to export and download the result set locally.

Infoworks AI also provides visualization capabilities on top of the data.  We’re simply going to select the ‘Chart’ tab in our previous example and maximize the frame.

As we can see, Infoworks AI selected a default visualization.  

I’d like to display a chart showing the number of customers by category in a pie chart, but I want to remove the other category.  I can simply ask Infoworks AI to “Modify the results to filter out the ‘Other’ category and display as a pie chart.’’ text box.

The results show that we do have a significant number of churned customers.  We can pass this on to our marketing team as a next step.

Summary

This concludes the series on using Infoworks AI to analyze data using natural language, artificial intelligence, and business glossary information.

In prior posts we learned about the use case, set up our environment, and explored the data.  In this blog post we saw how we can use Infoworks AI to apply a customer segmentation model to our data.  Using the general knowledge of the AI model on the RFM methodology, coupled with the technical metadata of the data warehouse, we were able to generate a SQL statement to calculate an RFM score for each customer.  Then, utilizing the business glossary in the knowledge base, we were able to apply a categorization of the generated scores.  Finally, we visualized the data, validating the churn issue for the Nexacore marketing department.

This blog series demonstrates the value Infoworks AI provides in streamlining the creation and organization of new data sets.  You can apply this analysis to your data or create different analytics leveraging the process demonstrated here.