Best practices for preparing your data for imports

Preparing data for imports can be overwhelming and confusing at times as you may not know what data you want to import in the first place, or how to format your data before importing so it looks exactly how you want it to in HubSpot.

We have pulled together our key best practices for how to prepare your data for import in schools to help guide you through this process. We will be looking at defining the purpose of your import and what objects you want to import your data into, exporting the right data from your current system, setting up your data import file correctly, and how to best check your data's format.

Our best practices for preparing data for import

Define the purpose of your import and ensure you export the right data

Before you start preparing your data for an import, you need to decide what objects in HubSpot your exported data will go into. Typically, all parent data sits under the ‘Contacts’ object in HubSpot, child data sits under the ‘Deal’ object, and agents and feeder schools sit under the ‘Company’ object for a school. However, if you are a college or University, you may not store parent data in your CRM, and therefore your contacts could be Alumni, current students, and agents for example, and your applicants in the admissions process are under the ‘Deal’ object.

It is important you establish these first steps before exporting any data to prepare for an import as these highlight what type of data you are exporting and where this data will go in HubSpot.

Once you have decided what objects you want your data to go into in HubSpot, you need to decide what data you want to export from your current system and import into HubSpot. Is this current parent data, or enquiry child data, or is it a list of Alumni students which you want to report on? It is key you define what data points you want to import such as ‘First name’ ‘Street address’ or ‘Year group of entry’ as this will be what data you export from your current system.

Here are some common data points that we see schools import into HubSpot:

Contacts (Parents, Agents etc.):

First name
Last name
Phone number
Email address
Street address
Postal code
Relationship to child

Deals (children):

First name
Last name
Middle name(s)
Date of birth
Country
Nationality
Year of entry
Year group of entry
Term of entry
Enquiry date

Companies (Agencies, Feeder Schools):

Company name
Company domain name
Street address
Address line 1
Postal code
County
Country
Main contact at company
Email address
Phone number

When thinking about what data to import into HubSpot, you also need to consider the different options each data point could have. For example, for your ‘Relationship to child’ data, are there options which have been created by various team members that you no longer use and therefore do not need to exist in HubSpot? Ensuring the data you import is clean, will save you lots of time cleaning your data in HubSpot.

Setting up your data import files

After the above is decided and you have exported your data fields from your current system into an excel or CSV file, you can set up your data import file.

You will need one excel or CSV file for your child (deal) data and one file for your parent (contact) data. The only occasion where both sets of data may be in the same file in the same sheet is when you are associating objects through an import – for more guidance with this, reach out to your HubGem Consultant.

When setting up your data import file, it is important that the top column headers match the HubSpot properties that your data is importing into. For example, you may have ‘First name’, ‘Last name’, ‘Phone number’ and ‘Email address’ in your top four columns, and each heading should match the wording of your HubSpot properties to make the import process easier.

You also need to ensure that each data file contains the unique identifier of the HubSpot object. For example, in your parent (contact) data file, you will need to include their email address – usually as the first column header.

Likewise, if you are importing children (deals) in a file, you won't need to include a unique identifier, as when you import deals into HubSpot, a Deal ID will be created for each deal, and this will be used as their unique identifier moving forwards. You will need to include Deal Name, Deal Pipeline and Deal Stage in a deal spreadsheet file though, so HubSpot knows where to create and place the deal in HubSpot.

Then for a company’s unique identifier, you will need to include a company's domain name (website).

To note: if you are using exported data from iSAMS, a deal’s unique identifier is their ‘School ID’ exported from iSAMS, and should be in column A of your child (deal) spreadsheet file.

Top Tip: if you want to pull data from a deal’s first name and last name to generate a ‘Deal Name’ you can use the Concatenate formula =CONCATENATE(A2,” “,B2,“, “,C2) to generate this easily.

When your file is set up with your column headers and unique identifiers, you can add your exported data with one parent, child or company per row, ensuring the data in each column corresponds to each column header too.

Checking your data format

It is important to check the data in your import file before you import it into HubSpot. Firstly, you need to check that any properties that are multiple-choice or dropdown select options match in HubSpot and your import file, both property label and internal value. For example, under the ‘Country’ column if you have ‘UK’ in your spreadsheet, and in HubSpot the 'Country' property option is ‘United Kingdom’, the data wont import into HubSpot successfully. The data in your spreadsheet file and the options in your HubSpot properties must match under each column header. This is a good opportunity to also check each column header matches the same property label in HubSpot as mentioned above.

Another key best practice to ensure your data is formatted correctly is changing data to the correct format, such as strings of numbers and date formats. If you are importing a string of numbers such as ‘Phone number’ into HubSpot, you need to ensure this data format is ‘Text’ instead of a ‘Number’ format. You can edit this by selecting the cell you want to edit, navigating to the ‘Number’ section in the Home tab, and changing the format from General to Text. This means your data will be formatted correctly in HubSpot properties and may apply to other sets of data such as School Codes or Mobile numbers.

Setting string of text to text format

Then, to format date data in the correct way, you would select the cell or multiple cells that are formatted as date, navigate to the ‘Data’ tab at the top of your spreadsheet, click the ‘Text to columns’ button in the Data tools section of the navigation bar, then click through the pages until the ‘Column data format’ page and select ‘text.’ See the image below to help you navigate this. This also may occur with other data you are importing such as ‘Enquiry date’, ‘Registration date’, ‘Child’s date of birth’ and ‘Visit date’ for example. By doing this formatting exercise, it corrects any data that may not be in the format you want such as wanting your dates as DD/MM/YYYY opposed to MM/DD/YYYY.

Text to columns image 2-1

Therefore, by following these steps and best practices, your data will be in the correct format, your spreadsheet will have the right headers to match your HubSpot properties, and the data you import into HubSpot will look exactly how you want it to.

If you have any questions about setting up your import file for HubSpot, please get in touch with your HubGem consultant!