Skip to main content

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
-1 votes
0 answers
36 views

How to get how many emails people are receiving emails over the past year?

I want to get the information how many emails people are receiving over the past year and can get an average number of emails per subscriber? how many are associated with each campaign.
Choudarie Shree's user avatar
0 votes
1 answer
31 views

Marketing Cloud - SQL Query to Return all Subscribers Who've been sent an email

I want to find out how many of my subscribers (who've been sent an email) have opened an email within the last 5 years. I want to include subscribers who've been sent an email and haven't opened an ...
Jeremy Thabes's user avatar
-1 votes
0 answers
36 views

Formula field not showing up in Data Extension

I'm creating a Data extension in Marketing Cloud, that data extension is created using Automation by running SQL query. But it's not considering formula field for data extension fields. Below is my ...
salesforce Developer's user avatar
0 votes
1 answer
34 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 ...
A S's user avatar
  • 5
0 votes
2 answers
47 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 ...
userjlq's user avatar
0 votes
1 answer
51 views

Syntax error in query

I have written this query in Automation Studio SQL query activity to retrieve data for unique opens in last 30 days. However i am getting this syntax error while trying to validate. Not sure why An ...
Srikanth Ryali's user avatar
3 votes
0 answers
85 views

Extract variable number of property values from a JSON Array in a Query Activity

Wondering if anyone working in SFMC/Marketing Cloud Engagement has a method for extract a variable number of array property values from a JSON string in a Data Extension row. I'm aware that the ideal ...
Adam Spriggs's user avatar
  • 30.8k
0 votes
0 answers
66 views

SQL code repeats result for first row only

I need to concatenate columns in an alphabetical order, so I used solution provided here: Marketing Cloud - SQL Contatenate columns in alphabetical order select stuff(x.c,1,1,'') as [constraint] from ...
MrSkover's user avatar
0 votes
1 answer
69 views

Marketing Cloud - SQL Contatenate columns in alphabetical order

I need to concatenate several columns in an alphabetical order. Is that possible to be accomplish with Marketing Cloud limited SQL? Below you can find the sample structure: SELECT TOP 1 constraintl, ...
MrSkover's user avatar
0 votes
0 answers
45 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 ...
Mik's user avatar
  • 3
0 votes
0 answers
42 views

Querying canceled job

received an email via Alert Manager telling me a job was canceled due to a script syntax error. Not sure which email it is but when I query the Job data view for that JobID, I get 0 results. At first ...
PForsyth's user avatar
1 vote
1 answer
74 views

Marketing Cloud SQL - check JSON array size

Inside the Data Extension there's one column that stores a JSON object. One of its key invalid_data is an array that can stores multiple smaller object inside. You can see the structure below: { "...
MrSkover's user avatar
-1 votes
1 answer
23 views

Update the existing records in DE, which is appended by another SQL with existing automation

I have an automation in which one SQL is appending the dada in Target DE, latter I found that this SQL is not getting values for all fields. So now I am updating my existing SQL to get values for ...
Krish's user avatar
  • 1
0 votes
1 answer
94 views

Error : Unable to create temporary data extension: Field name cannot contain the character ,. Field name cannot contain the character

SELECT LEFT(SUBSTRING(j.Journey_Name, CHARINDEX('_', j.Journey_Name) + 1, CHARINDEX('_', j.Journey_Name, CHARINDEX('_', j.Journey_Name) + 1) - CHARINDEX('_', j.Journey_Name) - 1), LEN(SUBSTRING(j....
Arpita Shrivastava's user avatar
1 vote
2 answers
81 views

Uniqueness in Click Data View?

The click data view unlike others seems to struggle with uniqueness. Job ID List ID Batch ID Subscriber Key Event Date UTC in Millisecond LinkName I thought these 5 fields from the _Click data view ...
GS Dev's user avatar
  • 15
0 votes
1 answer
25 views

SFMC - grouping records from Data Extension into 1 adding values into new fields

I am trying to group the records from Data Extension # 1 ('Self_Insurance_Expiry_Date_Entry_Step1') into Data Extension # 2 Data Extension # 1 has 2 records with same Contact__c and ...
Luiza Zdebska's user avatar
0 votes
0 answers
37 views

Lookup Customer Journey DE entry rowCount by using a field of a Data Extension

Hello everyone and thank in advance for helpfulness! Let me preface this by saying that I am working on Marketing Cloud and, specifically, I am in the process of building a Data extension of reporting ...
Fabrizio Mastracci's user avatar
0 votes
1 answer
41 views

How to get row number for jobid via SQL?

I am trying to extract jobids from data view bases on the emailname. How can I get the row number if I am getting back multiple jobids? I tried using ROW_NUMBER() but getting a static value of "1&...
kl2's user avatar
  • 353
0 votes
0 answers
22 views

SFMC Mobile Push Demographics - querying LastAppOpen, LastMessageOpen, LastSend, MessageOpenCount

I am working with a client that is looking to query MobilePush Demographics data such as LastAppOpen, LastMessageOpen, LastSend, MessageOpenCount. However, it seems these fields are not found in the ...
Ryan Moravick's user avatar
0 votes
1 answer
36 views

SFMC - SQL usecase

Write a query using SFMC Data View and save the records in data extension having Journey Name, Email, Journey Version, sent, open, bounce. (NEW DE fields: Journey_Name(text), Email(email), ...
wizard_of_salesforce's user avatar
1 vote
1 answer
63 views

Sendlog query is giving me only one records while there are lot of sent emails through various journeys

The query below is my Sendlog query however it gives me only one records, which makes nonsense as there are lot of other records should be popped up as emails were sent through various journeys. Can ...
손광섭's user avatar
-1 votes
1 answer
63 views

Can I merge multiple data extensions?

I am doing a task that requires personalization from multiple data extensions. Can I use Query Activities to merge them together? And also, can I use the finished merged data extension in my landing ...
xhb's user avatar
  • 21
1 vote
1 answer
61 views

Error when trying to update or insert records which are available from LIST To Data Extension using Sql Query

This is the Query I tried I am getting error stating Invalid Column Name "PhoneNumber" where I do have that Field presented in my List and Data extension also. What I am missing here, can ...
Vinu's user avatar
  • 21
0 votes
0 answers
47 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 ...
Spencer Bard's user avatar
0 votes
1 answer
62 views

Email Address is not showing in the Query Result

I am running below SQL Query to get the details from SFMC data views but I am getting NULL values on the Email address, journey name and journey version. Please help me to get these details Below is ...
Adam Coder's user avatar
-1 votes
1 answer
42 views

Segmentation by interaction in the campaign name marketing cloud

How do I create a segmentation in the marketing cloud of people who interacted with emails that contain specific names. For example, I sent campaigns with the name "events" and I want to ...
Victor Henrique's user avatar
0 votes
1 answer
66 views

Calculate Time to Open for emails sent from Marketing Cloud

I have a query to calculate TimeToOpen for emails and I'm getting both positive and negative values. How can a time that the email was opened minus the time that the email was sent be negative ? ...
GHG's user avatar
  • 15
0 votes
0 answers
49 views

Querying All subscribers to identify subscribers which are removed from Synchronized Data Extension

I'm trying to identify which subscribers are no longer part of the Synschronized Data Extension to be able to update their status in the All Subscribers List. I've used various queries, but I keep on ...
Daan Kramer's user avatar
0 votes
1 answer
68 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
23 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
84 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
101 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
  • 353
-1 votes
1 answer
86 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
  • 353
0 votes
1 answer
94 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
  • 5
0 votes
1 answer
130 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
61 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
79 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
151 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
155 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
204 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
66 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
  • 353
0 votes
1 answer
67 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
115 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
196 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
191 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
4k 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
158 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
88 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
78 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
130 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
2 3 4 5
20