Build Engagement Scoring Model

1\ define your model after compiling the data, you need to choose the factors that make your customers more qualified following the physical retailer model example, the following criteria and scores for customers can be applied criteria score criteria +1 click inside email +1 email bounced 1 recency of last order +1 to +5 average amount of orders +1 to +5 distance from home or store +1 to +5 notice how many factors have wide variations focusing on one, recency of last order, there is a scale from 1 5 correlating to how long ago a customer made their most recent purchase customers with orders in, for example, the previous month are given a high score of 5, while customers at the opposite end of the spectrum, —one purchase 11 months ago— are given a 1 customers whose last order was over a year ago may be removed from this scoring system entirely to reflect how cold of a lead they are ranged scoring provides greater nuance, as opposed to fixed scoring for criteria involving frequency, having a given value often provides similar insight into customer intent in this example, clicking an email adds one point to a customer’s score, so multiple clicks across multiple emails enhances their overall rank with your score criteria defined, it is time to implement the engagement scoring model using deselect the image below indicates the different selections created for the example, and the scores that will be calculated individually 2\ create a selection the first thing you need to do is create a selection that will calculate the points each customer collects for each criterion and store it in a data extension open deselect inside your marketing cloud instance click new selection to create a new selection provide the name customer engagement scores (or similar) for your selection choose your selected data extensions , in this case data extensions customers and orders create a relationship between them on id and customerid fields , and keep all records from customers data extension move on to the target definition screen and click on create data extension provide the name customer engagement scores and click save add the id field of the customers data extension to target data extension fields 3\ calculate individual scores calculate opens – clicks – bounces score next, you need to calculate the points for opens , clicks , and bounces at this point, positive and negative scores is not relevant, therefore you will calculate these three fields in exactly the same way this will also help evaluate overall https //deselect com/email delivery best practices gmail purge/ and engagement by counting how many times a customer has opened an email, clicked inside an email, or bounced out of an email this information can be found inside https //help salesforce com/s/articleview?id=sf mc as data views htm\&type=5 , which are tables generated by sfmc through data views you ’ ll find https //deselect com/blog/salesforce data cloud/ event tracking (e g , email / sms sends, email opens, link clicks) click on add new value inside the custom values section set name to opens score , choose aggregation as type and click on next choose count as the aggregation function choose subscriberkey of open data view as the field match customers’ email field with open ’s subscriberkey field the custom value looks like this click save repeat steps 1 – 6 for click and bounce and name them clicks score and bounces score respectively add all the created values to the target de’s fields calculate orders score next, you will assign points to customers based on when they placed an order based on the data, you could define the following rules 1 point, for orders in the past year 2 points, for orders in the past 9 months 3 points, for orders in the past 6 months 4 points for orders in the past 3 months 5 points, for orders in the past month assign scores to assign the different scores to your customers click on add new value inside the custom values section set name to orders score, choose dynamic value as type and click on next select number as field type and click on add criteria add order date from orders de to the filters section choose is after or on as filter type select relative date and set it to 9 months ago add order date from orders de second time to the filters section choose is before or on as filter type select relative date and set it to 1 year ago set the then value equal to 1 yur custom value should look like this repeat steps 3 – 6, but set the relative date to match the criteria we defined above, and the then value to the equivalent amount of points make sure the default value is 0 and click on save add the created value to your target des fields calculate distance score moving forward, you want to assign scores based on the distance from customers’ homes to your business' physical location you can do that based on their postal codes we know about the postal codes that values between postal codes 15019 15378 are 5 km away from your store, and will get 5 points between postal codes 15378 15737 are 10 km away from your store, and will get 4 points between postal codes 15737 16096 are 15 km away fromy our store, and will get 3 points between postal codes 16096 16455 are 20 km away from your store, and will get 2 points greater than postal code 16455 are more than 20 km away from your store, and will get 1 point assign different scores to assign the different scores to your customers click on add new value inside the custom values section set the name to distance score , choose dynamic value as type and click on next select number as field type and click on add criteria add zip code from orders de to the filters section depending on your organization’s data model, this field may be labeled postal code and may be nestled under the customers data extension choose between as the filter type set the first input field to 15019 and the second to 15378 click on save and set the then value equal to 5 your custom value should look like this repeat steps 3 – 7 for the rest of the postal codes mentioned above, each time decreasing the then value by 1 in the last group choose greater than as filter type make sure the default value is 0 and click on save add the created value to your target de ’ s fields total amount average lastly, you want to assign a different score based on the average of the total dollar amount a customer has spent on purchases in this time period (in this example, one year) for example, you can identify the following groups average between 0 – 25 gets 1 point average between 25 – 50 gets 2 points average between 50 – 75 gets 3 points average between 75 – 100 gets 4 points average above 100 gets 5 points calculate total amount average to get the customers of each group, you need to first calculate the average of the total amount for each customer click on add new value under the custom values section set the name to total amount average , choose aggregation as type and click on next select average value for field as the aggregation function choose total amount from the orders data extension as the field match orders’ customerid field with customers’ id field your custom value should look like this click on save add the created value to your target de ’ s fields click on the save data extension button save your work on the selection itself exit to the deselect segment homepage click on the create button 4\ total amount score create new selection in order to create the total amount score based on the total amount average we need to create a new selection click on run to populate your customer engagement scores data extension with results click on the new selection button name it final customer engagement scores add customer engagement scores data extension to your selected data extensions move on to the target definition screen and click on create data extension provide the name final customer engagement scores and click save click on the add all fields button remove the total amount average field create t otal amount score now we can calculate the total amount score by implementing the following steps click on add new value inside the custom values section set the name to total amount score, choose dynamic value as type, and click on next select number as field type and click on add criteria add total amount average from customer engagement scores data extension to the filters section choose between as the filter type set the first input field to 0 and the second to 25 click on save and set the then value equal to 1 your custom value should look like this repeat the steps 3 – 7 for the rest of the averages mentioned above, each time increasing the then value by 1 in the last group choose greater than as filter type make sure the default value is 0 and click on save add the created value to your target de ’ s fields at the top click to save data extension , then confirm with create click run to create the data extension you can also preview beforehand in the preview section 5\ calculate total engagement score calculate each customer’s total engagement score after calculating each criteria score separately , it is time to sum them up and end up with a single audience engagement score per customer create a new selection and name it customers with score add the final customer engagement scores data extension on the target definition screen create a customers with score data extension add the id field to your target data extension click on the add new value button set the name total engagement score, choose apply formula to a field and click next click apply any function and next choose number as the field type select the opens score field of final customer engagement scores de in the dropdown and click insert field add a plus operator (+) do the same for clicks score , bounces score, ordersscore, distance score and total amount score though because bounces has a negative score, we change the plus (+) icon in front of this field to a minus ( ) the formula should look like this double check the accuracy of your sql syntax with check syntax add the total engagement score field to the target data extension click on save data extension click create click run to fininsh