Google Analytics Traffic type 3

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.

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.

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.

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.