Google Analytics Traffic type 3


Please Share

On several of the Google Analytic’s pages you will see something called Traffic Type.     The values are something like this (Direct,Referral,Organic,Paid,Campaign).   Now if you are exporting your data out of Google Analytic’s you may have noticed there is no “Traffic Type” Dimension that you can query out.      That is because Traffic type is really just source/medium ( ga:sourceMedium)  split up.

Our DATA

Lets say that you extracted  date,  Source/medium  and Visits   from Google Analytic’s into a table called tStageTraffic.

tstageTraffic

Here we have my tStageTraffic table with some data in it.   You will notice that my wSource, wMedium and wTrafficType columns are null.   We are going to deal with them right now.

Split Source / Medium

The first thing we need to do is split source / medium column.

splitSourceMedium

 

All this query does is split out the data on the / and insert them into there own columns.    This is also very useful if you want to be able to just look at the source or just the medium.

splitSourceMediumdisplay

Here is what our table looks like now.

Build Traffic Type

Now for the really good part.  We need to figure out what the “traffic type” is.   It has taken me a little research to how to get each one of the Source / medium its own Traffic type.

update tStageTraffic set wtrafficType = ‘Direct’
FROM tStageTraffic
where wSource = ‘(direct)’
and wMedium not in (‘organic’,’referral’,’cpc’)
go

update tStageTraffic set wtrafficType = ‘Campaign’
FROM tStageTraffic
Where wSource != ‘(direct)’
and wMedium not in (‘organic’,’referral’,’cpc’)
go
update tStageTraffic set wtrafficType = ‘Referral’
FROM tStageTraffic
where wMedium = ‘referral’

go
update tStageTraffic set wtrafficType = ‘Organic’
FROM tStageTraffic
where wMedium = ‘organic’
go
update tStageTraffic set wtrafficType = ‘Paid’
FROM tStageTraffic
where wMedium = ‘cpc’

Results

Here is what our table looks like now.

traffictypes

Please Share


Linda Lawton

About Linda Lawton

My name is Linda Lawton I have more than 20 years experience working as an application developer and a database expert. I have also been working with Google APIs since 2012 and I have been contributing to the Google .Net client library since 2013. In 2013 I became a a Google Developer Experts for Google Analytics.


Leave a comment

Your email address will not be published. Required fields are marked *

3 thoughts on “Google Analytics Traffic type

  • Romain André-Lovichi

    Unless I’m mistaken, you are using the dimension “ga:sourceMedium” in your first request, and then you split it to get Source and Medium.

    I’m wondering if I can make this request with the two dimensions “ga:medium” and “ga:source” instead. Will there be any differences ?

    Thanks a lot !