Email List Compliance Automation
Concept · An automation to ensure Nielsen doesn't email opted-out users
Due to legacy infrastructure with Salesforce, our main email automation partner, we had to manually update a CSV file of opted-out users on over 35 Salesforce FTP servers, once per week.
I automated the process to (1) create a csv file from a google spreadsheet, (2) send the file to over 35 servers, and (3) to report back, via email, on the status of the updates.
I used Google Cloud's Cloud Functions, Bucket Storage, and Pub/Sub Service to automate the process.
The diagram below illustrates this process. The FTP Server List, is a list that contains the usernames of all the servers we want to update. The Suppression List itself is the google spreadsheet with all the email addresses that have opted out of emails.
On a scheduled basis, around noon each day, the Suppression List is converted into a CSV file and uploaded to a Google Cloud Bucket. When the Cloud bucket is updated, it triggers a Cloud Function that retrieves the FTP Server List of server usernames and creates a pub/sub message for each server username. Each pub/sub message then triggers a second Cloud Function that is responsible for retrieving the CSV file from the Cloud Bucket and transfers the file, via SFTP, into each individual server.
At the end, the automation checks to see if there are any SFTP or Google Cloud errors, and then attempts to update any errored out servers until it's successful.
- Google Cloud
Step 1 - Uploading a google spreadsheet to a Google Cloud Bucket
Step 2 - Create Pub/Sub Messages Based on Our FTP Servers List
Step 3 - SFTP the CSV File to each FTP server and report errors