Set MBS Audience Goals via SFTP Scheduled Query

MBS Audience - Goals

 This process allows for the updating of our entity table for inquiry details using information about email engagement metrics, something that cannot be done via a regular rule. It uses a query configured to run overnight to export data to the SFTP server to be consumed through a source format. This allows for an overnight process that will update the student record when/if the student does engage with the specific mailing we are targeting.

Research

Learning Lab course “Quick Wins in Slate with Underscore” / Updating Person Statuses with Ping/Email Engagement Rules

Process

1. Create a query to identify students who engaged with the content in a mailing and differentiate which link they clicked.

   1. Final Query: https://tusmgp.admissions.tufts.edu/manage/query/build?id=92b215da-b58c-416b-bd70-239639b67d91

   2. The output includes the Person Ref ID (for matching) and a subquery export set to concatenate two subquery exports, of which the first link clicked (of the two options in the mailing that are relevant to our needs) will be the result.

      1. The query is built on the "messages" base, which returns one row for every message sent (literally each email sent). This limits the results to just one row per message/recipient, because we want to be updating the person record with this information and having more than one message/person will create duplicate entries in the inquiry.

      2. The query is then filtered to the specific mailing and to only messages where a "click" occurred (eliminating thousands of irrelevant results and hopefully making the query run lighter/faster).

      3. A subquery export is added to ultimately generate either "gpa" or "reapplicant" as the field value, which is done by configuring the subquery export to concatenate two embedded subquery exports. Those sub-subquery exports filter on if the link clicked contained specific text in the URL and then generate a literal if that formula returns true. By limiting the subquery export to 1 row, only the first "click" (either to gpa or reapplying) will be generated as the field value.

2. Configure this query to:

   1. Make sure to include a field for matching -- in our case Person Ref ID

   2. Set the Execution Option to be "retrieve only the new records since the query was last run"

   3. Set the "Schedule Export"

      1. Path: ../incoming/EngagementAnalytics/MBS_Goals_Update_%FT%T.txt    (this is the folder and name of the file that will be sent to the SFTP/source format)

      2. Delimiter: Coma(,)

      3. Overnight window is recommended--low ask of the server

      4. Set for every day.

      5. Leave as inactive until you are ready to have the files start sending to the SFTP/source format

3. Create a new source format to consume the data generated by the query:

   1. Give it a name that works for your process/what is happening

   2. Format = TXT

   3. Set to Update Only

   4. Set the format definition to XML: <layout b="," h="1" t="&quot;" />

   5. Set Import Automation to use the import path/mask that you created in the schedule export (i.e., EngagementAnalytics/MBS_Goals_Update_*.txt)

4. Leave the source format as active but the remapping as inactive. This will allow you to send a file to the source format to do the remapping without having it fully consumed by the source format.

5. Go back to the query and to send a sample file to the source format edit the query properties and limit rows to 10. This will ensure that only 10 records are pulled. Then "Run to SFTP" to run the results and send to the SFTP as configured.

6. Go to the Database and "Force Process Pickup" then "Force Process Import" to force Slate to look for the file in the SFTP server. (this may take a few minutes--it can also happen on its own, this is just to make it happen more quickly)