Salesforce: Accounts with Activities

In many organizations, activity (contain of Task and Event) is widely used to monitor relationship and communication with customer and prospect. Instead of all happened through email, ideally it need to be captured in Salesforce as well.

Customer and prospect in Salesforce is re-present by Account object. Activity logged in Contact and Opportunity will be roll-up to the Account, event the activity is not directly linked to Account.

Use case: management would like to monitor how is the activity logged in Salesforce by Account and by Sales Rep in high-level.

Solution: use Salesforce report to produce real-time data and schedule it for future run.

Step-by-step:

1. Create formula field in both Account and Activity

The purpose of both this field is to count unique record.

For Account: Setup | Customize | Accounts | Fields
Name the field as Account Count

For Activity: Setup | Customize | Activities | Activity Custom Fields
Name the field as Activity Count. Use the same formula as in Account Count above.


2. Create custom report type

Navigate to Setup | Create | Report Types, create Accounts with & without Activities report type with following relationship.

3. Create matrix report

Add following Custom Summary Formula in the report:

# of Accounts = Account.Account_Count__c:SUM
# of Activities = Activity.Activity_Count__c:SUM
% of Accounts with Activities = Account.Account_Count__c:SUM - (RowCount - Activity.Activity_Count__c:SUM)) / Account.Account_Count__c:SUM

Explanation:

• The first two formula is optional, maybe just on report creation to help to debug if % is correct
• Account.Account_Count__c:SUM --> this will return unique number of Account
• Activity.Activity_Count__c --> this will return unique number of Activity, although this should be always unique
• RowCount --> this is total row return in report query, it will contain number of Account and if the Account have multiple activities, it will return the duplicate value, example: there are 10 accounts, 5 activities, but 3 of them linked to the same account, so row count will become 12 because 3 activities for the same Account.

4. Report result

The same solution can be used for other objects, such as how many percentage of Opportunities with Activities or Contact with Activities.

Reference: How to Calculate % of Accounts with Activities as % of Total Accounts