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 a developer and a database expert. I have been working with several of the Google APIs, since 2012. I helping others in the On-Line community to develop with the Google APIs, by creating my own blog www.daimto.com. This and my presence on a number of On-Line developer forums lead me to be noticed by the Google Analytics API development team. I was nominated for and recently became one of the first 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 !