Query Training: Development

Guided Query Practice

Try answering these questions by reading the prompt and working following the steps to create a query that gets you the results you are looking for. After you’ve completed these exercises, or if you are stuck on any of them, feel free to navigate to the guided query answers here.

  1. “Excuse me, can you get me a list of campaigns we’ve run that don’t have any events associated with them?”
    • start from the Campaign query on the Development homepage
    • set the criteria for the # of Events field to be “is equal to” 0
  2. “I need a list of all Pending Online Gifts where the Payment Method is either cash, check, or credit card.”
    • start from the Pending Online Gifts query on the Development homepage
    • pull in the Payment Method field and set the criteria to be “is one of” and check off the Cash, Check, and Credit-Card options
  3. “Would you be able to get me a query that shows everyone with an alumni primary development role where we haven’t met our target amount for them in the active campaign, but they have given something?”
    • start from a Giving History query on the Development homepage
    • set criteria for the Primary Development Role field to be “is one of” and check off the Alumni option
    • set criteria for the Total Giving field to be “is greater than” 0
    • set criteria for the Total Giving Over Under field to be “is less than” 0
  4. “Can you show me how much money we pull in through active monthly recurring donations?”
    • start from the Recurring Donations – Active query in the Recurring Donations section of the Development homepage
    • set the criteria for the Frequency field to be “is one of” and check off the Monthly option
    •  click on the Amount field and then add the Sum option to the Totals section
  5. “Please show me a list of all anonymous, posted pledges to our Active Campaign where the expected payment frequency is them giving us the amount all at once.”
    • start from the Find Pledges query on the Development homepage
    • pull in the Anonymous field and apply the “is” Yes criteria
    • apply the “is” Yes criteria to the Posted field
    • apply the “is one of” criteria to the Campaign field and select Active Campaign
    • set the Expected # of Payments criteria to be “is equal to” 1
  6. “It seems like we missed someone important. Can you query which of our prospects have a Rating of 1A and don’t seem to have a defined stage?”
    • start from a Managed Prospects query on the Development homepage
    • clear the criteria automatically applied to the Manager field
    • set the criteria for the Rating as “is one of” 1A
    • set the criteria for the Stage as “is one of” None Specified
  7. “Could you please make a Campaign Summary report and be sure to include first and last gift date? Additionally, can you sort it so that the most profitable campaigns in terms of total donations are toward the top? Also, hide the “None” campaign, please.”
    • start from the Campaign Summary query in the All Time section of the Development homepage
    • pull in the First Gift Date and Last Gift Date fields
    • set the sort order to be descending in the Total Donations field
    • set the Description criteria to be “is not” None
  8. “I’m looking for all Fundraisers and Appeals that we have scheduled after today for the Active Campaign.”
    • start from a Find Group Events query on the Development homepage
    • set the Event Type criteria to be “is one of” and check off Fundraiser and Appeal.
    • set the Start Date criteria to be “date is after” {today}
    • pull in the Campaign field and set the criteria to be “is one of” and check the Active Campaign box
  9. “I’d like to see a list of development classifications that we have available. Can you pull up a list of all development classifications with at least one constituent, subtotaled by classification type?”
    • start from the Find Development Classifications query on the Development homepage
    • set the Total Constituents criteria to be “is greater than” 0
    • While the Classification Type field is highlighted, click on the Subtotal Header button
  10. “Can you look and get me a list of all Online Giving Forms that we have set up to accept recurring donations?”
    • start from the Online Giving Forms query on the Development homepage
    • set the criteria for the Enabled Gift Types field to be “contains” recurring

Independent Query Practice

Now that you’ve had some practice building queries, attempt to build the following queries on your own. After you’ve completed this, check your answers here.

  1. “How much money did we soft credit to people with the parent primary development role in the 2017-2018 Annual Fund campaign?”
  2. “Can you build me a query of all people who have donated to us that live in North Carolina or South Carolina and also have the query contain links to both internal and external donor profile documents?”
  3. “I’d like a list of all people who have pledges that are over 180 days past due.”
  4. “Please provide a list for me of all people who are LYBUNTs for the AF 2018-2019 campaign and were also LYBUNTs for the AF 2017-2018 campaign.”

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Guided Query Answers

  1. “Excuse me, can you get me a list of campaigns we’ve run that don’t have any events associated with them?”
  2. “I need a list of all Pending Online Gifts where the Payment Method is either cash, check, or credit card.”
    • start from the Pending Online Gifts query on the Development homepage
    • pull in the Payment Method field and set the criteria to be “is one of” and check off the Cash, Check, and Credit-Card option
    • https://axiom.veracross.com/#/query/398087
  3. “Would you be able to get me a query that shows everyone with an alumni primary development role where we haven’t met our target amount for them in the active campaign, but they have given something?”
    • start from a Giving History query on the Development homepage
    • set criteria for the Primary Development Role field to be “is one of” and check off the Alumni option
    • set criteria for the Total Giving field to be “is greater than” 0
    • set criteria for the Total Giving Over Under field to be “is less than” 0
    • https://axiom.veracross.com/#/query/398089
  4. “Can you show me how much money we pull in through active monthly recurring donations?”
    • start from the Recurring Donations – Active query in the Recurring Donations section of the Development homepage
    • set the criteria for the Frequency field to be “is one of” and check off the Monthly option
    •  click on the Amount field and then add the Sum option to the Totals section
    • https://axiom.veracross.com/#/query/398092
  5. “Please show me a list of all anonymous, posted pledges to our Active Campaign where the expected payment frequency is them giving us the amount all at once.”
    • start from the Find Pledges query on the Development homepage
    • pull in the Anonymous field and apply the “is” Yes criteria
    • apply the “is” Yes criteria to the Posted field
    • apply the “is one of” criteria to the Campaign field and select Active Campaign
    • set the Expected # of Payments criteria to be “is equal to” 1
    • https://axiom.veracross.com/#/query/398096
  6. “It seems like we missed someone important. Can you query which of our prospects have a Rating of 1A and don’t seem to have a defined stage?”
    • start from a Managed Prospects query on the Development homepage
    • clear the criteria automatically applied to the Manager field
    • set the criteria for the Rating as “is one of” 1A
    • set the criteria for the Stage as “is one of” None Specified
    • https://axiom.veracross.com/#/query/398109
  7. “Could you please make a Campaign Summary report and be sure to include first and last gift date? Additionally, can you sort it so that the most profitable campaigns in terms of total donations are toward the top? Also, hide the “None” campaign, please.”
    • start from the Campaign Summary query in the All Time section of the Development homepage
    • pull in the First Gift Date and Last Gift Date fields
    • set the sort order to be descending in the Total Donations field
    • set the Description criteria to be “is not” None
    • https://axiom.veracross.com/#/query/398121
  8. “I’m looking for all Fundraisers and Appeals that we have scheduled after today for the Active Campaign.”
    • start from a FInd Group Events query on the Development homepage
    • set the Event Type criteria to be “is one of” and check off Fundraiser and Appeal.
    • set the Start Date criteria to be “date is after” {today}
    • pull in the Campaign field and set the criteria to be “is one of” and check the Active Campaign box
    • https://axiom.veracross.com/#/query/398142
  9. “I’d like to see a list of development classifications that we have available. Can you pull up a list of all development classifications with at least one constituent, subtotaled by classification type?”
    • start from the Find Development Classifications query on the Development homepage
    • set the Total Constituents criteria to be “is greater than” 0
    • while the Classification Type field is highlighted, click on the Subtotal Header button
    • https://axiom.veracross.com/#/query/398148
  10. “Can you look and get me a list of all Online Giving Forms that we have set up to accept recurring donations?”

Independent Query Answers

  1. “How much money did we soft credit to people with the parent primary development role in the 2017-2018 Annual Fund campaign?”
    • start from the Find Gifts query on the Development homepage
    • set the criteria for Gift Type to be “is one of” and then check off Donation Soft-Credit and Plg Soft-Credit
    • set the criteria for Primary Development Role to be “is one of” and check off Parent
    • set the criteria for Campaign to be “is one of” and check off only AF 2017-2018. Make sure that Active Campaign is not checked
    • ensure that the Amount field has the Sum operator pulled into the Totals box
    • https://axiom.veracross.com/#/query/398209 
  2. “Can you build me a query of all people who have donated to us that live in North Carolina or South Carolina and also have the query contain links to both internal and external donor profile documents?”
    • start from the Find Donor query on the Development homepage
    • Pull in both the Donor Profile Document (Internal) and Donor Profile Document (External) fields
    • set the criteria for the State Province field to be “begins with” NC
    • pull in another instance of the State Province field and set the criteria to be “begins with” SC
    • set the brackets around the two State Province fields and apply the OR conjunction between them by setting it on the second State Province field
    • https://axiom.veracross.com/#/query/398216
  3. “I’d like a list of all people who have pledges that are over 180 days past due.”
    • start from the “Past Due Pledge Installments” query on the Development homepage
    • set the criteria for the Over 180 field to be “is greater than or equal to” 0.01
    • https://axiom.veracross.com/#/query/398234
  4. “Please provide a list for me of all people who are LYBUNTs for the AF 2018-2019 campaign and were also LYBUNTs for the AF 2017-2018 campaign.”
    • start from the LYBUNT query on the Development homepage
    • adjust the criteria of the Campaign field to be “is one of” and check off the AF 2017-2018 and AF 2018-2019 options
    • https://axiom.veracross.com/#/query/398236
print

Related Articles