Glen Mazza's Weblog

Salesforce Marketing Cloud's Automation Studio is available for placing email interaction data into CSV files and having those files sent to an external location. Typical interaction data include sends, opens, within-email clicks, and bounces (full list) which are queryable via Data Views provided by MC. Once sent to the FTP server, the files can be downloaded and subsequently fed into other systems for further analysis. In this article, I'll be showing how to export clicks data. (For greater depth, Shibu Abraham offers several videos on various Automation Studio activities.)

Sending any data to the SFTP server usually involves a three-step automation:

• A SQL Query Activity to query from the data view and place the data in a data extension. There may be multiple SQL Query Activities if joining data from multiple sources.

• A Data Extract Activity to place the data into a file

• A File Transfer Activity to move the file to the MC SFTP server. This server can be accessed using SFTP tools such as FileZilla or, with Java, programmatically via JCraft JSch (older) or Apache MINA SSHD.

To export Clicks data:

1. An FTP location will need to be set up to receive the extracted files. There are multiple FTP server options supported by MC, but the Enhanced FTP option, using MC's own SFTP server, seems the most common and easiest to set up. Note though it will retain files for only a limited period of time (21 days as of this writing). See the Marketing Cloud Documentation for more information.

2. Create a data extension (DE) via either Email Studio or Contact Studio with names equal to the columns desired to retrieve from the Clicks view. It is this DE that will be holding the results of the SQL query activity. Unneeded view fields can just be omitted in the data extension definition. For any data extension field that is not guaranteed to have a value per the view definition, be sure to mark as "Nullable".

3. In Automation Studio, the three activities can be defined first and then attached to a new Automation, or the Automation created first and within the automation the activities created. (Be careful that activities can be shared between automations, so changing the activity definition within one automation will affect it for all automations.) For the SQL Query activity, one SQL query to run for clicks could be: select * from _Click WHERE DATEDIFF(day, EventDate, GetDate()) = 1 to obtain the prior day's clicks. When creating this activity be sure to specify the DE from the previous step to hold the query results. The SQL Query activity allows for specifying how to populate the DE, appending or overwriting, the latter is probably best in this case, so at each daily run there will be an extract of just the prior day's clicks.

4. Next, in the second step in the automation, create a Data Extract Activity, which generates a file from the prior populated DE and stores it in an MC internal area they call the "Safehouse". Substitution strings can be used to include the run date in the file name, e.g. prior_day_clicks_%%Year%%%%Month%%%%Day%%.csv. Ensure the filename chosen will generate unique filenames for each running of the automation, else the files will be overwritten (e.g., an automation running hourly but just having the day in the name will repeatedly overwrite the file.)

5. Create a File Transfer Activity in the third step, specifying the Safehouse file name chosen in the previous step and the FTP server to send it to.

6. Perhaps best to run the SQL query activity first manually to confirm the DE is being populated as desired (its contents can be viewed and exported in Email Studio.) Then, in Automation Studio, do a "run once" of all three steps and view the file on the SFTP server to confirm everything working as expected. Finally, the automation can then be configured to run daily or as otherwise desired, making a new file available on the FTP server each time.