In my last post I showed how an ABC analysis can be used to target customers with specific marketing initiatives. Read this first if you do not know what an ABC analysis is and why you should use it. In this article I show how to implement an ABC classification in Salesforce Marketing Cloud.

1. Define the goals

As always when we create a new process the first step is to think about the goals we want to reach. Like in the previous post I want to create a classification of customers depending on their spendings. To make it a bit more exciting I will add the last date we sent an email to the specific customer. So I plan to send a voucher to all class B customers I did not communicate with in the last month. To remember: our most valuable customers with the highest spendings are in class A. Class B as the second group contains the people with slightly lower spendings and the potential to become a class A customer. That are the people we want to encourage to further interactions and purchases.

2. Preparing the database

Let us assume that we have the purchase data already synced to Marketing Cloud. This can be from our CRM, the webshop, an ERP system or our loyalty program. Also it can be that you have to merge some data before you get the information you need. This depends on how your integration and general data model looks like. In my exemplary case there is a prepared data extension called “Orders”. It consists of the columns ContactKey, ReceiptType, ReceiptDate and PurchaseValue.

While I think the meaning of ContactKey, ReceiptDate and PurchaseValue is clear, I will explain what I want to achieve with the column ReceiptType. In the example the field ReceiptType can have two values:

• “Invoice” indicates that the customer spent money.
• “Refund” indicates a return of a product, which reduces the value of your customers’ overall spendings.

So this is all we need for now. In addition I will use the _sent data view later on. 

3. Definition of the ABC classes

As in my last post we calculate a valuation revenue based on the purchases the customer did in the last 12 months minus the refunds (this is a little tricky in Marketing Cloud as you see further down). The valuation revenue of the previous month will be halved every month and added to the current revenue. So for every purchase we perform this calculation:

Don’t worry, it will be easier to understand in a moment.

The sum of the monthly valuation revenues is then the criteria to define the ABC classes with the following ranges:

• Class A: > 80€
• Class B: 40-80€
• Class C: 0-40€

The criteria, calculation and classes can differ for your business. I have given more examples in this post.

4. Realization in Salesforce Marketing Cloud

In Marketing Cloud we start with the shown Data Extension. Before we are able to assign the ABC classes we need to calculate the valuation revenue. This again consists of the sum of purchases and refunds. Let’s start with the value of one past purchase as an example.

Customer	ABC1
ReceiptType	Invoice
ReceiptDate	01 August 2020
PurchaseValue	100.00
Today	01 October 2020

When we fill the expression with this data we get the valuation revenue for this purchase:

In SQL the expression looks like this:

PurchaseValue / POWER(2,DATEDIFF(mm, ReceiptDate, Getdate()))

Now we adapt it for the Orders Data Extension. To start with the invoices of the last year I added some conditions in form of a where clause. In addition I built the sum of all the calculated valuations.

SELECT ContactKey, ReceiptType, SUM(PurchaseValue / POWER(2,DATEDIFF(mm, ReceiptDate, Getdate()))) AS InvoiceSUM
FROM Orders
WHERE (ReceiptType = 'Invoice') AND (ReceiptDate > Dateadd(year, -1, Getdate()))
GROUP BY ContactKey, ReceiptType

Afterwards I will do the same for the refunds:

SELECT ContactKey, ReceiptType, SUM(PurchaseValue / POWER(2,DATEDIFF(mm, ReceiptDate, Getdate()))) AS RefundSUM
FROM Orders
WHERE (ReceiptType = 'Refund') AND (ReceiptDate > Dateadd(year, -1, Getdate()))
GROUP BY ContactKey, ReceiptType

Then we want to merge both queries into one. To get the ContactKeys from both columns also if the customer only has invoices or refunds, I use a full outer join for that.

I finally want to calculate the total valuation revenue. Since we differentiate between invoices and refunds the SQL query became a bit more complex. Also in this step we need to consider the possibility of empty fields and the limitations of the decimal data type in Data Extensions. Here we cannot simply subtract the refunds from the invoices. To solve this I use the case statement. This way we can distinguish between the different characteristics of datasets.

CASE 
WHEN A.InvoiceSUM is not null AND B.RefundSUM is null THEN A.InvoiceSUM
WHEN A.InvoiceSUM > B.RefundSUM AND B.RefundSUM is not null THEN A.InvoiceSUM - B.RefundSUM
ELSE 0 END AS ValuationRevenue 

Now that we have added this as a last step, we get our final expression to create the valuation revenue.

SELECT CASE WHEN A.ContactKey is not null THEN A.ContactKey ELSE B.ContactKey END as ContactKey, A.InvoiceSUM, B.RefundSUM,
CASE 
WHEN A.InvoiceSUM is not null AND B.RefundSUM is null THEN A.InvoiceSUM
WHEN A.InvoiceSUM > B.RefundSUM AND B.RefundSUM is not null THEN A.InvoiceSUM - B.RefundSUM
ELSE 0 END AS ValuationRevenue 
FROM
(SELECT ContactKey, ReceiptType, SUM(PurchaseValue / POWER(2,DATEDIFF(mm, ReceiptDate, Getdate()))) AS InvoiceSUM
FROM Orders
WHERE (ReceiptType = 'Invoice') AND (ReceiptDate > Dateadd(year, -1, Getdate()))
GROUP BY ContactKey, ReceiptType) AS A
FULL OUTER JOIN
(SELECT ContactKey, ReceiptType, SUM(PurchaseValue / POWER(2,DATEDIFF(mm, ReceiptDate, Getdate()))) AS RefundSUM
FROM Orders
WHERE (ReceiptType = 'Refund') AND (ReceiptDate > Dateadd(year, -1, Getdate()))
GROUP BY ContactKey, ReceiptType) AS B
ON A.ContactKey = B.ContactKey

As you can see in the Data Extension I write a valuation revenue of 0 into the record when the sum of the refunds is higher than the sum of the invoices.

As last I want to assign the ABC classes and also know when the last send to the communication was. After the previous query this is quite easy using the case statement and a join with the _sent data view.

SELECT ContactKey, LastEmailSend,
CASE
WHEN ValuationRevenue > 80 THEN 'A'
WHEN ValuationRevenue > 40 AND ValuationRevenue <= 80 THEN 'B'
ELSE 'C' END AS ABCClass
FROM Valuation
LEFT JOIN
(SELECT SubscriberKey, MAX(EventDate) as LastEmailSend
FROM _sent GROUP BY SubscriberKey) as sent
on Valuation.ContactKey = sent.SubscriberKey

Finally! We have the ABC classes assigned.

5. Usage in a journey

The most obvious way I can imagine to use the classification would be in another segmentation or a Journey Builder decision split. I decided to send a promotional email to the class B customers that did not get any message from me in the last 30 days. Therefore I just filter on the date of the last send and the class of the contact. 

If you want to use the classification in several journeys, I would recommend adding it as an attribute group to your data model in contact builder. Besides journeys, I can also imagine using the attributes in the content selection to create more dynamic emails, maybe with scores for different content topics. And of course there are a lot more combinations of ABC classifications and customer information to use. That’s the point where you can get creative. So have fun creating your own ABC analysis!