No, this is not another story about how it is “better to do something the hard way” – puhleeze, we all know that’s a myth. Ever do something the hard way unnecessarily, then discover afterward that there was a much easier way? Of course you have. We all have. This is one of those stories.
“How many of our accounts were created by converting a lead?” This sounded like a simple, straightforward request. So, feeling pretty darn smart because I knew how to get converted lead info – including the account IDs – off to Workbench I went.
Now, I’m not knocking Workbench – it’s one of my favorite admin resources! In a Workbench SOQL query, I can export converted lead IDs, plus the IDs of accounts, contacts, and opportunities that were created upon lead conversion. (And for more Workbench tricks, check out this blog post!)
My Workbench query yielded some great information. But it also required some Excel trickery, because although it provided the ever-useful ConvertedAccountId, some of those IDs appeared multiple times in the list – it will not differentiate between a lead converted to a new account vs. a lead converted into an existing account. So I had to go through and remove all duplicate ConvertedAccountId values.
In addition, all I could send to the person who requested this information was a number. I could also have attached the spreadsheet, but what if they wanted this information again in the future? I’d have to go through the same manual process again.
And then, guess what I found… an easy way to get that same info. And when I say “easy” I really mean “so easy that it blew my mind.” Here’s how:
- Create a new report using the Account History report type.
- Add the report filter Field / Event equals Created by lead convert.
- Set a date range if you want a specific time period, or leave the dates blank to show the total number of accounts that were created by converting a lead.
Now I have a report that anyone can run in real time to get that total. How cool is that? Honestly, I’m a little embarrassed that I had no clue that an account history report contained that handy little trick. But better late than never – this is going to be extremely useful!
A couple of bonus things that I learned while playing with these reports:
- You can also use this trick in a Contact History report or an Opportunity Field History report.
- You can use two filters, if you want to compare records created via lead convert vs. records created manually – just use the filter Field / Event equals Created by lead convert, Created., and turn your report into a summary report grouped by Field / Event. (Make sure to add a period after “Created” or it will not return those records.)
And I just wouldn’t be myself if I didn’t give you some disclaimers:
- If field history tracking is not enabled for the object in question, you cannot run a history report.
- The data returned in your report will only go back to when your org began tracking – so if field history tracking has not been utilized since your org was first set up, you may be missing some records (depending on how far you want to go back).