How to Create a Last Won or Last Lost Opportunity Contact Report

As a Salesforce system administrator, you’ve probably heard of these common requests before. For example, has anyone in sales, preparing for a calling campaign at the beginning of the year to drive business, walked over and asked you, “what accounts did we win opportunities with last year and who are their contacts?” Or, what about when marketing wants to nurture contacts from previously dead or lost opportunities, and asks, “what accounts did we lose opportunities with last year and who are their contacts?” We know, we get those requests all the time, too.

Unfortunately, unless your sales team is consistently adding Opportunity Contact Roles to each Opportunity record, which is always best practice, this can be a very difficult report to build. You may have tried to build a joined report, or have two reports and try to match the contact to the report manually.

Don’t fret. With some quick admin tricks, you can build Reports that you can use every year. All it takes is two roll-up summary fields!

Estimated Setup Time: 20-30 mins

Difficulty: Easy-Medium

1. Create Roll-Up Summary fields

1. From Setup, go to Accounts/ Fields. From there, click the “New” button, click on the “Roll-Up Summary” radio button and hit “Next”:

2. Enter the Field Label, “Last Opportunity Won,” and click Next:

3. Under the Summarized Object, select “Opportunities.” Then, select “MAX” under the Select Roll-Up Type section and “Close Date” as the Field to Aggregate:

This will display the most recent Opportunity close date for a given account. Now, we need to narrow this down to only won or lost Opportunities.

4. Go to the Field Criteria section and check the radio button “Only records meeting certain criteria should be included in the calculation.” For won Opportunities, select the “Won” field and have it equal to “True”: 

You want to avoid using the Status field for this. If your organization ever overhauls their sales process, you want to have one less place that needs to be updated. Note: this is not possible for Closed Lost opportunities.

5. Click the “Next” button to determine the visibility of the field, and “Next” again to add it to a page layout. Unless requested, I view this as a report only field and suggest you to keep it off the page layout for now. Click “Save & New” and begin the process building a Last Lost Opportunity Field.

6. Follow the same steps as above, making sure to enter in “Last Lost Opportunity Date” in the Field Label.

When you get to entering the Field Criteria this time find the Stage field and have it equal “Closed Lost.”

Click through the “Next” buttons and save the field. Now it’s time to finally build our Report!

2. Create New Report

Click on the Reports tab and hit the “New Report” button. Select the standard “Account” report type, unless you’re using a Custom Report Type. If you are using a Custom Report Type, be sure to add your two new fields to that Custom Report Type.

From there, you can pull in the Fields that you just created and any contact information your sales and marketing team may need. We like to clear all fields from the Report Type, and only add the columns that are most relevant.

You can add Report Filters to only show accounts that have not had an opportunity, have not had one in over a year, etc. 

Again, remember to remove any irrelevant fields or columns from your Report. Few things make it harder for sales to actively use a Report than too much unrelated information.

Now, send the Report over to Sales or Marketing, or better yet, schedule the Report to email to select Users, if you get this question every time at year end or quarter end. Consider your mission accomplished!

Feel free to leave a comment below if you have questions about building this report in your own Org. If you’re still struggling to create this or any other Salesforce Reports, reach out to one of the experts on our team!