Add Missing Campaign Primary Constituency for MED

Category: Attribute

Frequency: Monthly

Platform: SQL Server

Developer: IDS

Analyst: DMT
 

This integrity check identifies constituents that meet the definition for Michigan Medicine but do not have a campaign primary constituent attribute for MED so that the attribute can be added. Due to the complex nature of MED roll-ups, this is done in addition to the monthly process that creates these constituency attributes for all SCUs.

The OUD-DS3 best practice is for constituents with a connection to a unit to have a campaign constituency attribute to identify that connection to improve the ease of reporting.

This integrity check queries constituents who do not already have an active MED Campaign-Primary Constituency attribute and who do not meet the following exclusion criteria:

  • Constituent affiliation name is one of:

    • Not a Major Giving Prospect

    • PDA Research Exclusion

  • Constituent affiliation end date is null

  • Constituent affiliation site…

    • Is one of:

      • University of Michigan

      • Prospect Dev & Analytics

      • Development Majors Gifts Prgm

    • Is null

    • VP Area is one of:

      • VP for Development

      • Exec. VP for Medical Affairs

and who meet at least one of the following sets of inclusion criteria:

  • Faculty/Staff:

    • Constituent affiliation name is one of:

      • U-M Staff - Active

      • U-M Staff - Retired

      • U-M Faculty - Active

      • U-M Faculty - Retired

    • Constituent affiliation end date is null

    • Constituent affiliation site VP area = Exec. VP for Medical Affairs

  • Committee Member

    • Constituent group member end date is null

    • Committee name is not null

    • Committee site VP area = Exec. VP for Medical Affairs

  • Friend:

    • Constituent affiliation name = Friend

    • Constituent affiliation end date is null

    • Constituent affiliation site VP area = Exec. VP for Medical Affairs

  • Donor:

    • Revenue transaction type is one of:

      • Payment

      • Pledge

      • Recurring gift

      • Planned gift

    • Recognition amount is >$1

    • Designation site VP area = Exec. VP for Medical Affairs

  • Alumni:

    • Education attribute name is Alumni Affiliation

    • Education attribute value corresponding site VP area = Exec. VP for Medical Affairs

  • Current Student:

    • Education status = Currently attending

    • Education additional information school/college corresponding site VP area = Exec. VP for Medical Affairs

  • Parent:

    • Reciprocal relationship type is one of: Child, Stepchild

    • Reciprocal constituent meets criteria for Alumni or Current Student

  • Prospect Manager:

    • Prospect manager end date is null

    • Prospect manager has Development Officer constituent affiliation with:

      • Site VP area = Exec. VP for Medical Affairs

      • AND End date is null

    • Prospect manager has Fundraiser constituency

  • Constituent Relationship Manager:

    • Constituent relationship manager end date is null

    • Constituent relationship manager has Development Officer constituent affiliation with:

      • Site VP area = Exec. VP for Medical Affairs

      • AND End date is null

    • Constituent relationship manager has Fundraiser constituency

  • PDA Referral:

    • Constituent affiliation name = PDA Referral

    • Constituent affiliation end date is null or is in the future

    • Constituent affiliation site VP area = Exec. VP for Medical Affairs

The output of this integrity check is a batch to add a ‘Campaign-Primary Constituency’ attribute with value ‘MED’ and start date 7/1/2021.

Resources and References

DART Campaign Constituency Best Practices - OUD, Integrated Data Services

Unit Constituencies - Definition Datapoints

Print Article