Fix Salesforce Last Activity Date for Shared Activities

Understanding the "Last Activity Date" Limitation with Shared Activities

The LastActivityDate field on the Contact object is a critical metric for sales, marketing, and operations teams, driving re-engagement campaigns, lead scoring, and pipeline reviews. However, when the "Allow Users to Relate Multiple Contacts to Tasks and Events" setting (commonly known as Shared Activities) is enabled in your Salesforce org, this field becomes unreliable. It only reflects activity for the primary contact on a Task or Event, ignoring any secondary contacts.

Why Shared Activities Breaks Last Activity Date

Shared Activities allows a single Task or Event record to be associated with multiple Contacts. This association is managed through junction objects: TaskRelation for Tasks and EventRelation for Events. In these relationships, one contact is designated as the primary via the WhoId field, while others are stored as secondary relations.

The core issue is that Salesforce's standard LastActivityDate field is only updated when the activity is linked to the primary contact. Secondary contacts associated with the same Task or Event will not have their LastActivityDate updated, leading to incomplete and inaccurate reporting.

This limitation is documented by Salesforce and can significantly impact reporting accuracy:

• Re-engagement Campaigns: Marketing might incorrectly exclude contacts who were recently involved in an activity as a secondary contact.
• Sales Follow-up: Reps might overlook contacts with outdated LastActivityDate values, missing opportunities for timely follow-up.
• Lead Scoring: Contacts may receive lower scores due to an inaccurate perception of their engagement recency.
• Account Activity Tracking: An account's overall engagement level can be misreported if recent activities involved secondary contacts.

Implementing a Robust Solution for Activity Dates

To overcome this limitation, we can implement a solution that aggregates activity dates from TaskRelation and EventRelation directly, ensuring all associated contacts are accounted for. This involves creating custom rollup fields and a consolidated formula field.

Step 1: Create Custom Rollup Date Fields

We need to create two custom Date fields on the Contact object to store the latest Task and Event dates respectively.

1. Last_Task_Date__c: This field will store the most recent activity date from Tasks associated with the Contact.
2. Last_Event_Date__c: This field will store the most recent activity date from Events associated with the Contact.

Note: For this example, we'll assume the use of a tool like Rollup Helper from the AppExchange for creating rollups. The principles can be applied using Apex triggers or other automation tools if preferred.

Rollup Configuration for Last_Task_Date__c:

• Rollup Object (Parent): Contact
• Source Object (Child): TaskRelation
• Rollup Type: MAX
• Field to Aggregate: Task:ActivityDate
• Filter Criteria: IsWhat = false AND Task:Status = 'Completed' (Ensures we only consider tasks related to contacts and that are completed.)
• Result Field: Last_Task_Date__c

Rollup Configuration for Last_Event_Date__c:

• Rollup Object (Parent): Contact
• Source Object (Child): EventRelation
• Rollup Type: MAX
• Field to Aggregate: Event:ActivityDate (or Event:EndDateTime depending on your definition of event activity)
• Filter Criteria: IsWhat = false AND Event:IsPast = true (Ensures we only consider events related to contacts and that have already occurred.)
• Result Field: Last_Event_Date__c

Important Considerations for Rollups:

• Recalculation: Most rollup tools recalculate in real-time or provide options for scheduled recalculations. Perform a manual recalculation immediately after setup to backfill existing records.
• Testing: Always test these configurations in a sandbox environment before deploying to production.
• Permissions: Ensure the custom fields (Last_Task_Date__c, Last_Event_Date__c) are visible to the relevant profiles and permission sets.

Step 2: Consolidate with a Formula Field

Once you have the Last_Task_Date__c and Last_Event_Date__c fields populated, create a consolidated formula field to determine the most recent activity date across both tasks and events.

1. Create a new Formula field of type Date on the Contact object named Last_Activity_Date_All__c.

2. Use the following formula:

   IF(
       AND(NOT(ISBLANK(Last_Task_Date__c)), NOT(ISBLANK(Last_Event_Date__c))),
       IF(Last_Task_Date__c >= Last_Event_Date__c,
           Last_Task_Date__c,
           Last_Event_Date__c
       ),
       IF(NOT(ISBLANK(Last_Task_Date__c)),
           Last_Task_Date__c,
           IF(NOT(ISBLANK(Last_Event_Date__c)),
               Last_Event_Date__c,
               NULL
           )
       )
   )
   

This formula intelligently returns:

• The more recent date if both Last_Task_Date__c and Last_Event_Date__c are populated.
• The populated date if only one of them has a value.
• NULL if neither field has a date, preventing the display of a zero date for contacts with no recorded activity.

Step 3: Reporting with the New Consolidated Field

Replace the standard LastActivityDate field with Last_Activity_Date_All__c in your reports, list views, and dashboards. This will provide an accurate representation of contact engagement.

Example Reports:

• Contacts Without Recent Activity: Filter a Contact report where Last_Activity_Date_All__c is older than 30, 60, or 90 days.
• Activity Recency by Owner: Group a Contact report by OwnerId and add Last_Activity_Date_All__c with a MAX aggregation.
• Account Engagement Overview: Filter contacts by AccountId and sort by Last_Activity_Date_All__c in descending order.
• Contacts Never Contacted: Filter where Last_Activity_Date_All__c is blank.

Key Takeaways

• Salesforce's standard LastActivityDate field is unreliable when Shared Activities is enabled, as it only updates for the primary contact on Tasks and Events.
• This can lead to inaccurate reporting for re-engagement campaigns, sales follow-up, and lead scoring.
• The recommended solution involves creating custom rollup fields (Last_Task_Date__c, Last_Event_Date__c) on the Contact object by aggregating data from TaskRelation and EventRelation.
• A consolidated formula field (Last_Activity_Date_All__c) combines these rollup fields to provide a single, accurate date of the last activity for any associated contact.
• Replace the standard LastActivityDate with Last_Activity_Date_All__c in all reporting and automation that relies on accurate contact engagement recency.