28 votes
Accepted

Trying to automate contact deletion with SSJS

Updated March 2020 to accomodate for v2 authorisation and tenant specific endpoints. Please see the example of how I use this for bulk deletion. You should use the REST API, using the ...
Lukas Lunow's user avatar
  • 21.1k
14 votes

Making a REST API call from a Script Activity in Automation Studio

I've done a REST API call from a Script Activity. Here's an example: <script type="javascript" runat="server"> Platform.Load("core","1.1.5"); var DE = "Contacts_To_Delete"; var logDE = "...
Adam Spriggs's user avatar
  • 30.4k
11 votes

Query Activity system error in Automation Studio

Typically if Query Activities fail, it's one of these 6 things: Primary key violation -- your query results in duplicate rows not allowed by the primary key Inserting a null value into a non-nullable ...
Adam Spriggs's user avatar
  • 30.4k
11 votes

Subscribers currently in a Journey

Update May 2021: Due to recent changes in the internal table structure of Marketing Cloud Journey Builder, it is no longer possible to request this custom data view. I will provide an update as soon ...
Lukas Lunow's user avatar
  • 21.1k
11 votes
Accepted

Optimizing SQL for large volumes

My preferred method is to leverage the hidden, pseudo-identity _customObjectKey column to break up queries into manageable chunks. As long as your scheme can accommodate the overwrite-update/append-...
Adam Spriggs's user avatar
  • 30.4k
10 votes
Accepted

SFMC Query Timing Out

I'd recommend combining this into a single query. Also converting to the date data type will is less costly than converting all of the dates to strings. Something like this: SELECT ds....
Adam Spriggs's user avatar
  • 30.4k
9 votes

Query MobileConnect Lists

The reason for this not working is because there is currently a bug which does not update the MobileList names in the Marketing Cloud backend DB if they are renamed at any point. I had renamed my ...
Deployment Failure's user avatar
9 votes
Accepted

Delete Subscriber records in Salesforce Marketing Cloud

First, you need to create a sendable data extension, which at least should contain your subscriber key. This will be populated by querying Subscribers Data View, with your criteria defined in the SQL ...
Lukas Lunow's user avatar
  • 21.1k
9 votes
Accepted

How to use string_agg/group_concat in salesforce marketing cloud

EDIT - 8/9/2023 As mentioned by @charliefay in his comment, STRINGG_AGG() is now accepted inside SFMC Neither of these functions are available inside of SFMC Query Activities. SQL in SFMC is based on ...
Gortonington's user avatar
  • 30.5k
8 votes

Can Automation studio be scheduled less than once a hour

Yes. You can also use Script Activities to alternately execute two Automations. Automation1 (whatever automation step) Wait - x minutes Script Activity - Run Automation2 Automation2 (whatever ...
Adam Spriggs's user avatar
  • 30.4k
8 votes
Accepted

How to move data from a list to a data extension

Tony is correct. Just to add to this, for Enterprise 2.0 accounts (which current editions fall under) you can access the _EnterpriseAttribute data view to return the custom profile attributes for the ...
Macca's user avatar
  • 8,196
8 votes
Accepted

How to identify object type using contactkey in marketing cloud?

Contact ID's are starting with 003 and Lead ID's are starting with 00Q. Example Contact: 0030X00002EYLjkAYG Example Lead: 00Q0X00000YRg0VNGH This is probably the easiest way to differentiate them ...
Rain's user avatar
  • 5,725
8 votes

Import to Data Extension even if the CSV file has no rows (and has only header)

In order to do this, you will need to contact SFMC Support and request a business rule "Allow Blank File Processing" to be enabled on your instance. This allows the import of empty files without ...
Rain's user avatar
  • 5,725
7 votes

Date manipulation Function SSJS

You can do date math like you would in regular JS. I typically use a simple function to format the date: <script type="text/javascript" runat="server"> Platform.Load("...
Adam Spriggs's user avatar
  • 30.4k
7 votes
Accepted

SSJS Rows.Retrieve not returning any rows

I believe the issue relates to the "DataExtension.Init" part of your code. var stagingDE = DataExtension.Init('Third_Party_Email_Acquisition_Stagingv2'); This is because if you aren't retrieving any ...
Christopher Carswell's user avatar
7 votes
Accepted

Marketing Cloud Data Extract into the correct File Encoding format

I did a file export from a dataextension like this: DataExtract Activity1: Pattern: TEST_SAFEHOUSE_%%Day%%%%Month%%%%Year%%.csv ExtractType: Data Extension Extract Column Delimiter: ; Has Column ...
Johannes Schapdick's user avatar
7 votes
Accepted

Replace semi-colon not allowed in SQL - alternatives?

There is, but it is not intuitive. You will need to use the CHAR() function in SQL to do this as SFMC SQL Validator will not allow the reserved char ; inside of your query no matter if it is escaped ...
Gortonington's user avatar
  • 30.5k
7 votes
Accepted

Import contacts SFMC via API vs FTP

This depends on how often these contacts are updated. You can easily have 1M+ contacts, but only 10.000 of them have monthly activity, keeping the number of data update calls to SFMC at a minimum. ...
Lukas Lunow's user avatar
  • 21.1k
7 votes
Accepted

Do not Transfer File when Data Extension is empty!

Yes, you need to add a Verification activity before the File Transfer activity. Avoid unintended outcomes by verifying the data used in an Automation Studio automation. Use the Verification ...
zuzannamj's user avatar
  • 14.4k
7 votes
Accepted

What are the various reasons for "Not Sent" emails in journey builder?

Held and unsubscribed are both statuses subscribers can have in Marketing Cloud. Held: Three or more bounces, soft or hard, are received for the subscriber and more than 15 days have passed since the ...
zuzannamj's user avatar
  • 14.4k
6 votes

Can Automation studio be scheduled less than once a hour

Another option: - copy paste the original automation - let automation 1 run hourly at .00 - let automation 2 run hourly at .30 That worked for us.
jayca's user avatar
  • 75
6 votes
Accepted

Automation studio SFTP file drop trigger to Import Activity

So the way I solve file conflict scenarios if I have no control over the file naming patterns is: Create 2 File Locations pointing to 2 seperate folders in your FTP: File Locations: import/...
thechrishaddad's user avatar
6 votes

Errors: Incorrect syntax near 'LIMIT'

SFMC uses T-SQL syntax, so you need to rewrite your query using the TOP expression instead of LIMIT. SELECT TOP(10) SubscriberKey, EmailAddress FROM MASTER_IMPORT WHERE EmailAddress LIKE "%gmail....
Eduard's user avatar
  • 5,119
6 votes
Accepted

Old style JOIN (ANSI JOINs) syntax is not allowed. Split DATE

The SQL parser in SFMC is really dumb sometimes. I'd recommend something like what @Bo Hu suggested, but just add brackets around it: SELECT CUST_CODE , [EXCLUDED_FROM_NPS] , BIRTH_DATE , ...
Adam Spriggs's user avatar
  • 30.4k
6 votes
Accepted

SQL Query in Marketing Cloud Today = date

The CAST function in SQL converts data from one data type to another. By casting the GetDate() you're converting it to a string and comparing with a string in format of 'YYYY-MM-DD'. You can do ...
Brad Sapkota - altus.digital's user avatar
6 votes

SQL Incorrect syntax near ')'

Remove the last , before the bracket. It's the last item and doesnt need to be followed by a ,
0xsegfault's user avatar
  • 6,593
6 votes
Accepted

Journey Builder with updated data

Once a contact has entered the journey, all the data in your entry source DE for that contact will be saved in journey data. If you look at any decision split, you will see two options Journey ...
Praveen Kumar Bandi's user avatar
6 votes

Marketing Cloud Journey Builder tracking data isn't returning to Salesforce

The problem was that I didn't link my custom Data Extension with Contact on Contact Builder. Solution: I entered on Contact Builder, created a new Attribute Group. After that I clicked on "Link ...
Átila Castro Alves's user avatar
6 votes
Accepted

Salesforce Data Extensions not available for Contact Builder Data Model

Salesforce Data Extensions are indeed not available in Contact Builder - only in Email Studio. This is a limitation of the application, you are not missing anything. I normally set up the entire ...
Lukas Lunow's user avatar
  • 21.1k
6 votes

Query on system data views in Automation Studio

First of all ask support if they enable you to access the dataviews. I believe they have to enable it for your account. Create a data extension with these fields: https://help.salesforce.com/...
Johannes Schapdick's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible