Student Pulse - Resources and Support

The most common use cases for this type of query are when you would like to create a new engagement based on an attribute or condition about a previous engagement or the response to it.

Examples of this are: 

• Engagement Response/No Response
• The Channel That the Engagement was Sent Through
• Time Since Engagement Was Sent
• Time Since Engagement Was Responded To
• Time Since Engagement Was Sent and Responded To/Not Responded To
• The Answer to a Question in a Survey or Form
• Call Outcome or Status

Preface - Referencing One Engagement or Referencing Multiple Engagements  

All of the examples given below are written such that only a single engagement will be referenced. 

The condition for including only one engagement is: 

engagement_id = '******'

Note that only a single engagement_id is provided and an = is used. This is what you will see in the examples below.

All of these examples can be adapted to instead include multiple engagements and still have all of the subsequent conditions applied in exactly the same way that they are described.

The condition for including multiple engagements is:

engagement_id IN ('******', '******', '******')

As many engagements as you require can be added to the list between the two brackets.

Simply substitute engagement_id = '******' for engagement_id IN ('******', '******', '******') in any of the examples below to change them from a single engagement filter to one that filters multiple engagements.

In fact, this concept applies to all fields in the example queries, so you can apply the same logic to any field that you'd like to specify multiple values for.

To show what I mean, here is the first example query (Engagement Response/No Response) in both forms:

Single Engagement

SELECT

*

FROM

engagements

WHERE

engagement_id = '******'

AND

response_finished = True

Multiple Engagements

SELECT

*

FROM

engagements

WHERE

engagement_id IN ('******', '******', '******')

AND

response_finished = True

Example Queries

Engagement Response/No Response

Choose between True and False as required.

SELECT

*

FROM

engagements

WHERE

engagement_id = '******'

AND

response_finished = True

The Channel That the Engagement was Sent Through

This example query will use the channel field to filter for all engagements that were sent via the selected channel, though it's unlikely that you'd ever use this field as the only parameter. This parameter should be combined with others covered in the article to get meaningful and relevant data returned by your queries.

Here is the list of available values that you can use in the channel field grouped by their overarching communication method:

Phone Call

• ad_hoc
• phone
• phone_incoming

SMS

• sms
• sms_2way
• sms_hub_bind
• sms_hub_outgoing

Email

• email
• email_incoming
• hub_email

Meetings

• face_to_face

Support Point

• support_point

Miscellaneous

• chat_incoming
• passive
• site_intercept

The following query would return all engagements that were phone call cards generated by a Pulse engagement. To use another channel, we'd simply substitute 'phone' with another channel's value.

SELECT

*

FROM

engagements

WHERE

channel = 'phone'

Time Since Engagement Was Sent

Variant 1 - Date Range

SELECT

*

FROM

engagements

WHERE

engagement_id = '******'

AND

send_date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'

Variant 2 - Before or After a Particular Date

Choose the operator that is required: >, <, >=, <=

SELECT

*

FROM

engagements

WHERE

engagement_id = '******'

AND

send_date > 'YYYY-MM-DD'

Variant 3 - Amount of Time That Has Passed

You can change the unit of time to any of the regular units of time you're familiar with. Most commonly, you'll use years, months, days, hours, or minutes. These can also be combined in one string to create something like '2 days 6 hours'. More information can be found here.

As above, you can also choose the operator that is required: >, <, >=, <=

The following example would return all engagements that were sent more than 2 days ago. If instead I wanted to return all engagements sent within the last two days, I'd simply change the > to <.

SELECT

*

FROM

engagements

WHERE

engagement_id = '******'

AND

send_date > now() - INTERVAL '2 days'

Time Since Engagement Was Responded To

Variant 1 - Date Range

SELECT

*

FROM

engagements

WHERE

engagement_id = '******'

AND

response_recorded_date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'

Variant 2 - Before or After a Particular Date

Choose the operator that is required: >, <, >=, <=

SELECT

*

FROM

engagements

WHERE

engagement_id = '******'

AND

response_recorded_date  > 'YYYY-MM-DD'

Variant 3 - Amount of Time That Has Passed

You can change the unit of time to any of the regular units of time you're familiar with. Most commonly, you'll use years, months, days, hours, or minutes. These can also be combined in one string to create something like '2 days 6 hours'. More information can be found here.

As above, you can also choose the operator that is required: >, <, >=, <=

The following example would return all engagements that were responded to more than 2 days ago. If instead I wanted to return all engagements responded to within the last two days, I'd simply change the > to <.

SELECT

*

FROM

engagements

WHERE

engagement_id = '******'

AND

response_recorded_date  > now() - INTERVAL '2 days'

Time Since Engagement Was Sent and Responded To/Not Responded To

This combines the queries that have been mentioned above. For more information on the different ways that the date and time conditions can be applied, refer to the previous section.

Choose between True and False as required, as covered above.

The following query would return all responses that had been sent more than 2 days ago and have not been responded to.

SELECT

*

FROM

engagements

WHERE

engagement_id = '******'

AND

send_date > now() - INTERVAL '2 days'

AND

response_finished = False

The Answer to a Question in a Survey or Form

There are a couple of columns that can be used to reference the answer to a question.

You will always require at least one of: 

• question_id
• export_column

And at least one of:

• value
• value_text

Depending on the type of question and the way that it stores answers in the database, the field containing the answer you'd like to reference will differ between value and value_text.

You will be able to determine which one to use by simply running a query that only specifies the export_column or question_id of the question you'd like to use to see which response columns contain the values you need to reference for your query.

Example data of values in each field for responses to a few questions that may help clear this up:

There is no right or wrong way to use these fields. As long as there is either a question_id or export_column and either a value or value_text specified in the query, the answers to any question can be retrieved. It is sometimes easier to use one or the other in each pair when one of the fields contains a longer value, or a variable value (in the case of free text or calculated/expression questions).

Here are two queries that reference the answers to two different questions above, each using a different combination of fields to do so:

Query 1

SELECT

*

FROM

engagement_responses

WHERE

question_id = 'question1'

AND

value = '1'

Query 2

SELECT

*

FROM

engagement_responses

WHERE

export_column = 'Q2_Item 7'

AND

value_text = 'Always (every day)'

Adding in Additional Parameters

You may choose to filter the answers to a certain question within a specific engagement, across all engagements of the same engagement type, for a particular engagement channel, across all responses to a survey or form regardless of the engagement or engagement type, etc.

This does require joining two tables (engagements and engagement_responses), but this is all taken care of for you in the sample queries below. Just be aware that you will see a prefix to the fields we're referencing, which tells the system which table to look into to find the field we're asking it to use.

The following queries will show you a separate example query for each parameter mentioned above, but you could combine them within a single query if needed.

Variant 1 - Answer to a Question Within a Specific Engagement

SELECT

*

FROM

engagements AS e

LEFT JOIN engagement_responses AS er ON (e.engagement_instance_id = er.engagement_instance_id)

WHERE

e.engagement_id = '******'

AND

er.export_column = 'Q2_Item 7'

AND

er.value_text = 'Always (every day)'

Variant 2 - Answer to a Question Across All Engagements of the Same Engagement Type 

SELECT

*

FROM

engagements AS e

LEFT JOIN engagement_responses AS er ON (e.engagement_instance_id = er.engagement_instance_id)

WHERE

e.engagement_type_name = '******'

AND

er.export_column = 'Q2_Item 7'

AND

er.value_text = 'Always (every day)'

Variant 3 - Answer to a Question Across All Engagements for a Particular Engagement Channel 

SELECT

*

FROM

engagements AS e

LEFT JOIN engagement_responses AS er ON (e.engagement_instance_id = er.engagement_instance_id)

WHERE

e.channel = '******'

AND

er.export_column = 'Q2_Item 7'

AND

er.value_text = 'Always (every day)'

Variant 4 - Answer to a Question Across All Engagements That Have Used a Particular Survey/Form

SELECT

*

FROM

engagements AS e

LEFT JOIN engagement_responses AS er ON (e.engagement_instance_id = er.engagement_instance_id)

WHERE

e.survey_id = '******'

AND

er.export_column = 'Q2_Item 7'

AND

er.value_text = 'Always (every day)'

Call Outcome or Status

These queries reference a completely different table than the two used for all other sections in this article, as data that is specific to phone engagements is required.

In order to make this easy to integrate with the other parameters that have been covered, there are two sets of the same example queries. The first set only points to the phone_engagements table, which makes them easier to interpret and understand. The second already has joins to the engagements and engagement_responses tables so that you can copy and paste other parameters into them easily.

There are two fields that can be referenced to filter phone engagements.

The 'status' field is always going to provide the current status of that Call Card (including Call Cards that haven't been actioned yet).

The 'last_contact_outcome' field will allow us to see the outcome of the most recent call attempt for that Call Card. If there has not been a call attempt for a Call Card, this field will be blank and there will be no value.

Here are the possible values that can be used in each field:

Status:

• pending
• call_back
• complete
• failed

Last Contact Outcome

• no_answer
• call_back
• completed
• expired
• cancelled
• invalid_number
• do_not_contact

Here are the last_contact_outcomes that make up each status:

Example Query Set 1 - Phone Engagements Table Only

The following query would return all Call Cards that have not had a call attempt.

SELECT

*

FROM

phone_engagements

WHERE

engagement_id = '******'

AND

status = 'pending'

The following query would return all Call Cards for which the contact requested to be called back at a later time during the most recent call attempt. This would not return Call Cards that have had an unsuccessful call attempt after the contact that had requested a call back. We would need a more advanced query to return these. 

SELECT

*

FROM

phone_engagements

WHERE

engagement_id = '******'

AND

last_contact_outcome = 'call_back'

Example Query Set 2 - Phone Engagements Table Joined to Engagements and Engagement Responses Tables

The following query would return all Call Cards that have not had a call attempt.

SELECT

*

FROM

engagements AS e

LEFT JOIN engagement_responses AS er ON (e.engagement_instance_id = er.engagement_instance_id)

LEFT JOIN phone_engagements AS pe ON (e.engagement_instance_id = pe.engagement_instance_id)

WHERE

e.engagement_id = '******'

AND

pe.status = 'pending'

The following query would return all Call Cards for which the contact requested to be called back at a later time during the most recent call attempt. This would not return Call Cards that have had an unsuccessful call attempt after the contact that had requested a call back. We would need a more advanced query to return these. 

SELECT

*

FROM

engagements AS e

LEFT JOIN engagement_responses AS er ON (e.engagement_instance_id = er.engagement_instance_id)

LEFT JOIN phone_engagements AS pe ON (e.engagement_instance_id = pe.engagement_instance_id)

WHERE

e.engagement_id = '******'

AND

pe.last_contact_outcome = 'call_back'