Query Activity and Decision Split for DOI

Question

I read a lot about the query activity and Data View: Click, but I can not get this query activity to run.

In an AMP scripted multilingual Double Opt-in process I want to check the click on a confirmation link to accordingly split the journey. Engagement Split is not working here so I need to use the decision split.

I have built up a DE with all fields as described here. Then I tried to do it this way with no success.

How should my SQL looks like, if I want to use a field in the decision split "Click on Confirmation-Link TRUE or FALSE". Linkname/alias is 'Confirmation'.

Thanks for your help Oliver

Answer 1

You don't mention what the issue is with Engagement Splits as they sound like exactly what you need here.

In the absence of engagement splits, you could roll your own, but you would need to add your data extension to an attribute group in order to use in a decision split. This is similar to Eliot's answer.

Create Data Extension - (DoubleOptIns)

Add Your Data Extension to Contact Model

Add your data extension to an Attribute Group in Audience Builder -> Contact Builder -> Data Designer. You would link the new data extension from Contact.ContactKey to you data extension's SubscriberKey field.

Query Activity

Create a new Query Activity in Journey Builder -> Automation Studio -> Activities. The SQL needed should be similar to this:

SELECT DISTINCT
    SubscriberKey
FROM
    _Click WITH (NOLOCK)
WHERE
    LinkName = 'Double Optin Link' AND
    IsUnique = 1

And the query should be set to Update the DoubleOptIn data extension:

Automation

Create a new Automation in the Overview tab of Automation Studio and add your newly created Query Activity. Schedule the Automation to run once per hour.

Decision Split

You should now be able to use your new data extension in your Journey Builder Decision Splits.