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.
Got a burning SEO question?
Subscribe to Moz Pro to gain full access to Q&A, answer questions, and ask your own.
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
-
Whats (Other) traffic in Google Analytics?
When I look through all our clients, a few are receiving the majority of their traffic from (other). [ Acquisition > Channels > (Other) ]. The only option in (other) is "website" or "offline", whatever that may be. And even weirder, the avg session duration is 0:00. Any idea what this may be?
Reporting & Analytics | | W2GITeam0 -
Google Analytics - Events impacting session data
Hi Mozzers, Has anybody ever experienced event tracking significantly impacting session data? Here's a summary of what I'm finding: When I measure sessions to a particular hostname (a subdomain) in my Google Analytics report, sessions dropped by over 100% when I removed some event tracking on that subdomain (an event was triggered when a user clicked on a particular button). When I reinstate that event tracking, sessions go back up to around where they used to be. Can anybody understand why this might be happening? Thanks!
Reporting & Analytics | | A_Q0 -
How to exclude traffic for a specific mobile device in Google Analytics view?
Hi, Need help on how to exclude traffic for a specific mobile device in Google Analytics view. I have been searching and the only information available is excluding IP address of internal traffic. Is there any way to exclude traffic through a mobile MAC address?
Reporting & Analytics | | Khadija_K0 -
Funnel Problem in Google Analytics - Please help
Hello, I did set up a several funnel steps in my analytics account to get the funnel visualization report. But it's look fishy, something's clearly wrong that it shows only 10 visitors at the first step but actually more than 5k visitors has visited first goal page and followed through the another all steps. Everyday we are receiving 5k to 10k goal conversions but it not showing in the entire funnel steps through first step to last step. Please see the attached screenshot URL for funnel setup (Admin) and funnel visualization report comparing last two months traffic. Total goal conversions received 3,605 in last two months but in funnel chart (report) it showing only 2% conversions in last step. Anybody can tell me what is the problem with that or analytics not tracking properly?si65Y
Reporting & Analytics | | flightcentre200 -
Google Analytics: Trackbacks & Network Referrals?
Buon Pormeriggio from 15 degrees C mostly cloudy Wetherby UK 🙂
Reporting & Analytics | | Nightwing
Whats the difference between Trackbacks & Network referrals within Google analytics social media reporting? I'd like to specifically understand why a link to site i'm working on withing this post:
http://huddled.co.uk/huddled-interviews-nicola-schaefer-from-liverpool-fc-e-l-i-t-e-s-8335/ is classed as Trackback & not a Network refferral 😞 Illustrated here is the link thats being recorded as a track back:
http://i216.photobucket.com/albums/cc53/zymurgy_bucket/track-back-query_zpsbab2679b.jpg And here is the data:
http://i216.photobucket.com/albums/cc53/zymurgy_bucket/google-trackbacks-2_zps2861fa17.jpg But why is not showing up in Network referrals? Thanks in advance,
David0 -
Google Analytics Goal Tracking Not Working
I am trying to install goal tracking for a client to measure leads from SEO, and I'm running into problems with the Google Analytics goal tracking not working. The only thing that I can think of that is preventing goal tracking from working is that the URL confirming page request URI looks like "contact/#contact-form-4475" and perhaps the hashtag is causing problems. I wanted to ask the community if any of you have run into basic conversion tracking problems like this, where you need to track form fills on a website via a confirmation URL/destination URL goal, and what you did to solve it? I appreciate any input on how to get data on conversions either by fixing the goal in GA or using a different tracking tool. Thanks!
Reporting & Analytics | | williammarlow0 -
Google Analytics Queries
Hi we used a web company who put our analytics under their company name we have left them and set up a new tracking code not associated with them, but we still have access to the data and it is all contained under the same login, is it possible to merge the two tracking codes? so that we have seemless history. Also does anyone know if you can change the primary e-mail address (gmail account) to a corporate email account? Any help would be appreciated as we have researched both and from what we have found we can't merge the data and also we are stuck with the primary email address.
Reporting & Analytics | | loopylu030 -
Where are google analytics stats for iphone4
hi We were looking at the Google Analytics for one of our sites and noticed that there were NO pageviews from device=iphone and resolution=640x960 in the report. Given that iphone4 is supposed to be 640x960, and would be the most popular device (at least in our offices and everyone I know), it seems wierd. I sorted the Mobile Devices report by device and resolution to see what was available. The first 160 results were all device=not set. Finally got to device=iPhone and there were three entries: resolution 0x0 had 11 views resolution 320x396 had 45 views resolution 320x480 had 3,944 views. Hopefully all iphone4 users havent been classified as not set. Or is it possible that iphone4s claim to be 320x480 in browsers, as per http://www.alistapart.com/articles/a-pixel-identity-crisis/ Even worse, if I look at the Samsung Galaxy S II (myown phone), there are over 30 screen resolution combinations. Does anyone have anything to shed on this? I asked about it on the google analytics twitter account last week but havent had a response. Are there other analytics solutions that would distinguish between the iphones? Warning - this is a link to a large image, with the not set stats at the top. 6Sjji
Reporting & Analytics | | ozgeekmum0