Overview
This automation is intended to populate a Salesforce Marketing Cloud Data Extension with all the message open events from a Push Data Extract. It then queries the data to populate individual Phrasee experiment -opens Data Extensions which have been created automatically by the Phrasee integration.
Because this automation has steps to add queries that are created for each experiment it is suggested you create a test experiment in Phrasee and Start the Awesome to create some queries as place holders 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, create a Data Extension called phraseePushResults-dataextract. This name is important as the queries the Phrasee integration creates will use this name.
Create the Data Extension as follows. Take note of the primary key fields
Creating the automation.
Ideally this automation will run once a day and will have 11 steps including one minute waits between 7 activity steps.
Step 1 - Data Extract
Filename - PhraseePushResults_%%Year%%%%Month%%%%Day%%.zip
Step 3 - File Transfer
PhraseePushResults_%%Year%%%%Month%%%%Day%%.zip
Step 5 - File Transfer
Step 7 - Import File
Select the Data Extension created at the beginning of this article.
Please note: 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.