0

we are querying several data views such as the click of specified links (via web collect) and email opens and email clicks. In the target Data Extensions that stores all (unique) clicks we experienced that some contacts, from the same company, are clicking several links at almost the same time. We found out that this is due to spam bots that run the moment an email has been opened in the back and "click" all links within an email.

So we build a query to somehow filter "around" bot clicks, assuming that those bot clicks take time in the very first seconds of an open and all within one or 2 seconds so a behavior like this is an indicator that its a bot and not a human clicking.

Then we join the result with the Data Extension where our contacts are stored. However, we still have multiple records for one and the same URL and subscriber key in the resulting DE, even though we are using "unique" as a statement.

Queries are the following:

Select
o.JobID,
o.SubscriberKey as Subscriber_Key,
sub.EmailAddress as Email,
c.EventDate as EventDate,
c.url as URL,
c.IsUnique as IsUnique
from _open o
left join _job j on o.jobid = j.jobid
left join _Subscribers sub on o.subscriberkey = sub.subscriberkey
left join _Click c on o.jobid = c.jobid and o.subscriberkey = c.subscriberkey
where c.eventDate > dateadd(day,-30,getdate())
and DATEDIFF(second,o.eventdate,c.eventdate) >= 100

then:

SELECT

cc.PVSID,
cc.FirstName,
cc.LastName,
a.Email,
a.URL,
a.Subscriber_Key,
a.EventDate

FROM [TEST_AntispamFilter_open_clicks_difference] a
INNER JOIN [HaysDEClientContact] cc ON a.Subscriber_Key = cc.Subscriber_Key

Anyone spotting a mistake in the query we are missing??

0

Your query looks very similar to the one provided on this help page 'Filter anti-spam software clicks and clickbots from Marketing Cloud Tracking Data' but swapping out _open for _sent Security software often won't load images so opens would not be recorded. I would recommend sticking to _sent and if you want open data also add a join to _open

Select
c.AccountID as ClientID,
s.JobID as SendID,
s.SubscriberKey as SubscriberKey,
sub.EmailAddress as EmailAddress,
s.SubscriberID as SubscriberID,
s.ListID as ListID,
c.EventDate as EventDate,
c.url as URL,
c.LinkName as Alias,
s.BatchID as BatchID,
c.TriggeredSendCustomerKey as TriggeredSendExternalKey,
c.IsUnique as IsUnique
from _sent s 
left join _job j on s.jobid = j.jobid 
left join _Subscribers sub on s.subscriberkey = sub.subscriberkey
left join _Click c on s.jobid = c.jobid and s.listid = c.listid and s.batchid = c.batchid and s.subscriberkey = c.subscriberkey
where c.eventDate > dateadd(day,-1,getdate())
and DATEDIFF(second,s.eventdate,c.eventdate) >= 100

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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