18
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 DeleteByListReference endpoint. Here it works together with a sendable DE, and SSJS activity in Automation Studio:
<script runat="server">
Platform.Load(&...
12
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 = "Contacts_To_Delete_Log";
var log = DataExtension.Init(logDE);
/* for legacy API component package */
var url = 'https://auth.exacttargetapis.com/v1/requestToken';
...
9
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.emailaddress
, case
when ds.dateJoined >= convert(date, getDate()-14) then 'New'
when ds.dateJoined >= convert(date, getDate()-90)
and ds....
8
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 subscribers on the list:
Data View: EnterpriseAttribute
Sample usage would be along these lines:
SELECT
l.SubscriberKey,
l.Status,
e....
8
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 field
Inserting a value too long for the field (truncation)
Timeout -- if your query doesn't complete within the 30-minute timeout window, it'll error out.
...
8
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 without doing any lookups to cross-reference data.
8
My best advice is to ask Salesforce for a custom data view. This is something that can easily be done, however it will have a cost, as it is a custom development. However, it will let you achieve exactly what you are after.
Here is some sample data from such a custom data view, fetched in Query Studio:
select JourneyVersionNumber
,JourneyName
,...
8
My go-to 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-update/append-x pattern:
Here's a 4-group example using the mod operator:
Query 1: where de._customobjectKey % 4 = 0 /* action:overwrite */
Query 2: where de....
7
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 automation step)
Wait - x minutes
Script Activity - Run Automation1
Here's a sample:
<script runat="server">
Platform.Load("Core","1....
7
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 Query. Based on your use case, you should filter on fields: DateJoined and Status.
Once you have found the subscribers you want to delete, you can use the ...
7
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.
Some considerations, when it comes to API integrations for large data volumes:
Even when grouping multiple record updates into a single payload, will result in ...
7
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 activity to avoid unintended automation outcomes.
The activity lets you select a target data extension in your
automation and evaluate the target data ...
6
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.
6
Remove the last , before the bracket. It's the last item and doesnt need to be followed by a ,
6
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/articleView?id=mc_as_data_view_subscribers.htm&type=5
Create an SQL Activity:
SELECT
SubscriberID, DateUndeliverable, DateJoined, DateUnsubscribed, Domain, ...
6
It's certainly a recommendation to build an import process to your All Subscribers in order to keep in sync the email address from your Data Extension with the email address in All Subscribers.
Typically, if you import records to a data extension, those subscribers won't get added to 'All Subscribers' until you perform a send to the data extension / ...
6
I personally don't recommend SFMC providing Subscriber Key to your contacts. The only way you can identify/deduplicate your subscribers on import will still be on email address - which will change over time and generate duplicate records, and leading to inconsistent tracking history.
There is a number of methods to generate a unique identifier in SFMC, if ...
6
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 halting the process.
By default the settings in the backend force a stop on blank imports, but with this business rule enabled it is changed to continue.
6
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 or inside a string.
So for example, you would do: Replace(FIELD,'&apos' + Char(59), Char(39))
Char(59) = ;
Char(39) = '
To help remove the complexity of ...
6
If you need to get the value before the @ character, you'll need to use something like:
SELECT LEFT(Email, CHARINDEX('@',Email)-1) FIELD_ALIAS FROM YOUR_DE
Replace the FIELD_ALIAS with the name of the field where you want to store this value. Also, replace Email with the field from where you'll be getting the emails..
Basically, we are using the LEFT ...
6
One way to do this is to leverage LEFT JOINS and subqueries
SELECT Id, Name, EmailId
FROM (SELECT TABLE2.Id AS 'Id', TABLE2.Name AS 'Name', TABLE2.EmailId AS 'EmailId', TABLE1.Id AS 'SecondId'
FROM TABLE2
LEFT JOIN TABLE1
ON TABLE2.EmailId = TABLE1.EmailID) AS myTable
WHERE SecondId = Null
I have never actually ...
6
You can also do it without explicit JOINs, if you don't need to return any values from the other Data Extension.
select
t2.ID
, t2.Name
, t2.[Email ID]
from Table2 t2
where not exists (
select top 1 t1.*
from Table1 t1
where t1.[Email ID] = t2.[Email ID]
)
6
I did this before using SSJS the following way:
In your Script Activity using Marketing Cloud REST API endpoint to upsert values in the Data extension of your choice
https://developer.salesforce.com/docs/atlas.en-us.mc-apis.meta/mc-apis/updateDataExtensionIDAsync.htm
To consume a REST endpoint in SSJS you can use Script.Util.HttpRequest to build and ...
6
I would look into using Query Studio which is much more user-friendly and better for trial and error development work. Query Studio. It will help you as you learn and get up to speed.
I'd also really recommend reviewing the query/SQL documentation that SFMC has provided because it will help understand the limitations of SQL in SFMC vs the outside world. ...
5
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("core", "1");
var today = new Date();
var prevDate = new Date();
prevDate.setDate(today.getDate()-30);
Write("<br>today: " + ...
5
You can query a list, but only the default/out of the box fields, noted below:
Data View: ListSubscribers
Any additional/custom fields within a list would not be returned and/or your query would fail when referencing list field names that are not noted within the help documentation linked above.
In that case, the extract and import based automation you ...
5
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/ftp_file_drop_1/file.csv
import/ftp_file_drop_2/file.csv
Setup 2 seperate File Triggered Automations, each automation pointing to 1 of the file locations.
This ...
5
You can only have one WHERE-clause in your SQL statement, as they are per query rather than per join like in your statement.
So I suggest trying this query (from clarifications in comments and in chat this also includes all the changes necessary to fulfill the asker's requirements):
SELECT a.CustID, a.CustEmail, b.CustType, b.Status
FROM DataExtension_1 AS ...
5
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
, DATEPART(DD,BIRTH_DATE) AS DAY_DOB
, DATEPART(MM,BIRTH_DATE) AS MONTH_DOB
, DATEPART(YYYY,BIRTH_DATE) AS YEAR_DOB
FROM Import_Customer_TH
I did a quick test and this ...
5
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 Data Extensions".
So I select my Data Extension (DE) and linked my Primary Key of my DE (Salesforce Contact ID) with Marketing Cloud Contact Key.
At the end I ...
Only top voted, non community-wiki answers of a minimum length are eligible
