Here are a few examples of real-world problems where Apsona’s filtering and reporting features come in handy. As far as we know, there are no good solutions to these problems in native Salesforce. (If you happen to know otherwise, we would love to hear, please let us know!)
Please note that Apsona for Salesforce and Apsona Multi-step Reports can handle arbitrary custom objects and fields. But the examples in this list are limited to the objects available natively in Salesforce, to make them easy to understand. If you have specific questions, please contact us.
Campaign reach: The company in question runs e-mail campaigns, and wishes to ensure that their messages reached at least one contact in each target account. If, for a particular account, we did not have email addresses for any of the contacts of that account, we obviously would not reach that account. So they want to find every account such that none of the contacts of that account have email addresses, so that they can be singled out for special attention.
Solution: This can be done with a simple quantified filter, where we create an Account filter in which Contact.Email is null for all records.
Contacts with opportunities from specific campaigns. Find the contacts who had roles in opportunities in last Fall’s campaigns, but had no roles in any opportunities from last Spring’s campaigns.
Solution: This can be solved in one of two ways, either using nested filters or using multi-step reports. The nested filter solution can be used when you want to perform additional operations, e.g., add all the resulting contacts into another campaign, or mass-update them. But the multi-step report approach is perhaps easier to understand.
- With nested filters:
- Create two filters on the OpportunityContactRole object, one showing records for last Fall’s campaigns and the other for last Spring’s campaigns.
- In the Contacts tab, apply these two filters to the OpportunityContactRole object, with two terms: One requiring that the record id for the OpportunityContactRole is in the filter for last Fall’s campaigns for at least one record, and the other that the record id is not in the filter for last Spring’s campaigns for all records.
- With multi-step reports: Create a report with three steps:
- Retrieve the OpportunityContactRole records for opportunities from last Fall’s campaigns.
- Retrieve the OpportunityContactRole records for opportunities from last Spring’s campaigns.
- Retrieve the Contact records whose IDs match those of step 1 above and not step 2 above.
Contacts who are from NY or NJ, with least $5,000 in total opportunity value in 2013. Find the contacts who had roles in opportunities that closed in 2013, each of whom provided a total opportunity value of at least $5,000.
Solution: Variants of this problem arise quite frequently, when you would want to send your contacts thank-you letters or solicitations, and you wish to target just those who are most likely to respond. To obtain such a list, create a report on the Opportunity Contact Role object, retrieving the necessary Contact fields from the Contact object, and the sum of the Opportunity Amount field from the Opportunity object. Set up a filter that restricts to Contact records whose Home Address State is either NY or NJ, the close Opportunity Close Date is after 1/1/2013, and the Opportunity sum is at least $5,000 (which you can set via the metrics sub-panel in the Opportunity panel of the filter builder).If you wish to retrieve fields from other related objects such as the Account object or (in the case of the NPSP) the Household object, you can create a multi-step report along the same lines as outlined above, and add second and third steps for retrieving the related Account or Household data.
Campaign creation. Build a list of contacts who had roles in the opportunities from three specific campaigns, e.g., last year’s Winter, Spring and Fall campaigns. (In the non-profit world, these would be contacts who donated to those campaigns.) Create a new campaign and add all the contacts in the above list to this campaign.
Solution: One way is to create a nested filter which retrieves the specific contacts into the “All Contacts” view or the tabular view, and then use the Tools – Add to Campaign menu to add all the resulting contacts into the campaign. A second way would be to create a report on the OpportunityContactRole object to retrieve the necessary contacts, export them, and import them back into the CampaignMember object after adjoining the appropriate campaign name.
Opportunities closed by non-owners: Obtain the opportunities which (a) are owned by 4 specific users, and (b) were closed by someone other than the owner.
Solution: Create a report on the OpportunityHistory object. Set up the filter with two terms: Opportunity.Owner is among the four specific users, Status is closed, and LastModifiedBy is not among the four specific users.
Repeat opportunities: Obtain a list of the contacts who played a role in at least one opportunity in each of the three years 2010, 2011 and 2012. (Equivalently, in the non-profit context, find all the contacts who made at least one donation in each of those years.)
Solution: Create a multi-step report with one step for the contacts, and one step for each of the four years. See the screen shot below.
“Mid-range” contacts: (This example is specific to non-profits.) Obtain a list of the contacts who have donated at least $250 in the last five years, but no single donation has exceeded $1,000.
Solution: Create a multi-step report with three steps. The first step retrieves OpportunityContactRole records for contacts and donations, where the sum of the donations is $250 or more. The second step retrieves the OpportunityContactRole records corresponding to donations of $1,000 or more. The last step retrieves OpportunityContactRole records whose IDs are matched by those of the first step but not the second step.
Contact/Opportunity summaries: Create a list of all contacts who have had a role in opportunities worth a total of $5,000 or more. For each such contact, show their highest-value opportunity and its date, as well as a summary of their total opportunities by year, with the list sortable by year.
Contact relationships: In this example, contacts are related to each other via the custom Relationship object. The relationship of interest is solicitor-solicitee, where one contact (a solicitor) may solicit a number of other contacts (solicitees) as attendees to an event. The problem is to build a list of solicitor contacts, and for each solicitor, we want the list of corresponding solicitees. For each one of those solicitees, we need the summary of opportunities (total, largest, and most recent) in which they had a role.
Activity (Task and Event) Reporting: Activity records in Salesforce are also called Tasks or Events and include two fields: “Name” (which relates to Leads or Contacts) and “Related To” (which relates to any standard or custom object for which Activity Tracking is turned on). Apsona creates two surrogate tables to allow these polymorphic fields to be used with reporting and filters.
- Single Step Reports allow you to report on one Activity table (i.e. Contact Tasks or Opportunity Events) as a dedicated link between an object and their activities. You can also use the standard Task or Events object to create Single Step Reports.
- Multi-Step Reports come in handy when you want to link specific Tasks or Events from an object either to that object’s fields, or to information on a related record. This is the best method to extend cross-object reporting with Activity-related fields.
