There are a couple ways that this can work. I personally would use the STUFF method to create a delimited string in a single column instead of trying to do multiple rows.
Here is a previous SFSE answer discussing this.
And here is an article I wrote about this.
This is a sample query based on your above:
As Lukas mentioned, if you are doing this to be able to display information in the email, then DON'T - no need to transpose any data using SQL, as this is exactly what AMPscript is for.
I am assuming you want to display data for each person in an email and that you will have a reservation number in your sendable DE. Then, this should do it:
Yes, you are NOT allowed to use * while using joins in SFMC SQL activity.
And to answer your second question, YES there is a easy way of doing this.
You can just DRAG n DROP the DataExtension in the place of *
Please find the below illustration video link below.
Journey Builder, when configured in the entry event accordingly, listens to the run of an automation, not individual activities.
Your automation schema contains multiple automation activities from which you expect a result. This expectation is incorrect. You should get ONE injection into the journey once the automation is finished, which is your step 11.
You can use the Not Sent Tracking extract activity in an automation Studio. However, it is not a very well documented feature of Salesforce.
Alternatively, you could also try to subtract the bounces, sent, supressed(if any) from the target data extension and get an approximate count of not sent.
We have developed a similar solution for our client.
The idea is to use the automation instance object in conjunction with ssjs. There is a field known as 'Status' which you can use to fetch the instance status. However, the catch is that this object does not accept and clause. That means you can't say all the automation instances that ran in last 7 days and ...
You aren't joining to _Sent using the SubscriberKey, so for each row in your Unsubscribe Survey table, you'll be getting a row for everyone sent the email. The join should look like this:
[Unsubscribe Survey] u INNER JOIN
ON u.JobID = s.JobID
AND u.ListID = s.ListID
AND u.BatchID = s.BatchID
AND u.SubscriberKey = s....
You could break down your query into multiple ones where the first uses the "Overwrite" option and the following append the further data.
To do so you need a field that you can use to segment the data, so the separation is possible without missing some data.
For example if your DE contains customers and you have a numeric customer number, you could ...
Assuming that in each of the phone numbers, what you are looking for is prepending the part you want to keep, the following should work:
Replace all spaces inside of string via REPLACE()
Cut off all 'extra' characters from the left side of the string via RIGHT()
This should then leave you with your phone number without the unnecessary prepends or the ...
You're not selecting Country in your subquery. Assuming it's present in the [Master DB] data extension:
WHEN x.Country IN ('US', 'CA') THEN 'NA'
WHEN x.Country IN ('DE', 'FR', 'GB', 'DK', 'FI', 'SE', 'AT', 'PL', 'PT', 'NO', 'NL', 'IE', 'BE') THEN 'EU'
l.Email AS Email
,l.et4ae5__HasOptedOutOfMobile__c AS SubscribeSMS
,l.HasOptedOutOfEmail AS SubscribeEmail
,NULL AS FirstName
,NULL AS LastName
,l.Subscriber_ID__c AS SubscriberKey
,'Lead' as Source
,NULL AS MobilePhone
FROM Lead_Salesforce AS l
WHERE ISNULL(l.Subscriber_ID__c, '') <> ''
AND NOT EXISTS (SELECT 1 FROM Contact_Salesforce AS c ...
If you don’t set up your journey to have paths that can go into day 7, there’s no way to inject them directly into day 7. You’ll need to either use data attributes and a Decision Split to put them there.
For example, if you have a column name with day and a numerical value of 7. Then at the beginning of the journey have a Decision Split that puts them before ...