Scenario Overview

In this scenario we will demonstrate how to set up a Decision Table of type Library Calculation that segments Contacts into various groups based on their interest in new comics and their age. 

We will also show how this Library Calculation can be used by any number of other formulas to find out how likely it is that the Contact will respond to marketing. 

The North52 BPA solution works like this:

• A Decision Table of type Library Calculation is created on the Contact entity
  • The Decision Table will calculate the age in years of the Contact
  • It will use this age and the option set Interested in new comics to determine the chance of a sale
    • Note this option set is a custom field that will need to be created beforehand
• This Library Calculation will be triggered by a Save - Perform Action formula set up to trigger whenever the Favorite comic field changes
  • Note this is a custom Boolean field that will need to be created beforehand
  • Also the Save - Perform Action formula could do the work of the Library Calculation, however, using a Library Calculation allows for a centralized formula that can be executed by other formulas
• The Save - Perform Action formula will then decide if an email of new comic offers should be sent

Library Calculation

The Library Calculation will take the difference in today's date and the Birthday date. It will use this Age of Contact and the Interested in new comics to determine the likelihood of a sale. 

• Create a new formula, setting the following values in the Formula Guide:
  • Source Entity set to Contact
  • Set Formula Type to Library Calculation
  • Select the Decision Table editor
• Change the Name of the formula to Comic Segmentation
• Click Save
• Select cell A2 in the Decision Table Editor
  • Select the Functions tab
  • Search for DateDiff
  • Shift-click the DateDiff function to open up its wizard
    • For Friendly name, enter Age of Contact
    • For From Date, go to the Source tab
      • Expand Source
      • Search for Birthday and select it
    • For To Date enter UtcDateTime()
    • Leave Interval at Years
    • Select Generate

• Select cell A4, then Copy and paste the following data into cells A4-A11

  0
  ((1,3))
  ((4,12))
  ((13,19))
  ((20,39))
  ((40,59))
  ((60,99))
  >=100
  

• Select cell B2
  • Right-click and select Delete > Delete Column
  • Right-click again and select Insert > Insert Inline Calculation
    • You can leave the Set Name for now
  • Right-click again and select Insert > Insert Condition
• Select cell B2 which should now be a condition column
  • Select the Source tab and find and click on Interested in new comics (Value)
    • Note: this is a custom field, it will not appear if you have not created it
• Select cell B4
  • Expand Interested in new comics (Value) and select No
• Select cell B5 and then select Yes
• Position the cursor over the bottom right corner of cell B5 to show the fill handle, and drag down to cell B12 to copy the Yes value
• Select cell C2 and enter the text Chance of Sale
• Select cell C4 and copy and paste the following:

• Click Save
• Make note of your Short Code in the top right hand corner of the Formula editor as you will need it for the next formula

Formula Save - Perform Action

The Save - Perform Action formula will trigger whenever the Favorite comic field is changed. It will execute the ExecuteFormula function which calls the Library Calculation formula above and will return the Chance of Sale.

• Create a new formula, setting the following values in the Formula Guide:
  • Source Entity set to Contact
  • Set Formula Type to Save - Perform Action
  • Select the Decision Table editor
• Change the Name of the formula to Email comic offers to potentially interested Contacts
• Expand Source & Target
  • For Source Property, select Favorite comic
• Click Save
• Select cell A2 in the Decision Table Editor
  • Right-click and select Insert > Insert Condition
• Right-click and select Operation > Toggle Advanced Mode
  • Select the Global Actions sheet
    • Enter Check comic segmentation into cell A2
    • Enter the function ExecuteFormula('K4x', [contact.contactid] ) into cell B2
      • Note you will need to change K4x to the short code of your Library calculation
    • In cell A3 enter the text Send email - DC offer
    • In cell A4 enter the text Send email - Marvel offer
    • In cell B3 and B4 enter the text 'NoOp'
      • These two actions will not do anything in this example, however, this is where you could setup an email to be sent 

• Go back to the Decision Table sheet
• Click Save
• Select cell A2
  • Go to the Source tab
  • Expand Global Actions
  • Select Check comic segmentation. 
• In cell A4 -> A8 copy and paste the following:

• Select B2
  • Go to Source and expand Source
  • Find and select Favorite Comic {Value}
    • This is a custom option set that we have created. In this example we have set up the options as: Wonder Woman, Superman, Batman, Ms Marvel, Spiderman and X-Men 
• Select cell B7
• Expand Favorite Comic {Value}
• Select Wonder Woman, Superman and Batman
• Select cell B8 
• Select Ms Marvel, Spiderman and X-Men. 
• In cell C4, C5 and C6 enter 'NoOp'
• Select cell C7
  • Go to the Source tab
  • Collapse Source and expand Global Actions
  • Select Send email - DC offer
• Select cell C8
• Go to the Source tab
• Select Send email - Marvel offer

Testing

To test this formula, make sure to have the Interested in Comics, Birthday and the Favorite Comic fields on a Contact form. Then simply make a change to the Favorite and save. 

Note this example is for training purposes and the email will not be sent.