I have to admit I don’t get to work with Pardot much anymore, that’s just the nature of my job. However, lately, I’ve been asked a lot about the B2B Marketing Analytics App and how you can customize it to your needs. Now I’ve already touched on this topic before with the blog on custom velocity calculation of the marketing to sales funnel, but this time I will be looking at how you can add more fields to your Pardot datasets.
What is the use case?
So you might think why do I need to modify my Pardot datasets? Well, the B2B Marketing App comes with some out of the box datasets, that works very well for the dashboards that are created. Now if you want to add a filter or an additional chart to those dashboards based on some custom fields you have created in Pardot or even have in Salesforce, then it’s not possible to do. At least not via the templated app, but we can do it with recipes or dataflows. Let’s have a look at both options, which both works with the assumption that you have your B2B Marketing App up and working as well as data sync (formerly replication) enabled.
Get more data with recipes
Probably the simplest way to enrich your Pardot dataset is to use recipes. Recipes provide a very UI friendly way of applying transformations to your dataset. So let’s have a look at how we can modify our “Pardot Prospect” dataset from Pardot with some additional fields.
First, navigate to Data Manager by clicking the gear icon in the top right corner and select “Data Manager”.
From here we need to go to the “Dataflow and Recipe” section in the menu on the left-hand side and select the “Recipe” tab.
Next, create a recipe by clicking on the blue “Create Recipe” button in the top left corner. We now need to select the source data, which in this case will be the dataset “Pardot Prospects”.
This is followed by giving the recipe a name, which I have chosen to call “Pardot Prospects Enriched”. Once done click next to see a preview of the data.
You will notice that there are few fields in the dataset that represents ID’s from CRM (Salesforce). We can use those fields when we are adding data from Salesforce to the dataset and enriching the information. One thing you always have to be aware of is the structure of the data you are working with. Since we are working with prospect data we can map is to contact data in Salesforce, but remember a prospect can be a contact OR a lead, hence we have two ID’s in the dataset. If we use contact data lead data will not be added.
What we want to do now is to add contact data to the data we have from Pardot. So in the top right corner, you will find a button that allows you to add data.
Now you can add data from a dataset or from connected data. In this case we are going to use the connected data, so make sure to select the connected data tab. From here select the object you want to use from Salesforce, which as mentioned will be the contact object.
You will now see a preview of your data as well as the option to choose your ID’s from both data sources and to select which columns to add from your contact object.
For your “lookup keys” we need to select ‘crm_contact_fid’ as the first ID and ‘Contact ID’ as the second ID. It’s important to remember that this is a left join meaning the Pardot Prospects dataset is the left side and the contact object is the left side. All rows from the left side are included but only rows from the right side which matches to an id will be kept. Also remember that if you have two rows on the right side with the same ID only one row will be added in the join, the other will be dropped.
Underneath you can select the fields from the Contact object that you want to include in the new dataset by checking the box next to the field. Notice that fields from the Pardot Prospects dataset are blue while those from the Contact object are orange.
In my case, I have only chosen to select one field from my contact dataset the field ‘Total # of Activities’. Once done hit the “Done” button and you’ll notice that your transformation has been added to the transformation panel on the left side.
If you want to add more data go ahead and repeat the process. If not go ahead and click “Create Dataset” in the top right corner. Notice that you can change the name of the dataset, which fields to include and where to store the new dataset. If you are happy with your selections you can click “Continue”
You will now have to decide if you just want to run this once or if you want to schedule the recipe. Data will only be refreshed after the data has run, so I would suggest scheduling it to after your Pardot data has been loaded. If you are in doubt when that is, then check out the data monitor, which is the landing page when you load the data manager. It will tell you when the Pardot data was last refreshed if you look at the hour for the refresh that should be the same time every day.
In the case where you don’t see the object or field you need you may have to go back to your connected data. This is done by navigating back to data manager and from the menu select “Connect”
If you need to add fields to an object you already have synced click on the object name and you will be able to select fields you want to include in the sync. Once done you can click “Continue” and then “Save”.
If you need to add a whole new object that hasn’t yet been synced click on “Connect to Data” in the top right corner and choose the “Salesforce Local” tile.
Now you need to select the object you want to sync and click “Continue”. Now you can select the fields you want to be included in the sync just as described above.
Make sure to run your objects after the modification else that data will be missing from the data sync.
Get more data with dataflows
Using a recipe should work in most cases to enrich your Pardot datasets. However, there might be use cases where you need to create further transformations that are not possible to do in recipes then you can leverage the dataflow. Let’s have a look at how we can obtain that same as above in a dataflow.
We first want to create a new dataflow from the dataflow screen by clicking “Create Dataflow” in the top right corner and give your new dataflow a name. I’ve called mine “Enriched Pardot Data”.
Now we have a blank canvas to work with. The first thing we have to do is grab the data we need. Since the Pardot data is already in a dataset we need to use the “Edgemart” function.
This will open a new window where we need to select the “Pardot Prospects” as the dataset and then we, of course, need to give our new node a name. After this you can hit the “Create” button.
Next, we can add a sfdcDigest to the canvas. We want to use this function to grab data from the local Salesforce connection, in this case, the Contact object.
Again we need to give the new node a name and select the source object we want to extract – the contact object. Additionally we need to select the fields to extract by clicking the “Select Fields” and then check all the fields we want. Once done we can hit “Create”.
Now we need to join the two data sources defining the left side (the Pardot data) and the right side (the contact object). We will do this with the augment function.
First, we need to give the node a name whereafter we can choose the left source. As mentioned above the left source is, in this case, the Pardot data and the node we created to extract that data. We also need to define the lookup key from this source, which is the ‘crm_contact_fid’.
Now we need to define the relationship of the data before we select the right source. I have chosen to call it Contact, you can call it anything you want, but this is what will be used in naming the fields from the right source. The right source is the contact data, so we need to select the node representing the contact data. Also here we need to define the lookup key, which is ‘Id’. Finally, we need to select the fields to include from the right side. If you haven’t added them in the sfdcDigest then it won’t be availble in this augment. Once done click on “Create”.
For our transformation to be completed we and added to a dataset we need to have a ‘sfdcRegister’ node.
As always the node needs a name and a source, which of course, is the augment node. The rest we have to do is just give the new dataset an alias and a name, whereafter you can click “Create”.
You can now first update your dataflow by clicking on the “Update Dataflow”, which you need to confirm.
Next, you just have to run the dataflow in order to generate the new dataset. You can go to the Data Monitor afterward to see the status of your dataset. Once it has completed it’s available in your Shared App.
Whether you create the dataset using a recipe or a dataflow is less important, use whichever does the job and you are more comfortable with. Regardless once the dataset is created you can use it to create a new dashboard or you can replace the dataset that is being used in the B2B Marketing Analytics App with the new dataset.
Pardot has created some really helpful downloadable material on data manipulation and breakdown of the datasets from the templated app.
Dataset Breakdown paper (download will start when clicking the link) – an overview of all the datasets from the B2B Marketing Analytics templated app and the fields.
Data Manipulation paper (download will start when clicking the link) – how to and different use cases on how to manipulate your Pardot datasets