How to "transform data" in automations?

What is the "transform data" automation action used for?

• Transform data - automation action in the in-app automation builder allows you to change how data sets look according to different criteria.

You can use eight different "transform data" actions in all your automations.

1. Choose the "transform data" action in your automation steps.
2. Select a transform type:

Date formatting:

• Format dates using one of the available formats.

Date calculation:

• Calculate a new future or past date based on the input data.
  • This type of date transformation can help you set an offset in the future or past based on any of your contract variables with the date type. The new calculated date you have defined can be used in any of the following automation steps. 
  • You can choose one of the following operators to execute the variable date calculation:
    • Addition
    • Subtraction
    • The last day of the month
    • The first day of the month

Renewal date calculation:

Date calculation based on variables

• You can also use variable number input for any date calculations.
• This allows you to connect different data sets depending on individual date data fields available in the same contract.
• For example, you can calculate the Expiration or Renewal dates of contracts dynamically by adding the data field showing the expiry/renewal terms to the start date of the contract:

• This will allow you to automatically create or update the "Expiry date" for all contracts generated from the same template: 

• This is an example of the last automation step you can utilize, which will ensure the new data field gets created based on your variable date calculation:

• The "transform data" action can be configured as a variable data set calculation conveyed directly from your CRM system.
  • For example, the "Expiry date" fetched from HubSpot and estimated based on the contract start date and terms:

Math formula:

• Use spreadsheet-style formulas to perform various calculations on the input data.
  • You can use basic math operators (+, -, *, /, ^), group terms with parentheses, and use various rounding functions:
    • round(n, p) - will round the number ( n) to the precision number ( p), e.g.  round(3.1415, 2) = 3.14 
    • floor(n) - will round the number ( n) down towards the nearest whole number, e.g.  floor(3.14) = 3.0 
    • ceil(n) - will round the number ( n) up towards the nearest whole number, e.g.  ceil(3.14) = 4.0

Split by:

• Split the input data by the specific character or text. 
  • For example, you can split a list of products defined as variables with a , or any other character:

Extract email:

• Find and extract an email from the input data:
  • This transform type can find and extract specific email addresses from variables with additional inputs.

Find and replace:

• Find text in the input data and replace it with different text:
  • This transform type can find a specific value and replace it with another one.
  • You can set the toggle "use a regular expression to find" to perform a search with regular expressions listed here.
  • In this example, we want to find a representative name matching any of the values ("Jane or Janny") and replace it with "Jenny":

Extract number:

• Find and extract a number from the input data:
  • This transform type can find and extract a particular number from other variables with additional inputs

  • The expected format of the number is "100" or "1000.00":

Lookup table:

• Define lookup tables for any number of variables. If the data matches one of the specified entries, the corresponding value will be used.
• Click here to learn more about the data transformation usage in lookup tables for your HubSpot integrations. 

What is the "transform data" label used for?

• Each variable you want to transform requires a specific label. 
• This should be a descriptive name for the transformed variable.
• The label you define here will be available in the next automation steps, allowing you to use it for additional actions: