Using SSAS (SQL Server Analysis Services) Data Mining to Automate Marketing Analysis.

Back in a former life when I worked in the casino industry, we used Excel spreadsheets to segment our customer loyalty club members.  The segmentation was very manual and required many hours to complete to build a quarterly direct mail campaign to each segment with the appropriate offers for each segment so we would have a successful and profitable campaign.

The reason we did this was clear: when each individual customer (or small groups of very similar customers) receives a personalized and highly-relevant offer, there is much more chance that the message will resonate with them. In a world of marketing overload, customers have little patience to hear what you’re telling them. Thus, it’s critically important to tailor your messages and incentives so that they are the most relevant and interesting for each individual customer.

The manual segmentation process worked a little like this.

Step 1: Segment your Customers into cluster groups

The first step is to divide your customers into distinct groups based on their RFM (recency, frequency, monetary).  We would also add demographic data such as age, zip, gender to the RFM data.

Our goal of RFM was to market to each cluster, customer group, in a way that would maximize their LTV (Life Time Value). Here are some examples to think about:

  • Your best cluster consists of those customers who have been on the site recently (R), engage in many transactions (F) and spend a lot (M). You want to give these customers VIP treatment to encourage them to keep coming back.
  • For customers who have spent a lot despite making few purchases (high M/F), you want to send them offers that encourage them to come back to the site more frequently.
  • For customers who spent an above-average amount (high M), but haven’t been back to the site for a while (medium R), you want to give them aggressive offers to bring them back.

Step 2: Segment your New Customers into Actionable Sub-Segments

The best way to encourage new customers to become long-term customers is to give them a good experience. Beyond good on-site and customer service experiences, this is a good time to give them extra benefits or bonuses to make them feel welcome and appreciated.  Periodically we would perform cluster analysis on current high LTV loyalty club members to create profiles of how they acted when they first became members.  With this data we would base new member offers on their similar behavior patterns with current high LTV loyalty club members.


Going deeper, you should segment the new group into two important sub-groups which will receive extra attention:

  • One-time-only customers – these are customers who never returned after their first purchase (or payment, deposit, trade, etc.). Your goal is to bring them back again. Typically, you want to send them particularly attractive offers to encourage them to return in the short term.
  • New customer with high potential – these are the customers with the highest potential long-term value. You can identify this group using predictive forecasting, which uses customer modeling technology to incorporate both behavioral and demographic data to predict how a customer will likely behave in the future based on similar customer data from current loyalty club members.

Using SSAS Data Mining to automate segmentation

We can automate the above manual clustering process using SSAS data mining tools.  With my dataset of both demographic and RFM data I could build a clustering data mining project in Visual Studio.  The SSAS Clustering algorithm groups cases from a dataset into clusters containing similar characteristics. Using these clusters, you can explore the data and learn about relationships among your cases. Additionally, you can create predictions from the clustering model created by the algorithm.  

In my next post, we will create a Basic Data Mining project using Microsoft Dynamics GP data.  I will document the steps needed to complete a scenario for a targeted marketing campaign in which you create models for analyzing and predicting customer purchasing behavior and for targeting potential buyers.

Share this post

Related Posts

Checking Your CMMC Progress

Written by Alec Toloczko With Cybersecurity Maturity Model Certification (CMMC) requirements on the horizon, it’s crucial for organizations handling Controlled Unclassified Information (CUI) to adhere

Read More »