I am looking at a way i can get a report showing an account and have a related leads.

Lets assume that i would make a domain match of website of account and to determine the related leads.

I understand that there is no relationship between accounts and leads.I am looking at how we can relate the account and leads.

Option 1 : Create a junction object linking both lead and Account But i cant seem to get a common fields on report. i can get only the ownerid as the common fields on the report.

Any thoughts on how we can get this report and any other approach i can use to get this report

  • 1
    There is a standard report type called 'Report Type: Leads with converted lead information' would that work ?
    – brovasi
    Jun 10, 2015 at 11:10
  • Probably no. i am looking at unconverted leads. for this reporttype you probably would need to have the converted accountid field in lead to be filled in
    – Prady
    Jun 10, 2015 at 12:40
  • You might want to look at it this way. What criteria would you look at to determine whether the account already existed when converting the lead? Account Name perhaps, address and what else? Those would be the things I'd be looking at to create matches and not just using one common field as a criteria.
    – crmprogdev
    Jun 10, 2015 at 12:47
  • @crmprogdev you are right on the fields, i just gave an example of single field, but its going to be a combination of fields.
    – Prady
    Jun 10, 2015 at 13:32
  • Yes, it seems to me that it's almost as though you'll need to create the equivalent of a conversion criteria set for matching accounts. If they "match", you can add them to the report; equating them to an existing account. If they aren't "close enough", or satisfy enough of your criteria, then you either omit them or show them as a new account. Whether you need to use a junction object or just do it within your report would seem to be entirely up to you. Filters would seem to be sufficient to do the job.
    – crmprogdev
    Jun 10, 2015 at 13:43

2 Answers 2


Couldn't you create a lookup field on the leads object to lookup to the account object to create a relationship. Then you could create a summary report to group by that account lookup field on the lead object. That way an unconverted lead could be related to an account.

If you want to quickly match to update the values on existing records after adding this lookup field, you could create an update trigger that works like VLookup. You could choose a unique identifier on both records like Tax Id or another unique value and if it matches the value on the account record it returns the name value. Then you could use dataloader to initiate the trigger on those existing records.

  • thats a valid point, i am just thinking what if there is a multiple match of accounts
    – Prady
    Jun 10, 2015 at 14:00
  • Could create multiple lookups on fields, then somehow summarize or filter them. I'd think you'd then need to compare and rank them in order of importance. An exact match in either of one or two fields might indicate a 100% match even if the remaining two or three didn't completely match. To do that, it would seem you'd still need a junction object to perform the logic since it's a many to many relationship.
    – crmprogdev
    Jun 10, 2015 at 14:44

Here's an alternative that may strike you as coming out of left field (sorry - American baseball metaphor).

Use Excel Power Query (free) with Salesforce add-in (free):

  1. You develop two reports - one on Accounts, one on Leads
  2. Your Excel Power Query workbook downloads each report to a separate worksheet as Excel tables within the workbook
  3. Your Power Query configuration then creates a third worksheet that joins the two tables on domain name
  4. You then hide the first two worksheets

Now, there are some caveats with PowerQuery:

  1. It is a Windows-only solution
  2. Works best on 64-bit Windows, Excel 2013
  3. There is a limit on the number of rows that a given report can send to the Excel workbook as it uses the Salesforce Analytics API (2000). There are workarounds
  4. The report is launched from Excel, not from SFDC so the user experience is different. You can save Power Query workbooks to the Power BI cloud

The coolest thing about PowerQuery is how you can do all sorts of post extraction massaging of data (including Joins, column header renaming, missing data cleanup, additional calculations, and so much more.

I'm not saying this is your answer as there is a learning curve with Power Query and it is definitely an Excel-centric option.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .