Overview
This automation is intended to populate a Salesforce Marketing Cloud (SFMC) data extension with all the message open events from a push data extract. It then queries the data to populate individual Jacquard experiment -opens data extensions which have been created automatically by the Jacquard integration.
Because this automation has steps to add queries that are created for each experiment, it is suggested you create a test experiment in Jacquard and click Start optimisation to create some queries as placeholders for now.
For every Automation Studio activity or data extension you manually create, ensure the name and the external key are named the same.
To start, you should have already created a data extension called phraseePushResults-dataextract. This name is important as the queries the Jacquard integration creates will use this name.
Creating the automation
Ideally, this automation will run once a day and will have 11 steps, including one-minute Waits between seven activity steps.
Step 1 - Data extract
File Naming Pattern: PhraseePushResults_%%Year%%%%Month%%%%Day%%.zip
Step 3 - File transfer (move)
File Naming Pattern: PhraseePushResults_%%Year%%%%Month%%%%Day%%.zip
Step 5 - File transfer (unzip)
Step 7 - Import file
Select the phraseePushResults-dataextract data extension created you created.
This data extension can get quite large over a period of time and should be cleared out on a schedule.
Step 9 - SQL query
Add the _AR query that was created for the experiment. You will add one query per experiment to this step in the automation.
Step 11 - SQL Query
Add the _opens_query query that was created for the experiment. As mentioned before, multiple experiment queries can be added to this step.
Once this is all configured, add a daily schedule to the automation and activate it.