Can a Query Activity update a Data Extension in real time or is it only used to populate the historical data in the Data extension ?

  • You really need to provide more information. You have tagged this question with journeybuilder. Are you referring to the Update Contact Data Activity on the Interaction Canvas? Or a Query Activity in Automation Studio/Email Studio? What do you mean by the "historical data"? – Eliot Harper May 11 '16 at 11:05
  • I am referring to Query activity under Automation studio. Below is my requirement 1. We want to capture the link clicked in an email (during the journey) in an Data Extension in real time 2. Use that link captured in the Data extension in the Decision split(Journey Builder). [Ex: If user has clicked link 1 then do action 1 , else 2. If he clicks link 2 do action 3, else 4) How can we implement the above use case in Journey Builder – Ajit Mishra May 11 '16 at 11:29
  • @EliotHarper I have posted the question salesforce.stackexchange.com/questions/209848/…, as an extension of your solution. Thanks – Chandan Feb 28 '18 at 5:12

Thanks for expanding on your question. What you describe should be possible, however you would need to:

  1. Create a new DE containing a subscriber/recipient field as the Primary Key and also add boolean fields for link1, link2, link3, etc.
  2. Create a Query Activity to update the Data Extension you created from Step 1 to use the Click Data View so that it updates the boolean fields in the DE for each recipient.
  3. Create a one-to-one cardinal relationship to the DE you created in step 1 in Contact Builder, mapping the recipient identifer back to the Contact Key of the Contact Record.
  4. Now you can use a Decision Split Activity in a Journey Builder Interaction to define multiple conditional branches, based on the value in the DE.

You would then create an Automation to run the Query Activity you created in step 3 prior to firing the Event.


@ajit, based on your latest response, I have revised by updated answer below. Here are the procedural steps that you need to follow to get this to work.

1. Create Target DE for Click Data

Create a new Data Extension containing the required fields documented in the click data view table. Important note: the field types need to match exactly, otherwise your query will fail when executed. Refer to screenshot below.

DE fields

2. Create query to populate DE fields you created in step 1

Create a query that looks like this:

SELECT SubscriberID, SubscriberKey, ListID,
EventDate, Domain, URL, LinkName,
FROM _click
WHERE LinkName = 'Privacy Statement'

Note that LinkName is the alias name that appears in your HTML email. For example, "Privacy Statement" in the example below:

<a alias="Privacy Statement"
target="_blank">privacy statement</a>

Obviously you can include multiple LinkName values in your WHERE clause using an OR statement.

3. Run query

Run the query and you should see that the Data Extension you created is populated.

Sidenote: when you add an Email Activity in a Journey Builder Interaction and publish, it actually creates a Triggered Send in the Email app. If you open Triggered Emails from the Interactions menu, then on the left tree view, you will see your emails used in your Journey Builder Interaction within the 'Journey Builder Sends' directory. In the example below, the 'Imagination Series Intro' Interaction has two versions and here you see all emails used in the current version. The value for TriggeredSendCustomerKey is the four digit External Key on this page. You will see this appear in the DE that you populated from your previous query.

Triggered Emails page

4. Create Data Extension and Query for use in Interaction

You can now create a second Data Extension and a new query to update the fields of the second Data Extension based on the result of the previous query. You will use this Data Extension in your Decision Split Activity within a Journey Builder Interaction (note you will need to create a one-to-one cardinal relationship to this DE as described earlier).

  • Thanks for the response but my query is still not reflecting rersult. Below is the query i have used 'SELECT distinct SubscriberID as SubscriberKey, case when LinkName = ''abc.com' then '1' when LinkName = ''xyz.com' then '2' end Link1 FROM _click WHERE TriggeredSendCustomerKey = '16082' ' Am i using the correct format for LinkName, what does the LinkName refers to URL or the text 'Visit abc' redirecting to URL abc.com. The above two links are present under link activity section of the Email with external key 16082 – Ajit Mishra May 13 '16 at 12:27
  • I've revised my answer based on your feedback. – Eliot Harper May 16 '16 at 3:05
  • @ Eliot Thanks.Appreciate help. Have one final question, Is there a way, not to hard code the TriggeredSendCustomerKey value. For my use case going by your approach first i will send emails to the customers,(Intera1) would wait for the external key to get populated in the JB send data( Tracking info). Then would write a query to populate the values under Link1, Link 2etc fields and then use those values under decision split by creating interaction 2 . Is there a way to automate the process. The idea is to not to wait for getting the value of TriggeredSendCustomerKey manually & then fire query – Ajit Mishra May 16 '16 at 10:26
  • i can use automation to schedule my query however i have to wait for the value of TriggeredSendCustomerKey to write my query and then schedule it. This can be done only when the first Interaction(Sending emails with link) is already in running mode and the value of TriggeredSendCustomerKey gets populated – Ajit Mishra May 16 '16 at 10:31
  • If you are sending an Email (using the Send Email Activity), you would include a Wait Period to allow time for the Contacts to have a chance to open their email. You would then create a separate scheduled Automation. Regarding TriggeredSendCustomerKey, you don't have to use this and after thinking through this, it's probably wise that you don't (as if you publish a new Interaction version then your TriggeredSendCustomerKey value would change). I'd just use a specific LinkName as your alias value in your email links and include that in your query to update the Data Extension. – Eliot Harper May 16 '16 at 11:01

Not the answer you're looking for? Browse other questions tagged or ask your own question.