Loading

Filter anti-spam software clicks and clickbots from Marketing Cloud Tracking Data

Date de publication: Nov 13, 2024
Description

You may notice that certain sends have a large number of clicks that happen moments after the send goes out. These clicks are generally being "clicked" by subscriber's anti-spam applications checking the legitimacy/content of the links. This can dilute tracking as the click rate will be inflated. 

Over the last few years, many firewall and anti-spam, anti-virus security systems and software have been performing scans of emails for recipients that can cause 'false-positive' counts in Tracking for Email Opens and Clicks.  When such a system is in use, depending on how it has been configured, it may perform these scans on all emails and all links or only some emails and some links according to a set of rules in its configuration.

The security system will sandbox and render the email, causing an Open to be recorded at our Tracking servers.  It will perform a "trace" on URLS (links) in the email to make sure they do not redirect to any known malicious site or cause the download of malicious content, causing a Click to be recorded on our Tracking servers.

Since the variables on which emails/links to scan may depend on rules, such as "if I get more than 20 emails from the same sender for this domain, start scanning all emails from that sender for today/next 2 hours/next 12hours...", etc., this behavior may or may not be noticeable.

Where to find Subscribers in an email send

When it is noticeable, one way to view this is what is happening is to query the Open or Click db tables for one particular JobID in question.  If this is happening you will see a pattern that indicates that everyone, or most everyone, has recorded an Open within seconds of each other.  For Clicks, it will appear that most subscribers have somehow managed to click each link in an email simultaneously(or very nearly so) which is just not possible for a human to have done.

Résolution

How do I work around this? 

Filter clicks from Tracking Data

 

1. Create a new Data Extension.
2. Add the fields below:

 

  • AccountID - numeric
  • JobID - numeric
  • SubscriberKey - Text 250 char max
  • EmailAddress - Email Address
  • SubscriberID - Numeric 
  • ListID - Numeric
  • EventDate - date
  • URL - Text 4000 char max
  • Alias - Text 4000 char max (Nullable)
  • BatchID - Numeric
  • TriggeredSendExternalKey - Text 50 char max (Nullable)
  • IsUnique - Boolean


Fields and names can be removed or modified to fit your specific needs. You will need to make sure that any fields removed from Data Extension are also removed from Query text below.

 

Filter within Automation Studio


1. Create a Query Activity.
2. Specify the new Data Extension (created in the steps above) as the Target Data Extension.
3. Use the SQL below as the Query Text.

 

SELECT
c.AccountID,
s.JobID,
s.SubscriberKey,
sub.EmailAddress,
s.SubscriberID,
s.ListID,
c.EventDate,
c.URL,
c.LinkName,
s.BatchID,
c.TriggeredSendCustomerKey,
c.IsUnique
FROM _Sent s
/* Change to ENT._Subscribers if using in a child business unit */
LEFT JOIN _Subscribers sub ON s.SubscriberID = sub.SubscriberID
LEFT JOIN _Click c ON s.JobID = c.JobID AND s.ListID = c.ListID AND s.BatchID = c.BatchID AND s.SubscriberID = c.SubscriberID
WHERE 1=1
AND c.EventDate > DATEADD(day, -1, GETDATE())
AND DATEDIFF(second, s.EventDate, c.EventDate) >= 100
*Note* - Based on the Account Support Entitlement, changes to this query may be unsupported by Marketing Cloud Support.
4. Run the query either in a scheduled automation or via the 'Run Once' option.

 

Additional Notes

Fields specified in the Select clause can be modified to fit individual needs, but must correspond with fields in corresponding Data Extension.

Where Clause (last two lines of query text) look for:
 

  • Click timestamp was from yesterday, and the time between the send timestamp and the click timestamp is greater than 100 seconds.
  • This 100 second time frame can be modified to fit individual needs as well, depending how strict the user wants to get with the tracking data.
  • If needed for a specific job, add the sql code at the bottom of the query text (And s.JobID = [Insert Specific JobID Here])


There is currently no technical solution which allows our Tracking systems to know if the Opens or Clicks it records are human or system generated.  

If this is encountered  there is not a way to code a link or email open code to prevent such systems from causing this issue, other than to not enable Tracking of links or the recording of Opens on a Send which is typically not a desirable solution.

Numéro d’article de la base de connaissances

000381730

 
Chargement
Salesforce Help | Article