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
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??