Module Overview: Transform Data

The format functions can be applied to name, phone number, or any non-read-only text fields. These functions ensure that your data is formatted correctly.

Capitalization

• Format: Proper case person – Format name field values with proper case.
  Example: john doe >> John Doe or conan o'brien >> Conan O'Brien
• Format: Proper case company – Format company names to title case. This function can also be used to format addresses properly.
  Example: aaa insurance >> AAA Insurance or 123 main st. >> 123 Main St.
• Format: Lowercase – Format all letter characters to lowercase.
  Example: THe END >> the end
• Format: Uppercase – Format all letter characters to uppercase.
  Example: New York city >> NEW YORK CITY

Phone Numbers

• Format: Phone E.164 Standard +xxxxxxxxxx – Format numbers to the E.164 format, which can be used for international phone number formatting.
  Example: +(44) 161-868.8000 >> +441618688000
• There are a handful of options to format US phone numbers, both with or without the international calling code prefix:
  • Format: Phone USA +1xxxxxxxxxx – Example: +12126461919
  • Format Phone USA +1-xxx-xxx-xxxx – Example: +1-212-646-1919
  • Format Phone USA +1 (xxx) xxx-xxxx – Example: +1 (212) 646-1919
  • Format: Phone USA xxx-xxx-xxxx – Example: 212-646-1919
  • Format: Phone USA (xxx) xxx-xxxx – Example: (212) 646-1919
• Format: Phone Custom Separator – Use a custom separator, such as a dash "-" or a period "." between integers and set the pattern.
  Example: If Phone field, with Existing Text "2,2,4", and New Text "-", then "442012345678"  >> 44-20-1234-5678

Also see our blog post on Phone Number Formatting.

Other Formatting

• Format: Number 1,000s separator – Add a comma every third digit to the left of the decimal point. Sometimes called a thousands separator.
  Example: "1234567.23" >> "1,234,567.23"
• Format: Text with separator – Separate the values or characters in a field. Enter the number of characters to insert the separator after and the character or space to insert.
  Example: If the field is ProductID, with Existing Text "3,2", and New Text "-", then "PDP12345" >> "PDP-12-345"

The Map function lets you locate data within your database and replace one value with another.

• Map: Values – All the content in the field must match the Existing Text value. If it matches, then the entire field is overwritten with the New Text.
  Example: If the field is City, and Existing Text "NY|NYC|New York", New Text "NY", then "new york" >> "NY"
• Map: Terms – Find any standalone term in the field. If found, just the term is overwritten with the New Text.
  Example: If the field is Address, with Existing Text "st|st.", and New Text "Street", then "Main St." >> "Main Street"
• Map: Contains word – Find a specific word in the field. If found, the entire field value is overwritten.
  Example: If the field is Industry, with Existing Text "Tech", and New Text "Technology", then "Tech Sector" >> "Technology"
• Map: NOT contains word – Inversely, look for a specific word in the field. If it is not found, the entire field value is overwritten.
  Example: If the field is Favorite Color, with Existing Text "Red|Yellow|Blue", and New Text "Non-Primary Color", then "Purple" >> "Non-Primary Color"
• Map: Contains substring – Find a specific value anywhere in the string of characters. If found, just the value is overwritten with the New Text.
  Example: If the field is Email, with Existing Text "acmi", New Text "acme", then "pwong@acmi.co.pl" >> "pwong@acme.co.pl"
• Map: NOT contains substring – Look for a specific value anywhere in the string of characters. If it is not found, the entire field value is overwritten with the New Text.
  Example: If the field is Website URL, with the Existing Text ".co.uk|.ac.uk|.gov.uk" and New Text "Outside UK," then "www.acme.com" >> "Outside UK"
• Map: Starts with – Look for the specified value at the beginning of the data, and if found, the value is replaced with the specified text.
  Example: If the field is Phone Number, with Existing Text "(212)|(646)", and New Text "New York", "(212) 123-4567" >> "New York"
• Map: Default value (unmapped) – Often used when adding several functions to one field. When the value in the field doesn't match any of the preceding functions, you can define a default value to apply.
• Map: Numeric range – Replace a number that falls within a range with a set value.
  Example: If the field is Employees, with Existing Text "100-200", and New Text "SMB", then "175" >> "SMB"
• Map: Regex – Use a regular expression (regex) to find a pattern in your data and replace any occurrence with a specified value.
  Example: If the field is Postal Code, with Existing Text parameter "1[0-4]{1}[0-9]{3}", and New Text "New York", then "10013" >> "New York"
  
• Map: US Zip Postal Code to US State (abbrev.) – Use the zip/postal code value to write the two-character US state into a specified field.
  Example: If the field is Postal Code, with Target Field "State/Region", then "10013" >> "NY"
  

Add a value to the end of a field value based on what is in another field.

• Append: Text – Add a field value at the end of another field value, separated by a space.
  Example: If the field is Deal Owner, and Target Field is Deal Description, then "7000 units to Albuquerque in Q3" >> "7000 units to Albuquerque in Q3 Skyler Adams"
• Append: Multi-select field – Add an item to a multi-select checklist field.
  Example: If the field is Country Alpha-3 Code, and Target Field is Country Picklist, then "ARG" >> "Argentina"
• Append: Concatenate – Specify a separator and value to add to the end of the data.
  Example: If the field is Deal Name, with Parameter "/", and Field Name is Close Date, then "Acme B25" >> "Acme B25 / 4/12/23"

Remove or clear out a portion of field values.

• Remove: Terms – Used to remove specific values or terms from a field.
  Example: If Name field, and Parameter is "mr.|ms|mrs.", then "Ms Helen Smith" >> "Helen Smith"
• Remove: From ending – Trim the specified number of characters from the end.
  Example: If Company field, and Parameter is "3", then "Microsoft" >> "Micros"
• Remove: Trim – Trim the value to only keep the specified number of characters.
  Example: If Company field, and Parameter is "3", then "Microsoft" >> "Mic"
• Remove: Using delimiter remove Nth term – Indicate which term to delete, by count, then the value or delimiter that should replace it, if any.
  Example: If Mailing Address field, with Existing Text "2", and New Text " " (space), then "524 Broadway, New York, NY 10012" >> "524 Broadway, NY 10012"
  
• Remove: Text inside parenthesis – Remove text located inside parenthesis or brackets, () {} [].
  Example: "Jonathan (Jack)" >> "Jonathan"
• Remove: Top-level domain – Remove the top-level domain (.com, .ca, .edu, etc.) from an email address, domain name, or web URL.
  Example: "nytimes.com" >> "nytimes"
• Remove: Subdomain – Remove the subdomain (www., support., blog.) from a domain name or web URL.
  Example: "help.nytimes.com" >> "nytimes.com"
• Remove: From multi-select field – Remove an existing selected value from a multi-select field based on what appears in another field. 
  Example: If the field is Discontinued with a value of "Pro Plus," and the target field is Products, then "Events 360; Pro Plus; Forms Bundle" >> "Events 360; Forms Bundle"
  

Remove Whitespace

• Remove: Any and all whitespaces – Used to remove all whitespace from a field, regardless of the position of the whitespace.
  Example: "New York City" >> "NewYorkCity"
• Remove: Leading/trailing whitespace – Remove any whitespace located before the first character and after the last character in a field.
  Example: " New York City " >> "New York City"
• Remove: Successive whitespace – Remove multiple consecutive whitespaces within a field.
  Example: "New    York  City" >> "New York City"

Remove Specific Types of Characters

• Remove: Non-letters – Remove any character that is not a letter from the values. This includes digits or symbols.
  Example: "New York City 2020!!!" >> "New York City"
• Remove: Symbols – Example: "New-York (City) 2020!" >> "New York City 2020"
• Remove: Non-digits – Example: "New York City 2020!!!" >> "2020"
• Remove: Letters – Example: "New York City 2020!!!" >> "2020!!!"
• Remove: Digits – Example: "New York City 2020!!!" >> "New York City !!!"

Split up the values or characters in a field.

Split by Space

• Split: By space and pick just the 1st term – Keep only the term/string before the first space.
  Example: "John M. Doe" >> "John"
• Split: By space and pick just the 2nd term – Keep only the term/string after the first space. 
  Example: "John Doe Jr." >> "Doe"
• Split: By space into two fields – Keep only the term/string before the first space in the selected field and move the remaining portion of the value into a different field.
  Example: If Name field, and Target Field is First Name, then "John Doe" >> "John" remains in Name field, "Doe" in Target field.

Split by Delimiter

Split up field content by a specified delimiter, and keep or use the specified portion of the field content for the next step. The delimiter could be a space, dash, comma, etc. 

• Split: By any delimiter and pick the Nth term – Example: If Street Address field, with Existing Text "," and New Text "2", then "524 Broadway, New York, NY 10012" >> "New York"
  
• Split: By any delimiter and pick the last term – Example: If Street Address field, and the Parameter is "," then "524 Broadway, New York, NY 10012" >> "NY 10012"

Locate a certain term or string in a field wherever it may appear and replace that portion of the field value with a new value.

• Find & Replace: Any occurrence – Find the specified term or string and change it.
  Example: If Subscription field, with parameters Existing Text "2023", and New Text "2024", then "Basic 2023"  >> "Basic 2024"
• Find & Replace: Regex any occurrence – Use a regular expression (regex) to find a pattern in your data and replace any occurrence with a specified value.
  Example: If First Name field, with Existing Text "\(.*?\)", and New Text "(John)", then "Jonathan (Jack)"  >> "Jonathan (John)"

Duplicate or move a value into another field.

The Copy functions can be helpful when working with read-only fields.

• Copy: Value – Copy the entire field value into another field, leaving the original field data as is.
• Copy: Fill – Copy the entire field value into another field only if the target field is empty, leaving the original field data as is.
• Copy: Move – Remove the data from one field and put it in another. 
• Copy: Swap – Exchange the values between two fields.

Standardize states, provinces, or countries in any text field.

• Standardize: US states, CA provinces – Format the full names of US states and Canadian provinces.
  Example: "new york" or "N.Y." >> "New York"
• Standardize: US states, CA provinces (abbrev) – Format US states and Canadian provinces to standard abbreviations.
  Example: "Nova Scotia" or "n.s." >> "NS"
• Format country names to the ISO alpha-2, two-character abbreviations, or ISO alpha-3, three-character abbreviations.
  • Standardize: Country name to Code2 – Example: "United States of America" >> "US"
  • Standardize: Country name to Code3 – Example: "Canada" >> "CAN"
• Format ISO two or three-character country abbreviations to the full country names.
  • Standardize: Country Code2 to name – Example: "BR" >> "Brazil"
  • Standardize: Country Code3 to name – Example: "BRA" >> "Brazil"
• Change the standard country abbreviation to or from ISO alpha-2 to or from ISO alpha-3.
  • Standardize: Country Code2 to Code3 – Example: "US" >> "USA"
  • Standardize: Country Code3 to Code2 – Example: "CAN" >> "CA"

Modify characters or terms at the beginning or end of field data.

Add or Remove

Add or remove specified characters at the beginning or end of a value.

• Prefix: Add – Example: If field is Website URL, and Parameter is "www.", then "acme.com" >> "www.acme.com"
• Prefix: Remove – Example: If field is Company Domain, and Parameter is "www.", then "www.acme.com" >> "acme.com"
• Suffix: Add – Example: If field is Company Domain, and Parameter is ".com", then "acme" >> "acme.com"
• Suffix: Remove – Example: If field is Company Name, and Parameter is ".com", then "acme.com" >> "acme"

Replace

Replace an existing prefix or suffix of values with specified characters.

• Prefix: Replace – Example: If field is Subscription, with Existing Text "2023", and New Text "2024", then "2023 Plan" >> "2024 Plan"
• Suffix: Replace – Example: If field is Company Name, with Existing Text "Incorporated", and New Text "Inc.", then "Acme, Incorporated" >> "Acme, Inc."

Calculate a period of time based on an existing date and copy the result to another field.

• Dates: Add date interval (from field value) – Set a relative period of time between two date fields. Select a date field and select a field that indicates the number of days or months to add to the value.
  Example: If field is Submission Date, Period is "Days," and Field Name is SLA Length, then "2023-09-15" where SLA Length = 2 >> "2023-09-17"
• Dates: Add date interval (constant) – Select a date field and add a fixed number of days or months to the value. 
  Example: If field is Submission Date, Period is "Days," and New Text is "14", then "2023-09-15" >> "2023-09-29"
• Dates: Calculate days between dates – Calculate the number of days between two date fields.
  Example: Calculate days between Start Date and Submitted Date
• Dates: Calculate days from now – Calculate the number of days between the current date and the date value in the selected field.
• Dates: Today's Date – Write today's date following the ISO-8601 format.
  Example: "2023-10-31"

Perform basic math operations like add, subtract, multiply, and divide, or apply number formats.

• Math: Field value – Perform selected math operation on the field value, using a number from the second field.
  Example: If field is Amount, with Add Operation, and Bonus field has value of "20", then "100" >> "120"
• Math: Constant value – Use the same number to perform a math operation on all the field values. 
  Example: If field is Quantity, with Multiply Operation, and New Text is "5", then "10" >> "50"
• Math: Random – Sets a random number between 1 and a specified number.
  Example: If the Parameter is "100," select a random number between 1-100
• Math: Round – Round the number values to a specified decimal place.
  Example: If a number value is "100.12345", and the Parameter is "3", then the value becomes >> "100.123"
• Math: Absolute – Provide the absolute value of a number.
  Example: -10025 >> 10025

Pull existing data from one field and copy it into another.

• Extract: Substring – Select a portion of the value to copy by specifying the first and last characters by character count.
  Example: If the field is ProductID, with Existing Text "4", and New Text "6", then "PDP12345" >> "123"
• Extract: Domain from email – Copy a domain from an email address into a target field.
  Example: If Email field, then "john@seas.upenn.edu" >> "upenn.edu"
• Extract: Domain from URL – Copy the domain from a website URL into a target field.
  Example: If Website URL field, then "https://www.nytimes.com" >> "nytimes.com"
• Extract: Regex copy – Use a regular expression (regex) to find a pattern in your data and copy the value to another field while leaving the value in the original field.
  Example: If Parameter is "[0-9]{4}-[0-9]{2}", and the original value is "West Side Expo 2024-03-20", the value "2024-03" is copied to the Target Field.
• Extract: Regex move – Use a regular expression (regex) to find a pattern in your data and move the value to another field. The extracted value will be cleared from the original field while the rest of the value remains.
  Example: If Parameter is "[0-9]{4}-[0-9]{2}-[0-9]{2}", and the original value is "West Side Expo 2024-03-20", the value "2024-03-20" is moved to the Target Field, and the original value becomes "West Side Expo"
• Extract: Initials – Copy the first character of each string in the selected value to create initials.
  Example: If Deal Name field, "Cascades Inc - New Deal" >> "CI-ND"

Navigate to Data Management > Transform Data. Select the database and record type in the top menu. Then explore the templates for an existing solution that may be close to what you need.

In Step 1, under the Filter tab, select the Field to work with. Use the Condition to set the rule that the data in the selected field must meet. With this filter, you're telling Insycle what records you would like to update.

In this example, we want to fill in missing country values based on the two character top level domain. 

The filter finds records that have a missing or empty Country/Region field, a non-empty Email field, an Email Domain that has at least one character (.) followed by a period (.), and then two lowercase letters ([a-z]{2}), and an Email Domain that does not end with common top-level domains like .com, .net, .io, etc.

Click the Search button, and a preview of the records that match this filter will load in the Record Viewer at the bottom of the page. Have a look through the results to see if there's anything else you want to filter out. If you change the filter, click the Search button again to reload the Viewer.

Under Step 2, give Insycle instructions on what changes to make to the identified records.

Select the Field that contains the value you want to start with, then select the Function and enter the parameters. Click the plus at the end of the row to add an additional function for this field.

Each function will use the value output from the previous function, so the sequence of them matters.

In the example below, the top-level domain (TLD) from the email address is being used to populate the Country value. Once the TLD is identified, if it's a two-character country TLD, it's converted to the full country name and then entered into the Country field. Because the value is copied, the email value remains unchanged.

Preview Changes in CSV Report

Data transformation is a bit more difficult to visualize than basic value-A-to-value-B changes, so before applying changes to your CRM data or when you're developing your new template, previewing the results is an important step. 

Under Step 3, click the Review button and select Preview mode.

Click the Next button to go to the Notify screen, where you can select recipients and add context to the CSV report email.

On the When tab, click the Run Now tab, and select which records to apply the change to (you could do All, but if you have a large number of records, you may just want to do a chunk for your preview), then click the Run Now button.

Insycle will generate a preview CSV and send it to your email. Open the CSV file from your email in a spreadsheet application and review the values for each row.

You can see the (Before) and (After) values side-by-side. The Deal Name (After) values have been rewritten to concatenate the customer name, deal close date, and product line. 

If your results don't look how you expected, return to your filters in Step 1 and functions in Step 2 and try making some adjustments, then preview again.

Apply Changes to Your CRM Records

When you're satisfied with the results in your preview, you can apply the changes to your CRM.

Under Step 3, click the Review button again, and this time select Update mode.

On the When tab, you should use Run Now the first time you apply these changes to the CRM. If you have a large number of records, you may want to do a smaller batch to review the results in your CRM.

After you've seen the results in the CRM and are satisfied with how the operation runs, you can save your configuration as a template and set up automation so this transform operation runs on a set schedule. If you have several templates you'd like to run together automatically, you can create a Recipe. Additionally, HubSpot users can integrate Insycle Recipes into HubSpot Workflows.

Return to the Template menu at the top of the page and click Copy to save your configurations as a new version of the template you started with. Then click the pencil to edit your new template name.

Under Step 3, click the Review button, and select Update mode.

On the Notify tab, select the send option appropriate for your automation: Always send, Send when errors, or Do not email.

Add any additional recipients who should receive the CSV (and make sure to hit Enter after each address). You can also provide additional context in the message subject or body.

On the When tab, select Automate and configure the frequency you'd like the template to run. When finished, click Schedule.

You can view all your scheduled automations at any time on the Operations > Automations page.

With the Activity Tracker, you have a complete audit trail and history of changes made through Insycle, including processes run in Preview mode or data syncs. At any time, you can download a CSV report that lets you see all of the changes that were made in a given run of the operation.

Navigate to Operations > Activity Tracker, search by module, app, or template name, then click the Run ID for the operation.

If there isn’t a single function under Step 2 that makes the change you’re looking for, you can still complete the step in one template.

Functions can be layered to make changes in a series of steps. They will cumulatively apply to the field value, executing in order from top to bottom.

For example, you can use two functions to clean up Website URL values with multiple directories. Instead of “https://app.insycle.com/data/bulk/contact/,” you just want to keep the main domain value, “insycle.com":

1. The Extract: Domain from URL function will remove the https:// and subdomain, but the directory values after the domain, '/data/bulk/contact/' would remain. Leaving the Target Field blank, will write the value back to the original field.
2. To remove those directory values, add the function Split: By any delimiter and keep Nth term to remove everything after the forward slash, “/”, and keep the value in the first section.

For another example, you can combine values from different fields (Deal Name, Close Date, Amount, Next step) into a single formatted string to create a concise and standardized deal or opportunity identifier or description.

Here's a breakdown of what each function is doing:

• Append: Concatenate concatenates (combines) the value in the "Deal Name" field with the value in the "Close Date" field, using the "/" character as the separator.
• Split: By any delimiter and pick the Nth term, splits the concatenated value from the previous step by the character "T" (representing a date/time delimiter) and selects the 1st term (N=1).
• Append: Concatenate appends (adds) the value in the "Amount" field to the end of the value from the previous step, using the "/" character as the separator.
• The second Append: Concatenate function adds the value in the "Next step" field to the end of the value from the previous step, using the "/" character as the separator.
• Format: Proper case company formats the concatenated string from the previous steps by capitalizing the first letter of each word, following a typical company name formatting convention.

You may need to do some experimentation. Use the Preview mode and CSV report to see if you get the desired result. If not, adjust your functions, maybe try reordering them, and then Preview again.

With your filter and layout set, Insycle can now export your data segment. 

In the Transform Data module, you can export your data one time immediately or set up automated exports. This will export the current data without the transform updates.

Under Step 1, click the Export button.

In the Export popup, select New File and click Next.

On the Email tab, enter additional email addresses (hit Enter after each), a Subject, and Description. The CSV export will be attached to this email whenever it is generated.

To export the data once:

On the When tab, click Run Now and select which records to apply the change to (in most cases this will be All), then click the Run Now button.

To set up automation for recurring exports:

On the When tab under Automate, set up the frequency your CSV should automatically be exported--hourly, daily, weekly, or monthly.

Then, data that matches your set filter will be automatically exported and emailed to you or your team on a set schedule, improving data collaboration and letting you keep an eye on important customer data.

If you'd like to see additional fields in the Record Viewer or your export, click the Layout tab under Step 1, find the desired field in the right column, and drag it left to the Visible Fields column. Drag and drop fields to reorder them in the Visible Fields column. 

Note that adding additional fields to the Record Viewer does not affect what is in the Step 3 review CSV report, only the Record Viewer and export from Step 1.

The Transform Data Preview CSV includes a fixed set of fields: record identifiers, a deeplink to the record in the CRM, and the Before and After values for the transformed fields. Other fields cannot be added.

To see all the fields that are in your Layout and Record Viewer, use the Export button under Step 1. This will export the current data without the transform updates. For more detail, see the Advanced How-Tos above.

Yes. It is best practice to build separate templates for formatting each specific field. This helps you to keep things organized and troubleshoot issues if they arise. If you wind up with several related templates you'd like to automatically run together, you can create a Recipe.

Yes. If you are having trouble figuring out how to format a specific field, contact customer support via the live chat to receive help with building your template.

Filtering data before running an operation ensures that Insycle will only analyze the records that you want to focus on. Otherwise, Insycle will look at all records of the selected record type in your CRM. 

There are a few reasons filtering is important:

1. You only want to change a segment of records that meet certain criteria. 
2. If you have thousands of records, the operations can take a very long time. It is more efficient and manageable to filter your data down into small segments and use multiple templates rather than running operations for your entire database.
3. There may be an overwhelming number of results. If you are trying to analyze your data, adding a filter or narrowing filter criteria lets you work with a relevant, reasonably sized subset of data.