Importing Data from a CRM into Sitefinity Insight
In this Sitefinity Insight video tutorial, our President and Sitefinity MVP, Brad Hunt, will show you how to import data from your external CRM into Sitefinity Insight.
Your CRM captures a lot of useful contact and interaction data that is helpful for creating better digital experiences for your customers. We hope you enjoy this video and if you have any questions, please reach out to us.
Video Transcript:
Hi, I’m Brad Hunt, president at Smooth Fusion. We are a web development company specializing in the Sitefinity DXP platform. In this video, I will demonstrate how to import data from an external CRM into Sitefinity Insight. First, we will review the benefits of importing CRM data into Insight, then we will review an example scenario where we export contact and interaction data from an external CRM and import it into Sitefinity Insight. For this video, I will be using HubSpot, but you can use any CRM or external system that you would like if it can export CSV data.
The benefits of importing external data into Sitefinity Insight
The first benefit of importing external data is simply to enrich the contact data you have in Sitefinity Insight because Insight gives you a fuller picture of your customers.
A key feature of Sitefinity Insight is the ability to use personas and lead scoring to personalize the content on your site. The more data you have about your customers the more specific and relevant you can make your marketing segments by giving them the right content at the right time.
Finally having data from an external CRM can improve your overall view into the customer journey. You will be able to see the journey timeline for the customer including activities they perform on and off your site. That is all for the slides and now we will look at Insight and HubSpot.
How we set up Sitefinity Insight for demo
In Sitefinity Insight, we are looking at a data center called Automate demo that I have set up for this demonstration. It is connected to a Sitefinity website running at automate.smoothfusion.com.
Back in Insight, we have one contact currently and that is me. I have one lead scoring type setup that has three stages: cold, warm, and hot and I have a couple of example personas set up for marketer and IT admin but otherwise, the data center is empty and is ready for me to import data.
How we set up HubSpot with fake contacts and data for demo
So, we will look at HubSpot. I have set up a free account in HubSpot and I have created a hundred contact records. These records are generated from a fake data set so that I do not expose anybody's PII or Personal Identifiable Information. In this data set, I have several out-of-the-box properties from HubSpot like name, email, phone number, and company. I have created one custom property called newsletter to indicate whether this person is a subscriber to our newsletter. Out of these hundred contacts, there are about 25 or so that are subscribers, which is indicated with a “yes” and the rest are not. I’ve also pinned over here a custom view so in HubSpot a view is filtered, and I’ve applied a filter just to show the 25 people that are subscribed to this newsletter.
So, this is an example of customer contact data that I want to import into Insight along with a custom property for the newsletter. I also want to demonstrate how to import interaction data.
So, for this example let's assume that we have a group of salespeople that are scheduling sales calls with potential customers and they're trying to do a demo of some software or product that we're trying to sell, and for this demo how they're signing up is through a HubSpot tool that's connected to a salesperson’s calendar.
A potential customer could come to see available times on the calendar and book a time with a salesperson, and when that's done these folks show up in a different list called demos. I’ve used HubSpot just to generate a list of people who've had a booking through this calendar integration tool so that I can see a list of people that hopefully have had demos with our salespeople.
We'll use this as example data for interaction data because these are interactions that are taking place outside of the website, outside of Sitefinity, or outside of Sitefinity Insight. They are happening with real phone calls with people and so we want to import that interaction data into Insight.
Get Your Team Trained on Sitefinity Insight
Exporting data from HubSpot as a .CSV file
So, let's start first with the newsletter data. I’ll go back to my view and go under table actions export view and I want to change to CSV format because that's what Sitefinity Insight expects. I will export the file, and once that file's ready, I’ll open it locally and look at the columns. Let me expand these to see what is in the file.
Preparing a .CSV file to import into Sitefinity Insight
We have a contact id, that is a unique identifier for this person in HubSpot. We have a first name, last name, email, and HubSpot automatically added this associated company id. In fact, it added it twice, and I’m not sure why but we don't need that for our import. Let's get rid of these two associated company id fields.
Now we're left with some basic contact data including name, email title, and so on. Our custom property for a newsletter that we want to pull into the siphon of the insight. So, since I have edited that file, I’m going to go ahead and save it now.
How to import data into Sitefinity Insight
I’m going to return to Sitefinity Insight and go under administration, then I’m going to scroll down to my data center automate demo and look at data integration. Right now, because this data center is connected to my website, you'll see that there's already an externally configured data source called website and that's just to track all the interactions that happen on the website. Since we're pulling in data from a CSV, I’ll choose to connect to a data source and then import a CSV file.
Error when uploading data into Sitefinity Insight
Next, I want to navigate to the file that I downloaded from HubSpot. Here is my export of the newsletter participants and what's going to happen is Insight will run a series of validation steps to make sure that the file meets all of the requirements.
Understanding the requirements for importing data with a .CSV file
You'll see that I received an error, and it tells me there is no unique id column. So, let's pause and look at what the requirements are for the CSV files. This link takes me to the Sitefinity documentation where I can read more about the CSV file format and the expected columns of data should be in my file.
At the top, there's a little bit of an explanation of the file and the different columns that are expected. Then as you scroll down there are some definitions and additional data about each column. But if I scroll all the way down to the bottom, we can see examples of the different kinds of files that we can import.
The first example is an example of client metadata. In other words, contact data for our customers, and this is what I’m trying to import currently. So, you will notice that there's a column called unique ID and after that all the individual properties from the customer.
This is the problem, in my file, if I go back to the CSV that HubSpot generated, there is a unique ID column for contact ID, but it does not have the expected name. So, I will change that to a unique id and save. So let us go back and look at the rest of the data columns here. So, I have got a unique ID which I have changed and so hopefully that should solve the problem with the validation message we had.
Then I have some other columns of data that I am pulling in from HubSpot and again this is an example of contact data or client metadata. In a moment, we will return and we will talk about interaction data specifically and talk about the columns that that file expects. But for now, we have got the unique ID problem solved and then we have the rest of the columns. We should be able to try to re-import that again. I’ll get rid of the file that I chose previously, and I’ll select it again, now it has the unique ID column.
Warning message when uploading data into Sitefinity Insight
Okay, so we have gotten past that first red error message that said we were missing the unique id but now we have some new error messages and it's telling us that the client metadata column, first name, is not yet defined as a contact property alias.
In fact, this message is repeated for first name, last name, job title, newsletter, and associated company. So, what is that about? To explain this, I want to explain how the contact properties work inside of Sitefinity Insight.
What are contact properties and data centers and how do they work in Sitefinity Insight?
In Sitefinity Insight, there's the concept of contact properties. These are properties that are shown in the Insight user interface in places like the contact details or if you're adding a rule based on demographic data for a persona, for example.
Also behind the scenes in Insight, there's the concept that there could be fields of information that just exist in the data center that aren't necessarily shown in the user interface.
Mapping contact properties to data center properties in Sitefinity Insight
When a new data center is created in Insight, many of these contact properties are mapped to the respective fields in the data center. Email is mapped to email, first name to first name, and so on for all the properties.
But in our case, we are trying to import a new field for the newsletter. Once we do the CSV import that newsletter field will be present in the data center as a property that's behind the scenes.
But we can't use it in the user interface until we create a new contact property and map that to the newsletter field that exists in the database so that helps explain why the newsletter might have shown up in that yellow validation message.
But what about our other fields. We also had things like job title, first name, last name. These actually already exist so if we look on the left-hand side there's a contact property for the first name, for the last name, and for job title. However since our import data had spaces in the names of those columns Sitefinity Insight recognizes those as different properties.
So one option we have would be to change the names in our import file to remove the spaces and that way those properties would be automatically mapped to the contact properties or what we can do is keep the original property, so for example, job title is still mapped to job title but we add a property alias to the new version of job title with the space in it.
The same is true for first name. Map that to the original first name and also create an alias for first name with the space and finally the same with last name. By creating these property aliases we don't actually have to go and change the names of the columns in our import file, so that is what we will do.
We will go back to Sitefinity Insight, we understand now what these warnings mean and so I’ll continue. Before I get to that mapping step the next thing that the system is asking me to do is to provide a label for this imported data and this is important because we want to be able to tell later where this data came from once we have data in the system from multiple sources.
So I’m going to give this a label of HubSpot and click import data. A background process will now start running and it will import those 25 records and we can see that it was successful. These have been imported as visitors and so far we've not loaded any interaction data so we would expect that to still be zero.
Let's go look at those contact properties I was mentioning. If I click on the contact properties menu I can see all of the default properties that I have when Sitefinity data center is first created.
Editing contact properties in Sitefinity Insight to an alias for mapping
Some of the ones that we needed to map were job title. So if I click on job title we can see that it's currently using data from the job title property that already existed behind the scenes but if we choose to select from a list we'll be able to see all of the other properties that have come in from other data sources which at this point is just the one csv that I’ve imported so that job title property already exists, we want to add job title.
Now anytime there is a contact property in the system called job title, it's pulling data from the original property as well as the new data that we've imported. And you can see, there's a priority given, so if there's data available for the job title it will first use that, and if not then it will look at the job title.
So, we want to do the same thing for the first name. Select from a list, and choose first name. So, now we have both of those added we'll do the same thing for the last name. Select from a list the last name, and the other one was a company-associated company.
So, now we've handled those cases where our imported data had spaces in the names, and we can see that first name, last name, company, and job title are there but we're still missing newsletter.
Adding a new contact property in Sitefinity Insight
So, we want to go in and add a new contact property this time, and we're going to type in the newsletter and then we will select from the list of things that have been imported previously and choose newsletter, and hit save.
Data precalculation message in Sitefinity Insight: "it will take effect after the next data precalculation"
Note here, there's a note at the top that says that the property that we added as well as these changes that we've made are not yet applied. These will take effect after the next data recalculation. When that does happen it varies based on your data center.
However, if I go up to general settings, I can see that at 5 pm in my local time zone that data will be calculated and then those new contact properties will take effect. So, keep that in mind if you're importing things that cause changes to your contact property data. You may have to wait a bit before those are visible in the system.
So now we've imported some contact data, we've mapped a new contact property and we've taken some existing properties and given them new fields to look at that match what our file was like for our imported file.
So, let's go back over to analytics, and look at our contact list and make sure those people exist in our data center. So, once I go to automate demo and scroll down to contacts, I will see now where I had one contact before, now I have 26. It is showing that those 25 people were successfully added.
If I click on a contact email address, I should be able to see some of the properties that were imported as well as contact info. Now you'll see again some of these things are not yet available because that data hasn't finished importing and the precalculation has not run yet. So, once all that's populated, the data will be visible in the system, but that contact did make it in the system, and we've shown that those 25 contacts are now in our contact list.
So, that's all we need to do for the contact data. What about the interaction data? That's a little bit different.
Importing customer data interaction data from HubSpot into Sitefinity
So let's go back to HubSpot and look at our demos-pinned view here. And again, these are the people that have taken the time and watched a demo with a salesperson on a phone call and each of these actions took place at a specific date and time as indicated when their meetings were.
I want to export this data just like I exported the contact data in CSV format. Once that file is ready, I’ll look at what's inside. So, we've got a contact id which we already know from the previous example needs to be changed to a unique ID. I’ve got a first name, last name, email, associated company ID, again which I’ll delete. I’ve got the date of the last booking, in other words when they had their demo, job title, and associated company ID, which I will again delete.
Since I’ve already mapped the versions of the contact properties with the spaces, I don't need to change these here because we've already added those to the system. So, I’ll save that file.
File format requirements for Sitefinity Insight and importing interaction data
Before I try to import it, let's go back and look at the file format requirements that Sitefinity Insight expects.
So, recall I mentioned that for interaction data the file format looks a little different. For each of the interactions, you provide a unique ID, a predicate, an object, and a timestamp. The unique ID still refers to an individual person. In this case, the 744 unique ID refers to this person James Butt, who has been previously imported from a different contact file.
So, in this case, I’m saying James Butt took some action so the predicate is a verb. This person took some action, and the action was on some object, so this person visited a page of content and when did that happen? It happened at the time indicated in the timestamp, and so if you like, you can import only interaction data that has these four columns.
You can import only contact data with contact data columns, or you can mix them in the same file. So, in this case, you can see that on the first row we've got a unique ID for this person. They've visited some page and then you are also in the same file importing the contact info for that person or you may have just interaction data on one row and no contact data for a person.
So, it's up to you and how your data is. In my case, my file probably has a mix of people that have been previously imported that were already subscribed to the newsletter. However, maybe some people weren't subscribed to the newsletter. So, I’ll provide both data in my example.
So, again, the predicate is the verb or action the person took, and the object is what the action was on. Then, we also need a timestamp. So, let's go back to my file. I’m going to add a predicate. I’m going to add an object. And I already actually have a timestamp column. I just need to change the header to timestamp.
We're going to choose for this predicate, "viewed", past tense. And the object is that they viewed a "demo". Each of these people viewed a demo. You can choose the predicate and the object to be whatever you want them to be. You may just give a little thought to what those are so that you keep them consistent and organized within Sitefinity Insight. But for our case, the fact that these people viewed a demo is appropriate.
Then, for the time stamp let's go back and look at the documentation one more time because there are some notes there about timestamps that we need to understand. So, again, a timestamp is provided if you are uploading interaction data, which we are in this case. There's a specific format for the timestamp data. It needs to be an iso 8601 format and you can click that link to read more about it. But I’ve also got a slide to explain that in a little bit more detail.
Understanding time data formats in Sitefinity
In the documentation, you see an example like this (2021-02-25T18:36:45.250+3:00) which can be a little confusing at first so let's break it down into its components.
First, is the date. Which is four digits for the year. Two digits for the month. Two digits for the day.
Then, there is a time component, which starts with a "T" followed by the time and 24-hour time in hours, minutes, and seconds. And in this example, you’ve got a dot 250, which represents the milliseconds. You may not have that very often and it's optional, you don't have to have milliseconds.
Finally, there's a time zone offset. For this interaction, how far off UTC did this take place. You'll need to calculate that based on when your interaction took place for your time zone. So, in this example, this interaction took place three hours ahead of UTC time. So let's go back to the file that we exported.
So, here's our timestamp and we want to change these into that required format. In my case, I really would like to capture the times that these demos took place because I want to see how that data affects the customer journey.
I want to know when or what time of day that customer watched the demo, and if they saw pages on our site before or after that demo. If you don't care about the time information, you can just remove that and import the dates only. For me, I want to keep that.
How to change day/time format in Excel or .CSV using a custom string
So in excel, I’m going to select all those fields and right-click and go to format cells. I can use a custom string format to get these in this format that matches what we expect and again that's year, year, year, year, month, month, day, day. (yyyy-mm-dd). And you can see as I type that it gives us a sample that matches our data. So, we can make sure it looks right, and then there is a "T" value followed hour by colon minute, colon seconds. (Thh:mm:ss)
And so now we can see we've kept 4, 6, 2021, at 11:15 (2021-04-06T11:15:00).
Then the last thing we want to do is the time zone offset. So, I’m in central daylight time which is minus five hours off UTC time. So, to show that here, I’m going to do a quote minus 5 colon 00 close quote ("-5:00") The quotes just mean use that same value in every one of these cells. Because every one of those is going to be minus 5 hours off, these date format strings previously will help you have the right value for each of those dates.
So, when I click ok, you should see all those changes to the proper format. And they have and they've kept the times 11:15, 10:30, 10:45, and so on. So, I’m going ahead and save this.
I will now go back to Sitefinity Insight and I’ll go import that interaction data under administration > Automate demo > Data integration. I’ll connect to a data source as I did before and import a CSV file and this time, I’ll choose the demos file. It’ll run through its validation steps as before.
This time, we shouldn't receive any warnings because we've already mapped those other field names with spaces. Again, I’ve got the option to either add a new data source or choose an existing data source.
It’s important when you’re importing subsequent data from the same data source that you’ve used before that you choose the same label. Not only is that convenient so that you know where the data comes from behind the scenes, but Insight uses the label and the unique ID column to uniquely identify that contact. This helps avoid any situations where two different systems might use the same ID for different users. So, it uses those values together to uniquely identify the person.
It's also important to note that if you upload data from different systems with different labels, but the email is the same, then those records will be merged into a single contact record inside of Insight. So, just keep those things in mind as you’re importing data.
Now that second import will run and if that's successful, we expect to see interactions, which we do. Now let's go back over to analytics and go back to our data center. Scroll back down and look at contacts. Now we have 27 contacts. So that tells us that there was one person that was new that hadn't been imported before and it's probably this person at the very top.
If we click on this record and go to the data, we can actually now see that there is contact data for this person "Em Overstone", along with her email and company. Note something here, now we’ve got an interaction on the journey timeline, which we didn't have before for the contact data because now we’ve actually recorded to Sitefinity Insight that some interaction has taken place with this user.
When this user comes back to the site and starts to interact with the site and view pages, and so on, we will see those as part of the journey as well.
Defining lead scoring rules in Sitefinity Insight
So, now that we've got that interaction data in the system, we can start to actuallly use it to define lead scores for example, or lead scoring rules.
Let's go into lead scoring, into our sales lead, and we can imagine that if a person has taken the time to meet with a salesperson and they have watched a demo, then those people could potentially already be a hot lead.
So, if I go into my rules, we should now see that new predicate that we imported from that interaction file is available here in our list. So, now if a visitor completes the following: if they viewed, and as I start to type demo, it will auto-populate with the value that I’ve imported from the file. If they viewed a demo, let's go ahead and give those people 150 points and put them right into the hot stage of this sales lead.
Now that's been generated and willl take a few minutes and behind the scenes Sitefinity Insight will start to process that data and it will actually give those points to the people that we imported.
Adding rules for personas
We could do something similar in personas if we wanted to go add a rule to personas in Sitefinity Insight. We should see that the same predicate shows up here "viewed" and then also under personal characteristics as I mentioned we will see that newsletter field show up here, but I have to wait until the next data calculation at 5 pm my time. After that, I can come back here, and I can look under this list and I will see that new newsletter field and I could use that then to create a rule based on that demographic data.
Creating a conversion in Sitefinity based on imported customer data
I could also come up with conversions and I could create a conversion based on things that have been imported. I could create a conversion based on that new custom predicate. If they have viewed a demo. Then I could give this conversion a name, "view demo" for example, and save it.
Then, behind the scenes, Sitefinity Insight will turn some of the data and it eventually will update this report to show conversations that have happened based on that particular action. After just a few minutes this conversion report should be updated, and now I see four conversions based on the data that I imported with the interaction data. I can drill into that conversion and see when they happened. I can also see the contacts that converted based on the ones that we imported.
How to track sales leads in Sitefinity using lead scoring
And I’ve given it enough time now that if I go back to the lead scoring section, I should also be able to see those same four visitors show up under the sales lead scoring type. I can drill into there and see that there are four visitors that are now in the hot stage of the sales lead, lead score. If I look under contacts, I can see the individuals that were imported as part of my interaction data.
Personalizing a page in Sitefinity based on customer data
So, now to kind of wrap up this demo, let’s see how we would personalize content based on a user that was in this hot stage.
To do that, I’ll go over to Sitefinity. I’ll go under the marketing menu, choose personalization, and I’m going to add a new user segment.
First, I’ll choose the characteristic based on Sitefinity Insight lead score for the sales lead and anybody that's in the hot stage. I'll just call that a hot sales lead and hit save.
Now if I go back to my pages, and go to my home page, perhaps I want to give people that are in the hot sales lead stage a different call to action on the homepage. Maybe I want to add something here above these other pieces of content. So up here I’ll choose to create a personalized version of this page. That way I can do whatever I want with it. If I want to change out any of the content. And the hot sales lead segment, we'll see this version of the page. Now I could add a new content block to that container. And maybe this gives them some piece of content that's different that encourages them to take that next action. So, I can publish that. That way somebody that comes to the site that has been a known user to us in the past, they've filled out a form, we’ve got their contact info, and now we have this extra info that's come in from HubSpot. We’ll be able to show them the specific piece of content, this specific call to action.
Well, that concludes the video. I hope that you found it useful and got some ideas for how you might use this in your own business. If you've got questions or want to consider working with Smooth Fusion on a project, feel free to reach out to me at [email protected].