Format Names, Phone Numbers, Addresses

Navigate to Data Management > Transform Data, pick a record type, and explore the default templates for a pre-built solution.

Under Step 1, adjust the settings to filter the CRM records down to those you would like to format. This ensures you aren't trying to format fields that don't contain any data. And it is best practice to only tackle one field at a time.

The example below will search for contacts with the first name in all caps:

• Field: First Name 
• Condition: regex
• Value: [A-Z]{3,}

This regular expression is looking for:

• Uppercase letters from A to Z: [A-Z]
• Three or more occurrences of these uppercase letters: {3,}

You can add any filter relevant to your use case—lifecycle stages, record creation dates, engagement triggers, etc. Insycle can use any data in your CRM in the filter step.

Click the Search button and scroll down to the Record Viewer at the bottom of the screen. All records that match your filter will appear. You can alter the fields that appear in this preview from the Layout tab in Step 1.

Under Step 2, give Insycle instructions on what formatting and cleanup 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 their sequence matters.

There are pre-built functions for standardizing phone numbers, countries, states, domains, and other values, so you should explore the options in the Function dropdown or review the Function Catalog.

In the above example, these standardizations are being made to the First Name in each contact record:

• Remove whitespace from before or after the value.
• Remove salutations such as Mr or Mr. or Mrs or Mrs. 
• Capitalize the first name value.

The next example will format specific parts of the Street Address field values. The "Map: Terms" Function will look for the Existing Text and if found, will change it to the New Text value. Here in the Existing Text fields, the bar character "|" between the values means OR, as in, look for "street" or "st.".

Altogether, these rules will standardize the Street Address like this:

• Street → St
• Drive → Dr
• Avenue → Ave
• Boulevard → Blvd

Preview Changes in the CSV Report

With the filters and functions set up, you can preview the changes. It's important to verify that your formatting is working as expected before those changes are pushed to your live database.

Under Step 3, click the Review button, then select the Preview option.

On the Notify tab, add any additional recipients who should receive the CSV (and make sure to hit Enter after each address). You can add additional context to the subject line and email body.

On the When tab, click Run Now 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.

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; this example shows First Name (Before) and First Name (After).

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

Apply Changes to the CRM

If everything in your CSV preview looks correct, return to Insycle and move forward with applying the changes to the live CRM data.

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 you are satisfied with how the operation runs, you can save all of the configurations as a template, and set up automation so this formatting 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.

By automating with a template, you'll ensure that your fields are consistently and automatically formatted on an ongoing basis. 

You can combine functions to isolate parts of a value, and apply formatting before copying to a destination field.

Extract First and Last Name from email address

You can look for email addresses that follow the "firstname.lastname@domain.com" format, then combine functions to parse and extract potential first and last name values from email addresses, format them properly with capitalization, and populate the respective First Name and Last Name fields accordingly.

In this example, the filter under Step 1 is set up to:

• Find records where the First Name field is empty or does not exist.
• Find records where the Email field contains a specific string or pattern (the "contains" condition has no value specified in the screenshot).
• Find records where the Email field matches the regular expression pattern "[a-z]{2,}.[a-z]{2,}", which would match email addresses with at least two lowercase letters before and after the "." separator.
• Find records where the Email field does not match the negative regular expression pattern "[0-9@#$%^&()+-:].*", which would exclude email addresses containing numbers, @ symbols, or certain special characters.
• Find records where the Email field does not contain the string "info|marketing|admin|sales|", which appears to be filtering out common role-based email addresses.

Under Step 2, the functions are set up as follows:

• The first function splits the Email field value by the "." delimiter and selects the 1st term from the split. This would extract the text before the first delimiter in an email address, which is typically the username or first name portion.
• The extracted 1st term from the Email field is then formatted using the "Proper case person" format function, which capitalizes the first letter of each word (e.g., "johnsmith" becomes "John Smith").
• The formatted value is then copied into the First Name field.
• The second function splits the Email Username field value by the "." delimiter and selects the 2nd term from the split. This would extract the text after the first delimiter in an email address, which is often the last name portion.
• The extracted 2nd term is formatted using the "Proper case person" format.
• The formatted value is copied into the Last Name field.

Select the CSV File for Import

Navigate to Data Management > Magical Import, then select the database and the record type in the top menu.

Click the Choose button and select the CSV file that you would like to import. Uploading a file into Insycle does not import it to the CRM right away. Instead, you can prepare the data in a variety of ways before actually importing it to the CRM.

Map CSV Columns to CRM Fields

After uploading your CSV data, Insycle analyzes your file and compares the columns in the file to fields in your CRM database.

A column is included for each field in the CSV, with the CSV Column header appearing as the top row. Then, Insycle automatically detects the matching CRM fields and maps them for you. The second row shows the mapped CRM field names.

If your CSV contains fields that are not matched to your CRM, a red exclamation point icon will show. 

These "Not Mapped" fields need to be mapped manually by clicking the dropdown and finding the appropriate CRM field. If they remain unmapped, they will not be imported. If you choose to leave these unmapped, you can remove them by hovering over the column name and clicking the “X”.

Specify How to Import Values for Each Column

With all relevant fields mapped, tell Insycle how to use the data on a field-by-field basis by selecting the Import Mode, which is the third row in the table.

You can also preview and edit the import data directly in Insycle by clicking the pencil icon next to any field when you hover over it.

If a record has errors, the red exclamation point notification will be shown on the left side of the record. When you hover over the red exclamation mark, an explanation of the error will be displayed.

Records that have errors in them will not be imported. Other fields will be imported as normal.

Insycle uses Matching Criteria to compare the CSV with your CRM data and identify existing records for the same entity. The criteria must be "unique identifiers" that would only belong to a single item, such as email addresses, phone numbers, street addresses, or ID numbers.

For example, a different individual in the database should not have Maria Smith's email address, phone number, or ID number listed on their contact record. If a contact record shows a match in any of those fields, we recognize that it is Maria Smith's contact record.

Under Step 2 on the Import tab, select the Matching Criteria from the dropdown.

You can set multiple matching rules that are evaluated in order from top to bottom. This allows you to find matches based on various criteria, all in one step. 

Note that when using an email field, Insycle will automatically cross-reference any additional email fields in the records for a match. If using a domain field, Insycle will check additional domain fields. 

Select one of the Records Modes to tell Insycle how the imported data should be handled during the import process. 

• Update existing and create net new – If unable to find a corresponding record, a new record will be created in your CRM.
• Only update existing – If a corresponding record is found, it will be updated with the data from your CSV import. Records that are not matched with an existing CRM record will not be imported.
• Only create net new – Only records that can not be matched with an existing record in your database will be imported. Records that already exist in your CRM will not be updated.

You can make formatting changes and other updates to the CSV data in Insycle before it is uploaded to your CRM. 

On the Functions tab of Step 3, select the Column Name from the CSV file.

Select a Function for each column, telling Insycle how the data should be formatted when it is imported. When finished, click Apply.

In the above example, the settings will do three things:

1. Capitalize first and last names.
2. Standardize countries to the three-letter ISO alpha-3 codes.
3. Format phone numbers to the E.164 international standard and remove any letters.

Explore the Function dropdown to find formatting options for any field in your CSV.

For additional control over the data you import, use the options under Step 4.

• List – Add your imported data to an existing list. Only available for HubSpot.
• Bulk Update – Update a specific field on import. For example, you could update all imported records to have the Job Title of “Founder,” regardless of the data in the CSV file.
• Associate – Associate contacts to companies, deals, and custom objects automatically when importing data. Select a CSV field and the CRM field you’d like to match it with to create associations. When using Salesforce, this is known as “Link.”
• Date Format – Format dates contained within your CSV file to your preferred standard.

By default, Insycle will attempt to import all of your formatted CSV data into your database. If needed, you can select individual records for import in the Record Viewer. 

Note that changes are applied to your CRM immediately; there is no preview step. To import, click the Import X Contacts button under Step 5. 

After the import runs, the Import Result breaks down the import information—how many records you tried to import and how many succeeded, failed, were updated, deleted, or unmodified. Click the Run ID to open a CSV record of the import.

A record of these changes can be found anytime in the Activity Tracker.

After you've seen the results in the CRM and you are satisfied with how the operation runs, you can save all of the configurations as a template to reproduce the same formatting operation in the future. 

Insycle uses Matching Criteria to compare your CSV to your CRM data. Matching Criteria must be "unique identifiers." These are data points that could only belong to a single entity—such as email addresses, phone numbers, street addresses, or ID numbers.

Additionally, when using an email field, Insycle will automatically cross-reference any additional email fields in the records for a match. If using a domain field, Insycle will check additional domain fields. 

If a row in your CSV is not being matched to a CRM record and you know that it should be, there are several potential causes:

1. The Matching Criteria you chose does not match between the CSV and CRM

   Have a look at the data in your CRM using the Grid Edit module, adding columns to the layout so you can explore the fields and values. Then, compare this against the columns and values in your CSV to find a reliable but unique field that matches the two sources. 

2. You are using too many fields in Matching Criteria rule

   You can select more than one field in each Matching Criteria rule; however, ALL of the fields must match, not just one or some of them. If you include five fields and four of them match, but one doesn't, Insycle will not consider the rule a match.

   Typically, it is best to use a single field for your first Matching Criteria to improve the likelihood of finding existing records in your CRM. Then you can add additional rules to match multiple fields.

   

3. Your Matching Criteria is too broad

   Insycle uses Matching Criteria to compare your CSV to your CRM data. If you're using a field that is not truly unique as Matching Criteria, it's likely that Insycle won't be able to identify one single record as a match. For instance, there could be many people with the first name, "John" in your CRM. This is why uniqueness is key.

   When selecting your Matching Criteria, make sure it is truly a "unique identifier." These are data points that would only belong to a single record—such as email address, phone number, street address, or ID number. For companies, it could also be company name, or company domain.

   

4. There is a syncing issue

   To refresh the data in Insycle, navigate to Settings > Sync Status, and next to the account name, click the Sync changes from last day button (lightning bolt icon). Alternatively, you could log out of Insycle and then log back in.

   For help re-syncing a specific field, contact support.

If there isn’t a single function 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 apply to the field value cumulatively, 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 get rid of 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 use several functions to populate the country value by using the email address:

1. Select the email field with the value to start with.
2. Select the Split: By any delimiter and pick the last term function. 
3. In the Parameter field, enter a "." to use as the delimiter, telling Insycle which part of the email value to isolate and use for the next steps.
4. Click the plus at the end of the row to add an additional function for this field.
5. Now that you have the two-character country code, select the Standardize: Country Code2 to Name function to transform it to the full country name. Click the plus.
6. Select the Copy: Value function and the Target Field the country name should be written into.

HubSpot's automatic phone number formatting features can conflict with Insycle's ability to format phone number fields. If HubSpot's automatic phone number formatting is turned on and you'd like to use Insycle for formatting, you'll need to revert this and un-apply the format in HubSpot.

This can be a little tricky because clicking "Remove" alone will not revert the format. You need to also add one space to the phone number.

Add the space at the end of the phone number field in HubSpot, then click Confirm.

Lastly, make sure to click the Save button.

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.

While you can run the Transform Data module without a filter, Insycle will analyze your entire database. This is not advisable, as the operations can take a very long time for large databases. Instead, 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.

Yes. Ideally, you'll want to build separate templates for formatting each specific field. This helps you to keep things organized and troubleshoot issues if they arise.

Yes, Insycle can help you format country codes. For more information, see the Format Phone Numbers for Country Codes article.