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....


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

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 first bounce. https://help.salesforce.com/articleView?id=mc_es_bounce_mail_management.htm&type=5 Unsubscribed: An unsubscribed status prevents all sends to ...


4

The SQL validation in Marketing Cloud does have a strange quirk that it seems to treat underscores (_) near the From string as spaces - this causes the query to interpret the From part of Valid_From as the command that's responsible for the source of Data. Since it's not the last column selected, the system tries to look for a data extension named ,. To ...


4

You can't remove specific records from a data extension using a Query Activity. Query Activities can only Overwrite, Update or Append records, not remove them. So, you'll need to take a different approach. If you want to avoid SQL, create two filtered data extensions - one with a filter selecting only records where the email address field ends with your ...


4

When coding AMPScript using a Shared Data Extension in an Enterprise 2.0 account, you add the prefix "ENT." to the data extension name. Doc: https://help.salesforce.com/articleView?id=000320283&language=en_US&mode=1&sfdcIFrameOrigin=null&type=1


4

When you choose "External SFTP" as the location type, your Url field should have the absolute path to the ftp folder you need to access. Example: sftp://ftp.domain.com/folderForSFMC/ You can find your folder's absolute path by viewing the folder in an FTP client and right-clicking on the folder to "Copy URL(s) to Clipboard" (that's the option in FileZilla)....


4

You can utilize the SQL function MAX() to get the most recent date from the field. This combined with a GROUP BY will ensure you only get the most recent date with each unique record. As you are working with 3 data views, the below may time out, so you may need to split it into multiple queries to ensure it runs, but below should do it: SELECT s....


4

You aren't joining to _Sent using the SubscriberKey, so for each row in your Unsubscribe Survey table, you'll be getting a row for everyone sent the email. The join should look like this: [Unsubscribe Survey] u INNER JOIN _Sent s ON u.JobID = s.JobID AND u.ListID = s.ListID AND u.BatchID = s.BatchID AND u.SubscriberKey = s....


4

There are a couple ways that this can work. I personally would use the STUFF method to create a delimited string in a single column instead of trying to do multiple rows. Here is a previous SFSE answer discussing this. And here is an article I wrote about this. This is a sample query based on your above: SELECT a.Reservation , a.Name , ...


3

Replace your single quotes surrounding the Data Extension name with square brackets. Any field or data extension name that contains a space should be surrounded with brackets. Select o.JobID, o.SubscriberKey, o.EventDate, o.BounceCategory, o.SMTPCode, o.SMTPBounceReason, o.ListID, s.ListName, s.[Email address] from [_bounce] o INNER JOIN ent.[EDU SPIRIT ...


3

There is no automatic way to listen to imports/updates to a Data Extension. If you import a CSV a file drop automation could be suitable, otherwise you will need to trigger the API using a program/code. However, check if a realtime trigger is really necessary for your use case or a data extension entry source with a recurring schedule is also sufficient. API ...


3

You need to use CASE - WHEN - THEN - END constructs in Marketing Cloud, as Microsoft SQL is used in the background. It somewhat looks like this: CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END I'm not sure if your is alphanumeric expression works, but if it doesn't you ...


3

If you're tracking conversions using the Collect Implementation that shows up in Web & Mobile Analytics and have implemented a Catalog in Email/Web Recs, you can use the IGO_PURCHASES joined to the IGO_PROFILES Data Extensions. There's the JobID, BatchID etc you need to join to your _Job Data View in IGO_PURCHASES, providing you're using Conversion ...


3

Journey Builder, when configured in the entry event accordingly, listens to the run of an automation, not individual activities. Your automation schema contains multiple automation activities from which you expect a result. This expectation is incorrect. You should get ONE injection into the journey once the automation is finished, which is your step 11. So ...


3

As Lukas mentioned, if you are doing this to be able to display information in the email, then DON'T - no need to transpose any data using SQL, as this is exactly what AMPscript is for. I am assuming you want to display data for each person in an email and that you will have a reservation number in your sendable DE. Then, this should do it: %%[ set @...


3

If you want to do a daily SMS report, I would advise not to use the SMS Message Detail report, but to go straight for the the data from the SMSMessageTracking data view: The data is much more robust - 41 fields vs 13 in the report It's a data view, so you can grab it directly with SQL (no export and import) and apply any transformations you might need at ...


3

I've previously outlined my go-to for timeout mitigation here. You can use the hidden _customObjectKey column in every DE and the % mod operator to split the processing of your query into multiple queries. For your query, you could split it into 3 queries like this: Query 1 SELECT Sub_ID , Field1 , Field2 , Field3 , Field4 , Field5 , Field6 , Field7 , ...


3

There is an option to add CC in the user initiated definition (assuming the cc business rule has been enabled for your account). You can set a personalization string such as %%CC_Address%% in the value that is either a DE Column or a variable set in the Ampscript of the message. Data Extension Setup: User Initiated Send Definition Setup:


2

Use a query activity with overwrite as the update type on the DE with something like: SELECT field1 ,field2 ,field3 FROM AnyDE WHERE 1=0 Obviously 1 does not equal 0 so it should overwrite it with zero records.


2

Let break this down in to steps: First, start by giving each record a value of 1 in the column where where applicable SELECT Key, IIF(Source = 'Phone',1,0) as Source_Phone, IIF(Source = 'Web',1,0) as Source_Web, IIF(Source = 'facebook',1,0) as Source_facebook FROM [Old_DE] Based on your example above, you'd get a result set as such Key ...


2

You can do all of this in an SSJS Script Activity of Automation Studio Given following: Data Extension name and external key: recordsToUpdate Custom object: fromSFMC__c Flag field: flag Field you want to send to SF: datafield You can use below SSJS. It will also update the flag in the data extension, so you can keep track on what has been sent to ...


2

The primary key on _Sent is [JobID,ListID,BatchID,SubscriberID], so you can either change the primary key on your results table to avoid the error, you can use a GROUP BY clause in your query or use a PARTITION BY clause in a subquery and restrict rows returned after partitioning. GROUP BY SELECT sub.SubscriberKey AS SubscriberKey, sub.EmailAddress ...


2

The reason for your timeout error is you are running an expensive query that is timing out after 30 minutes. Salesforce has a useful page on optimisation of query activities. Many of the points I think you'll find useful in tuning the query above, most notably the use of intermediary/staging tables and joining on Primary Key fields. Also consider the need ...


2

Not being an SQL guru, I can't tell you whether this is the most efficient way of fetching and counting all the unique domains, but it at least does the job: SELECT RIGHT(Email, LEN(Email) - CHARINDEX('@', email)) as emaildomain , COUNT(Email) as EmailCount FROM [Your data extension] WHERE LEN(Email) > 0 GROUP BY RIGHT(Email, LEN(Email) - CHARINDEX('@'...


2

Looks like you're utilizing all of the tricks already. I'd build a new DE for the Opens and revise your query left join to it. Opens query: select o0.subscriberid , max(o0.eventDate) eventDate from _Open o0 where o0.isunique = 1 group by o0.subscriberid /* name: MostRecentOpens */ /* target: MostRecentOpens */ /* action: overwrite */ Then I'd repeat ...


2

You just need a SQL to join your pickup data extension with Master data extension and populate the target data extension with whatever additional attributes/ fields you need. Then put that query in an Automation and schedule it to run every hour.


2

You can find those records containing multi-byte characters by converting the First Name field to a single-byte data type and then comparing against the First Name for equality: SELECT FirstName FROM MyDataExtension WHERE FirstName != cast(FirstName as varchar(1000)) If you're specifically interested in locating records that contain the ...


2

You can do this using the functions DateAdd, DateDiff and a multiplication. The query could look like this: SELECT DateAdd(day, DateDiff(day, movein, moveout)*0.7, movein) as [reminder_date] FROM exampletable For your example this results in the date 2020-01-22. Further resources: DateAdd function reference DateDiff function reference


2

This is because all your columns in select statement needs to be in the group by clause. Ideally, it should only be the subscriber key / job id(In case you are trying to find the max sent date for a job). Refer to this article


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