silverpop, silver pop, relational table, relational tables, import, tutorial, lesson, how to, email service provider, engage, silverpop engage, 3point5, 3point5.com, case study

By Jimmy Jacobson

3point5.com has a unique service that links retail sales professionals in the outdoor/sporting goods market with manufacturers in the same market. Manufacturers offer training programs hosted on 3point5.com and in return, retail professionals receive discounts on gear for passing the quizzes at the end of each on line training course.

Silverpop is a hosted Email Service Provider solution with some nice personalization features and some that are works in progress. We decided to go with Silverpop Engage because of their advertised Relational Table functionality. In the end this didn't work the way we expected but we were able to achieve our goal of extremely personalized emails with a selection of offers based on the user's membership in different manufacturer programs. This article will be heavier on the technical details, being written by a developer and not a marketing guru.

We discovered some important rules about working with Engage's relational table features through trial and error and then confirming via support ticket requests through Silverpop's help desk.

1. You cannot update a relational table using a recurring update. You have to do it manually anytime you want to update a relational table stored in Engage.

2. You can only associate a relational table with a list, not with another relational table. You can associate as many relational tables with one list as you need as long as you provide the reference columns in the list to map the tables to.

3. Mailings that use a list generated by a query and not the actual list do not have access to the parent list's relational tables for personalization fields inside of the mailing. This is a big item to keep in mind when designing your lists. You can use a relational table associated with a list for either querying the list, or personalizing the mailing, but not both.

Our goal at 3point5.com was to send a personalized welcome email to each of our users a set number of days after they had signed up on our site. The personal content was a list of the content specific to that user on the 3point5.com website for training and purchasing. Causing one member to see something like:

While another might see something like this:

This list could potentially feature a unique combination of three of the 120+ brands signed up with 3point5.com depending on the user's access. Silverpop offers a few ways to customize the content of email templates. The basic method involves rule sets. I'm not going to go into detail about them, but rule sets would not work for us because as of this writing we have 120 brands available and climbing quickly. Using a rule set would require us to constantly be updating a huge if/then/else structure to accommodate new brands and this would quickly become an intractable solution.

We decided to use the relational table functionality of Engage. After discovering the limitations mentioned above through some trial and error and support from Silverpop's customer support department, we settled on the following design.

The brands table contained one row for each brand available to retailers in our system. The brands were sorted on our side by an algorithm before the list was written to a CSV file so we could make sure the 3 most relevant brands to a user were always presented. The list and table were mapped together using a foreign key field. This was accomplished using the "associate table" link in Engage when viewing the list. A relational table can be imported or created using the same form as a list, just with a different drop down option selected.

The goal of having personalized brands presented to the user was done using the "Insert Relational Table Data" button while editing the mailing template body. After clicking the button for this option, a pop up window appeared to choose a table to draw the data from. Then the standard Engage criteria builder is used to build the query used to filter the data from the table. For our schema we chose the foreign key field on the left criteria equals the primary key field on the right. Engage will then show you a screen with sample code for displaying the relational table data in your email. Engage then generated sample HTML to display a table, which we replaced with our template-ized HTML. We set the max number of results to 3 and set the default for no results to nothing, so if a user has no brands they will not see anything out of the ordinary in the email.

Finally, we set up this mailing template as part of a campaign with a custom trigger. We used the registration date as the trigger and set the email to send to users with a specific registration date that was earlier than the current date, every day. On our side, we have a automated job that pushes out a CSV file daily with all the new users in the previous 24 hour period. This file gets pushed to Silverpop daily and an automated job in Engage updates the list. The result is, after registration, all our new users receive a personalized email reminding them of the specific brands they can choose for training and great discounts.

Relational tables inside of Silverpop Engage offer a lot of power, but have some pretty serious limitations as well. I am disappointed by the lack of automatic updates via jobs for relational tables through the Engage platform. It is possible to update Relational Tables via Silverpop's API, according to their documentation. It is also important to remember that you can't use a relational table associated with a list for both querying the list and personalizing a mailing template associated with a query on that list. It has to be one or the other. Keeping these constraints in mind while designing our lists and relational tables, we were able to achieve our goal as first conceived and have a very good open rate with this particular email campaign.

Silverpop Help Pages

• A Silverpop Relational Table Case Study
• Tutorial: How to import a new list in Silverpop Engage Marketer
• Using Silverpop Relational Tables