Speaker 0: Hello, ParDreamin. Uh, thanks for joining us today. We have the amazing Mike Kruiser, uh, here to talk to us about data science and ad hoc reporting from Pardot and by API. I I hope you’re not afraid of coding because you’ll be definitely sharing that during the session. Can everyone hear me? Just want to make sure, um, you guys can hear me and as I’m talking. If not, please, uh, I don’t know. In the note, but if you can’t hear me, you won’t know. Um, but if you some housekeeping items before we kick this off. As we go through the session, please feel free to drop any questions in the chat. And if we do have time, we’ll go through them at the end of the session. And this session will be recorded, so you’ll be able to hey, Michelle. We’ll be able to come back to this room roughly two hours after to get access to, uh, the the video, the recording, and the slides. So looking forward to that. And, um, if you guys are if ready if if Mike, if you’re ready, I’m going to be passing the stage on to you.

Speaker 1: Awesome. Hi. Uh, my name is Mike Kruiser. I’m a CRM Market Automation Solution Architect here at Sercante. Um, today’s topic is is data science and ad hoc reporting using the Pardot API. And by ad hoc reporting, I’m I’m not talking, like, Tableau reports, really pretty CRM reports or something like that. I’m talking about getting lists of data of of prospects or activities or emails that we can do stuff with. So very, very task specific, um, not pretty, just quick and dirty, get data so we can do stuff and get things done. That’s what I mean by ad hoc reporting. Uh, this is a programming thing. It’s for programmers. If you’re not a programmer, um, that’s okay. You can watch this because you can see what’s possible, and you can ask your programmer to to do this for you. So first thing, what’s data scientist? Uh, what’s data science? Uh, a data scientist is someone who creates programming code and combines it with statistical knowledge to create insights on business data. Too many words. Too many words. Someone who creates insights on business data. That sounds like a marketer, doesn’t it? Marketing and reporting. Yeah. We’ve got that scary thing, programming code. Scary. Um, it’s okay. It’s okay. Uh, I don’t care about a lot of that. You know, data science is a big topic. I’m not a data scientist. I just there’s this one really cool tool that I really like called Jupyter Notebook, and it allows us to do really amazing, powerful things. So what we’re going to cover today is a brief intro to Jupyter Notebook, a brief intro into the Pardot API. And I’m going to go through a couple examples, uh, around a little bit of code, um, to to show what we can do with with this technology. Jupyter Notebook. It’s it’s a lot of programming. Um, it’s like programming Excel, except it’s not Excel, um, and a little bit of reporting and stuff like that. But it’s it’s like, anything we want to do in Excel, we can do with Jupyter Notebook, uh, programmatically, and we could basically, you know, push a button and just get it to repeat all the time. It’s an interesting tool because it’s it’s a web-based, um, user interface. So we we open up in Google Chrome or Firefox, Safari, whatever our our browser choices. Um, but we have a a piece of the the tool that needs to run command line. It’s basically a piece of software that that opens up a a a that you interact with the browser. We don’t run that outside, like, on the cloud or anything like that. We can run it on our computer, and and it works well. So this is a weird one to install, uh, because this is weird double thing. So we can do a command line installation if you’re running, you know, Windows, uh, subsystem for for Linux, uh, your Mac, a a Linux computer. There’s command line, um, installation. Uh, if you use Docker, you could do a Docker install as well. Um, this integrates into, uh, Versus Code. So if you want to run this within our Versus Code environment, we can do that. And then there’s also, uh, new, like, two weeks ago, uh, a new desktop app installation that we can use. And that’s actually what I’m going to show is this desktop app, um, which is pretty new. So a quick start. Um, we have to run that command line thing, and then it will give us a bunch of log, and then it’ll give us a URL with a token. And we copy that, and we paste that into our web browser before it pops up. That’s how we, um, need to interact with it for for, uh, with the command line versions of of installation. And it it’s not obvious that you need to do it that way. But, uh, once you figure that out, that’s that’s what you need to do. Or if you’re running a desktop app, you just double click and it opens like any normal desktop app would. Uh, the the file format, it’s it’s a weird one. So this is code. This is text. This is reports. And this file format will grab data that you’re running. So as you’re running it, it grabs, like, data previews and stuff like that, and it loads and saves all that in in the file format. So you have to be careful with saving these files because you may inadvertently have have customer data, um, on your desk on your computer, uh, that you don’t want. So we got to be careful with the file format in this one. So it’s a little bit of a a scary file format in that regard. So the Pardot API. Pardot API, there’s a whole lot it can do for us, but for ad hoc reporting, solving problems, we tend to only to be concerned about with with prospects, visitor activities, uh, maybe custom redirects, um, likely emails, list emails, emails, emails sent, um, maybe prospect lists. There’s a whole lot more available to us with the Pardot API, but we’re not so much concerned about that generally. Um, there’s a couple versions of the API. Um, three and four are what we’re going to be using primarily. Um, there’s a new five that’s being built up right now, and we’re getting a lot of brand new features in five without any of the existing features that are available in three and four. So we might need to, like, 90% of time use three or four, probably four. And then we might need to pull in and and grab, like, this really cool shiny thing of five just to do something, um, new. In a year or so, we’ll probably get, um, version five, and we use that one, um, all the time because it will be complete at that point. So how do you use the Pardot API? Well, we create code. Right? Um, and that works. I mean, it’s what it’s designed for. It’s what APIs are for. Um, but there’s also another tool. It’s called Postman, and it’s it’s like a web browser but for APIs. And you can you can try API calls and and say if variables and your things and and and push a button, and then it will work or may not work, and you kind of work with it. Um, I find I spend a lot of time in in Postman figuring out the right question to ask the API. Um, and once I get it figured out, then there’s a a little code tool built into the, uh, API or then to Postman. Well, it’ll give me that, like, a code snippet. I can just copy and paste that into my my Jupyter notebook, and I can, um, just do it that way. It’s kind of like an easy button. Um, there is a a a post in collections, so you can just download a a collection as they call it, and it’s got all the Pardot API stuff built into it. And that’s on the link on the screen. The hardest part of this process is authenticating. Um, there’s a lot of resources on how to set up, uh, the SSL authentication for API log and stuff like that. So you you’re going to have to find one of those, uh, resources, um, to to get started with that. So examples. Right? Demo time. The first example we’re going to work with is is deleted prospects because working with deleted prospects in Pardot is impossible because they’re in this box that you can’t touch. And the only thing you could do is is, like, undelete them. Well, now you risk automation rules running, emails getting sent, these records being synced to the CRM, um, um, when we don’t want to. So, like, no. We don’t want to do that. So we can use the API, and we can actually grab work with just the records inside the, um, the reset tool bit. Right? So let’s look at that. So now code time. Right? So this is Jupyter notebook, and I’ve got a bunch of stuff that we’re not really worried about hidden. So there’s more to this, and this is available to download on on GitHub, on the links on the the slide deck if you want it. And kind of what we’re going to do is grab an export. So I’m doing an API export. I’m looking at the prospects, and kind of the interesting thing is is I’m looking at deleted. I want deleted through. So I’m going to export all the deleted prospects, um, from my org. Right? Then I’m going to read it in. So one of the things we noticed with with Jupyter Notebook is ad hoc reporting. Right? So we’re doing a lot of work, and every time we do it, it’s it’s different. I’m I’m solving this problem, or I’m solving that problem, or trying to figure things out. So there’s a lot of stuff in here that’s commented out because we’re we’re doing this, and and that’s not what we want. We tried this other thing. So we’re constantly twiddling and fiddling and and doing different things. So, um, I find that I have a lot of commented out things that, uh, I’m not doing right now, but it’s useful. I’ve done it in the past, and they want to do it again in the future. And I just kind of manage this, um, these changes this way. Um, so that way I I can, uh, react to different questions, different problems. And then, um, I I can write out the data. So in this case, I’m just deleting or, uh, reading all the deleted prospect. I’m just creating a CSV file. So if I wanted to run this code, uh, there’s some options up here. I could push this double fast forward run, and that will run all the code. And it goes through and it just it does the work. So there’s a little asterisk here that’s showing me that it’s it’s running. So it’s running. And then I get out here, and I’ve got, uh, reports. Right? So it’s generated a report. So I know in my demo org, I’ve got a total of 28 prospects, um, that have been deleted, one contact and one lead. And, oh, some extra data I pulled in. None of these records have bounced. Right? So we can do some reporting in here as well, but, really, the the cool thing is generating this the CSV file so we can do things elsewhere. So the next thing I’m going to show is is exporting from Pardot. Uh, this is going to be a little bit more of a complex export. In this case, we’re looking for email bounces from Pardot. And, uh, we’re going to grab a couple of different fields. Uh, we’re going to do some filtering, and then we’re going to save this to, actually, a couple of different CSV files. So getting back into the demo, I’ve got another example here. A lot of the same stuff. Um, so we’re doing the same export on the same prospects, but we don’t have that deleted equals true down here. And I’ve added, um, other fields, you know, email bounce, um, email bounce reason. Right? And then when I go into reading these export files, this line is different. So before, I’d I’d used, uh, this line, but now I am filtering out that data. I’m looking just for hard bounce before I load this data into a into a PDF. PD is short for pandas. That’s a library we use that gives us the the Excel like, um, data manipulation. And this structure this Excel structure is called a data frame. So, uh, we’re doing a data frame, uh, manipulations with the Pandas library within this is Python. So that’s how we kind of load that different set of data. And then I’m lazy, and there’s a computer here to do what I needed to do. So I like having the computer split the data apart for me. So I’m going to have the computer go and say, I want all the leads and put them in one file. I want all the contacts, put them a different file. And then those records that aren’t leads or contacts, those are prospects, and I want them in my prospects file. So that way, when I need to do a I forgot to update the data or something like that. Right? I can have the file already ready for data loader so I can have my contacts. I could update data with data loader, my contacts, and the same with leads. And then my my straight, uh, prospects, I could do the same, um, thing. Um, or I can use a a for API version, uh, sorry, MCEA, um, where I need to have a CRM ID in order to do imports. I’ve got that, uh, available as as a break broken up file. So I have CRM IDs and non CRM IDs. Um, it’s already done with us. And what this looks like is when we write out the data, notice, um, on the prior demo, her data write was pretty pretty simple. This one’s a lot more complex because I’m I’m writing out the different, uh, files. But, uh, yeah, it’s basically, um, you know, push button get work. It’s pretty simple once we get this set up. So that’s the second example that we’ve got. The third example is really hard, creating an email audit. I want to know all the emails that I sent out to a prospect. Well, I want to know this for all my prospects. I want, uh, an export of all the emails and who they’ve been sent to. So So this is complex. We’re touching a lot of different things. We’re touching our visitor activities to get those emails that are sent out. We’re touching our our email templates to be able to grab the what we actually sent. We’re grabbing, uh, prospect data to see what’s going on. There’s a lot going on here. So if we go into this demo and this one takes a little bit longer time, so I’m going to push start now, uh, while we go through it. So a lot of the same setup, But if we notice the first thing when we do our export, we’re actually exporting off a different activity. And, uh, because we’re we’re trying to grab the the email sent, so that’s visitor activities, how that data is captured in Pardot. Reading that export, um, pretty simple export read because I know exactly what’s going on. And when we do the work, notice our our data save is much more complex. Right? So, uh, there’s actually two sets of files. There’s a second one I’ve hidden. Uh, but when we go in, we’re grabbing, you know, the list emails, and we’re writing out a list of of everybody that was sent to. Right? We’re also grabbing that that HTML um, record. Um, the HTML version of the email, we’re writing that out to a file within a folder. We’re doing the same thing with the text. We’re writing out the the text version of the of the folder. So there’s a lot more work to do to get this done. But once we do this work and every time we want to run this, we just we just push that go button, and, uh, and we have the capability. The the the task is getting done. Right? So other uses. We can we can preprocess data. So I’ve got I’ve got a a bunch of complex data. I’ve got, um, a dataset that’s got multiple entries for a single prospect. I want to import that in. It’s kind of a lot of work to do that in Excel. We could. We do pivot tables and stuff like that. But we also write a Jupyter notebook that kind of does that with code, and and, you know, we can push the the the run button, and it just does all the work, and it can give us, uh, you know, summaries or something like that of of data. So if we got multiple line items, you know, they’ve they’ve made multiple orders. I could actually create a a single field with multiple orders in it because I’m kind of summarizing into a a a single cell and format it so it’s ready to to import. So we could do a lot of really cool, um, Excel work that would take us hours every time we need to do it. We build we spend more of our time than that to build it, but once we build it, then it’s it’s two, three minutes to to run that, and and it very reliably does that work over and over and over again. We can we can audit our dynamic content. Um, we can look at those email templates that were sent, and we can say, what dynamic content pieces were in use, what tags, what merge fields. So that way if we need to update a dynamic content, we’re not exactly sure everywhere where this is being used, we can we can see that. We get a list create a Excel sheet, um, of where these dynamic content blocks are being used. So we can we can make sure that we’re not damaging, uh, uh, an an email template that were in use inadvertently because because we forgot that we’re using a dynamic content piece in that thing. Um, we can use this something like this to to recheck our custom redirects. Right? Just go through the custom redirects and make sure that those URLs are are valid. Um, we can export the files. It’s pretty cool capability we have with the the new API version five. We can migrate data from, like, one Pardot instance to another. Um, some pretty cool stuff we can do with, uh, uh, this tool once we’ve figured out how to use it and how to start doing our automations and how to do this ad ad hoc reporting. It’s kind of a different way of of viewing our data. Uh, but once once it’s there, it’s pretty cool. So thank you for listening. Uh, I’m Mike Kruiser. You can find me on LinkedIn or or Twitter. It’s where I’m actually the most active is is, uh, on Twitter. Uh, email me if you have any questions. Um, And then the the source code and the examples are available, um, at at the GitHub URL at the bottom of the screen. So if you need to, um, if you were interested in grabbing this, you can go grab that source code.

Speaker 0: Mike, uh, am I good to share that link here in the chat for them?

Speaker 1: Yes, ma’am. Please do.

Speaker 0: If you guys have any questions, uh, feel free to drop them into chat. And thank you, Mike, for a great session. And, honestly, uh, it’s been great kind of deep diving into what you can do with the API, so it’s great for everyone to know. Um, and we want to, you know, thank you guys for joining the session and a special shout out to our sponsors for their support. Without them, Pardot Dreaming, you know, wouldn’t be possible. So make sure to drop by the sponsor booths to learn more about what they all do and get some points to be eligible for some cool prizes. Um, we have a lot of great sessions for the rest of the day lined up. So, you know, then one coming up soon. Actually, we have a question, but let me just finish this. Uh, Business Units and Pardot Sandbox Lessons Learned is coming up next, so make sure to check it out. Um, we have a few more minutes, so I’m happy to take a question if you’re good with that. I’m going to push the screen.

Speaker 1: So another, um, technical session like this one tomorrow, uh, hosted Um, or so, and it’s talking about, uh, using the Pardot API to do email sends and, uh, um, how to different ways of of working with that. And it’s a longer session, so, um, Adam will get much more into into the weeds with with these, uh, these details. This is a short session, so we can’t really get into it very well.

Speaker 0: So the

Speaker 1: The, uh, um, I saw Mariah, um, had a had a question. Where did it go?

Speaker 0: Can you see it? And can you show your final product of your third demo email sentence?

Speaker 1: Um, so I can’t, um, because it’s still running. It’s a nice thing about demos and and, um, um, the the Pardot exports takes ten, fifteen minutes in order to do. So it takes, you know, just the the Pardot process. So it takes some time to to to run that. Um, the code will sit there, and and we’ll wait for that time so you can basically move on and and do other things. But, uh, in ten, fifteen minutes, we should get a a a folder, an open folder with the different, uh, email IDs. And then inside that email ID will be, uh, an HTML file, text file, and a CSV file. You can, uh, you can go to, uh, the the link that, uh, Rana posted, and the the notebook will be, um, available there. So you can try this yourself if you wish.

Speaker 0: Awesome. And we have another question from Chris. Can Pardot API be used to pull form handler raw data?

Speaker 1: Yes. Um, it’s a little bit interesting to get to form handlers through the API. We have to look at the the visitor activities, and we look for the form handlers being used in order to get a list of form handlers. Pardot API doesn’t give us the ability to say, I want to know everything about all the form handlers. It’s just not a question the API can allow us to answer, but we can get to that data, um, through the visitor activity. Um, let the code go through, find every time a form handler has been used and and, uh, compile that list. Um, I think there was a related question from Britney. Um, can the Pardot API be used to pull the last time a prospect opened an email? Yes. That’s the same process. We’re looking at that visitor activity, and we’re looking for email opens, and we can we can find, uh, that, uh, that information. It’s naturally, we want to go Pardot for or prospect first and then look at the people, but it’s just the data is not structured that way. Um, so we have to kind of come at it from backwards and and look through all the activities and then find the prospect that we want.

Speaker 0: And I think we have another question from Nancy. In Pardot, can we prevent duplicates?

Speaker 1: In Pardot itself, under allow multiple prospects, same email address, um, as long as we follow the rules of of record creation, stuff like that, it it will try. Um, but with the the allow prospects multiple, um, same email address, we can’t natively. But we could use a tool, uh, API tool to basically export all the email addresses, define our matching, and to give us a list of records that we want to merge. Um, we don’t have the ability to merge by API, but we could do a delete. So you can say, like, this record, that record, and you can go through that spreadsheet, load that back in, maybe run it to another Jupyter notebook that you load that CSV after you’ve, um, you’ve done that decision making and then, um, have the the code go through and and and do the deletions, uh, if you’re just going to do a straight deletion.

Speaker 0: I think we have, like, another question. Can I pull a list of all the segmentation lists, uh, with all the prospects of each list? We want to see how much overlap we have with our prospects across our departments.

Speaker 1: Yeah. So if we look at what are the things we can do with the API, we can do lists by API, so we can ask actually pull that that this membership for both dynamic and static lists. They’ll give us a list of prospect IDs. There’s a little bit more work, um, because we’re probably not interested in prospect ID. We’re probably interested in in email address, so we didn’t have to use the API to look up every prospect ID to get that, um, email address. But, luckily, the the code doesn’t care, um, and and the computer doesn’t care to do that extra that extra work.

Speaker 0: Awesome. Thank you for answering all that. Uh, I think that’s kind of the time we have right now. So make sure to head over to agenda to check out the full list of sessions we have today. Thank you, Mike, for presenting. Passover your day. K. Bye, everyone.