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
1 answer
38 views

More elegant way of keeping count of records in multiple data extensions?

I am trying to keep a weekly count of the number of records in a number of data extensions, in another data extension. But the limitations of SFMC's SQL are too much for my limited knowledge of SQL, ...
Michel Hendriks's user avatar
0 votes
0 answers
18 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(...
Nicole Jung's user avatar
0 votes
0 answers
41 views

ERROR: Could not convert date and/or time from string data type

We are trying to convert a string field to a date field using the CAST function in Marketing Cloud. Our goal is to confirm that people have not made a purchase in the last 7 days, in fact from 5pm one ...
Silvia Stefanie Marsillac Font's user avatar
0 votes
1 answer
55 views

Extracting _sent data view from parent business unit via SQL query not returning all records

I am trying to extract _Sent data view records in a data extension via SQL query from the parent / enterprise business unit in SFMC. When I am running this query (expectation is that extracted data ...
kl2's user avatar
  • 325
-1 votes
1 answer
40 views

How to create a sql query in SFMC with where clause with date and time

I am trying to create a query that pulls the record from _open data view before a specific date and time, how to do that in sql? I am currently doing this for defining the date: Select * from _open ...
kl2's user avatar
  • 325
0 votes
1 answer
71 views

Query for Subscribers Click Counts and SendLog

I'm in the process of building a query that will give me a count of all links a subscriber has clicked on from recent sends. I have a query written out using the salesforce documentation, and I have ...
A S's user avatar
  • 1
0 votes
1 answer
49 views

How to get average of email Sent per month per Account from SFMC?

I want to get the average number of email send on monthly basis per account from the Salesforce. I have created a Master DE in SFMC with Contact & Account Data combined in it and I was trying to ...
Adam Coder's user avatar
0 votes
1 answer
46 views

Querying "Send Count" and "Last Send" in "Push Demographic Table"

There are a few fields that appear in the "Push Demographic Table" (Only Available in Data Designer - Contact Builder) but not on _PushAddress Dataview Table like "Send Count", &...
Khoa Nguyen's user avatar
0 votes
0 answers
53 views

Combining SFMC Job, Send and Click Tracking Extracts to create Impression Tracking by Job Report

I am trying to create a custom report in SQL to mimic following 'Impression Tracking by Job Report' in SFMC for all Jobs but currently testing with just one job ID to narrow down my testing by I am ...
Pardeep Singh's user avatar
-1 votes
1 answer
93 views

Unique Clicks per Subscriber and Unique Clicks per subscriber per link/url

I am trying to achieve following results using SFMC data views and SQL but unable to produce a query that can meet all these requirements in one piece of code. Also, my requirement is to create a net ...
Pardeep Singh's user avatar
0 votes
1 answer
69 views

How to convert text data type to emailaddress data type in marketing cloud

I have a SourceDE with field name "Email" as a text data type, but this field has emailaddresses only. I am writing a Query Activity in marketing cloud to tranfer the data from SourceDE to ...
SFMC User's user avatar
0 votes
1 answer
80 views

Trying to write a sql query with ssjs

I'm trying to write a SQL query dynamic with ssjs. Right now i did this code <script runat="server"> Platform.Load("core", "1.1.1"); try { var today = new Date(); ...
Magalí's user avatar
0 votes
0 answers
44 views

How to pull unique values from unsubscribe data view when there are duplicates?

Currently, we are pulling the data from data view _unsubscribe with this approach (where primary key is concatenation of all fields). The reason we need a primary key is to avoid adding duplicate data....
kl2's user avatar
  • 325
0 votes
1 answer
54 views

Query the AddMethod field containing the SalesforceSubscriber value

currently I'm working on identifying the contacts that are in Marketing Cloud and came from the Marketing Cloud Connector. If I query the _ListSubscribers, it will only show the following values for ...
MHG's user avatar
  • 15
-1 votes
1 answer
77 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 ...
Venkat's user avatar
  • 11
0 votes
1 answer
55 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 ...
Noubar Topalian Rivas's user avatar
-1 votes
1 answer
93 views

SQL CASE Statement is not working in SFMC Query

I am writing a query to get the specific value based on the CASE Statement. I am checking with two conditions. One for the value of the column name and other on the if another value exist in the other ...
Adam Coder's user avatar
0 votes
1 answer
1k views

SQL Query Issue to get last 12 Months data from a DE in SFMC

I am trying to get the data from a DE for last 12 months but when I am running my Query, I am only getting data November 2022 but I want to have data from each month after from November 2022 till now ...
Adam Coder's user avatar
0 votes
1 answer
86 views

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

I have a set of steps that I want to process in Salesforce Marketing Cloud, but I'd like to make it easier for my users. I have a set of steps I want to run if a column is "True" and a ...
Greg Whitaker's user avatar
0 votes
1 answer
77 views

Export view details from Journey Builder

I don't see a way of exporting this view from a journey activity I've tried with SQL but I only see one activity. SELECT sub.EmailAddress as email_address, ja.ActivityExternalKey FROM _Sent s ...
Johhnytska's user avatar
0 votes
1 answer
65 views

Query using CASE WHEN erroring

I am working on a query to reduce duplicates in our dataset, and I keep running into an error: "An error occurred while checking the query syntax. Errors: Incorrect syntax near '077'." The ...
user55359's user avatar
2 votes
1 answer
117 views

Removing 1 billion rows of a DE

As the title suggests, I need to figure out how I can remove ~1 billion rows of a Send Log data extension. I can't even change the data retention policy until the count is < 100 million. I thought ...
PForsyth's user avatar
-1 votes
1 answer
59 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 ...
lisim's user avatar
  • 1
1 vote
1 answer
72 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" ...
Anay Shrikar's user avatar
3 votes
2 answers
165 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 ...
Maciej Barnaś's user avatar
0 votes
2 answers
88 views

Combine two SQL queries in Salesforce Marketing Cloud

On the SMC platform, we employ two SQL queries to fetch data pertaining to a specific column. We pull data from two tables in the Data Extension. However, when attempting to merge these two queries ...
Larisa Rusinovich's user avatar
0 votes
1 answer
64 views

SQLQuery for non Opens for 2 Journeys

I'm trying to build SQL query for non-openers for 2 Journeys: X and Y Could you please advice what is wrong here? I didn't get any error. Simply I got 0 results or results are wrong. SELECT DISTINCT s....
Natalia's user avatar
0 votes
0 answers
38 views

LWC Lookup does not return value for different language

We have a screen flow hosted on SF Community Site with language option as English , Spanish ,Portugues & Creole . One of the screen has a lookup (LWC component) which displays list of records and ...
Manish Das's user avatar
0 votes
1 answer
65 views

Query Returning Duplicates

Goal: Query all emails sent for particular emails with the contact information and bounce information for each. Issue: Query results contain duplicate records. Question: How can I modify the query so ...
MarquezMoore's user avatar
0 votes
1 answer
80 views

validate cloudpage data

I'm very much stuck on how to approach this, so any guidance would be greatly appreciated! Background A client wants to build a simple cloudpage from which entities will fill in their details, and ...
JordanMiguel's user avatar
-1 votes
1 answer
78 views

Hi, does anyone know where is the error of this query?

does anyone know where is the error of this query? Its returning no results. Thanks! SELECT smt.SubscriberKey, smt.CreateDateTime AS Sent_Date, smt.Mobile, smt.MessageText AS SMS_Text, j.JourneyName ...
Weslei Carvalho Santos's user avatar
0 votes
1 answer
50 views

Any suggestions on better or optimized approach that returns single row from multiple rows based on ROW_NUMBER in SFMC

I have a customer data extension where customer records are saved with order numbers like this: CustomerOrderDE: CustomerID OrderID OrderTotal 1 101 $100 1 102 $200 2 103 $50 3 104 $75 3 105 $60 ...
kl2's user avatar
  • 325
0 votes
0 answers
51 views

After inserting record's into SFMC Data extension, I see some unwanted texts are inserted along with normal text

I have inserted record's into DE. But after insertion, I can see some unwanted texts are also insered into the fields. Expected: question (field) How are you I&I ? (Records) uploaded records: ...
SFMC Developer's user avatar
1 vote
3 answers
275 views

Dynamic SQL Top() clause in SFMC

I am looking to automate an IP Warm Up process. We have our plan that defines the number of contacts that should be sent to on each day across each domain group. I would like to automate this, so am ...
Chris Hughes's user avatar
0 votes
1 answer
175 views

Recommended approach for coupon assignment in SFMC?

In SFMC, we are currently using claimrow() approach for coupon assignment but in past we have seen some issues with this where same coupons getting assigned to same subscribers since coupon email is ...
kl2's user avatar
  • 325
0 votes
1 answer
71 views

Combining 3 SQL Queries into a Single Query

I'm facing a challenge with my SQL query in Marketing Cloud and would appreciate the community's help in resolving it. Recently, I created three separate Data Extensions as part of a segmentation. To ...
Mik's user avatar
  • 3
0 votes
0 answers
55 views

deduplicate SQL query using ROW NUMBER() method

I have a scenario where our SFMC instance is receiving leads daily from Salesforce through the synchronized lead object data source. these leads are ingested into salesforce from several third party ...
sam sfmc's user avatar
-1 votes
1 answer
76 views

case : check if field A exist in field B

i have a data extension with the first 3 fields : Id, Email_source1 and Email_source2. i want to create the field named X. The goal is to check if Email_source1 is in Email_source2. Does someone know ...
ayakuza91's user avatar
0 votes
1 answer
58 views

How to get specific number of email send within a time

I want to get a list of Subscriber that has received 13+ email in July and know their aggregate unique Opens. Here is what I did but there is no result returned. SELECT se.SubscriberKey, sub....
Emily714's user avatar
0 votes
0 answers
72 views

Window Calculations in SAQL

I'm new to SAQL. I'm seeing a reference example where they are calculating three months ago: sum(sum(A)) over ([-3..-3] partition by Manager_Name order by ('DATA_DT_Year', 'DATA_DT_Month')) as '...
Venkata's user avatar
0 votes
1 answer
111 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 ...
JNC's user avatar
  • 39
0 votes
1 answer
65 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 ...
Sohail's user avatar
  • 549
0 votes
0 answers
112 views

Query Error: Automation failed due to system error

I am seeing this error when trying to run a query in Query Studio attempting to return other products an author has written for a given user with a recent order: "Automation failed due to system ...
Collin O'Rear's user avatar
0 votes
1 answer
37 views

Same selection query is giving different results in terms of selected records

I have a 3 step automation: Step 1 selects the audience for the journey entry data extension (this overwrites the DE Offer_Extension_Warmup_Journey_Entry) Step runs the exact same query but logs the ...
Pruteik's user avatar
0 votes
0 answers
77 views

SQL Query Not quite working - Unengaged Contacts

I'm using SSMS and trying to identify contacts that have received 10 or more emails in the past 1 year and have not clicked or open any. When I run the query, some contacts fit the parameters, but I ...
Gbengoose Wuru's user avatar
-1 votes
1 answer
83 views

Data Extension - Combine records with same value

I need to combine rows with the same CONTACTKEY and instead of having two with different "Topic" value, it should have Topic set as "Both". In the Data Extension both Email and ...
MrSkover's user avatar
0 votes
1 answer
67 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 ...
Boody29znexus's user avatar
1 vote
2 answers
450 views

How do we update subscribers email address in all subscribers list when contact change their email?

Contact located in Salesforce Service is added with "[email protected]" mail. Salesforce Service is connected to Marketing Cloud. Note: Subscriber Key is Salesforce Contact ID. Subscriber related ...
cameron harwood's user avatar
-1 votes
1 answer
53 views

people who clicked on the last 4 emails and did not buy

I need to make a sql to get the subscribers that clicked on the last four emails I sent and did NOT make a purchase. it is a simple sql I know, I have tried several ways but it is not returning data ...
user130272's user avatar
0 votes
1 answer
83 views

Marketing Cloud SQL String Formatting to Retrieve Only Part of Data

I am importing a file data which cannot be edited from the source. I am trying to modify and extract the only needed information via Marketing Cloud SQL. Below is the original file field data and the ...
Hannah Kim's user avatar

1
2 3 4 5
20