For additional information on what campaign credit means, see the counting methodology definitions article.
Campaign credit is meant to be used only in campaign periods, a campaign filter must be applied in all queries. This should be done using a sub-query (may be nested in another sub-query) on [RecogRevenueFACTID] In List [RevenueCampaignRevenueFACTID] Where [Top Campaign Campaign LookupID] is contained within a list or set equal to the campaign being analyzed.
Reporting on Campaign Credit
When reporting on campaign credit, a secondary query and a variable (calculation) must be utilized.
The query pulls:
-
Pledge commitments
-
Planned gift expectancies of all vehicles except life insurance
-
Payments with [Recognition Revenue Application] of “Donation”, “Donor challenge”, “Matching gift”, or “Recurring gift”; and [Recognition Revenue Category] of “Life Insurance Distribution - Greater Than Expected” and all “Life Insurance Premium%” values.
-
Planned gift expectancies with a vehicle of “Life Insurance”, but only the “Original” setups
-
Payments on planned gifts and pledges with a recognition type of “Donor Challenge”
-
Other-Other-Payments (OOPs)
|
AND
|
|
OR
|
|
AND
|
[Recognition Revenue Application Type]
|
Equal to
|
Gift
|
|
OR
|
[Recognition Revenue Transaction Type]
|
Equal to
|
Pledge
|
|
[Recognition Planned Gift Vehicle (+)]
|
In List
|
Bequest;Charitable gift annuity;Charitable lead annuity trust;Charitable lead unitrust;Charitable remainder annuity trust;Charitable remainder unitrust;Living trust;Other;Pooled income fund;Retirement plan assets
|
|
AND
|
[Recognition Planned Gift Vehicle (+)]
|
Equal to
|
Life insurance
|
|
[Recognition Planned Gift Type (Orig/Addition) (+)]
|
Equal to
|
Original
|
|
|
AND
|
[Recognition Revenue Transaction Type]
|
Equal to
|
Payment
|
|
[Recognition Revenue Application]
|
In List
|
Planned gift;Pledge
|
|
[Recognition Type]
|
Equal to
|
Donor Challenge
|
|
|
AND
|
[Recognition Revenue Transaction Type]
|
Equal to
|
Payment
|
|
[Recognition Revenue Application]
|
In List
|
Donation;Donor challenge;Matching gift;Recurring gift
|
|
[Recognition Revenue Category]
|
Not In List
|
Life Insurance Premium;Life Insurance Premium Payment U-M;Life Insurance Premium Payment-Other;Life Insurance Distribution - Greater Than Expected
|
|
|
|
|
AND
|
[Recognition Revenue Application Type]
|
Equal to
|
Other
|
|
[Recognition Revenue Application]
|
Equal to
|
Other
|
|
[Recognition Revenue Transaction Type]
|
Equal to
|
Payment
|
|
|
|
[RecogRevenueFACTID] In List [RevenueCampaignRevenueFACTID]
|
Where
|
[Top Campaign Campaign LookupID] In List 2012Victors
|
Figure 1. A screenshot of the Business Objects query for campaign credit.
The [Constituent LookupID] fields from the base query and the campaign credit secondary query are merged. The final calculation for campaign credit is: ([Recognition Revenue Application Amount] - [Recognition Pledge Application WriteOff Amount(+)]) In([zConstituent LookupID])
Filtering on Campaign Credit
When filtering on campaign credit totals, a subquery that utilizes [Sum of Recognition Amount] for a cumulative amount filter must be utilized. Using that built-in amount field is an overrepresentation of written-off transactions, so the final reported campaign credit value may be less than the filtered cumulative value.
Figure 2. A screenshot of a Business Objects subquery filtering on campaign credit during Victors for Michigan totaling $50,000 or more.
This subquery is part of the larger definition of the base population (e.g., active individuals, organizations, alumni of the Marsal Family School of Education). It may include additional filters on the credit in the filter, such as credit to a particular SCU, within a range of fiscal years, and so on. The example in Figure 2 is looking for donors with campaign credit during the Victors for Michigan campaign of $50,000 or greater.