Can you arrange Google Analytics source/medium traffic by percentage change?

[VanguardCommunications]

I'm doing a year to year traffic audit for a client.  I would like to analyze Google Analytics source/medium traffic by percent change.   Is there a way to do this?  Do I have to create a custom variable?

9BH70RO

[KristinaKledzik]

As most people here have said, the best way is to just do this work outside of GA. Custom variables have to track current site performance, so I don't believe there's a way to build a custom variable that looks at data as it's been recorded in GA in the past.

There are a lot of ways you can put the data together, but when I usually do is use Google's Compare option as you select the time period you're looking at. When you export the data to Excel, Google will create a separate row for every time period, like this:

Source / Medium   |     Date Range                              |     Sessions     .....

google / organic      |     Jan 13, 2015 - Feb 22, 2015     |     278,834   ...

google / organic      |     Jan 13, 2014 - Feb 22, 2014     |     247,424   ...

You can combine those into one row fairly easily with VLookup, as Ray suggested, or by creating a Pivot Table, where Source / Medium is the Row Label, Date Range is the Column Label, and Sessions (or whatever metric(s) you're interested in) is the Values.

If you've created a Pivot Table, copy those values over to a new sheet, and calculate the percent change in each row, just like Ray said.

Hope this helps!

Kristina

[DonnaDuncan]

If I'm understanding you correctly, I think you're going to have the same problem with channels. Google changed their reporting structure in July 2013.

[Ray-pp]

That would be effective if the Source/Medium was consistent between both years. You might have better luck with Channels for this method (less variation between years), but Source/Mediums are probably going to vary between years. I.e. 2013 includes a Source/Medium that no longer exists in 2014, so the data would not align neatly.

[MonicaOConnor]

You don't have to run a VLookup necessarily. You can align the data side by side and do a simple division formula to calculate the percentage of change. Just copy and paste the data into two columns, and use the IMSUB to find the difference, then divide for a percentage.

[Ray-pp]

Hi VC,

Unfortunately, I do not think there is a default reporting option to sort a compared Source/Medium report by % changed. Absolute change is available (although sometimes doesn't sort properly) and may give you a good idea of the traffic differences - Absolute change may even be better since it could identify a low %, but high traffic change.

To find exactly what you're looking for I suggest:

• Export the Source/Medium data for 2014
• Export the Source/Medium data for 2013
• Run a VLOOKUP function in Microsoft Excel to have a single table with 2014/2013 data

Now, you can calculate percentages, absolute values, and sort/filter the data as you see fit. Google Analytics provides a lot of the necessary data analysis tools, but it doesn't replace the power of MS Excel :)....yet.

[Hutch42]

The easiest way to do this is to export the data and work with it outside of Google Analytics. I reccomend using http://www.nextanalytics.com/ to automatically export data from GA into excel.