New answers tagged query
0
I did not have to go through a similar scenario but I think you have your answer in the following part of the documentation.
We recommend the following:
When possible, avoid paging by filtering your queries of external objects to return fewer rows than the batch size, which by default is
500 rows. Remember, obtaining each batch requires a queryMore() call,
...
0
First of all, I have to call this out: this is a very poor data model. By storing data in CSV in a text field, you are sacrificing much of the innate capability of the Salesforce database or indeed any database to effectively relate, index, query, and search structured data. That's going to make all further implementation you do more expensive, less ...
0
In the majority of places, Marketing Cloud uses Central Standard time with no Daylight Savings.
Within the last few years, SFMC started supporting the AT TIME ZONE directive in Query Activities. With that you could do something like this in your query:
SELECT
u.UserId
, u.EmailAddress
, p.LastLogin
FROM ENT.[User] u
INNER JOIN Player p ON u.UserId = p....
0
You can't return the fields that you specify by querying Mobile Lists. Somehow, MC only lets you retrieve SubscriberKey from Mobile Lists both for MobileConnect and MobilePush.
You can use MobileConnect data views to retrieve all the fields available on MobileConnect Data. Please check this link to see available fields and data views. From these data view, ...
0
If you do a retrieve call on this object(DE) you will get the fields that are queryable.
But the only useful one is Subscriberkey it appears all the other fields have the same values for the entire audience
1
You have to specify if you only want unique opens -- otherwise, _Opens reflects every open of an email by a subscriber:
select
o.SubscriberKey as 'EMAIL'
, o.eventdate as 'Data_abertura'
, o.jobid
, o.ListID
, o.BatchID
, o.TriggererSendDefinitionObjectID
, j.EmailName
, j.EmailSubject
, j.DeliveredTime
, j2.JourneyName
, j2.VersionNumber
, j2....
0
I am guessing your dates are in the following format: DD-MM-YYYY and you are looking to output them as YYYY/MM/DD? If so, you should be able to use the CONVERT() function to do this and not need to do substring and concat functions.
Lets say the [date] value is '22-12-2020' (where this is December 22nd, 2020) and you want the date to be displayed as 2020-12-...
1
You would need to find the foreign key(s) that correctly matches each row in the two DEs for this task.
As @MateuszDabrowski stated, INSERT INTO, etc. are not acceptable, only queries via SELECT. To this extent you need to use a JOIN in your SQL Query to accomplish this.
To that extent, you would want something like the following:
SELECT a.primarykey,
b.[...
1
Marketing Cloud doesn't support INSERT INTO, just standard SELECT.
Use SELECT with JOIN in a Query Activity of the Automation Studio and output to the desired Data Extension.
1
As you want to use a query for that, you can do it with a simple join:
SELECT
de2.EmailName
, de2.TotalSent
, de2.UniqueClicks
, de2.UniqueOpens
, de1.SourceCode
FROM DataExtension2 AS de2
LEFT JOIN DataExtension1 AS de1
ON de1.EmailName = de2.EmailName
However, I wouldn't recommend doing it that way, as it would require ...
0
One of the many ways to achieve this is by using Ampscipt. Assuming you want to retrieve and store this data while sending the email only. Below is the code -
%%[
set @emailName = EmailName
set @sourceCode = SourceCode
UpdateDE('Data Extension Two',1,'EmailName',@emailName,'SourceCode',@sourceCode)
]%%
This script will go into your email message and will ...
0
Please try below SQL.
SELECT Acc.Id
,MAX(CASE
WHEN Eve.Status__c = 'In Progress'
AND Eve.Subject = 'Event'
THEN 1
ELSE 0
END) AS [HasBookedEvent]
,MAX(CASE
WHEN Eve.Status__c = 'Completed'
AND Eve.Subject = 'Event'
THEN 1
ELSE ...
0
The Job data view picks up all email sends, so no need to adjust it for the use case you described:
NOTE The Job data view provides results for all email send jobs in the
Enterprise. The data view doesn’t categorize the results by send
method.
Source: https://help.salesforce.com/articleView?id=sf.mc_as_data_view_job.htm&type=5
If I am not mistaken, the ...
0
You will need to aggregate the sent data and then join it with _ListSubscribers data view to get the list name.
This should do the work:
Select
s.JobID,
count(s.JobID) AS [Emails Sent],
MIN(s.EventDate) AS [Sent Date],
l.ListName
FROM _Sent s
JOIN (SELECT DISTINCT ListID, ListName FROM _ListSubscribers) l
ON s.ListID = l.ListID
GROUP BY s.JobID, l.ListName
...
Top 50 recent answers are included
