3 votes

Dynamic SQL Top() clause in SFMC

You could do use the row_number() window function to assign a sequential number to each row (in random order) and then only select rows that less than or equal to your volume value. Something like ...
Adam Spriggs's user avatar
  • 30.4k
3 votes
Accepted

Sending Push Notifications Within Journeys Triggered By An API Event Without DeviceID

As long as the contact in your API event is already tied to a device ID in the Mobile Push channel, journey builder can send the Push Notification message to that contact without needing the Device ID ...
Duc Le's user avatar
  • 3,083
3 votes
Accepted

SQL Query Not Working

Email is ambiguous since you're not aliasing anything. Here's how I'd write it: Select l.Status , l.Name , l.FirstName , l.LastName , l.MiddleName , l.Title , l.Email , l.Phone , l.MobilePhone , l....
Adam Spriggs's user avatar
  • 30.4k
3 votes

Marketing Cloud SQL - Can we do maths using field values

If you number all of the records on the waitlist, then you can definitely just select that many results. Something like this: Sample data CREATE TABLE waitlist ( waitlistId int, numSlots int ...
Adam Spriggs's user avatar
  • 30.4k
2 votes

Automation Studio Activity (Journey Entry) not appearing. (Permissions?)

You need to configure a journey with a Data Extension with automation set as entry source, it will add this activity automatically to your automation. When you select Automation, the journey ...
zuzannamj's user avatar
  • 14.4k
2 votes

How many Automation will Create & Run In marketing cloud For standard success plan?

This has nothing to do with the success plan (which is defining the level of support received by you from Salesforce). You should instead look at the edition. Here you can see how many automation runs ...
Lukas Lunow's user avatar
  • 21.1k
2 votes
Accepted

Can I convert string to datetime in Marketing cloud's SQL?

Based on what you provided, the string data is having the datetime format like this yyyyMMddHHmmss (20230823143306) so I would convert it to a SQL datetime data type then format it to "dd/MM/yyyy ...
Duc Le's user avatar
  • 3,083
2 votes
Accepted

How Do I Break Down a JSON Array Format CSV Column Into Individual Columns Within SFMC Data Extensions?

I think likely the big issue you ran into is that you were storing your newRow as an array instead of an object, which is what Rows.Add requires. Might explore trying it with outputRows being an array ...
Gortonington's user avatar
  • 30.5k
2 votes

SSJS code to add existing query activity to an existing automation fails

It looks like the trouble in your code is how you're tinkering with the activities array. The error message is giving you a heads up about an "Object expected: push" issue, indicating that ...
aryashah2k's user avatar
2 votes
Accepted

Is there a way to process SQL queries in Salesforce Marketing Cloud based on criteria in another unrelated table?

Here's a pattern that I use for referencing a "parameter" data extension. You can reference the results of the join in a case statement or in the where-clause, depending on the specifics of ...
Adam Spriggs's user avatar
  • 30.4k
2 votes

I want to create a Frequency Capping process on Number of Emails/ SMS/ Push messages sent to a customer in the last 1 or last 7 days

Error 1: An error occurred while checking the query syntax. Errors: The multi-part identifier "m.CreateDateTime" could not be bound. This error occurs when you are trying to reference a ...
Rafał Wolsztyniak's user avatar
2 votes
Accepted

Dynamic SQL Top() clause in SFMC

Nice solution by Adam. a different alternative I sometimes use is to store the "TOP" number in a control table with key/value pairs, and look it up based on some condition (e.g. id could be ...
Jonas Lamberty's user avatar
1 vote

Marketing Cloud | Automation Studio | Output SSJS WSProxy to Landing Page or other viewable place

If you are looking at the cloudpage preview, try publishing the page and the script should execute. For reasons unknown to me, the preview of the page I believe is handled like "sendable context&...
Jonas Lamberty's user avatar
1 vote

How to delete emojis using SQL in Marketing Cloud

You can try something like this: SELECT subject, REPLACE(CAST(subject as char(255)), '??', '' ) AS cleaned_subject FROM [Jobs] In the query above, I transform the data in "subject" ...
Duc Le's user avatar
  • 3,083
1 vote

Unable to decrypt a file securely from external file location and move it to a Safehouse in Marketing Cloud

You can ask SF Support to enable your account the "Delete file from ET FTP or Safehouse" Extract Type in the Data Extract Activity. Once done you would be able to place Data Extract Activity ...
Niko's user avatar
  • 2,680
1 vote
Accepted

Updating an Email Send Definition with SSJS to update data extension AND publication list

Your code looks fine. However, the PubListKey parameter needs to fill with a Publication List customer key value not a Publication List name. The following here is a fast way to retrieve and get the ...
Duc Le's user avatar
  • 3,083
1 vote
Accepted

Can we Run An Automation for 24 Hours by adding repetitive activities to ensure we are processing data we captured in Data Extension

Running a single automation for 24 hours is theoretically possible, but far from recommended. Main reason: should your current automation fail to finish within 24 hours, the next run will be skipped. ...
Lukas Lunow's user avatar
  • 21.1k
1 vote
Accepted

Setting multiple First name in SFMC mobile connect

You can only create one account level from name in each BU. However ampscript does work within this field. And each message can have a configured from name Check out the help doc: From Names After it’...
EazyE's user avatar
  • 8,447
1 vote
Accepted

Marketing Cloud SQL String Formatting to Retrieve Only Part of Data

You can use Cross Apply function to parse the delimited field values. SELECT a.Original_Field, SUBSTRING(a.Original_Field, b.categories_index, CHARINDEX(',', a.Original_Field, b....
Duc Le's user avatar
  • 3,083
1 vote

How to execute journey exists in child BU from parent automation?

I don't think the journey in the child BU will recognize the automation in the parent BU since they are 2 different BUs. Updated based on Lukas Lunow's idea from the comment. Normally, in this ...
Duc Le's user avatar
  • 3,083
1 vote

SQL filter on Date doesn't work

I haven't tried this particular function in SFMC, but it can parse the date string in that format: select parse('Wednesday, May 03, 2023 9:20 AM' as datetime) parsedDate parsedDate 2023-05-03 09:20:...
Adam Spriggs's user avatar
  • 30.4k
1 vote
Accepted

Extending my use of SQL and UNION to add information about source

Your issue is, that UNION only appends distinct values. So when you use UNION, the dataset is appended, and any rows in the appended table that are exactly identical to rows in the first table are ...
Lukas Lunow's user avatar
  • 21.1k
1 vote
Accepted

Script to create a DE in MC automation studio enterprise edition

If you run it in Marketing Cloud it's better to use WSProxy - in the back-end is still SOAP API but it's much easier to write the code. This code works for me in both Script activity in Automation ...
Florin Valean's user avatar
1 vote
Accepted

How to add categories based on two different variables in SQL Query in SFMC

You can use SQL Case Expression to return a value based on a condition. Try this: SELECT c.[JobID] , c.[SubscriberKey] , c.[URL] , j.[EmailName] , c.[LinkName], CASE WHEN c.[URL] like ...
Duc Le's user avatar
  • 3,083
1 vote
Accepted

Can I still send back the tracking data of IER to Salesforce even after doing a send?

No. The workaround is to create these missing IER records through Automation Studio script activity or Journey Builder salesforce object activity. You can use Data Views to find the proper content.
Rain's user avatar
  • 5,725
1 vote
Accepted

I want to search the list of SQL Query Activities which uses my target data extension in Select statements without explicitly storing them in DEs

You mention that you do not want the cloudpage to be accessible. Your page is insecure even if you enter a search function. Suggestion - secure it: https://sf-marketing.com/marketing-cloud-user-...
Jonas Lamberty's user avatar
1 vote

Is it possible to replace a Data Extract file in an exported ZIP?

You can extract and import Not Sent tracking data into a Not Sent Data Extension and do whatever you please with it. It's convoluted, but it should give you the flexibility to launder the data before ...
Adam Spriggs's user avatar
  • 30.4k
1 vote
Accepted

Automation failed due to system issue but works in query studio

Here's a sargable version of your query: select f.applicationDate from Importfile_N f WHERE f.applicationDate >= convert(date, getDate()-17) and f.applicationDate <= convert(date, getDate()-5) ...
Adam Spriggs's user avatar
  • 30.4k
1 vote

Programatically update the "Used for Sending" value for DEs in folder and all child folders

Speaking of your code: First of all, it is not recommended to use undocumented API endpoints, since they might be changed/removed/disabled at any point in time, thus making the solution in a long run ...
Niko's user avatar
  • 2,680
1 vote
Accepted

Find all SQL Query Activities which contain specific DataExtension in Query

This is a solution for those who do not know anything about Cloud Pages or how to send a SOAP message to the MC API. Combining the other answers, as well as a script by Zuzanna Jarczynska that I also ...
Sander de Jong's user avatar

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