Introduction

Non-profit organizations who need to easily and quickly identify a core list of people (not in a household), households, and organizations for recurring mailing or email communication efforts, this article will provide you with a quick and easy walk through of how to identify a full list of people, households, and organizations. If your organization actively uses households, often you will find the need to exclude the 'people' contacts as you expect to target the household contact record only. This set up with lead you through those steps. This is also a great base report to leverage as a source file and use as a subquery for other ongoing query needs.

Determining the desired end state

As a best practice in query building it is always import to consider the desired end state. Following this best practice is a great first step before jumping into building a query. In this use case, the desired end state is to:

1. Create a source list of all people (who are not in a household), households (excluding the individual people records), or organizations
2. Expose the salutation for purposes of mailing a letter campaign
3. Only target active contacts and non-deceased persons
4. Only target contacts who have elected to receive mail
5. Expose limited giving history for internal awareness including:
   1. Current Fiscal Year Giving (direct financial donations - including soft credits)
   2. Affiliate Current Fiscal Year Giving (important for households or organizations - exposing direct contributions this fiscal year from members of the household or organization)
   3. Last Year Amount (direct financial donations from last calendar year)
   4. Affiliate Last Year Amount (important for households or organizations - exposing direct contributions last calendar year from members of the household or organization)
   5. Lifetime Amount (total overall giving for direct financial donations)

How to Create the Query

Step 1: To create this query navigate to the Automation & Workflow App > General > Queries > Create New (upper right hand corner) > Filter: Contacts Database > Select: Contacts table > Click CONTINUE

Step 2: Next we will begin to build the fields that are needed in the source file to be presented to the organization. If you are using this for use within the email/mail blast feature within CharityEngine, always include the Contact ID.

In the SELECT field, add:

1. From Contacts data subgroup: Id, Full Name, Type, Receive Mail, Formal Salutation, Primary Street Address 1, Primary Address City, Primary Address State, Primary Address Postal Code, Primary Email Address
2. From the Organizations data subgroup: Primary Point of Contact Name (not required for mailing purposes)
3. From the Households data subgroup: Head of Household Full Name, Spouse Full Name (not required for mailing purposes)
4. From the Giving History data subgroup: Current Fiscal Year Amount, Last Year Amount, Lifetime Amount (reflects direct contributions including soft credits)
5. From the Affiliated Giving History data subgroup: Current Fiscal Year Amount, Last Year Amount (reflects indirect contributions including employee giving or household member giving)

Note, the user can move the fields within SELECT to order in the final desired state for the organization.

Pro-Tip: Click SAVE to secure changes and avoid lost work.

Step 3: Next we will begin to build the filters to narrow down the outcomes. This filtering feature will ensure to that people who are a member of a household are excluded, while also including only people who are not a member of a household.The first conditions as outlined in our preparation include Active contacts and Receive Mail and a Person/Household/Organization.

In the FILTER field, add:

1. From the Contact data subgroup:
   1. Active Equals True (or 1) - this excludes any non-active contact records
   2. Receive Mail Equals True (or 1) - this includes only contact records that have a communication preference of yes, receive mail

Note, that because this is an AND the filter connector should reflect AND.

Now, we need to begin locating the person, household, or organization. In this use case, we need to create nested filtering using an Or statement - in order to achieve this, the user will click the Plus icon in the top right hand corner within the FILTER section. This can also include nesting filters within a nested filter.

 Click on the Plus icon within the FILTER section to add the first nested filter. Continue by adding:

1. From the Contact data subgroup, add:
   1. Type Equals "Household" - note, this will identify any contact type that is a household which is a target goal outlined in step 1
   2. Type Equals "Organization" - note, this will identify any contact type that is an organization which is a target goal outlined in step 1

Now that we have identified households and organizations, we need to identify people who are NOT part of a household and are NOT deceased. Because we have an AND statement for this segment, we need to add a nested filter within the OR of household, organization contact filtering. Click on the Plus icon within the sub filter to add a new grouped filter.

Now, begin building out the filtering to identify people who are NOT in a household AND not deceased. Continue by adding:

1. From the Person data subgroup add:
   1. Household Contact Id Equals 0 - this identifies only people contact records that are NOT connected to a household as a member.
   2. Deceased Equals False (or 0) - not deceased status on contact record
2. Ensure that the filter for this sub filter is set to AND as the condition is NOT in a household AND NOT deceased.

Note: Now that we have identified Household, Organizations, OR People (identified as not associated with a household contact id and not deceased), ensure that the sub query for contacts is set to OR

Pro-Tip: If a user is creating a a multi-filter and/or nested filter, we recommend adding one data filter at a time to build the query and ensure the filtered data point that was added returns the expected results. This helps with faster and easier troubleshooting for the end user. Multi-step filters can quickly become complex - so add one filter at a time to ensure you are meeting your conditions.

Step 4: Click on RUN to generate the query results.

Pro-Tip: Use the Sort icon next to RUN to apply sorting condition. Or click on the EXPORT icon to export results. Always click SAVE to secure your changes.

FAQ and Related Resources

Q. Where can I learn more about CharityEngine reporting tools?
A. Learn more in our article Reports & Analytics: An Introduction to Frequent Reporting Options within CharityEngine

Q. How do I create a listing of transactions for People (not associated with a household), Households (and the members associated with the household), and/or organizations?
A. Learn more in our article Queries: What Can I Do with Queries - Creating A Transaction List - People, Households, Organization

• Exporting Data: How to Schedule an Export Data Job
• Data Hygiene: De-Dupe Households
• Query Builder- Adding External Queries
• Queries: What Can I Do with Queries - Locating Contacts Within X Miles
• What Can I Do with Queries: Within X Miles
• Queries: What Can I Do with a Query: Donations Within X Days
• Queries: What Can I Do with Queries - Event Registration
• Query Builder Tool
• Adding a Seed List
• Data Importing: How to Import Contact - People Data
• Assigning Groups, Tags, and Opt- In Lists Through Importer
• Contacts
• Contacts: How to Create, Use and Manage Cases
• How to Clear Contacts from Groups, Tags, and Lists Using Trigger Tool
• Trigger Tool – Creating an Opt-In List
• Queries: What Can I Do With Queries - Resend Initiatives to Contacts Who Have Not Opened Them
• Contacts: Updating Donor Passwords
• Contacts: Creating a New Person Contact from an Organization Record
• Contacts: How to Create a New Affiliate/Chapters
• How to create Household members from the Household Contact Record
• Reports & Analytics: How to Use the Report Builder Tool
• Contacts: Managing Contact Status Types and Priority
• Contacts: How to Easily Bulk Update Contacts (Groups, Solicitors, Activities, Opt Ins, etc.)
• Sending Email Campaigns from the Contact Listing Screen
• Reports & Analytics: How to Access Lapsed Donors Reporting
• CharityEngine Best Practices for Employees and Volunteers Who Leave an Organization
• Calculated Custom Fields
• Workflow & Automation: Data Governance – Contact Management (Merge, Delete, Inactive)
• Email Campaigns: How to Build Opt In List - Using Query Builder to Segment Existing Lists
• Reports & Analytics
• Sustainer Records: How to Deactivate Sustainers in Mass
• Configuration: How to Configure for a Fiscal Year and Downstream Reporting
• Automation & Workflow
• Contact Records: Creating Custom Field Groups and Custom Fields
• API Configuration: How to Configure your WealthEngine API Key
• Data Governance: Updating Contact Records Through the Transaction or Contact Screen
• Mapping Data to Imports- How to Confirm Opportunity Dates are Imported Correctly and Update Them
• Online: User Center Configuration
• Communication: How to Send Messages from Listing Screens
• Data Governance: Best Practices for Backing Up Data
• Data Importing: How to Import Transactions
• Contact Records: Changing Contact Type
• Contact App: How to Add (and Delete) a Solicitor to a Contact Record
• Reports & Analytics: An introduction to custom dashboards within CharityEngine
• Workflows: How to Bulk Delete Transactions Using Transformation Tool
• Tributes: How to Clean Up Tributes with Acknowledgees
• Data Importing: How to Import Relationships
• Contact Management: Contacts Who Are Online Now
• Memberships: How to Create & Manage a New Member - A Comprehensive Guide
• Memberships: How to Manage Existing Membership Records
• Tributes: How to Clean Up Tributes without Acknowledgees
• Developer: API - Installing the CharityEngine Public Web API
• Contacts: How to Create and Manage Activities
• Workflow & Automation: Data Governance - Transaction Management (Duplicate Transactions)
• Reports & Analytics: How to leverage the Out-of-the-Box Campaign Dashboards
• Contact Records: Addresses
• Automation & Workflow: A Basic Understanding of Triggers
• Contact Emails
• Contacts: How to Add Badges to a Contact Record
• Contacts: Creating & Adding Tags or Groups
• Contacts: How to Identify and Use the Outlook & Constituent Ranking
• Printing the Contact Summary Page
• Data Import: How to Import Soft Credits
• Email Activity Tracking within CharityEngine
• Configuring Salutations
• Adding Seasonal Address
• Contact Record Page
• Online: Configuring API User Account
• Accounting: Accounting and Financial Reconciliation
• Automation & Workflow: Manually Sending Year End Statements to Households
• Automation & Workflow: Using the Import Functionality to Append Data
• Data Enrichment Job: How to Update Address with the Zip +4 and USPS Address Correction feature
• Contacts - Editing Contact Record Panels
• Sending Tracked Emails
• Contact : Notes
• Trigger Tool: How to Auto Assign Contacts to a Household
• Quick Actions
• Contacts/Transactions : Quick Filter - My Preferences
• Contacts: Comprehensive Guide for Usage and Management of Tributes
• Opportunities & Moves Management: Comprehensive Guide for Set Up, Configuration, and Reporting
• Contacts : Files
• Reporting and Dashboards
• Reports & Analytics: An Introduction to Frequent Reporting Options within CharityEngine
• Reports & Analytics: Overview and Usage of the Reports & Analytics Application
• Automation & Workflow: Modifying a Data Job
• Automation & Workflow: An Introduction to Queries
• Contacts: How to Create and Manage Household Contact Records
• Data Governance: How to Manage Data Storage Limits
• Data Importing: How to Import Contact - Organization Data
• Data Importing: How to Import Contact - Household Data
• Automation & Workflow: Data Transformation
• Automation & Workflow: An Overview of Triggers & Use Case - Examples
• Contacts: Adding Contacts to Your Basket for Exporting
• Data Importing: How to Import Pledge Accounts & Historical Pledge Transactions
• Contacts: A Guide to the CharityEngine Projects Feature
• Contacts: Relationships & Connections
• Queries: What Can I Do with Queries - Creating A Transaction List - People, Households, Organization
• Contacts: How to Create and Manage Organization Contact Records
• Queries: What can I do with Giving History
• In-Kind Gifts: How to Create and Manage In-Kind Gifts - A Comprehensive Guide
• Campaigns & Marketing: Unveiling Success: The Art of Monitoring Results in Non-Profit Email Campaign
• Reports & Analytics: How to Use the Constituent Density Report
• Contacts: How to Create and Manage People Contact Records
• Contacts: How To Set Your User Prefereces
• Data Governance: Change Logs
• Data Importing: External Bulk Verification Services
• Query Best Practices
• Query Builder Documentation
• Automation & Workflow: Merge History
• Contacts : Deleting Contacts in Bulk
• API Query Mode
• Email Deliverability Best Practices
• Contacts: People, Households, & Organizations search
• Data Importing: Contacts & Use of Contact Matching
• Exporting Data: How To Export Data from a Query