My go-to is to leverage the hidden, pseudo-identity _customObjectKey column to break up queries into manageable chunks. As long as your scheme can accommodate the overwrite-update/append-update/append-x pattern:
Here's a 4-group example using the mod operator:
Query 1: where de._customobjectKey % 4 = 0 /* action:overwrite */
Query 2: where de....
I would look into using Query Studio which is much more user-friendly and better for trial and error development work. Query Studio. It will help you as you learn and get up to speed.
I'd also really recommend reviewing the query/SQL documentation that SFMC has provided because it will help understand the limitations of SQL in SFMC vs the outside world. ...
Held and unsubscribed are both statuses subscribers can have in Marketing Cloud.
Held: Three or more bounces, soft or hard, are received for the subscriber and more than 15 days have passed since the first bounce.
Unsubscribed: An unsubscribed status prevents all sends to ...
The SQL validation in Marketing Cloud does have a strange quirk that it seems to treat underscores (_) near the From string as spaces - this causes the query to interpret the From part of Valid_From as the command that's responsible for the source of Data.
Since it's not the last column selected, the system tries to look for a data extension named ,.
You can't remove specific records from a data extension using a Query Activity. Query Activities can only Overwrite, Update or Append records, not remove them. So, you'll need to take a different approach.
If you want to avoid SQL, create two filtered data extensions - one with a filter selecting only records where the email address field ends with your ...
When coding AMPScript using a Shared Data Extension in an Enterprise 2.0 account, you add the prefix "ENT." to the data extension name.
When you choose "External SFTP" as the location type, your Url field should have the absolute path to the ftp folder you need to access.
You can find your folder's absolute path by viewing the folder in an FTP client and right-clicking on the folder to "Copy URL(s) to Clipboard" (that's the option in FileZilla)....
You can utilize the SQL function MAX() to get the most recent date from the field. This combined with a GROUP BY will ensure you only get the most recent date with each unique record.
As you are working with 3 data views, the below may time out, so you may need to split it into multiple queries to ensure it runs, but below should do it:
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....
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:
Replace your single quotes surrounding the Data Extension name with square brackets. Any field or data extension name that contains a space should be surrounded with brackets.
from [_bounce] o
INNER JOIN ent.[EDU SPIRIT ...
There is no automatic way to listen to imports/updates to a Data Extension. If you import a CSV a file drop automation could be suitable, otherwise you will need to trigger the API using a program/code. However, check if a realtime trigger is really necessary for your use case or a data extension entry source with a recurring schedule is also sufficient.
You need to use CASE - WHEN - THEN - END constructs in Marketing Cloud, as Microsoft SQL is used in the background.
It somewhat looks like this:
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
I'm not sure if your is alphanumeric expression works, but if it doesn't you ...
If you're tracking conversions using the Collect Implementation that shows up in Web & Mobile Analytics and have implemented a Catalog in Email/Web Recs, you can use the IGO_PURCHASES joined to the IGO_PROFILES Data Extensions. There's the JobID, BatchID etc you need to join to your _Job Data View in IGO_PURCHASES, providing you're using Conversion ...
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.
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:
If you want to do a daily SMS report, I would advise not to use the SMS Message Detail report, but to go straight for the the data from the SMSMessageTracking data view:
The data is much more robust - 41 fields vs 13 in the report
It's a data view, so you can grab it directly with SQL (no export and import) and apply any transformations you might need at ...
I've previously outlined my go-to for timeout mitigation here.
You can use the hidden _customObjectKey column in every DE and the % mod operator to split the processing of your query into multiple queries. For your query, you could split it into 3 queries like this:
There is an option to add CC in the user initiated definition (assuming the cc business rule has been enabled for your account). You can set a personalization string such as %%CC_Address%% in the value that is either a DE Column or a variable set in the Ampscript of the message.
Data Extension Setup:
User Initiated Send Definition Setup:
Use a query activity with overwrite as the update type on the DE with something like:
Obviously 1 does not equal 0 so it should overwrite it with zero records.
Let break this down in to steps:
First, start by giving each record a value of 1 in the column where where applicable
IIF(Source = 'Phone',1,0) as Source_Phone,
IIF(Source = 'Web',1,0) as Source_Web,
IIF(Source = 'facebook',1,0) as Source_facebook
Based on your example above, you'd get a result set as such
You can do all of this in an SSJS Script Activity of Automation Studio
Data Extension name and external key: recordsToUpdate
Custom object: fromSFMC__c
Flag field: flag
Field you want to send to SF: datafield
You can use below SSJS. It will also update the flag in the data extension, so you can keep track on what has been sent to ...
The primary key on _Sent is [JobID,ListID,BatchID,SubscriberID], so you can either change the primary key on your results table to avoid the error, you can use a GROUP BY clause in your query or use a PARTITION BY clause in a subquery and restrict rows returned after partitioning.
SELECT sub.SubscriberKey AS SubscriberKey,
The reason for your timeout error is you are running an expensive query that is timing out after 30 minutes. Salesforce has a useful page on optimisation of query activities. Many of the points I think you'll find useful in tuning the query above, most notably the use of intermediary/staging tables and joining on Primary Key fields. Also consider the need ...
Not being an SQL guru, I can't tell you whether this is the most efficient way of fetching and counting all the unique domains, but it at least does the job:
SELECT RIGHT(Email, LEN(Email) - CHARINDEX('@', email)) as emaildomain ,
COUNT(Email) as EmailCount
FROM [Your data extension]
WHERE LEN(Email) > 0
GROUP BY RIGHT(Email, LEN(Email) - CHARINDEX('@'...
Looks like you're utilizing all of the tricks already.
I'd build a new DE for the Opens and revise your query left join to it.
, max(o0.eventDate) eventDate
from _Open o0
where o0.isunique = 1
group by o0.subscriberid
/* name: MostRecentOpens */
/* target: MostRecentOpens */
/* action: overwrite */
Then I'd repeat ...
You just need a SQL to join your pickup data extension with Master data extension and populate the target data extension with whatever additional attributes/ fields you need. Then put that query in an Automation and schedule it to run every hour.
You can find those records containing multi-byte characters by converting the First Name field to a single-byte data type and then comparing against the First Name for equality:
FirstName != cast(FirstName as varchar(1000))
If you're specifically interested in locating records that contain the ...
You can do this using the functions DateAdd, DateDiff and a multiplication. The query could look like this:
DateAdd(day, DateDiff(day, movein, moveout)*0.7, movein) as [reminder_date]
For your example this results in the date 2020-01-22.
DateAdd function reference
DateDiff function reference
This is because all your columns in select statement needs to be in the group by clause. Ideally, it should only be the subscriber key / job id(In case you are trying to find the max sent date for a job). Refer to this article