Altru Post-Visit Survey Query: Step by Step
← Back to Blog

How to Build the Altru Query for an Automatic Post-Visit Survey Email

July 18, 2020

A post-visit survey email needs an Altru query that returns yesterday’s visitors and no one who has already been emailed. Build it as an ad-hoc Sales orders query with three output fields (first name, email address, sales order record ID), filter on Scan date is yesterday, and exclude any order that already has a Post survey email note. This video builds the query; how to send an automatic post-visit survey email connects it to an Email Designer template.

Steps

  1. Create the note type (0:53). Go to Administration › Code tables, open Sales order note type (it was on page 7 of the list in the video) and add a new entry, Post survey email (1:13). The query uses it to exclude anyone already emailed. More detail: how to create a custom note type with Altru.
  2. Start the query (1:34). Go to Analysis › Information library, click Add an ad-hoc query, choose Sales orders and click OK. Remove the default Lookup ID column.
  3. Add three output fields (2:03): the constituent’s first name, email address, and the sales order record ID.
  4. Filter to people you can email (2:59). Under Constituent, set Is individual equals Yes, require the primary email address (Yes), and set Email address is not blank.
  5. Filter to yesterday’s visitors (3:45). Go to Sales order item › Sales order item ticket › Tickets and set Scan date is yesterday.
  6. Exclude anyone already emailed (5:28). Under Notes, add Note type, choose Not one of, select Post survey email, and check Only return records that do not match any of the selected values. This checkbox is important.
  7. Preview (6:16). You should see first name, email address and sales record ID. In the video the preview is empty because no tickets were scanned the day before (a weekend).
  8. Name and save (6:36). A clear name such as Post visit survey – Email Designer tells your team what it feeds. Click Save and close.
  9. Connect it to an email (6:57). Add the query to Email Designer and set the template’s note type to the same Post survey email type.

Common questions

Why filter on scan date instead of the event date?

Scan date means the person actually came (4:25). Someone who bought a ticket but didn’t show up shouldn’t get a post-visit survey.

What if we don’t scan tickets?

Use Programs › Program events › Start date is yesterday instead (4:09).

Can I survey people who visited a week ago instead of yesterday?

Yes. The video uses yesterday, but you can set the date filter to a different window.

Why do I need the note type?

When the email sends, a Post survey email note is attached, and the query’s Not one of filter drops that order from the list, so nobody is emailed twice.

Why is my preview empty?

No tickets were scanned yesterday, or everyone scanned has already been emailed.

Video transcript

Hey, this is Rick with Social Good Software. In this short demo I’ll show you how to set up an email so you can send a post-visit survey with Email Designer. More specifically, today we’re creating the query, and we’ll dig into combining the query with an actual email in a separate video. So let’s jump right in. We’ll create this query from scratch.

Before we create the query, we’ll create a new note type. We have a video that explains how to create note types in more detail, but I’ll show you quickly how to create one through the Administration interface. I go to Administration, then Code tables, then page seven, and look for the one called Sales order note type. I click into it, and you can see I have three sales order note types. I’ll create a new one called “Post survey email”. We need this note type so we can structure the query to exclude anyone who has a post survey email attached to them.

Now that’s out of the way, I go to Analysis, Information library, and create a new query from scratch. I click Add an ad-hoc query, and because this is about sales orders, I create a Sales orders query that will hold the details for our post survey email. I click Sales orders, then OK, and get rid of the lookup ID because we won’t need it. We need three things for this email: the person’s first name, their email address and the sales order record. Under Constituent I find their first name, then their email address, then the sales record ID. That’s essentially all we need for the email.

Now we’ll add a couple of restrictions to make sure we’re sending this email to people after their visit. More specifically, we’re targeting people who visited yesterday. You can set this up differently, for example people who visited a week ago, but we’ll focus on everyone who visited yesterday. Under Constituent we say Is individual equals Yes. Then for their email address we want the primary email address, so we say Yes to that too. And we make sure the email address is not blank.

Next we grab the scan date of their visit. You can do this with the scan date of when the ticket was scanned, or with the program event date, but we’ll use the scan date. I go into Sales order item, Sales order item ticket, then Tickets, and choose Scan date is yesterday. That gives us all the tickets that were scanned in yesterday. If you don’t do ticket scanning at your organization, you can use the ticket date instead: go to Programs, Program events, and set Start date is yesterday. In our case we want scan date, because we only want to send post-visit surveys to people who actually came and visited the campus. So I delete the start-date filter and keep Scan date is yesterday. If someone bought a ticket but didn’t show up, we shouldn’t send them a post-visit survey. Scan date is a quick way to say they did visit, and setting it to yesterday gives us a list of only the people who attended yesterday.

The last part: the moment the email is sent, we attach a note so this query knows it no longer needs to send that email. So we head over to Notes, where you’ll see Note type. Move it over, choose Not one of, then select Post survey email. Make sure you check this box, it’s really important: Only return records that do not match any of the selected values. Then click OK. Now you have the data for the post-visit survey email, and the moment the email goes out, the order has the post survey email note, so we don’t send it again.

If you preview, you won’t see anything here because it’s the weekend, but normally you’ll see a list of constituents with first name, email address and sales record ID. That’s all there is to setting up the query. Now give it a name. I’ll call it “Post visit survey – Email Designer” so we know what it’s for, and click Save and close.

All you have to do now is add this query to Email Designer and set up your email, and when you do, make sure you select the Post survey email note type. We have a separate video on how to do that. If you have any questions about this process, feel free to reach out. We’re always happy to help.

Continue reading

How to Track Instructor Activity for Scheduled Programs in Altru

How to Track Instructor Activity for Scheduled Programs in Altru

“How many classes did Maria teach last spring, and which ones?” It sounds like a question Altru should answer in ten seconds. In practice, many education departments end up digging through calendars, email threads, and a payroll spreadsheet to piece it together, because the instructor was never recorded anywhere a query could find. Altru is

How to Turn a Group Sales Overpayment Into a Donation in Altru

How to Turn a Group Sales Overpayment Into a Donation in Altru

A school books a field trip for 60 students, sends a check for the full quote, and then 48 kids show up. The teacher says, “Keep the difference, it’s a gift.” Now you have a group sales order that is overpaid, money that is really a donation, and a deposit that has to balance. The

Can a Spouse Be a Fundraiser in Altru? Solicitor Credit for Couples

Can a Spouse Be a Fundraiser in Altru? Solicitor Credit for Couples

It happens more often than you’d expect. A board member and her husband both care about your capital campaign. She asks a friend for a gift, but it was really his introduction and his follow-up phone call that closed it. Development wants him credited as the fundraiser on that gift. Then someone at the desk

Social Good platform

See it in action

Get started with Social Good

Book a demo to see Social Good Software in action for Altru.

Book a Demo
Social Good platform

See Social Good in action

Tell us a bit about your organization and we’ll take you straight to short demo walkthroughs of the software.

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.