In summary, this post will teach you how to optimize the hand-off of email content from your content creation team and create automation that will:
- Send email content from a Google Sheet to Zapier
- Create an email program, email asset, and smart campaigns for this email
- Upload images from Dropbox to the Design Studio using the API so that they can be used in your email
- Populate your email by transferring the content from your Google Sheet to email variables using the API
- Create dynamic content sections and populate these sections with the content from your Google Sheet using the API
N.B. If it is your first time using the API or you need a quick refresher then check out the Quick-Start Guide to the API to see how to make your first API requests in Postman before transitioning to making requests in code or in the Zapier automation tool.
Dynamic Content Overview
Dynamic content refers to the ability to swap different content into your emails based on which customer segment the person viewing your email is in. For example, if you segment your customer database by which product they are interested in e.g. SMS, Calls, Data, then a person in the SMS segment will see SMS content and so on.
Consequently, the first step toward using dynamic content is by creating a Segmentation and defining the smart list criteria for each constituent segment.
The next step is to work with your content creation team to get the different content that will be sent to each segment so that you can put this content into your emails!
Collecting Email Content using Google Sheet Templates
Month YYYY Tab
As marketing operations professionals we want to make it super simple to get the content for our emails from the content creation team. This is why creating email templates in Google Sheets like this Marketing Newsletter Template is such a great idea because it lets the content team know what copy they need to fill out and where they need to fill it out, what sizes images need to be, and where they should provide links for images used in the email.
When it comes time to make the newsletter for the upcoming month the content creation team can simply duplicate the “Month YYYY” tab used for the previous newsletter and replace the content in this newly duplicated sheet with the content for the upcoming newsletter. By comparing the rows in this tab to the “Newsletter Image Preview” (scroll all the way to the right) you will notice that there is a row for each section in the email allowing the content creation team to fill out all the titles, copy, destination URLs, and Dropbox image links needed for each section.
In order to upload images from Dropbox to the Design Studio using the API (see Steps 8 and 9 below) it is important that the correct link for the Dropbox image is provided. To get this link in Dropbox, right click on the image’s row from a list view containing the desired image, select “Copy Link Address”, paste this value into the “Design Image Links” field in the Google Sheet. This link should have
?role=work at the end. The Dropbox Path For REST API YouTube video will demonstrate the steps outlined above.
In this newsletter example the “Lead Content”, “Card 1”, “Card 2”, and “Card 3” sections are the same for each customer but the two “Other News” sections are dynamic and will show the customer either SIP Trunking (SIP), SMS, or Call Control (CC) content depending on which segment they are in.
Back End Tab
Once the marketing operations team has the email content in a structured way within the “Month YYYY” tab then we can map each of the content sections, whether text, destination url, or dropbox image link, to their corresponding email variables within the email template using the “Back End” tab.
If the variable is a global email variable then you do not need to specify a module id along with the variable id. However, if the variable is a local email variable then you need to specify both the variable id and the module id within which the variable is housed so that you can update it with the API. For more information on email variables see the documentation here.
If you want to see how to set up your emails to use variables then copy the code from the marketing_newsletter_template.html file into a new email template.
All the content from the “Month YYYY” tab is pulled into the “Back End” tab automatically using
vlookup formulas so don’t worry there is no need to do this manually!
You might have noticed the “mkto_link#” placeholders in the “Value” column. These placeholders will be replaced with URLs to the images hosted in the Design Studio (Step 10) after the images linked in the “Dropbox” column have been uploaded to Marketo using the API (Step 8 and 9).
Finally, all the values for the email variables, the Dropbox image links, and the dynamic content sections are concatenated together using the
* character so that when the big, shiny, green “Submit” button is pressed they are sent to the “Submissions” tab triggering the Zapier automation.
Importing Data into Zapier & Creating an Email Program
1. Once the new row has been created in the “Submissions” tab, this will trigger the “New Spreadsheet Row” trigger in Zapier and then cause the 11 subsequent “Code by Zapier” Python actions to run in succession.
2. Get the access token needed as authentication to make all subsequent requests. Since there are a lot of subsequent requests to the API and the token life is limited this step also checks to make sure that the remaining token life is greater than 120 seconds. If the remaining token life is less than 120 secs then the Python code will wait until the current token expires and then request a new token, which will always have a lifespan of 3600 seconds (get_marketo_access_token.py).
3. Either get the ID of the existing “Newsletter YYYY” parent folder or create a new “Newsletter YYYY” folder which will house all the monthly newsletters for this year (get_parent_folder.py).
4. Since each marketing newsletter program follows the same naming convention “Email_YYYY_MM_MonthName_Newsletter” we can use this knowledge to find the ID of last month’s program (get_last_program.py).
5. Once we have the ID of last month’s program, we can clone it to use as a basis for this month’s program (clone_program.py).
6. While the API request to clone a program does return the ID of the new program (step 5), I found that while the step succeeded to create the new program it often did not send the successful response to Zapier within 10 seconds. Since Zapier has a 10 second timeout limit on all actions this meant that Step 5 would be marked as failed and all the subsequent steps in the zap that need the program id failed.
Consequently, I introduced a fail-safe action that will search for the newly created program by name (since we know from Step 5 what we called it) and then set up all subsequent actions to pull the program id from this Step 6 instead of Step 5 This means that even if Step 5 is marked as failed the rest of the zap can proceed by using the program ID obtained from Step 6 (get_program_id.py).
Creating an Email & Populating Email Variables
7. You might be wondering why we need to create a new email in Step 7 instead of modifying the email that already exists from cloning last month’s newsletter program. The reason we need to create a new email, is that emails used in A/B tests cannot be found using the API.
This means that if last month’s email used an A/B test then the email in this month’s program will also have an A/B test setup (since we cloned last month’s program and therefore email too) and therefore we would not be able to find or modify it using the API.
Therefore, Step 7 creates a new email i.e. one without an A/B test, that can be populated with the API and the email cloned from last month’s program can then later be deleted manually (create_email.py).
8. Use the Dropbox asset paths for each image provided by the content creation team in the Google Sheet to get a link for each image that can then be used to upload this image straight from Dropbox to Marketo (get_temp_dropbox_links.py).
If you want more information on uploading assets straight from Dropbox to the Design Studio then check out the Uploading to the Design Studio via API post.
9. Upload the images straight from Dropbox to Marketo using the links for each image obtained from Step 8 (upload_images.py).
10. The “mtko_link#” placeholders in the “Variable Values” concatenated string are replaced with the URLs of the images that were uploaded to the Design Studio in Step 9. Next, the “Variable Values” are matched to their corresponding email variable id and module id combinations so that these variables can have their values updated in the email (update_email_variables.py).
Creating & Populating Dynamic Content Sections
11. The two “Other News” sections at the bottom of the email are turned into dynamic content sections (GitHub file).
12. The two dynamic content sections created in Step 11 are then populated with the content from the Google Sheet for each segment within the segmentation e.g. SMS, Elastic SIP Trunking, Call Control, and Default. The content from the Default segment is set up to alternate between the content from the other 3 segments depending on what month it is (GitHub file).
Et Voilà! Once all the API requests have been made, you will have a new email program created in your yearly newsletters folder with an email asset that has been populated with all the content from the Google Sheet.
And you know what the best part is?
You didn’t even have to lift a finger!
Here are some follow-up posts that will hopefully inspire some of your own ideas for automation with the API:
- Merging Leads in Bulk using the API
- Uploading to the Design Studio via API
- Updating Period Costs in Bulk Via API
Also if you are not familiar with the API then I would recommend starting with the Quick-Start Guide to the API to see how to make your first requests in Postman before transitioning to making requests in code or in the Zapier automation tool.
The content in this blog has been reviewed by the Community Manager to ensure that it is following Marketing Nation Community guidelines. If you have concerns or questions, please reach out to @Jon_Chen or comment down below.