Category: Query Tips
UNC Aggregate Query Node Updates
In an effort to improve the consistency and usability of our Constituent Recognition aggregates, a new set of query nodes is available for use. These new query nodes have a consistent naming convention and output format, and instead of having separate nodes from Individual and Household Totals, these new aggregates have both numbers in them. All the new query nodes begin with “UNC Aggregate”.
The old aggregate query nodes are flagged with the word “[DEPRECATED]” to identify them.
Please refrain from using the former nodes in any new queries created and whenever possible, replace any existing queries with these new aggregates as you use the query.
Importing Lookup IDs Using an Import Selection
This content is Onyen protected. Please enter your Onyen to log in and view it.
Export Processes
What is an Export Process?
Most Davie processes identify the “who” using various methods, mark constituents with the communication, and then create a file using fields in an export definition. An Export Process uses a single query/selection to identify the “who”, it does not mark records in any way, and creates a file using an export definition. It is a simple way to get information about a population when not running a communication process.
What is an Export Definition and how is it used?
An export definition is a defined set of fields which can return a wide variety of information about a population. It returns one row per constituent, and often has many fields/columns. Export definitions are used in various Davie processes, such as correspondence processes, marketing efforts, event invitation processes, and export processes. The different processes each function in a unique way, but the final file generated by the process is created using an export definition.
How can I alter the fields in an Export Definition?
For an export definition that belongs to your site, if you wish to add, remove, or reorder the columns, submit a ticket to Davie Support. Only a UDO Business Analyst should alter an export definition.
How to Run an Export Process
- Click Administration from the Navigation Menu. Select Export if you are not taken directly to the list of export processes.
- Search for Export Processes for site
- Click on Poplar and select Edit
- Click the magnifying glass by Selection, and search for desired selection. This can either be a query saved as a selection or an existing import selection.
- For Export definition, ensure desired export definition for your site is populated.
- Some sites have multiple export definitions for different purposes
- To alter which export definition is populated, click the magnifying glass and search for desired definition.
- Click Save
- Click Poplar and select Start Export
- Once the process is complete, click Download output from the Recent Status tab
- Files are only current as of when they were created. The Export Process must be rerun if updated information is required.
Smart Queries – A Fast Alternative
Smart queries are a simpler, easier alternative for some of the standard querying that users often need to accomplish. Access to smart queries is available upon completing Introduction to Querying, and doesn’t require the in-person, ad-hoc query training session. Examples of queries that can be accomplished with a smart query are pulling all active alumni from a particular school or degree, pulling all recognized donors for a specific time frame and giving area, or pulling all current members of a committee or group. A printable listing of available smart query definitions, along with descriptions and filters, can be found here
Smart Query Definitions
Table of Contents
- UNC Constituent gift recognition by designation and amount
- UNC Gift revenue payments by date and designation info
- UNC Designation list
- UNC Fundraising purposes with stewarded donors
- UNC Alumni by school, degree and location
- UNC Alumni by school, major, degree and location
- UNC Constituents by educational involvement
- UNC Current students by school, level, class, etc.
- UNC Interactions completed by date, site, owner, constituent
- Constituents by committee/group membership
- Event registrants by event and date
- UNC Unit Annual Downgrade Smart Query
- Smart query definition name: UNC Constituent gift recognition by designation and amount
Smart query definition description: Returns constituents with recognition by site, designation or department by date
- Smart query definition name: UNC Gift revenue payments by date and designation info
Smart query definition description: Returns revenue payment transactions at the application detail level that include constituent name and primary address information, not to be confused with recognition.
- Smart query definition name: UNC Designation list
Smart query definition description: Returns a list of designations by site, department, and accounting chart fields
- Smart query definition name: UNC Fundraising purposes with stewarded donors
Smart query definition description: Returns fundraising purposes with administrators and stewarded donors with no end date including purpose details, donor name and primary address
- Smart query definition name: UNC Alumni by school, degree and location
Smart query definition description: Returns living UNC alumni by school, degree, city, state, country and county; includes ex-mats and looks at primary address
- Smart query definition name: UNC Alumni by school, major, degree and location
Smart query definition description: Returns living UNC alumni by school, major, degree, city, state, country and county; includes ex-mats and looks at primary address
- Smart query definition name: UNC Constituents by educational involvement
Smart query definition description: Returns constituents by educational involvement, includes primary address
- Smart query definition name: UNC Current students by school, level, class, etc.
Smart query definition description: Returns current student constituents with education information and primary address
- Smart query definition name: UNC Interactions completed by date, site, owner, constituent
Smart query definition description: Returns completed interactions with status, comments, site, rating, employer, and primary contact info (does not include pending steps)
- Smart query definition name: Constituents by committee/group membership
Smart query definition description: Returns constituents by committee or group, includes primary address
- Smart query definition name: Event registrants by event and date
Smart query definition description: Returns events with registered constituents name, ID and balance
- Smart query definition name: UNC Unit Annual Downgrade Smart query
Smart query definition description: Returns donors that have gift recognition to a site, designation, or PS department for both the current and previous fiscal year, but level of support has decreased.
UNC Aggregate Planned Giving Nodes
The following nodes are now available in the query designer and can be requested to be added to your export definitions:
UNC Aggregate Planned Giving All Time
UNC Aggregate Planned Giving By Calendar Year
UNC Aggregate Planned Giving By FY
UNC Aggregate Planned Giving By FY and Site
UNC Aggregate Planned Giving By Site
Similar to our other aggregates, these nodes show recognition, both for individuals and households. Since these display recognition amounts, they cannot be used for commitment reporting purposes. However, they provide valuable information regarding a constituent’s giving activity to UNC and specific sites.
**Please note, if a planned gift is marked anonymous and accordingly does not have recognition associated with it, it will not be included in these nodes.
UNC Constituent Academic Recognition Node
In order to provide more granularity in querying and available data for exports, we have added the UNC Constituent Academic Recognition node. All the activity that is summarized by the traditional aggregates (gift recognition) can now be accessed in detail, with additional information about the gift and designation. The full list of fields is displayed below.
Since this is the detail version of what is provided in the Aggregates, please note that only gift payment and matching gift claim recognition is returned. Pledges and Planned Gifts are not included, and gifts without recognition will not be included. And, since it is recognition, this can only be used to review a donor’s recognition and activity, it cannot be used to pull financial or reporting information.
The field named Tribute Gift is a Yes/No field that identifies if there is a tribute gift on any portion of the original gift. If a gift was split among many designations but only part of the gift has tribute information, this field will still display Yes for all recognition credits associated with that gift.
Recognition Year Counts on UNC Aggregate Academic Giving Nodes
Two new fields providing counts of fiscal year giving have been added to some of the aggregate nodes.
Total Recognition Years – This is a count of the number of fiscal years an individual has in recognition to UNC or a particular Site, PS Department or Designation.
Consecutive Recognition Years – This is a count of the recent consecutive years a donor has in recognition to UNC or a particular Site, PS Department or Designation. In order to have recent consecutive giving, a constituent must have at least had recognition in the previous fiscal year. Additional consecutive years of giving, either in the current FY or before the previous FY will be included in the count, but only if it is consecutive.
These fields have been added to the following nodes:
UNC Aggregate Academic Giving All Time
UNC Aggregate Academic Giving By Designation
UNC Aggregate Academic Giving By PS Dept.
UNC Aggregate Academic Giving By Site
Household Lookup ID and Household Dedupe ID
There are two fields in the UNC Demographic node that provide Household ID information, Household Lookup ID and Household Dedupe ID. They are available in the query designer and they have been added to most export definitions. They both are useful in different situations.
Household Lookup ID
What is it? – If a constituent is currently part of a household (spouse or partner), this will display the lookup ID for that household. Constituents in the same household will have the same Household Lookup ID. However, if a constituent is not in a household, this field will be blank.
When is it useful? – If searching for instances where both spouses are in a file, this field can be leveraged along with the Highlight Duplicate Values feature in Excel. A common example is if you are reviewing a prospecting file and you wish to identify instances where both spouses are in the file.
Household Dedupe ID
What is it? – This field will never be blank. If a constituent is in a household (spouse or partner), the Household Lookup ID will display. However, if a constituent is not in a household, then the constituent’s individual lookup id will display.
When is it useful? – This works well when using the Remove Duplicates feature in Excel. A common example is if you need to “combine the household” for a file because it will be used to send a mail piece (acknowledgements, general correspondence or events; marketing efforts have the functionality) and you don’t want to send two pieces to a single household.
Query –Do not use a query to communicate with constituents
This content is Onyen protected. Please enter your Onyen to log in and view it.
Constituents – Honorary Degrees – Alumni?
This content is Onyen protected. Please enter your Onyen to log in and view it.