Paul W. Carlson, CPA (00:00):
Hi, this is Paul Carlson, CPA with Law Firm Velocity. And today, we're going to look at how to track referral activity by referral partner, and then how to calculate a cumulative referral partner score. In this example spreadsheet, we have referral partner A has given us two referrals so far within this report. And so we can see the deal names, we can see the details of the deals brought in from the referral partner. We go down to referral partner B, here we can see that referral partner B has given us one referral and we have given them three out, so their cumulative referral score is a negative two. And then we can also see that referral partner C has not provided the firm with any referrals inbound, and we've only given them one referral outbound. This is sample data. This is easy to see when there's only a handful of transactions over the course of a year and there's hundreds of lines that this report becomes very valuable.
(01:13):
The context of the report is that as the firm is establishing and developing referral partners, that we can keep score on how our referrals are producing. We want to keep track of who we're getting referrals from. In this case, referral partner A is our star referral partner. We want to make sure that someone has spoken with that referral partner and that they understand we appreciate the referrals and we're doing everything we can to stay at the top of their list of people to contact when they need help. For referral partner B, we do not seem to be getting a return on the referrals that we're giving them. Maybe we need to talk to referral partner B and understand why we're not getting referrals from them, or maybe we need to talk to them about the value we're providing for them and how they can help us within our own firm.
(02:10):
With the example in the context, let's take a look at how we actually came up with this report. This seems to be something that should be in every CRM but none of them have this. And we'll just talk through how we made this come out of Pipedrive. Pipedrive is a visual CRM that as we add deals, that we can move them graphically between stages as deals go through the process. How we've set up the referral tracking ... Picked the wrong example. This was a referral. We added two custom fields into the tool. Within the tool, we can specify the referral partner that the referral came from and we can specify who the referral went to. And a cool thing to note here is this is actually connected to contacts within the database. What that does is we type in their name and we select from a contact that's already in there. And that helps us make sure that the referral partner name is consistent throughout the reports, that when we run the Excel report, if this is spelled differently in some places the reports won't work.
(03:33):
This is how we're tracking the referral activity. Then we go over to the list view. Here's a list view of the referrals that we've received, this is just a detailed perspective of the graphical Pipedrive. To get this over to Excel, first we filter it for the dates we want and then we export the filter results to Excel. Okay. And here we have the exported Excel sheet. We need to do some massaging to the data because the key issue we have is we have referrals in data in this column and referred out in this column and we need to group these together into a single column for us to run the subtotals. We're going to insert three columns. The first is going to be referral partner, the second column is going to be referral in or out, and the third is going to be score. Let's make these wider.
(04:46):
Okay. The next step is to sort by deal referral received from. Here, we have all the referrals here. We want to copy this set here, these were referrals in. Since the day we received referral, we give these a score of one. Next, we need to organize the referral data out. Here, we want to copy all of the referred out rows, add them here. And so we copy referrals out to this column, these were referrals out. And these all get a score of negative one. We can copy that down below. Yeah, okay. All right, so now we have these three columns. These columns have the detail that we need.
(06:13):
We just need to do a couple more sorts. We do data sort, we want to do sort by referral partner, and then we're going to do referral in or out. And then we're going to do by deal title. You could do it by date if you like. Click okay. Now we have all of the referral activity by partner. Now we can use this magical subtotal tool that each change in referral partner we want to use this function, actually we want to use sum, and we want to sum that score column and click okay. And here we have the report we started with. We can see that referral partner A has given us net two referrals. Partner B has given us negative two referrals. And with that, that's how we can use Pipedrive and a little bit of Excel to track referral activity and scoring by referral partner. If you need any help with accounting with your law firm, please give us a shout at Law Firm Velocity. Thank you, Bye-Bye.
.jpg)
