Creating an Automation for Unsubscribe using Automation Studio- Marketing Cloud

Question

I am trying to create an automation process to filter out my least active subscribers and then unsubscribe them from my communications (my subscribers are saved as a salesforce data extension). I was able to perform the filter activity and send it to a new data extension. I am currently trying to create a SQL Query to change the contact id on this new data extension to 0 and then perform an INNER JOIN with the original data extension to essentially delete these subscribers from my data. The SQL query seems to be the problem:

SELECT [Contact ID]
FROM ATest
SET [Contact ID] = '0'

This was my first query but it gives me an error for invalid syntax near 'SET' (ATest being my data extension name). My 2nd query was gonna be the JOIN clause.

This is my 2nd week using Marketing Cloud and I just learned SQL so I welcome any better ideas on how to solve this or other ways to do this more easily.

Thank you

Answer 1

You cannot use SET statements within Marketing Cloud, so your query above is not going to work. If your end goal is to unsubscribe these people from receiving marketing communications, then you are going to have to use a different approach.

Try something like this for your automation:

Step 1: Execute SQL Query to pull the email addresses you want to unsub from your SF data extension into a new data extension. Here is the query you would want to run for that:

SELECT EmailAddress, 'Unsubscribed' as Status FROM SF_DE WHERE some_criteria = whatever_value

Step 2: Execute Data Extract activity to extract that data extension's contents.

Step 3: Run File Transfer Activity to transfer that file name out of the Safehouse.

Step 4: Run Import Activity to import your file from the safehouse and into the All Subscribers list with an add/update data action in order to have these people be unsubscribed from receiving communications in SFMC.

Here are some relevant links for some background on these activities:

SQL Activity

Data Extension Extract Activity

File Transfer Activity

Import File Activity