Object Mapping: Create a Substitution
The Substitution Tool allows users to transform existing data by either replacing the current value with a hardcoded value or pulling values dynamically from another column. Example use cases would be breaking out Paid Search Campaign Types into their respective ad platforms or grouping Opportunity Type values with a standard set of Types to simplify revenue reports.
- From the main landing page, admins can select the Settings page by clicking the cog in the top right-hand corner.
- The left-hand menu bar contains an “Object Manager” with a list of objects (Accounts through Person). Each of these objects has a substitution option. In this example, we’ll use the “Campaign” table.
- The Home page for the Campaign Table allows you to select “Substitution” to create logic to substitute data in select columns of your Campaign table:
- After clicking on the Substitution Tool, choose the column with values you would like substituted or exchanged for different values:
- Once you’ve selected the column that you would like to use, select “Add Rule.” This adds logic that will be used to transform the data in the previously selected column. You can create multiple blocks of logic that assign different values to your column, which can be dragged and dropped to re-order the priority of processing for each step:
- The upper “+” button allows the user to create a group of rules in a block. The lower “Add Rule” button allows the user to add independent (non-grouped) rules within the block. Rules that exist in a Block are looked to first when determining if a statement is true or false. An example would be if you substitute a Google Paid Ads cm_type if the original cm_type was Search AND the cm_name contained Google and then check whether the cm_name contains Google Ads and apply the same Google Paid Ads value if it’s true. Groupings allow more control of the order and priority of logic in the block.
- Pictured below is an example of a set of rules within a block. When users add rules, they have the ability to choose “AND” or “OR” logic. When you elect to use “AND,” both arguments must be met. In the example pictured below, because the group is set to “AND” it requires both “Requirement 1” and “Requirement 2” to be met in order for the system to set the statement to True and apply the “THEN” value. If “OR” was used, only one of the requirements would need to be met in order to make the statement True and apply the “THEN” value.
- This screenshot shows you an example with a third requirement included. In this case, all three statements must be met for “Value 1” to be enforced because “AND” is selected:
- To add more rules to trigger a substitution outside of the initial grouping, click the “Add Rule” button. This gives you the ability to use both “AND” and “OR” logic between an initial grouping of rules and a second ruleset. If you select “AND,” The first group in the block will be evaluated and then the second group, and only if all are true will the substitution be made. If you select “OR,” the first group in the block will be evaluated and then the second block. If the first group is true or the second rule is true, the rule will be considered true (even if the other is false) when “OR” is selected.
- In the example below, there are two stand-alone rules in the block. The “OR” option is selected, so if either of the two rules is true (even if the other rule is false), the “Then” Value will be written to the cm_type column.
- In order to create a second block to be evaluated, click the “Add Block” button. The system will first evaluate the first block and then evaluate the second block.
- Blocks contain their own logic to replace values. Additional blocks let you replace your field values with a different value than prior blocks. As an example, if you wanted to break out “Paid Search” into different platforms based on the Campaign Name and change the campaign type to “Operations” whenever various values are in the Campaign Name, one block could be set up to dynamically change the Paid Search value to match Bing, Google, or Yahoo, and a second block could be dedicated to finding Operational campaigns. The system evaluates the blocks in the order they appear on the screen.
- Important: If none of the blocks are True, the system will set the value for the existing data to NULL for that column unless the user creates an “Otherwise” rule by clicking the “Add Otherwise” button.
- When an “Otherwise” statement is used, the user can designate what the system should substitute in place of “Null” for any value that doesn’t meet the criteria for any of the “Blocks” created. For example, you may want to indicate that the system should keep the existing cm_type value when rules are not met.