All Questions
38 questions
0
votes
1
answer
1k
views
SQL query to update field only if it is empty
I need to run a query which updates a target DE with some rows from another. There is a field called 'origin' which shows on the target data extension where the data came from.
I appreciate this may ...
CommunityBot
- 1
0
votes
0
answers
68
views
_AutomationInstance data view is not returning right data
can anyone lend me a hand?
I'm having trouble retrieving data about my automations using the data view _AutomationInstance. Automation Studio is returning incorrect data for certain automations in ...
- 3,835
3
votes
2
answers
421
views
How to delete emojis using SQL in Marketing Cloud
I have a Data Extension named "Jobs", where I keep records from dataview Job. My team started to use emojis in subject lines of the e-mails, so also in my table "Jobs", I have ...
- 193
-1
votes
1
answer
157
views
Is there any way to get the SMS Opt-Outs rate in account level?
Our client want to know sms opt out rate in account level, so that based on results they can plan campaign volume/KPIs.
Let me know is there any OOB features in MC? If no, Kindly let me know feasible ...
0
votes
1
answer
411
views
Contacts without Channel Addesses not working properly Record Count 0
I have been trying to delete Contacts from our Parent BU, which is as far I know the only BU where you can run an Automation with Data Extract "Contacts without Channel Addresses".
The ...
- 2,809
-1
votes
1
answer
67
views
Automation for birthday adding contacts the day after instead the exact day
we're experiencing some issues with a birthday automation query.
Our query runs everyday and should filter contacts from 2 DE (contacts that are celebrating their birthday on that day) and put it in a ...
- 31.1k
1
vote
1
answer
131
views
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
The basic process we are planning to use for achieveing this is:
Create a DE that has SubscriberKey and 6 columns "EmailsCount_7days" "EmailsCount_1day" "SMSCount_7days" ...
- 3,401
0
votes
1
answer
169
views
Can we Run An Automation for 24 Hours by adding repetitive activities to ensure we are processing data we captured in Data Extension
We need to continuously process data(need to make an API call using SSJS script activity to external system which is time sensitive) which we are capturing in Marketing Cloud Data Extension via SFMC ...
- 22.2k
0
votes
1
answer
93
views
How to get 2nd Saturday of Last Month in Where Clause
I am trying to setup an automation which will run daily and get the data from Capture DE which have a field name 'LastCapture' date type. The data is loaded on 2nd Saturday of each month.
I am trying ...
- 8,336
0
votes
1
answer
227
views
Re-Subscribe contacts who have unsubscribed on contact creation as a result of Journey using salesforce data entry
I have a journey with a SF data as entry source, this journey captures new contacts created in SF using SFMC connector. On Salesforce, when a contact is created, the initial state of emailoptout is ...
- 182
0
votes
1
answer
366
views
Is it possible to run an automation only when a data extension is re-populated with data? Not knowing when data is coming
Background: Currently, we have an automation job where audience is pushed to FTP and automation trigger is based on file drop. When file is pushed with a set naming convention on FTP, automation ...
0
votes
1
answer
99
views
How to get records from sendable audience who did not make it to _SENT log without hardcoding the jobid
I have a DE extension where I have saved 1 record by querying:
DE NAME: DataView_Job_DE
Select top 1
JobID
,EmailName
,CreatedDate
,JobType
,JobStatus
,Row_Number() OVER (Partition by jobid ORDER BY ...
- 10.3k
0
votes
1
answer
285
views
How to selecting value from another data extension in select statement in SFMC
I have a DE extension where I have saved 1 record by querying:
Select top 1
JobID,
EmailName,
CreatedDate,
JobType,
JobStatus
from _job
Output is saved in data extension: DataView_Job_DE
Now, I am ...
- 10.3k
0
votes
1
answer
78
views
Specific Domain SQL Query result incorrect
I am trying to pull list of emails for a particular domain (which is aproblematic domain) and somehow my results does not make a sense--meaning that I'm getting way less than expected. To further ...
- 35
0
votes
1
answer
417
views
How to create a priority logic for email in SFMC based on data
Current Use-case:
We have a data extension with the following fields:
CustomerID
Customer_EmailAddress
Customer_Category
1
[email protected]
Category_101
1
[email protected]
Category_202
2
[email protected]
...
- 819
1
vote
1
answer
118
views
Check for existing records in Data Extension when appending data
We're sending anniversary mails to members every time they've lasted another year with us :)
This is the SOQL appending data to the Data Extension [Anniversary Flow - Trigger]
SELECT
ID as 'Subscriber ...
- 551
0
votes
0
answers
423
views
Is it possible to perform validation of data in data extensions in SFMC automatically?
Currently, we have a ton of AdHoc (one-time emails) each month and for each email:
the email creative is different
audience list and DE fields are different
So, every time we get the list imported ...
- 353
0
votes
1
answer
2k
views
SQL Error - "Field name cannot be blank"
We are trying to run the following query:
SELECT
a.nihrm__BookingContact__c as "BOOKING_CONTACT",
a.Name as "ACCOUNT_NAME",
b.Email as "EMAIL",
b.FirstName as "...
- 66
0
votes
1
answer
697
views
Access Synchronized DE with SSJS
I want to use script activities within automations instead of query activities, however, I am unsure if I can access the synchronized DEs via SSJS.
I have tried researching this, however, I have been ...
- 14.5k
0
votes
1
answer
971
views
SELECT from _sent dataview - duplicate value error occuring
I am trying to create an Automation to update sent dataview records daily.
The code I've tried is the one below, but it kept showing error messages with
"Violation of PRIMARY KEY constraint. ...
- 10.3k
1
vote
1
answer
10k
views
SQL Query : Using JOINS with CASE Statement [closed]
I've 3 DE
MASTER DE with fields SubscriberKey(PK), donottrack, Createddate, Modifieddate
Staging DE with field CustomerID(PK), Createddate
Reference DE with field donottrack default value false.
...
- 71
0
votes
1
answer
372
views
In an automation, I want to query a number from one table and then use it as a parameter for a select top statement in another query
I hope I'm explaining this well enough. I have table/DE a, where column a is strings and column b is numbers. I want to query table/DE a and select a record that has a specific number, and then use ...
- 10.3k
0
votes
1
answer
1k
views
Automation SQL - get random value for each record [closed]
I need to populate some Data Extension using another Data Extension that contains all the accounts data. I want to attach a new column and the value for that column should be randomized from another ...
- 10.3k
1
vote
1
answer
1k
views
SQL query to get Open rate by domain
I'm trying to build an SQL query to get the Open Rate Percentage by domain for an email send through Journey build or scheduled send. Below is the query I have trying to figure out, but it's giving me ...
- 30.9k
0
votes
1
answer
223
views
Violation of Primary Key while using DISTINCT
Im trying to update a Data Extension with data from another Data Extension, very simple, you'd think.
The target Data Extension has SubscriberKey as Primary Key and the source Data Extension doesn't ...
- 22.2k
0
votes
3
answers
349
views
How to A/B split a table as part of a query [closed]
Hey guys have a simple use case;
I want to take a table and assign 80% of the records a variable of 'A' and the remaining 20% of records a variable of 'B'
I know I could easily accomplish this in 2 ...
- 348
0
votes
2
answers
804
views
Checking for a non ascii character in SQL Query activity
I have certain records in my DE, where the first name contains '�'. I wish to extract such records. Obviously a like wouldn't work. Is there a way to do this in SQL Query Activity.
- 8,336
0
votes
1
answer
77
views
Create an automation to update Test accounts Status from "Unsubscribed" to "Active" [closed]
I am sure this is a thing, but we have a group of "test" email accounts that our QA group regularly tests. Of course the unsubscribe is one of those things they test, and often we have to reset the ...
1
vote
3
answers
1k
views
Marketing cloud SQL Query Deduplication
I am trying to deduplicate my entry data extension based on the completeness of field values. For Instance, i have the a data extension with same email address, the record that has maximum field ...
- 30.9k
0
votes
3
answers
543
views
Use measure in SQL
I would like to use a measure in a SQL request.
Here is my SQL request that works :
SELECT
C.[Civilite] as 'Civilite',
C.[Prenom] as 'Prenom',
C.[Nom] as 'Nom',
C.[Email] as 'Email',
C.[...
2
votes
2
answers
394
views
Why is my code querying All Subscribers producing results that don't exist in All Subscribers?
In Marketing Cloud - I'm trying to write an SQL that will pull everyone who has opened an email in the last 90 days. I'm getting results from the below SQL, but when I spot-check the data, I'm finding ...
- 53
1
vote
3
answers
4k
views
SFMC Find and Replace via SQL query
I have a data extension where values are imported via an Import Activity. One of those values is a URL ending in .biz. I'm trying to write a query that finds all instances of .biz with .com but ...
- 191
1
vote
2
answers
2k
views
SQL Query for a MarketingCloud campaign opens?
I'm trying to create an automation that looks back at a specific campaign and sets a flag on their profile if the user opened the email. This campaign would run every day and then talk back to ...
- 314
0
votes
1
answer
211
views
Using query to populate target data extension for resending automated mail
Every week we send out a mail on tuesdays.
This mail is named so it always contains this: "TOBERESENT".
Every saturday an automation sends out a mail that is an altered version of the tuesday mail. ...
- 56
0
votes
1
answer
10k
views
Query issue - Automation failed due to system error
I have a sql-query where i do not know my mistake therefore it would be nice if you could help.
SELECT
SUM(b.HardBounceCount) AS HardBounceCount,
SUM(b.SoftBounceCount) AS SoftBounceCount,
SUM(b....
- 4,258
0
votes
1
answer
424
views
Deleting records from a DE 3 months after they are added
I want to write an automation which deletes records from a certain DE after the present date is 3 months after their date_added attribute. I know this is possible with DELETE FROM and DATEADD() in SQL,...
- 22.2k
2
votes
1
answer
880
views
Exact Target SQL- LIKE condition does not return anything
New to ET and have written a query in the automation studio. I have two tables I am looking through and am trying to return what matches my LIKE condition. The LIKE is looking for emails from TableA ...
- 25
0
votes
1
answer
168
views
SQL Query Script to Pull Automation Data
How can I write a SQL query to pull a list of all recipients and their email addresses of an email in an automation?
- 31.1k