Identifying key donors

In this example, we are looking to find donors whose giving history match the following criteria.

  • The donations sum made by the donors/contacts sum should be atleast $10,000 for the last 3 years.
  • Each individual donation to be less than $500.

This multi-step report is built in 3 steps. The first two steps come from the Opportunity Contact Role object, with the same set of fields but with different filter criteria. The third step is from the Contact object.

  1. Step 1 is from the Opportunity Contact Role (OCR), with fields from the Contact and Opportunity objects, and filtered by fields from the Opportunity object. Basic fields required are the Full name (Contacts) and the Opportunity Amount (Opportunities) and the contact lookup field (OCR), which is required for the linkage. Filter terms from the opportunity object include the close date range for the last 3 years and the Opportunity Amount:Sum (a metrics field from opportunities) to be atleast 10,000.
  2. Step 2 is similar to step 1, except for the filter criteria. This time we want the Opportunity Amount as we are looking for individual donations. You can clone Step 1 of the report and just change the filter condition, where the Opportunity amount is greater than $500. No linkages required in this step too. If you run the report, you will get 2 separate blocks of contacts, which match the respective filter terms.
  3. Step 3 is created from the contact object. Select the fields you require for the final report including the contact ID field, which is necessary for the linkage. This step has no filters (you can add filters if required). The linkage step is the key here. You  need to link all the with the contacts that match the filter criteria in step 1 but not the filter criteria in step 2. You also need to get all the records that match the 2 relationships. This means that only the contacts that match step 1 but not step 2 will show up in the final list.

The image belows will give you an idea of the linkages for this report.

step3.jpg

Powered by BetterDocs