This forum post will run through how to create a report which shows the last time contact was made with a Sales Lead, whether it was via an activity or an email. The screenshot below shows an example of this:

Why should I build this report?

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

How do I make this change?

The steps below run through how to create this report.

Step 1: Create a report based on Leads

Navigate to Start > New > Report > Create a new blank report > Sales Leads

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 'Lead' columns useful:

• Id
• Lead person's name
• Organisation
• Assigned to name
• Status

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

• Id
• Sent
• Sender
• Subject
• Context (You only need to drill through 'Emails >' to get to this one

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

• Id
• Subject
• 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 leads, 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 :

• Leads 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 leads which may have emails but no Activities. - Activities > Record State > is not > Open
• Emails which have been sent to the Lead (rather than received from) - Emails > Context > is not > From

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

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

1. Click Add Summary View
2. Add Grouping Column to group by 'Lead Id'
3. Add Value Columns for:
   • 'Lead person's name'
   • 'Organisation'
   • 'Assigned to name'
   • 'Status'
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 Lead Completed Date and Sent, to have a report which lists down all Open Leads 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.