1

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 ? – Cloud Ninja Jun 10 '15 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 '15 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 '15 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 '15 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 '15 at 13:43
1

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 '15 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 '15 at 14:44
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.