Query Training: Communications

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. “I need to send an email to all parents of students in the 6th grade. Can you build a query for me of these parents while ensuring that their email 1 is also present? There is no need to include parents without email addresses.”
    • from the Communications homepage, click on the Find Groups query in the Emails section
    • click on Parents, then Parents by Grade Level, then the Parents w/students in Grade 6 link
    • pop out the query on the General tab of the Group record using the “Open Query” button
    • set the criteria for the Email 1 field to be “is not blank”
  2. “Can you get me a list of all parents and grandparents that are on the bounce or spam lists?”
    • click on the Bounce/Spam List Statuses query in the Emails section of the Communications homepage
    • pull in the Role field and set the criteria to be “is one of” and check off the Parent and Grandparent boxes
  3. “I created a new Channel Category but don’t know where to find it or even remember what I named it. Can you give me a list of all channel categories with no channels in it so I can figure this out?”
    • click on the Channel Categories list on the Communications homepage
    • set the criteria for the # of Channels field to be “is equal to” 0
  4. “Could you please get me a list of all scheduled emails that people are receiving that come from “Advancement Office”?”
    • start from the Scheduled Emails query on the Communications homepage
    • pull in the From Name field and set to the criteria to be “begins with” Advancement Office
  5. “I created a school-defined group for use in emailing, but didn’t add anyone, so it doesn’t seem to be appearing for me. Can you get me a list of all school-defined groups in the general category that don’t have any members in it?”
    • from the Communications homepage, click on the Find Groups query in the Emails section
    • click on the School Defined section, then click on the SCHOOL-DEFINED: General option
    • open up the query design and change the criteria for the Member Count field to be “is equal to” 0
  6. “Would you be able to get me a list of all emails that weren’t delivered due to the system not delivering the email to an address that has previously bounced?”
    • start from the Bounced Emails query on the Communications homepage
    • clear the criteria in the Date Sent (Local) field
    • set the criteria in the Mailbox Response field to be “contains” Not delivering to previously bounced address
  7. “I’d like to send an email to all students whose primary language is English, French, or Spanish and have repeated a grade. Can you set up that query for me? Because I want to email them, don’t include any students we don’t have an email for.”
    • start from the Find Groups query on the Communications homepage
    • click on the Students option, then STUDENTS again, and then the Students who hav Repeated a Grade Level option
    • pop-out the query on the General tab of the Group details screen using the Open Query button
    • set the criteria for the Email 1 field to be “is not blank”
    • pull in the Language field and set the criteria for it to be “is one of” then check off the English, French and Spanish options
  8. “Hey, can you tell me how much it would cost us to send an SMS in each of the following countries: United States, Mexico and Canada?”
    • start from the Cost Per Country query on the Communications: SMS homepage
    • set the criteria for the Country field to be “is one of” and then type in “Canada; Mexico; United States” (NOTE: a semicolon in a field acts as an OR operator)
  9. “We’re looking to send out a satisfaction survey for parents of other programs students from the 16-17 school year. Where would I go to find the email address for the distribution list?”
    • start from the Find Groups query on the Communications homepage
    • select the Other Programs option, then choose Other Programs by Year
    • from here note the email address in the Parent Email Distribution List column in the Other Programs in 16-17 row — this is the email for the list
  10. “We’d like to send out a 10-year congratulations email to all current staff members who will have their 10th year of employment this year and have their emails in our database.”
    • start from the Find Groups query on the Communications homepage
    • click on the Staff/Faculty option, then click on Staff by Year Employed
    • select the Group name of the staff who were hired ten years ago (in this case, Staff employed in 09-10)
    • pop out the query on the General tab of the Group record using the Open Query button
    • set the criteria of the Email 1 to be “is not blank”
    • set the criteria of the Roles to be “does not contain” and type in “staff/fac – frmr”

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. “Could you please pull in the Email 1 and the Email 2 for all individuals on the bounce/spam list? Also, could you only include people who have a Veracross username?”
  2. “We want to send out three different emails for men, women, and gender non-conforming for our class reunions this year (2019). Could you build me three queries for Alumni who Graduated with their graduation year ending in ‘9? One for each option?”
  3. “Can you get me a list of all system emails that have been sent out to confirm logistics request submissions?”
  4. “We’re looking to send out an email to all people who live in Tennessee asking to attend an event that we’re hosting there. Can you get me a query that would get me there? Also, could you exclude anyone with the Do Not Solicit flag?”

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Guided Query Answers

  1. “I need to send an email to all parents of students in the 6th grade. Can you build a query for me of these parents while ensuring that their email 1 is also present? There is no need to include parents without email addresses.”
    • From the Communications homepage, click on the Find Groups query in the Emails section
    • Click on Parents, then Parents by Grade Level, then the Parents w/students in Grade 6 link
    • Pop out the query on the General tab of the Group record using the “Open Query” button
    • Set the criteria for the Email 1 field to be “is not blank”
    • https://axiom.veracross.com/#/query/398253
  2. “Can you get me a list of all parents and grandparents that are on the bounce or spam lists?”
    • click on the Bounce/Spam List Statuses query in the Emails section of the Communications homepage
    • pull in the Role field and set the criteria to be “is one of” and check off the Parent and Grandparent boxes
    • https://axiom.veracross.com/#/query/398260
  3. “I created a new Channel Category but don’t know where to find it or even remember what I named it. Can you give me a list of all channel categories with no channels in it so I can figure this out?”
  4. “Could you please get me a list of all scheduled emails that people are receiving that come from “Advancement Office”?”
  5. “I created a school-defined group for use in emailing, but didn’t add anyone, so it doesn’t seem to be appearing for me. Can you get me a list of all school-defined groups in the general category that don’t have any members in it?”
    • from the Communications homepage, click on the Find Groups query in the Emails section
    • click on the School Defined section, then click on the SCHOOL-DEFINED: General option
    • open up the query design and change the criteria for the Member Count field to be “is equal to” 0
    • https://axiom.veracross.com/#/query/398401
  6. “Would you be able to get me a list of all emails that weren’t delivered due to the system not delivering the email to an address that has previously bounced?”
    • start from the Bounced Emails query on the Communications homepage
    • clear the criteria in the Date Sent (Local) field
    • set the criteria in the Mailbox Response field to be “contains” Not delivering to previously bounced address
    • https://axiom.veracross.com/#/query/398413
  7. “I’d like to send an email to all students whose primary language is English, French, or Spanish and have repeated a grade. Can you set up that query for me? Because I want to email them, don’t include any students we don’t have an email for.”
    • start from the Find Groups query on the Communications homepage
    • click on the Students option, then STUDENTS again, and then the Students who hav Repeated a Grade Level option
    • pop-out the query on the General tab of the Group details screen using the Open Query button
    • set the criteria for the Email 1 field to be “is not blank”
    • pull in the Language field and set the criteria for it to be “is one of” then check off the English, French and Spanish options
    • https://axiom.veracross.com/#/query/398415
  8. “Hey, can you tell me how much it would cost us to send an SMS in each of the following countries: United States, Mexico and Canada?”
    • start from the Cost Per Country query on the Communications: SMS homepage
    • set the criteria for the Country field to be “is one of” and then type in “Canada; Mexico; United States” (NOTE: a semicolon in a field acts as an OR operator)
    • https://axiom.veracross.com/#/query/398424
  9. “We’re looking to send out a satisfaction survey for parents of other programs students from the 16-17 school year. Where would I go to find the email address for the distribution list?”
    • start from the Find Groups query on the Communications homepage
    • select the Other Programs option, then choose Other Programs by Year
    • from here note the email address in the Parent Email Distribution List column in the Other Programs in 16-17 row — this is the email for the list
    • https://axiom.veracross.com/#/results/398434
  10. “We’d like to send out a 10-year congratulations email to all current staff members who will have their 10th year of employment this year and have their emails in our database.”
    • start from the Find Groups query on the Communications homepage
    • click on the Staff/Faculty option, then click on Staff by Year Employed
    • select the Group name of the staff who were hired ten years ago (in this case, Staff employed in 09-10)
    • pop out the query on the General tab of the Group record using the Open Query button
    • set the criteria of the Email 1 to be “is not blank”
    • set the criteria of the Roles to be “does not contain” and type in “staff/fac – frmr”
    • https://axiom.veracross.com/#/query/398445

Independent Query Answers

  1. “Could you please pull in the Email 1 and the Email 2 for all individuals on the bounce/spam list? Also, could you only include people who have a Veracross username?”
    • start from the Bounce/Spam List Statuses query on the Communications homepage
    • pull in the Email 2 field to the query design
    • pull in the Username field and set the criteria to be “is not blank”
    • https://axiom.veracross.com/#/query/398469
  2. “We want to send out three different emails for men, women, and gender non-conforming for our class reunions this year (2019). Could you build me three queries for Alumni who Graduated with their graduation year ending in ‘9? One for each option?”
  3. “Can you get me a list of all system emails that have been sent out to confirm logistics request submissions?”
    • start from the Recent Sytem Emails query on the Communications homepage
    • clear the criteria defined in the Date Sent field
    • set the criteria for the Subject to be “begins with” Logistics Request Submission
    • https://axiom.veracross.com/#/query/398516
  4. “We’re looking to send out an email to all people who live in Tennessee asking to attend an event that we’re hosting there. Can you get me a query that would get me there? Also, could you exclude anyone with the Do Not Solicit flag?”
    • start from the Find Groups query on the Communications homepage
    • click on People, then PEOPLE by US State, and finally People who live in Tennessee
    • pop-out the query from the General tab of the Group record by clicking the Open Query button
    • set the criteria for the Email 1 to be “is not blank”
    • pull in the Do Not Solicit field and set the criteria to be “is” No
    • https://axiom.veracross.com/#/query/398524
print

Related Articles