separate-street-address-9.png

How to Extract City, State, and Zip Code Values from a Full Address

Separating a full address into multiple fields can be useful for personalization, segmentation, and data analysis. Without that separation, it's not possible to segment by those attributes.

With Insycle's Transform Data module, you can analyze full addresses and based on where the commas in the address are, copy the relevant portions to the appropriate fields. 

Insycle includes a pre-built Recipe that features four templates for extracting different elements of an address.

Insycle's Pre-Built Recipe for Street Address Separation

Insycle templates are a saved collection of processes and settings that run in modules or apps. These can be added to a Recipe, which is a related collection of templates that can be run in sequence and automated. 

The Extract Address Fields from Full Address Recipe includes four templates that extract the different elements of a full address: street address, city, state, and zip code. The extracted values are then copied to separate fields.

To use this Recipe, navigate to Operations > Recipes in the left navigation, and select it from the dropdown menu.

separate-street-address-2.png

Learn more about Recipes.

Step-by-Step Instructions

Process Summary

  1. Filter records down to only those that have the correct format.
  2. Instruct Insycle to identify the relevant part of the address, using the comma placements.
  3. Copy the selected data into the new field.
  4. Preview then apply the changes to the CRM records.
Step 1: Find Records with Full Addresses

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 this section, we'll use the Extract City from Full Address template as a starting point.

Under Step 1, configure the Filter to look for records that have data in the Street Address field with a value that follows the specified format and comma placement.

Addresses that do not match the format will not be included in this operation (for example, a street address that is missing commas).

separate-street-address.png

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 make changes to the filter, click the Search button again to reload the Viewer.

transform-data-contacts-break-address-record-viewer.png

Step 2: Configure Functions to Copy Portions of Address

Separating City from Full Address

Under Step 2, you tell Insycle how to identify the city contained within the full address by specifying where to look between the commas. Then, that data needs to be copied over to the City field.

separate-street-address-3.png

  1. Select the field that contains the full street address value from the Field Name dropdown.
  2. Select the Split: By any delimiter and pick the Nth term function.
  3. In the Existing Text field, enter a comma, ",". This tells Insycle to look in between the commas.
  4. In the New Text field, enter "2". This tells Insycle where to look between the commas. The 2 refers to the second block of data between the commas. (To capture the street address, you'd enter 1 into the New Text field.)transform-data-contacts-break-address-split-illustration-1.png
  5. Click the plus at the end of the row to add another function for this field.
  6. Select the Copy: Value function and choose the Target Field to copy the city value to into. This does not alter the original Street Address data, but puts a copy in the City field.

Separating State from Full Address

In this section, we'll use the Extract State from Full Address template as a starting point.

State and zip code have an additional wrinkle to consider because there is no comma between them. For example, "NY 10012."

separate-street-address-5.png

  • Use the Split: By any delimiter and keep just selected term function, telling it to look at the third block of data.
    transform-data-contacts-break-address-split-illustration-2.png
  • To isolate the state from the zip code, add another Split: By any delimiter and keep just selected term function. This time, enter a space " " in the Existing Text field, and a "1" in the New Text field. The "1" tells Insycle to look before the space in the selected block.
    transform-data-contacts-break-address-split-illustration-3.png
  • Then use the Copy: Value function and select the Target Field to copy the state value into.

If the states are abbreviated, you may also want to add a function to change them to their full names.  

separate-street-address-7.png

Step 3: Preview Changes and Update CRM Records

Preview Changes in CSV Report

Before applying changes to your CRM data, or when you're developing a new template, previewing the results is an important step. 

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

transform-step-3-preview-mode-your-crm.png

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.

transform-data-step-3-preview-mode-run-now.png

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. In this example, the City (After) values have been copied from the Street Address data, filling in the blanks, and in some cases replacing what was there.

transform-data-contacts-break-address-city-csv.png

If your 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 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.

transform-data-step-3-update-mode-run-now.png

Save Templates and Set Up Automation

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 operation runs on a set schedule. 

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.

save-template-copy-and-rename.png

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.

transform-data-step-3-update-mode-always-send.png

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

transform-data-step-3-update-automate-weekly.png

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

If you have several templates you'd like to automatically run together, you can create a Recipe. Additionally, HubSpot users can integrate Insycle Recipes into HubSpot Workflows.

Advanced How-Tos

Using the Move vs Copy Functions

When configuring Step 2, there may be cases where you want to move a value instead of copy.

The Copy: Value function will duplicate data from a specified field into a target field. The data in the source field will remain as is.

With the Copy: Move function, the data from the source field is moved into the target field, and the relevant data is cleared from the source field.

You may want to use the Move function if the data belongs in the target field, and should not be in the source field. For example, if the full address appears in the Street Address field when it should hold only the street name and building number. The extra data can be moved to the City, State/Region, and Post Code fields and removed from Street Address.

transform-data-contacts-break-address-step-2-move-city-w-arrow.png

Learn more about using the Move or Copy functions.

Templates for Copying Parts of Full Address into Other Fields

Insycle provides built-in templates for pulling various pieces of data from the full address. These can provide a good starting point.

  • Extract City from Full Address 
  • Extract Street Address from Full Address
  • Extract State from Full Address
  • Extract Postal Code from Full Address

There is also a Recipe, "Extract Address Fields from Full Address," which includes all four templates that can be run in sequence. To use this Recipe, navigate to Operations > Recipes.

Exporting Filtered Data, One-Time or Automatically

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

In the Transform Data module, you can export your data one time, immediately or set up automated exports.

Click the Export button.

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

Grid-edit-export-1.png

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.

Grid-edit-export-when.png

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.

Grid-edit-export-schedule.png

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 helping you keep an eye on the most important customer data.

Adding Additional Fields to the Record Viewer

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. 

transform-hubspot-contacts-get-country-from-email-layout.png

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.

Troubleshooting

Not Seeing Fields Added to Layout in Preview CSV

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.

Step 1 Export

Frequently Asked Questions

Should I build a separate template for each field I want to format?

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.

Can you help me build a custom template to format a specific field in my database?

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.

transform-data-chat.png

Why do I need to filter my data?

Filtering data before exporting 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 make changes to 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.

Additional Resources

Related Help Articles

Related Blog Posts