Questions tagged [sql]

Structured Query Language (SQL) is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS)

Filter by
Sorted by
Tagged with
0
votes
1answer
10 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 ...
1
vote
1answer
826 views

Query Journey Builder Sends

I want to run a query to pick up all subscribers who received a JB email in the last 7 days from a specficic Journey. I found this article but it is not filling the data extension! https://help....
0
votes
2answers
44 views

Using a sql query in Marketing Cloud to update a date (birthday) value in the DE to the date this year

I have an automation (running monthly) which adds records into a data extension if their birthday falls within the next month Records are then added into a journey which uses the Birthdate date field ...
0
votes
2answers
46 views

Unopened Query confusion

I am trying to query for unopen email subscribers in the last 180 using the _subscribers and _open data views. Due to the open view being limited to showing only opens and not unopens. I am querying ...
0
votes
3answers
681 views

How to correctly handle timezone difference in data views from Marketing Cloud

I store the last open times for my emails in a separate Data Extension queried from the _opens data view. The problem I have is that I need to be able to see who's received an email today via a ...
0
votes
1answer
45 views

Why is my RowNumber partition creating duplicate rows on the same information

I need to allow entrants into a journey in Journey Builder multiple times depending on the country of their booking and then preserve the entry and exit dates at a country level. I have created a ...
0
votes
0answers
27 views

Does Marketing Cloud SQL support STRING_SPLIT? [duplicate]

I am trying to use the STRING_SPLIT function within my query but the system returns an error stating the column is not a known data extension. Here is my query: select personcontactid as subscriberkey,...
0
votes
1answer
26 views

Unable to Remove Duplicate records in Query Activity [closed]

I'm using the below query to fetch only the unique "emailid" field value but still I'm getting duplicates SELECT Distinct Emailid , EmailName FROM _job a WHERE a.SuppressTracking=0 and a....
0
votes
1answer
27 views

Query Unsubscribes from a specific MID during a specific Period

I know this code is going to be slightly jacked up... but here it goes... I need a list of people who unsubscribed from a specific MID during 10/1/19 through 3/31/20 Select l.SubscriberKey, l....
0
votes
1answer
794 views

Query Journey Goals and Exits

There appears to be little documentation around these functions in Journey Builder, so hoping someone may have some insight. Can this data be accessed through the Journey Data Views or any other ...
0
votes
2answers
508 views

Marketing Cloud SQL Query to determine no of null columns

I am trying to execute the below query in a query activity. The intent is to find how many emails a customer record have. The query is executing without errors but the No_Of_Email column is not ...
1
vote
1answer
146 views

Marketing Cloud Data Relationships - what kind of joins are created?

To be honest, I've never messed with Data Relationships before. I've always used SQL queries to segment my data. So, with Data Relationships, my assumption is that a SQL query is generated on the ...
0
votes
2answers
51 views

Data Extension Data to JSON with SQL

I'm quite new to SQL and I've hit a road block in my knowledge. I am using Automation Studio to write a SQL query that takes the data from a data extension and transforms it into JSON. The idea will ...
1
vote
4answers
305 views

Customer Mobile Push Details Extract

We have a requirement to send mobile push messages from the data extensions as we need to segment data and put in the data extension.The data extension have only contact key.We need to export the ...
0
votes
0answers
22 views

Extracting and exporting (file transfer) SQL query from automation/data extension in 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
1answer
60 views

IN/NOT IN functions not working in Marketing Cloud SQL query Activity

I'm running into this strange issue without being able to understand why it is happening. I want to run this simple query: select a.[Account:PersonContact:Id] from [Salesforce_Data_Event_DE] as a join ...
0
votes
1answer
18 views

Get a one to many database output in one column in a one to one database Salesforce Marketing Cloud

We have a one to many table where we store all our transactions and a table where we store all the seperate items they bought. So it looks like: | **TABLE ONE** | | **TABLE TWO** | | ...
0
votes
0answers
23 views

What is the SQL for linking the _DeviceId field in _PushAddress to the SubscriberKey in _Subscribers

We have configured our App to register for mobile push using the SubscriberKey value that exists in Marketing Cloud. When we view Contact in the Mobile Push channel of All Contacts I can see the ...
0
votes
2answers
52 views

Mass remove records from suppression list

I have a case where I am being requested that any record that has received an email in the last 3 days needs to be suppressed from additional emails. This includes single mass sends, and any journey ...
1
vote
2answers
41 views

How can I get conversion stats via SQL in Marketing Cloud?

I can get this data using Extract Data in Automation: https://help.salesforce.com/articleView?id=mc_as_create_a_data_extract_file.htm&type=0 but I was wondering, If I can get clicks via SQL with ...
0
votes
1answer
486 views

Inserting a value into a field in a Data Extension where records match on a key in Salesforce Marketing Cloud

Please excuse my ignorance, as this seems like a simple thing. I'm just new to to SQL and am having trouble working it out. I have a data extension of records with a blank Variable field. I have a ...
0
votes
1answer
46 views

Compare current time (hours) with time range in Transaction SQL

I need to capture the time of a file drop in an automation in SFMC in hours. So, I think I will have to capture time stamp as now() in hours. Then I have to compare the current hour to a time range, e....
0
votes
0answers
33 views

Case When Substring not applying logic correctly

I have a field in SFMC that is a concatenation of two julian dates and a character. I need to split out the dates and convert them to gregorian dates in two seperate fields. However, the julian data ...
0
votes
1answer
23 views

SQL Query returning null values for Boolean fields from Data Extension

I have 2 Standard Data Extensions and an automation written to get values from one DE to other. The issue I am facing is Boolean values from one DE is not copying exactly in the other DE. False values ...
0
votes
1answer
32 views

Convert Julian Date in a string to Gregorian Date format [closed]

I have a text field that contains a string of two julian dates and a hyphen. Example:20140-20160 Structure: YYDDD-YYDDD I need to be able to output the two julian dates into two date type fields. ...
0
votes
1answer
29 views

SQL query not updating the target data extension

I am trying to run a sql query in Automation studio MC for syncing my Sync DE( Contact_salesforce) to a local DE. I want to send email to tht=at local DE in the next automation. But I am facing issue ...
0
votes
1answer
23 views

Marketing Cloud Query Activity

I have tried to run the below query in marketing cloud and it has thrown below error. "error saving the query field.an expression of non-boolean type specified in a context where a condition is ...
0
votes
1answer
28 views

Is possible to increase timeout for automation studio?

I have several SQL activities that are facing timeouts and I would like to know if it is possible to increase the time to get timeouts and why? Thanks
0
votes
1answer
21 views

Uniqueness of TriggererSendDefinitionObjectID

I'm trying to create an automation that will report on the performance of certain metrics for emails that belong to a Journey. What I want to know is how unique is TriggererSendDefinitionObjectID when ...
0
votes
1answer
696 views

Custom Personalization Strings in Content Builder

I am trying to add the ability to the marketing team to have custom personalization strings in Content Builder. I managed to create them but I have an issue to fill the Email Demographics table with ...
1
vote
0answers
712 views

SFMC: SQL Query on Dataview objects to calculate Journey's sends, opens, and clicks

I want to calculate the sends, opens, and clicks of all journeys. Expecting the records in Date Extension consists of records in the following sequence: JourneyActivityObjectID VersionID ActivityName ...
0
votes
1answer
33 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
1answer
15 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 ...
0
votes
1answer
32 views

Multiple Random Selections Using Union- erroring

I'm trying to get to a random 31,500 from one segmentation group and another random 31,500 from another segmentation group (and so on) in SFMC automation studio using SQL. I know I could create ...
0
votes
1answer
38 views

SQL Case Statement

I'm trying to write a SQL Query for the below. I have list of email address in table. I need to pull Subscriber key associated to that email address from another table if that email address is not ...
0
votes
1answer
39 views

Automation to check if the email is opened or clicked

How do I check and assign 'Yes' value to the 'open' and 'clicked' row in my DE that I retrieved in _sent data view. This is my current code: select j.JourneyName, j.VersionNumber, ja....
0
votes
0answers
14 views

Top 15 most recent JOBIDs for a subscriberkey

I am trying to write a query that can help us identify out of the 15 most recent JOBIDs a subscriberkey received, how many were opened? My thinking is to break this up into a few queries: 1. A query ...
0
votes
2answers
51 views

How to trim a particular word from a string and store it? [closed]

I am storing the URL given below in a field in DE: https://www.tbd.com/tbd-bags/?utm_source=sfmc&utm_medium=email&utm_campaign=_4in1tbd_20170421&utm_term=nav4+bags&utm_id=9087&...
0
votes
2answers
31 views

Create an automation without a PK

I'm trying to create an automation with a file import to update a data extension. The problem is that my data extension doesn't have a PK and in order to create a query activity to populate the data ...
0
votes
1answer
43 views

How to create a SQL query using CloudPage input?

I have a simple CloudPage using the Smart capture form. The form collects a number and writes that number into a column called 'input_number' in a data extension. Is it possible to fetch the '...
1
vote
1answer
42 views

How to determine when someone has been removed from a campaign (REST API)

Similar to this question, I'd like to be able to fire off a query (from an external application) to determine when someone is removed from a Salesforce Campaign. The question I linked has a good ...
2
votes
1answer
62 views

How to parse json using SQL in Marketing Cloud

I'm looking to use SQL, in an Automation, to parse JSON data which is a JSON Array. I have 5M+ rows where I plan to parse the JSON data and write it to another DE where each id value is a separate row....
0
votes
0answers
27 views

Query with no error in synthax doens'tgive any result

I have written this query: SELECT Client_Id__c, Abandoned_Cart_Id__c, Status__c, Total_Amount__c FROM Abandoned_Cart__c_Salesforce with Overwrite option. and the synthax is correct. The issue is ...
0
votes
1answer
38 views

How to identify the % of time between two dates + how to find records between two dates using SQL [closed]

Relating to my question related to finding a date X% between two dates using SQL, I'm having a challenge resolving two other similar things: How to identify the % of time between two dates. Here's ...
0
votes
2answers
44 views

Inner Join with Data view and data extension in SFMC not working

I am trying to do a inner join with one data extension and the data view _sent, inner join on subscriber key from _sent and contactid from data extension. I want to fetch the max event date i.e the ...
0
votes
0answers
32 views

Error in query with Synch DE

I have synchronized two De from sales Cloud successfully and I'm now trying to run a query using them. I get an error message which tells they are unknown. I wonder which can be the error causing ...
0
votes
0answers
29 views

SQL for creating a sendable DE from three DE

I have to create a sendable data extension, which joins three DE: - Contacts DE - Abandoned cart DE - Cart Items DE The three DE are linked in an attribute group. I have written the query selecting ...
0
votes
1answer
46 views

Marketing Cloud ContactID, where do I find this?

how do i access the "contactID" in SQL in the Marketing Cloud ? what data view is it in? when looking at contact builder I see data like this. Contact Contact ID: 1030837 Contact Key: ...
0
votes
1answer
31 views

Data View Counts not Matching FTP upload counts

So I am exporting data views using the automation studio for each of the data view objects (open, click etc.) to FTP then from there our backend guy takes these csv files and inserts them into another ...
0
votes
1answer
31 views

Ascending order date for each customer [duplicate]

I'm trying to see the order date in an ascending order for each customer and also how many order dates each customer has so I know how many times a customer has ordered. I've tried order by date but ...

1
2 3 4 5
12