Tracking

How to distinguish between direct visits, referral visits and clicks from organic search when tracking with a custom pixel? In the course, the main info on campaigns comes from UTMs from the URL, but you would also wanna analyze organic traffic, that does not usually have tracking parameters in a link.

REPLIES

makaroni4

over 4 years Reply

Really good question, I guess it makes a great material for a new lesson

Let’s imagine that pageviews from our custom pixel are stored in the following table. To distinguish between direct/search/referral and paid visits we only need values of pageview URL and referrer URL:

id	url	referrer_url
1	https://bindle.com	NULL
2	https://bindle.com?referrer_id=foobar	https://t.co/
3	https://bindle.com?utm_campaign=black_friday2020	https://google.com
4	https://bindle.com	https://google.com

Direct visits

The characteristic of direct visits is an empty referrer URL. This is the pageview with id = 1 from our table.

In the database the actual value could be an empty string instead of NULL. This is why we’re filtering out pageviews with both NULL referrer URLs and '':

WITH direct_visits AS (
  SELECT *
  FROM pageviews
  WHERE
    referrer_url NOT IN (NULL, '')
)

SELECT *
FROM direct_visits

Referral visits

In my practice, we always want to track which user shared the link and invited more users. It usually means that there’s some kind of user ID in the URL. Take a look at the pageview with id = 2.

We’ll simply match the name of the URL parameter to select all referral visits:

WITH referral_visits AS (
  SELECT *
  FROM pageviews
  WHERE
    url LIKE '%referrer_id=%'
)

SELECT *
FROM referral_visits

Paid clicks

The unique feature of paid clicks are UTM params. This is the pageview with id = 3.

It still makes sense to filter for visits from Google to make sure the URL didn’t go viral:

WITH paid_google_visits AS (
  SELECT *
  FROM pageviews
  WHERE
    url LIKE '%utm_campaign=%'
    AND referrer_url LIKE '%https://google.com/%'
)

SELECT *
FROM paid_google_visits

Organic search visits

Organic search visits have the same referrer_url as paid – https://google.com/ (without any parameters). That’s the pageview with id = 4.

The target URL (our homepage or a blog post URL) also won’t have any parameters:

WITH organic_search_visits AS (
  SELECT *
  FROM pageviews
  WHERE
    url NOT LIKE '%utm_campaign=%'
    AND referrer_url LIKE '%https://google.com/%'
)

SELECT *
FROM organic_search_visits