Can you arrange Google Analytics source/medium traffic by percentage change?
-
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?
-
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
-
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.
-
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.
-
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.
-
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.
-
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.
Browse Questions
Explore more categories
-
Moz Tools
Chat with the community about the Moz tools.
-
SEO Tactics
Discuss the SEO process with fellow marketers
-
Community
Discuss industry events, jobs, and news!
-
Digital Marketing
Chat about tactics outside of SEO
-
Research & Trends
Dive into research and trends in the search industry.
-
Support
Connect on product support and feature requests.
Related Questions
-
Traffic Drop Observations: Google UI, Mobilegeddon
Our traffic has dropped 25% in the last 3 weeks while maintaining our keyword rankings. At first, we thought it was due to the simultaneous change in Google UI where they removed the separators between ads and organic results. However, now, we have also noticed that our user devices have gone from ~ 50% desktop/50% mobile to 20% desktop/80% mobile. Just food for thought ...
Reporting & Analytics | | Humanovation0 -
Traffic drop after analytics troubles
Hi For two weeks we had an artifical low bouncerate & high pageviews/visit in our Analytics reporting. The day we corrected the bug in Analytics - our bouncerate & pageviews/visit returned to normal levels - however we saw our search traffic go down massively(-50% in sessions). The bug in the Analytics was caused by a second Analytics tag which was included in an external script which interfered with our own tag. The drop in traffic occurred just after the removal of the second script (which was only on our site for two weeks). We didn't touch our own tagging - and there were no technical changes on the site during this period, and there is no seasonal trend which could explain the sudden drop of traffic. We double checked our tagging - and the analytics tag is present & working on all the pages of our site. On the organic traffic report from analytics you can clearly see the when the troubles with analytics started & ended (artificial low bounce rate) - and that the traffic drop starts right after the reporting issue ended. Webmastertools also indicated a lower number of views/clicks, but not to the massive 50% drop. Is it possible that Google uses the measurements from Analytics for it's SERP's? Or should there be another reason, and where should we start looking? Appreciate your help! e5GmoMM
Reporting & Analytics | | DirkC0 -
What is the best way to embed Google Analytics charts on our site?
We want to build out this functionality so a client can log into our site and view data. I know its possible but I can't find any articles about the steps so that my team can move forward.
Reporting & Analytics | | appbackr0 -
Google Analytics Not Working
I added the code before tag but still google not showing it is installed. Status: Tracking Not Installed Last checked: Mar 15, 2013 10:38:10 PM PDT Can someone check my domain - www.plugnbuy.com
Reporting & Analytics | | chandubaba0 -
How to devide Google traffic into local google traffic (.com , .uk , .cn etc) in Analytics
Hi, friends In google analytics , we can see search traffic from google, but is it possible to deeply see from which local google? eg: .com , .uk, .hk etc? Thanks alots
Reporting & Analytics | | topchinaseo
Boson0 -
Does anyone know what's happened to google analytics -> traffic sources -> SEO -> queries many of my accounts are showing a drop to zero in the laste few days
Howdy mozzers It's in the question title really. Zero impressions showing for the last few days on. Multiple accounts Any thought out there
Reporting & Analytics | | Big_Partnership0 -
Google Analytics Organic Keyword (Not Provided) Alternative Hacks?
I'm sure everyone is sick of the (Not Provided) keywords because I know I am, and Google reckons that you should only see 10% or less (I'm currently around 13% and growing). I am seeking alternatives or hacks that will help display the keywords? If you have a hack to actually display the keywords, please share it your ideas and thoughts? I recently implemented a hack that will show the landing page of the keywords which is better than nothing. But as an eCommerce site, I have about 10% of my transactions which not provided, knowing the keywords would really help. Would love to hear your thoughts?
Reporting & Analytics | | upick-1623911 -
No Social Sources in Google Analytics - what am I doing wrong?
Hello Everyone, I'm having a strange issue: I DO NOT have in my Google Analytics the "Social" tab under the Traffic Sources category. Look at the first image of this post: http://marketingland.com/google-analytics-social-reports-8138 How do you "get" that to show? Hope somebody has this issue and can help, Thanks a lot, Alex
Reporting & Analytics | | pwpaneuro0