Posts Tagged ‘campaign member reports’

How to find your top donors across multiple campaigns

Tuesday, March 4th, 2014

Building Salesforce reports can sometimes be very challenging. You start building a report and you realize you cannot access the objects you want, or use the appropriate lookups, or apply the relevant filter criteria. Apsona Multi-step Reporting (MSR) addresses some of these problems. This reporting app lets you build reports spanning any number of objects, native or custom.  It lets you traverse dependencies in either direction (parent-to-child or child-to-parent), and can handle multiple dependencies between the same pairs of objects. For example, you can create a report spanning the Campaign (parent) – Opportunity (child) – Opportunity Contact Role and back up the chain to Contact (parent). Another plus with Apsona MSR is the ability to run reports without having to create report types.

A few key concepts to bear in mind:

  • Reports are built in steps.
  • Each step retrieves data from one object.
  • Each step carries its own filter conditions, independent of the other steps, and these conditions can be cross-object.
  • Steps are linked to previous steps via lookup fields or picklist values.

We will use an example to illustrate these ideas. Frequently, non-profit organizations need to identify their top donors and learn their interests, so that the organization can target the right people in future campaigns. In that context, they often need to identify donors who donated in multiple campaigns, or those who have donated to two or more specific campaigns.

As a simplified example, suppose we are conducting a dinner gala, to which we would like to invite our top donors from the Kids Literacy and the School supplies Campaigns. So we want a list of those donors who have contributed in both the campaigns. Intuition suggests that we can produce this report by first creating two lists of donors, one for each campaign, and then identifying the common ones. And this is precisely how you would build a multi-step report for this purpose. The Opportunity Contact Role (OCR) object is the basis of this report, since an entry in that object tells us the existence of a donation by a donor. The Donation object has a lookup to Campaign, and therefore accessible from the OCR object. So here are the three steps of the multi-step report.

  1. Find the donors for Kids Literacy. From the OCR object, get the Donor/Contact look up field. From the Donor object, get the donor’s Full Name. In the filter terms, select the Campaign field from the Opportunity object and select Kids Literacy as the value. Save the step.
  2. Find the donors for School Supplies. Clone Step 1 and just change the filter term value of the Campaign to School supplies. Save the step.
  3. Find the donors in both campaigns. From the Contact object, select the Contact ID field (required for linking to the previous steps), Full name and any other Contact fields you require for the report. Click the linkages tab. You will now see that you have two linkage options, one from each step. Notice that this is where we use the lookup field values retrieved by the previous two steps. You will need to select both the linkages, to enforce the condition that you want contacts from both campaigns. Save and run the report, and you have your desired results.

Notice that this report retrieves data twice from the same object (Opportunity Contact Role), with different filter criteria. This capability is needed frequently in real-world situations. In addition, Multi-step Report data can be visualized in charts, groups, and pivoted matrices.

We would like to thank Idealist Consulting for the opportunity to contribute this article to their blog.

Blog Category:

Searching across a chain of objects in Salesforce

Saturday, December 28th, 2013

Salesforce users often need to search for data records in one object that depend on conditions in a related object. Frequently, the dependency carries over to multiple related objects, or to chains of such relationships. For example, suppose you need to find all the Campaigns that have targeted the contacts from your Partner accounts. To retrieve those Campaign records, your primary search condition is imposed on the Account object (looking for Partner accounts). Having found those accounts, you must find the Contacts in those accounts, and then the Campaign Member records of those Contacts, and finally the Campaigns to which those member records refer. Thus we have a four-object chain of dependencies that must be traversed to produce the results you need.

Such a query is not very easily constructed in native Salesforce. But Apsona for Salesforce provides the tools you need to solve problems like this. The way Apsona solves the problem is by repeatedly applying the idea of a filter, to produce what is called a nested filter, as follows. A filter is simply a name given to a specific search condition, associated with a specific object. For example, you can create a filter on the Account object, that asks for Account Type matching Partner, and name it Partner Accounts. You can then apply this filter in the Contact object, since the Contact is related to (actually, a child of) the Account object. When doing so, you would retrieve the Contacts of your Partner accounts, and you would use the Partner Accounts filter as a nested filter. You have thus carried a search condition on the Account object over to the related Contact object. You can then repeat this step over the entire chain of objects, thus producing the result you want. See the diagram below, showing the relationships among these objects.

Here is the series of steps to produce your results:

  1. Start with the “primary” object that drives the filter condition. In this case, it is the Account object, since we start with the condition derived from partner accounts. Create a filter on the Account object, identifying your Partner Accounts. You can do this either in the console view (list-and-detail) or in the tabular views in Apsona. Save it with the name “Partner Accounts.”
  2. Create a filter on the Contact object. In doing so, if you open the Account panel of the filter builder, and select the record id field of the Account object, you will see the “in filter” option available. Select that option, and then select the “Partner Accounts” filter. Save this filter with the name “Contacts from Partner Accounts.”
  3. Create a filter on the Campaign Member object, using the Contact filter created in step 2, in the same way.
  4. Finally, create a filter on the Campaign object using the Campaign member filter from step 3.

What we did here was make a filter on the primary object, namely Account, and applied the filter to the related object namely Contact. You can repeat this process as many times as you want, thus carrying filter conditions across a chain of relationships. We use the term nested filter to refer to a filter that uses another filter within it, in this manner.

Searches, filters and reports in Apsona work across the board whether it is retrieve data for campaign management, sales management or case management. Apsona for Salesforce can be downloaded from our AppExchange listing for a 30 day free trial, and works with all editions of Salesforce.