Solve Common SFMC Problems with AMPScript (Using Automation Studio) - Part 3

 I've been helping a client migrate from their existing ESP to Salesforce Marketing Cloud. They have a fair number of people who unsubscribe from their communications and resubscribe later. Their old platform handled this well, but due to the data architecture this process is cumbersome with SFMC. Not to mention that this is complicated by running both systems side by side for IP warming and to "keep the lights on". But through AMPScript and running it as a script in automation studio it's a piece of cake. I'm sure you can find a use for this, or at least say, "Why didn't I think of that"

Here's the problem. The old service's information was brought into SFMC, and any subscriber who was marked as unsubscribed was put into a suppression data extension. Only active subscribers were put into the "subscription" data extension. But some subscribers go to the old emails and reactivate their subscription to the the client's communications. That works flawlessly, because automation studio handles the updates, but what about the gap between when the first historical data was dumped into SFMC and the go live date? 

Simply stated they want to update the subscriber status of the delta data buy manually uploading a file  to the Resubs data extension. Sure you could do this through a query, but that can be complicated because you may have missing data points, but with AMPScript it becomes easy!

First, write your AMPScript as a code snippet content block in content builder. I've included the AMPScript I'm using here which is the rowcount at the top and then the update or delete data extension function. Don't worry, delete data extension sounds like it will delete the whole data extension, but it is only deleting the record out of the data extension.

%%[var @rows, @row, @rowCount, @updated, @i 
set @ updated = "FALSE"
set @rows = LookupRows("Resubs", "updated", @updated)
set @rowCount = rowCount(@rows)

if @rowcount > 0 then
for @i = 1 to @rowCount do

var @resub, @contactid, @status, @optinsource, @optinmethod, @update
set @row = row(@rows, @i)
set @resubs = field(@row, "email_address")
set @contactid = field(@row, "contact_id")

set @status = "subscribed"
set @optinsource = "Resubs"
set @optinmethod = "Resubs"
set @update = "TRUE"

DeleteDE("Suppression", "email_address", @resub)
UpdateDE("Subscription","1", "contact_id", @contactid, "subscription_status", @status)
UpdateDE("Subscription","1", "contact_id",@contactid, "opt_in_source_latest", @optinsource)
UpdateDE("Resubs","1", "email_address", @resub, "updated", @update)

next @i

endif
]%%

Basically, this AMPScript is telling SFMC to look at the Resubs data extension for a count of each unique record that has an updated status of "FALSE". Meaning that they haven't gone through this process yet. The reason this was included was from an audit perspective. Then the AMPScript defines which rows are your matching criteria to update the data extensions that need to be updated, and what additional information needs to be added to those target data extensions. And it tells SFMC to do this for the first record, then the second record, then the third record and so on.

When you save the AMPScript code snippet block, record the ID number of the newly built content block. You'll need it when you go to Automation Studio to build your script function.

In Automation Studio, set up a new script. You'll use this as your script text:

<script runat="server">
var ampscriptcode = Platform.Function.ContentBlockByID("XXXX");
</script>

Replace the XXXX with the actual content block ID from SFMC, and save your activity. Then you can build an automation to run the script.

What my script does is it looks at the Resubs list for every email address that has an updated status of FALSE. It matches that record in the Suppression data extension and removes that address from the data extension. Then it updates the main subscription data extension to change the subscriber status to "subscribed", and changes the opt in source and opt in method. The final step is to change the Resubs updated field to TRUE. Then do it all over again, until SFMC runs out of FALSE records.

This particular example deals with my clients specific problem, the focus of this but the main point is that you can write AMPScript and that AMPScript doesn't have to be executed via an email send or by your subscriber landing on a cloud page. Using AMPScript in this fashion has actually given you another way to manage your own data without having to get your DBA or IT team involved.

You can use AMPScript to provide a better email build experience for your marketing team or use it to provide a better form experience for your customers and potential customers.