This forum post will show you how you can build a report to show the last time contact was made with a person related to an Opportunity. This will include both emails and Activities.

Why should I build this report?

This report will show you if your Opportunities are or aren't being followed up and therefore which ones need further action.

How do I build this report?

The steps below run through how to build this report.

Step 1: Create a report based on Opportunities

Navigate to Start > New > Report > Create a new blank report > Opportunities

Step 2: Add columns that are relevant to you

On the Details tab add the columns that are the most relevant and contain important information. You may find the following 'Opportunity' columns useful:

• Id (rename to Opportunity Id)
• Opportunity reference
• Name (rename to Opportunity Name)
• Prospective Customer Name
• Assigned to name
• Amount (home currency)
• Stage

Because you don't email an Opportunity, you email the people related to the opportunity, you need to add the 'Email' columns you want to display by drilling through 'Related People > Emails > Email >', which may include:

• Id (rename to Email Id)
• Sent
• Sender
• Recipients
• Subject
• Context (You only need to drill through 'Related People > Emails >' to get to this one)

Add the 'Activity' columns you want to display by drilling through 'Activities >', which may include:

• Id (rename to Activity Id)
• Subject  (rename to Activity Subject)
• Type (rename to Activity Type)
• Completed date

Step 3: Add criteria to restrict your results

You may want to restrict your results further to only show a particular set of Opportunities, for example those which are open, assigned to particular people or not updated in the last 2 weeks. We've restricted the results by adding criteria to only show :

• Opportunities which are in a State of Open. - State >  is > Open
• Activities which are Completed (Record State is closed) - You need to add this as 'is not Open' rather than 'is Closed' otherwise it will exclude all Opportunities which may have emails but no Activities. - Activities > Record State > is not > Open
• Emails which have been sent to a person related to the Opportunity (rather than received from) - Related People > Emails > Context > is not > From

Step 4: Create a summary view to group by Opportunity Id

This will allow you to see the last contact made with your Opportunities. 

1. Click Add Summary View
2. Add Grouping Column to group by 'Opportunity Id'
3. Add Value Columns for:
   • 'Opportunity Name'
   • 'Amount (home currency)'
   • 'Assigned to name'
   • 'Stage'
4. Add Summarised Columns for:
   • Completed Date
   • Sent
     For both of the above, set Calculate to 'Maximum' and un-tick 'Display summary' checkbox. 
5. Add a Calculated Column called 'Last contact date', with the Formula below:

    

    IF(MAX(PARENT('Completed date'))>=MAX( PARENT('Sent')) OR MAX( PARENT('Sent')) IS NULL , MAX( PARENT('Completed date')), MAX( PARENT('Sent')))

    

6. Add another Calculated Column called 'Days since last contact', with the Formula below:

    

   DATEDIFF(NOW(), IF(MAX(PARENT('Completed date'))>=MAX( PARENT('Sent')) OR MAX( PARENT('Sent')) IS NULL , MAX( PARENT('Completed date')), MAX( PARENT('Sent'))))

You can then hide any columns which aren't relevant anymore such as Opportunity Id to have a report which lists down all Open Opportunities and the last Contact date with them, as per the screenshot below. 

Step 5: Share this Report with those that will be using it.

By default, the report will only be shared with the System Admin Users. Use the padlock to share with any other users as appropriate.