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.
1. Create formula field in both Account and Activity
The purpose of both this field is to count unique record.
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
- 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.