1

I'm trying to build a segment based on click behavior, but I'm not that familiar with SQL and running into issues. I'm hoping that someone here might be able to help :)

Essentially, I'm looking to create a query that gives me this:

Email Addresses from within a data extension (let's call the data extension BRAND_TABLE) where [FIELD1 = true OR FIELD2 = true] AND where Join Date is BEFORE 1/1/2013 AND [where last click date is BEFORE 1/1/2013 OR where a click has never happened]

1
  • What do you consider a click?
    – dphil
    Jan 5, 2016 at 16:20

2 Answers 2

0

It looks like you are trying to build a segment that consists of inactive email addresses. The code below is 90% complete.

I have broken my answer into two separate queries and leave the rest (combining, correcting, simplifying) for you.

First, you might want to read up on data views found here at _Click Data View. This table contains all the click events. The trick here is that you are combining information from two different tables so you need to perform an operation called inner join that matches on a table field that exists in both tables, in my example below it is, SubscriberID but depending on your table fields, it could be different.

More information on JOINS can be found here at JOINS on TECHNET

SELECT b.email_address FROM BRAND_TABLE b JOIN _Click c ON c.SubscriberID = b.SubscriberID WHERE (b.FIELD1 = TRUE OR b.FIELD2 = TRUE) AND b.join_date < '2013-1-1' AND c.EventDate < '2013-1-1'

The second part of this code is to select email addresses that have not engaged in any click activity in other words, email addresses that do no exist in the _Click data view. This is better known as an Anti-join.
SELECT b.email_addresses FROM BRAND_TABLE b WHERE NOT EXISTS (SELECT b2.email_addresses FROM BRAND_TABLE b2 JOIN _CLICK c2 ON c2.SubscriberID = b2.SubscriberID)

Additional notes:

  1. You should be able to omit the join date criteria from the first query because it is impossible for a subscriber to have a click event date before their join date.
  2. SFMC is based on SQL SERVER (TSQL) whose syntax differs slightly from SQL. I didn't realize this the first time around!
0
0

You can do it in a single query with a LEFT JOIN on _Click:

SELECT 
b.email_address
FROM BRAND_TABLE b
LEFT JOIN _Click c ON (c.SubscriberID = b.SubscriberID)
WHERE (b.FIELD1 = 1 OR b.FIELD2 = 1)
AND b.join_date < '2013-01-01'
AND (c.SubscriberID is null OR c.EventDate < '2013-01-01')

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .