Bulk merging duplicates in Marketo Engage - Part 2 - Data cleanup in Excel

For the users who prefer no-code or low-code solutions, using Excel directly with its built-in features can be more effective. Now I will walk you through the process of step-by-step to accomplish the bulk merging tasks from Marketo Engage from Excel. First you need to find your possible duplicates. Smart List. You can do this by going to your database tab. Inside of the database, find your possible workspaces. In our case, go ahead and select the default workspace. Underneath the System Smart Lists tab, go ahead and select your possible duplicates list. Once selected, go to List Actions and select Export List. You have the option of choosing between the visible columns, meaning the columns that are going to be available to you. When you first select the People tab, it will give you a select option of fields available, or you can select all columns. All columns will give you all of the fields associated to a lead, and it will be very confusing if you are not using all of them, so I suggest either using the visible columns if those are the fields that you are looking to merge, or to provide a curated list before you select the download option. I have selected Visible Columns Only for the sake of this example, and selected comma-separated values. Once downloaded, go ahead and navigate to that Excel file. Then we need to sort. The way that I have sorted this, if you would like to follow along, is to first sort by email address, and then sort by updated date descending. Once you have this list, we need to add multiple columns. The first column we are going to add is the Master column. The Master column will denote which record is going to be the main record, the master record, where all duplicates are going to update this single record, and that will be the one that survives after the merge process. The additional columns that you see that I have added here are the fields that I would possibly want to merge with. For this example, I am only going to choose Last Name, but I have named it Merge Last Name, or Merge First Name, and so on, and you can do this for each of the fields that you wish to merge together during this process. This is when we start doing some of the Excel magic. You can do this however you like. You don’t even have to use Excel to do everything that I’m showing you. You could use an SQL database if that is what you are comfortable with. There are so many different options here, but like I said before, we are going to use Excel. The formula is going to identify the pre-sorted first instance of a possible duplicate. That will become your master. Here is an example of the formula, assuming that email is in column A, and the first row of data is row 2. As you can see, the formula is applied in our first row of data, and now we will drag that to populate the remainder of our downloaded information. In the next step, we will review the merge fields. For columns where data might be missing or conflicting, we will use this formula with LIKE IF or IF ERROR to consolidate our information. In this example, I used the IF formula. The formula is explained as follows. IF lastName does not equal blank, fill the field with the lastName value. Only if the next row is not a master value. It is checking that the next row is still a duplicate, but if it is master, then start over. Don’t do anything. Same process. Once we have the formula for the first row of data, go ahead and bring that all the way down for the rest of our data. You can continue to do this with all the remaining fields. You can do it with firstName, jobTitle, company. But for this example, just doing the lastName will work for us. Now that we have the data sorted, specified as master, and all the fields that we wish to merge together are merged, we now need to filter our excel sheet so that only the masters are remaining, removing the duplicates. Do this by filtering out the master column so that only master is shown. Now that we have our filtered data of only master files with all of the merged fields, we need to copy our data and paste it into a new sheet so that there are no hidden fields.