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 ?
Thanks for expanding on your question. What you describe should be possible, however you would need to:
- Create a new DE containing a subscriber/recipient field as the Primary Key and also add boolean fields for link1, link2, link3, etc.
- 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.
- 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.
- 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.
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, TriggeredSendCustomerKey FROM _click WHERE LinkName = 'Privacy Statement'
LinkName is the alias name that appears in your HTML email. For example,
"Privacy Statement" in the example below:
<a alias="Privacy Statement" href="http://www.domain.com/privacy" title="domain.com/privacy" 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.
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).