Add Missing Campaign Primary Constituency for MED

Category: Attribute

Frequency: Monthly

Platform: SQL Server

Owner: IDS
 

What Does It Do?

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 identifies constituents that meet the definition for the 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.

How Is This Done?

This integrity check is run using a SQL Server view called [INTEGRITY_CHECKS].[dbo].[IDS.V-INTEGRITY CHECK-ATTRIBUTE-Add Missing Campaign Primary Constituency MED(DW)], which is located in DB-OUD > Databases > INTEGRITY_CHECKS > Views.

That view queries constituents that do not already have the MED Campaign-Primary Constituency attribute and who do not have the ‘Not a Major Giving Prospect’ or ‘PDA Research Exclusion’ affiliations for UM, PDA, Development Major Gifts Prgm, VP Development, or Exec. VP for Medical Affairs, and who are part of the defined MED constituency for the campaign. This includes all of the following individuals:

  • Faculty/Staff: active, former, and retired faculty and staff of Exec. VP for Medical Affairs with a current affiliation

  • Committee: current committee group members for any committee associated with the Exec. VP for Medical Affairs

  • Friends: constituents with friend affiliations to the Exec. VP for Medical Affairs

  • Alumni: constituents with an alumni affiliation to the Exec. VP for Medical Affairs

  • Donors: constituents with recognition credit of $1 or more for payments, pledges, recurring gifts, or planned gifts made to the Exec. VP for Medical Affairs

  • Current Students: constituents that have an education record with a status of ‘Currently Attending’ for the Exec. VP for Medical Affairs

  • Parents of Current Students or Alumni: the constituents that have a child/step-child relationship with any current students or alumni

Resources and References

DART Campaign Constituency Best Practices - OUD, Integrated Data Services

Unit Constituencies - Definition Datapoints

S:\IDS\MES\SQL Scripts\DATA QUALITY\ATTRIBUTE

Snapshot GUID: BBDW.DIM_CONSTITUENT.CONSTITUENTSYSTEMID + ‘-MED’

Print Article