Transcription
All right, in this video, we are going to stack our list.
What we've done up until this point is take all the various lists that you guys sent over and compiled them into one document. We also went and pulled just barely under 5,000 tax delinquent leads and added those. So this list is about 72,000 records. I think, yeah, 72,000 records.
What we're going to do now is see which one of these properties fall on multiple lists. We're going to walk through this process. Now that we have it all uploaded in here, we have it in just a basic template. We have all the different list types over in one column, and that's what we need to get it set up.
So now we're going to go to Extensions, then Power Tools, and click Start. If you don't have Power Tools, you can get it; I think it's about 25 bucks a year. Very, very helpful.
All right, so we pull that up and go to Merge and Combine, and then we're going to combine duplicate rows. All right, so it goes through and it says, "Hey, which part of the sheet do you want us to pay attention to?" We want it to do the whole sheet, so you can see here everything's highlighted. Go next.
Now it's going to ask us which column we want to pay attention to in order to look for duplicate records. For us, it's going to be the property address. We also want to do city and state as well. Don't do zip because a lot of times they're formatted differently, and if one of them is formatted one way and another is formatted the other, it won't pick it up. So just do those three. Click next.
Then it says, "Hey, when we find duplicate values, where do you want us to merge? What values do you want us to merge?" We're going to go down to List Type and select Action. It's going to be Merge Values, and then you can say what it's separated by—in this case, a comma, which is perfect.
So that's it. We go and press Finish. It's going to go through and do its thing. With a list this size, it's probably going to take a few minutes. It's going pretty quick. They did some kind of update to this in the last probably six months, and it works a lot better than it used to. In the past, if you had a list this size, it would time out. So it's nice to see that it's working.
All right, I'm going to pause this while it finishes up. Okay, there it goes. All right, so it found 7,100 duplicate rows and combined them down into 3,400 duplicate rows.
Let's go ahead and close this out. Now you can scroll down through here, and under List Type, we can see all of the different properties that have at least two different list types, which are the ones that are stacked.
So that's the first piece. The second piece is that if somebody owns 10 different properties, we don't want to mail every single one of their properties that they own. So we want to duplicate based on the mailing address column.
What I'm going to do real quick is just duplicate this sheet. The reason why I'm doing that is in case anything goes wrong, we can always just come back to it the way it is. All right, so there's our duplicate. We'll still keep working in this first sheet; we'll just save that second sheet as a backup.
Now, in order to be able to do the deduplicating of the mailing addresses, we're going to go back, start up Power Tools the same way. This is the main screen. Click on DD and Compare, and then we're going to click on Remove Duplicate Rows.
Okay, so again, it identifies the full sheet, which is good. Next, now it's going to ask us what we want to consider a duplicate. It can either do, "Hey, if it finds any duplicates, it removes everything." We don't want it to do that. We want it to keep the first time that it finds it but remove the second, third, and fourth instances when that happens. So we'll do that next.
Now it asks us which columns does it want us to pay attention to when it comes to the duplicates. We're going to do Mailing Address, Mailing City, Mailing State. Don't do zip for the same reason we talked about previously.
All right, do next. Then when it finds one of these duplicates, what do you want us to do with it? We're going to say we want it to move it to another sheet. So it's just going to create another sheet down here with all the duplicates. Move to another location, new sheet, and then click Finish.
Now it's going to go through and find all the duplicates. It'll find the first one, keep it in the list, and the second, third, fourth, if there are any, they'll take those and put them into another sheet. So we'll give this a couple of minutes. This part may take a little bit. I'm going to pause the video and then we'll come back whenever it finishes up.
All right, it just finished going through that process. The whole thing probably took about a minute, minute and a half. You can see here that it found 2,986 duplicate rows. So all of these are duplicate mailing addresses. It still has the original in here.
Now, one thing that we do have to pay attention to is this. Let's see, you can see inside here it removes some of ours that were the stack. So I don't want it to remove any of the stacked leads.
Here's what we're going to do: go back up here to the top, highlight everything, click on the filter right here. I'm going to go here to List Type, clear it all, and I'm just going to select the ones that have some type of stack on them. If it doesn't have a stack, I'm skipping it. But all of these are stacked. Some really good stacks in here.
Right, that's not a real stack. That's not a real stack. Not sure why that happened. All right, so that's all of them.
Now we just have stacked leads, and you can see here that there are 104 of them down here at the bottom. So what I want to do here is just grab these 104 leads, and I'm just going to simply copy them, go back to the original sheet.
So probably the absolute correct way to do this is to go up to the top, insert 100 extra rows, and paste them in there. Then do the deduplicate based on the mailing address again. There's only 104 of these, so I'm not super concerned with accidentally mailing 100 of them twice.
So let's go back over here, and what we're going to do is just simply paste in all those results here. Now we have all of our stacked leads inside of this one main sheet.
What I would do from here is go in and, kind of like we did just a minute ago, set up a filter on everything like that. Go ahead and select all of the ones that are stacked, create a new sheet down at the bottom, and just make a stacked list only.
So we would have a stacked list, and then we would have the leftovers in this sheet. Then we can go through and prioritize them based on which lead types we want to target in regards to whatever our budget is once we figure out how many stacked leads that we have.
So anyway, that's how you take all of your leads, get them into one file, and then run the stack through the stack software to figure out which ones are on multiple lists. If you have any questions, just let me know. Thanks, bye.