All Questions
Tagged with automationstudio query
88 questions
0
votes
1
answer
50
views
Query with Union of two Synchronized Data Extensions
I have a query using a Union to combine two Queries. In the Query editor I run into no errors but when I click finish, I get the error Cannot find table 0. So syntax errors show up so I am lost.
Here ...
- 5
0
votes
2
answers
81
views
Simple Queries - Sent
I'm new to writing queries but I thought this SENT query would be simple. I'm just trying to get All sends for the last 30 days. But when I run the query, nothing populates in the DE and nothing ...
- 1
0
votes
0
answers
80
views
SQL Query to update DE field when "x" hours away from date/time field which is already formatted to the correct timezone
We are working on building a journey that is extremely dependent on utilizing a date/time field (StartDateTime) to determine when emails/sms are delivered. Given the constraints in Journey Builder ...
0
votes
0
answers
31
views
BounceCategoryID from Bounce Dataview field doesn't return the right value to Boolean field
I have problems during SQL that it doesn't retrieve the right value to Boolean field.
Now I extract Bounce DataView and if BounceCategoryID(number type) is 1002,1003 then retrieve 'true' to Spam field(...
3
votes
2
answers
417
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
421
views
Can I convert string to datetime in Marketing cloud's SQL?
I have string data like '20230823143306'.
Is there any way to convert it to a datetime format, like '08/23/2023 02:33:06 PM', using SQL?
I've heard that it might be possible to perform this conversion ...
0
votes
1
answer
105
views
SQL Query Not Working
I'm trying to have this query pull all subscribers into a data extension who are not within another data extension. I'm not sure why it's not working.
Select Status, Name, FirstName, LastName, ...
- 3
0
votes
1
answer
83
views
Clone data from one field to another in a data extension in marketing cloud
I have a Data Extension in Marketing Cloud and I am using Automation Studio to create a querie that copy the data from one field to another.
I want to pass all the event_id data to the new field ...
- 1
0
votes
0
answers
186
views
Problem with automation and Sql Query
I have a problem with an automation:
the query inside has no error and runs correctly, but no record is extracted. If I try to run it externally (via run once and query studio) the records are ...
-1
votes
1
answer
64
views
Query Error - Timeout
my query had an error "QUERY FAILED - TIMEOUT".
The query is:
SELECT DISTINCT
S.JobID,
S.ListID,
S.BatchID,
S.SubscriberID,
I.ActivityName,
I.JourneyName,
I.JourneyId,
I.VersionId,
S....
0
votes
1
answer
295
views
How to apply inclusion and exclusion criteria when data extensions are linked by one to many relationships?
I have a contacts DE table like this:
Contacts
CustomerID
CustomerName
Country
1
Alfreds Futterkiste
Germany
2
Ana Trujillo Emparedados y helados
Mexico
3
Antonio Moreno Taquería
Mexico
4
Around ...
- 353
0
votes
0
answers
24
views
Not able to create record in All subscribers via SQL query activity [duplicate]
Greetings of the day!
I am trying to create record in All subscribers via SQL query activity: below is my SQL query
**select Email as 'Email Address',
Email as 'Subscriber Key',
[First Name] as 'Full ...
2
votes
3
answers
222
views
SELECT all from 1 of 2 tables, based on the table with the most rows
This is a bit of a weird question, but I will explain the problem first.
Every day we import a refreshed set of data in to a table called MARKETABLE_TABLE.
Sometimes there is an error with the ...
- 31
0
votes
1
answer
613
views
Select * in SQL within Marketing Cloud does not update the records in a Data Extension
I have 2 indentical Data Extensions say table1 & table2. email is the primary key in both these Data extensions
I run below query to update Data extension table2
select * from table1
The query ...
- 3
0
votes
1
answer
295
views
DateTime function in Automation Studio query
I have the following check in my query
[Event:Start_Date_and_Time__c] >= DateAdd(day, 2,cast(GetDate() as date))
But the problem is that if the event was created on 08/19/22 5:00 am, and the time ...
-1
votes
1
answer
216
views
Marketing Cloud SQL Syntax Error for a data cleansing activity [closed]
I'm trying to run a SQL query in Automation Studio in order to perform some data cleansing activities for a name field (here called 'BB'), however, I am getting the following error thrown from MC (...
0
votes
1
answer
395
views
Records duplicated on SQL Join (Marketing Cloud)
I'm trying to return the results of subscribers who have been sent emails, when they've been sent, if they've opened/clicked and whether or not is house bounced/they've unsubscribed.
I'm using the ...
- 43
0
votes
1
answer
1k
views
SQL Query Timeout Help SFMC
I have the following query that is consistently timing out for the last few weeks. It had ran perfectly fine since 2019, previously. I know the query needs to be optimized, but I don't see anything ...
- 25
0
votes
2
answers
782
views
Query Error : field "Contact Key" required for targeted DE
Good morning,
I have a Synchronized DE [Contacts_Salesforce_9] and I have to write a query to link it to a Regular DE because I need to put it as an entry data source in a Journey.
However, with the ...
- 11
1
vote
1
answer
78
views
MC query dedupes records automatically
I've noticed some quite odd behavior from running queries in automation studio.
Below is the query in question:
SELECT C.id
, CM.id
FROM ent.CampignMember_Salesforce CM
JOIN ent.Contact_Salesforce C
...
- 151
1
vote
0
answers
85
views
Count id in Data Extension and Upsert to another Data Extension using SSJS
This question might be a basic one but I am very new to SSJS so thank you for your understanding.
The data extension names JourneyA, JourneyB, JourneyC...infinity are the result of Journey Builder. ...
- 23
0
votes
1
answer
666
views
Query Activity - Merging Data Extensions with Different Column Names [closed]
I have a Data Extension called “Merged Data Extension Newsletter” that has fields called
“FirstName”, “LastName”, “Email”, and “Id”
I am using ID as the Primary Key.
I have another Data Extension ...
1
vote
1
answer
91
views
Creating a log of all subscribers' most recent email. It's missing some people. SQL help?
For about 18 months I've been running an automation to keep a list of all subscribers and the date of their most recent email. I have two queries and two data extensions. Number 1 is:
SELECT
...
- 898
0
votes
1
answer
489
views
SQL issue in populating a Data extension in Marketing Cloud
Could anyone help identify why this SQL won't populate to a data extension:
Select l.Id
, l.Email
, l.FirstName
, l.LastName
, l.MC_Lead_Mobile_Phone__c as MobilePhone
, l.Salutation
, e....
3
votes
1
answer
3k
views
How to use string_agg/group_concat in salesforce marketing cloud
I am trying to write something similar in query studio on SFMC. But keep getting string_agg is not recognized as a built-in function name. Please help. Also, what could be the alternative.
Thanks
...
0
votes
3
answers
3k
views
Pushing data from one data extension to another
In Marketing Cloud, I have two data extensions. Both Data Extensions are being populated via entries in a journey. One runs off of the contact object and the other runs off the opportunity object. The ...
- 1
-1
votes
1
answer
3k
views
SQL query to find Job ID and email name for triggered & scheduled email sends
I was trying to implement this answer given in StackExchange - Query for Emails sent in the past month, unsubscribes opens, clicks for each
If you look at, “III-C. SQL Query”, Paragraph in the answer, ...
- 59
0
votes
1
answer
503
views
Find the ‘total sent’ for each JOB IDs in last 1 month along with ‘list name’ it was sent to [closed]
Currently by using this query, I get the number of total_sends for a particular JOB id.
But I want to modify the Query so that it shows total_sends , ListName , Date for that each job ID within a ...
- 59
1
vote
1
answer
1k
views
SQL query to find unengaged subscriber confusion
I want to exclude subscribers who have not ‘opened’ or ‘clicked’ email in last 6 months.
I have the below sql query. Is this SQL query correct?
Does the final results of this sql query results have ...
- 59
0
votes
1
answer
869
views
Unable to get percentage value in SQL Marketing cloud
Everyone
The below query is working fine without bounce_rate condition. When I am adding the bounce_rate it is throwing an error. Anyone please suggest on this.
SELECT
j.emailname, j.emailsubject, s....
- 15
0
votes
0
answers
155
views
Error: Violation of PRIMARY KEY constraint - How can I avoid inserting changed data with same PK?
I'm having an issue with a primary key violation that is a little different than others posted. I have a Contact, who can have one or more Reservations. I need to pull a list for a Journey very ...
- 1
1
vote
1
answer
675
views
SQL query to exclude subscribers
I'm trying to pull the data from orderDE, where purchaseddate is (day - 1). if the customer only purchased a single product in a day, include him in results. If a customer purchases more than 1 ...
0
votes
0
answers
170
views
subscriber query string preference page
is there a way to extract the preference centre query string for each subscriber using a query?
The aim is to pass this string with also the email address and Subscriber key to an external mailing ...
- 5
0
votes
1
answer
944
views
Automation Timeout
I'm facing an intermittent issue with one automation. A month ago I received a message from SFMC reporting a timeout error in one Automation, but then I re-run the automation the error doesn't show ...
-1
votes
2
answers
455
views
SQL - GETDATE() not working
I have two queries for identifying customers for promotion. The first target is composed of customers who did their last purchase more than 30 days from the current date and the WHERE clause works ...
- 1
0
votes
0
answers
603
views
Rownumber() is skipping numbers in query results
I'm using a query to collect records from 5 tables, exclude records from a 6th table, and then give the results an incremental number using row_number(). But when I look through the results, some ...
- 309
2
votes
1
answer
883
views
Optimizing SQL for large volumes
We're working with somewhat large sets of data, around 15M - 35M records per DE, each row with 150+ attributes.
What are some ways to optimize SQL queries when handling many records?
For an exaple, ...
- 5,794
0
votes
1
answer
1k
views
Marketing cloud not allowing the use of SELECT * when there is a join in the query
As the title suggests, if I try and write a query like:
SELECT *
FROM TableA
LEFT JOIN TableB
ON TableA.Field = TableB.Field
I get an error saying
An error occurred while checking the query syntax. ...
- 912
0
votes
1
answer
448
views
Subscribers not sent to
I had to get the report of bounced and not sent subscribers for a particular send in a automation studio.
I can get get the bounded subscriber from the _bounced DV, how can i get the Not sent ...
1
vote
1
answer
92
views
Help understanding Primary Key Violation
We collect unsubscribe survey responses and store them in a DE. I want to run a query to combine the data from this DE with the Job and Sent data views to get a complete picture of what email the ...
- 912
0
votes
2
answers
869
views
SFMC Query Error "An error occurred while checking the query syntax. Errors: Error while parsing query. Please check the syntax"
I am creating this SQL query in Automation Studio. This query gives me an error:
An error occurred while checking the query syntax. Errors: Error while
parsing query. Please check the syntax
When ...
- 69
1
vote
0
answers
1k
views
Can suppression lists be updated from Automation Studio?
Our Marketing Cloud hierarchy consists of 170 child business units and a single parent business unit.
Local marketing emails are sent from the 170 child business units.
We track opens and clicks in ...
- 551
0
votes
1
answer
390
views
How to put in SQL to see only records that in the field do not have specific data in the marketing cloud?
I am facing a problem when trying to filter the leads and contacts that come from CRM to be used in email marketing.
The idea is that the filter does not add people who have the field conta.Name equal ...
0
votes
2
answers
101
views
Query performance | stuck if more than 2 joins are in sql query
I'm trying to get Total number of clicks per each URL for Newsletters and joining 3 data views: click, job and sent.
The query was successfully executed when I tested with 2 joins on click and job ...
- 1
0
votes
1
answer
32
views
Query activity returning rows that it shouldn't
I have a query activity that is returning rows that it shouldn't I'm guessing there is a problem with my sql but cannot figure it out. The idea of the query is to create a 'dormant' list, with these ...
- 912
1
vote
0
answers
1k
views
Exporting a file from a data extention automation in salesforce marketing cloud
There is a database extension in salesforce marketing cloud that I need to export from the automation. I didn't create the DE and am unsure how to export it for use outside of salesforce marketing ...
0
votes
1
answer
156
views
Why does my conditional average query return 0 in MC Query?
I have a table that I am trying to calculate average values based on conditional statements.
I have used conditional statements before so I know the function is supported, but for this particular ...
- 4,918
0
votes
1
answer
562
views
Salesforce Marketing Cloud Query - Count by Date and Appointment Status, Group By CustomerID
I'm not proficient in SQL Query. I'm hoping someone on here can help me write this query for Saleforce Marketing Cloud, which uses a "limited" SQL Server 2016.
Problem: I'm trying to extract month ...
1
vote
1
answer
2k
views
SQL Query for getting distinct domain and count
I have a Data Extension which has email address. I want to store all distinct domains and counts of these emails in another DE
For eg: I have [email protected], [email protected], [email protected].
So my o/p ...
- 1,693
0
votes
1
answer
206
views
Tips for using SQL skills efficiently in Automation Studio [closed]
As a Marketing Cloud marketeer I really see queries as very powerful in creating strong segments.
So, I've dived into SQL and query writing using codeacademy and w3schools, but even though I find ...
- 551