Report that combines lead records with *just* the associated opportunity record

[FrankE]

We're doing some advanced funnel analysis. Some reporting is obvious and easy: % of leads converted; average time from lead creation to conversion; % of opportunities that go to "closed won"; average time to close; etc.

Reporting on the progression of a lead to opportunity over time is eluding us. We don't need this to be highly granular; that is, we don't want lead stage history (open to contacted to qualified, etc.) or even opportunity history (evaluating, negotiation, closed, etc.) What we want to do is:

- Lead create date
- Lead conversion date
- Opportunity close

The "lead report with opportunity information" category seems like it should work, but doesn't. It shows *every* lead that was converted to a contact, regardless of whether an opportunity was also created. And multiple contacts associated with that opportunity all inherit the opportunity information, so you wind up with a wildly large number of "opportunities" (which are really contacts).

We just want a straight line of sight from the creation of the lead, its conversion to an opportunity, and opportunity close -- and *only* those leads/opportunities.

It feels like this must be the type of question that's routinely asked, but I haven't found a forum post on it. All advice appreciated!

[jkucera]

Hi Frank,

The straightforward way to do this is to use the "Leads with converted lead information" as it has all 3 dates on it. 

If you want more contact or account fields that you don't see on the report, you can create a custom report type, with Leads as the base object.  Then save it, and under "Fields available for Reports", click Edit Layout and then the link "Add Fields related via lookup".  From here you can get to any field for Account, Contact, and Oppty of that converted lead, as well as lookups from those objects to other objects such as Primary Campaign Source.

[FrankE]

Thanks John.

As I'd mentioned in the post, I'd tried the "Leads with converted lead information" report type since it does indeed have all 3 dates. Trouble is, it still returns *every* lead, mixed in with the smaller percentage of leads that were actually converted to an opportunity.

But you got me to thinking about how I could isolate just the subset of leads converted to an opportunity, and of course adding a filter to ensure that the "Opportunity Created Date" has a value did the trick.

Thanks again.

[jkucera]

Glad to hear my bad answer helped you find a good one