Comparing % Change, Google Analytics
-
Hey Mozzers,
Is there a simple way to compare the "% Change" in traffic when comparing two separate time periods in a single Google Analytics report?
When comparing data from two separate time periods, an exported CSV doesn't include the % Change (booo!), and there's no option to sort by % Change within the GA report, essentially forcing you to scroll through all the results to pinpoint the major movers and shakers.
I'm not averse to using spreadsheets to sort this data, but I'm thinking that I'd likely need a macro to make this work, something like this. However, none of the macros on that page are working (possibly because they were designed for a previous version of Analytics).
All suggestions are appreciated.
Thanks!
-
Got it! Pivot table led me to the promised land. Thanks for the advice.
-
Appreciate the suggestions! Forgive my Excel ignorance, but the difficulty stems from not being able to directly compare and sort the data.
So let's say I want to pull my top 500 keywords driving traffic for a week's time and compare them to the previous week. If I pull a CSV from Analytics in a report comparing the two weeks, it'll show the same keyword (biscuits) in rows 8, 9, the same keyword (sandwiches) in rows 10, 11, the same keyword (buns) in rows 12, 13. I could use formulas like =D8-D9 to compare the difference in total traffic for a keyword, and =(D8-D9)/ABS(D8) to get the percentage, but then I can't sort by the difference (because the formula will calculate whatever moved during the sorting), which leaves me in the same bind as I have with Analytics -- not being able to sort by the change in traffic over two different time periods.
If I try to pull two separate CSV's from GA, from two separate weeks, and copy/paste that data into two separate columns, the data won't align correctly. For example, "buns" have been the top keyword driving traffic one week, so it'll be A1, but it was the fourth best keyword the next week, putting it into A4. In other words, the data isn't aligned.
I'm all for trying to display this data with a pivot table, but I'm not sure where to begin. Do you have any resources you could recommend?
-
Excel FTW! A pivot table could handle this for you as Basil said. Or if you wanted to do a one-off formula, it would look something like this:
=(B1-A1)/B1
Where B1 is your number from the more recent time period and A1 is the number from the previous time period.
Hope this helps!
Tim
-
Hi Dan,
There is no way to do that with GA, but you can with excel. What you're trying to accomplish can be easily done and summarized in an excel pivot table (which can also include predefined formulas).
Let me know if you need any help figuring out how to create one.
Basil
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
-
We setup a new Google Analytics account for the new domain but the old one is still there
Hi, Our company has changed their domain almost a year ago and after the migration, they created a new Google Analytics account rather than changing the address on the existing GA account. Now I can still see that Google indexing the old domain. Just wanted to ask if we can still tell Google that we've changed the domain on the old one? Or, should we just go ahead and use the new one and delete the old one? Very confusing, but our traffic is constantly dropping although we rank for on the first page for tracked keywords, just want to understand if there is a connection between the organic traffic droppings and not telling Google we've changed the domain. Thanks! NJt7vbJ
Reporting & Analytics | | Lucidica0 -
What is Local SEO in Google Analytics (Organic Source)
Recently, I saw "Local SEO" is mentioned as the organic source. Can someone please tell what is this and from where Google is fetching data for this source?
Reporting & Analytics | | Kevin.Monks0 -
Google Analytics View Filters
Using the same GA property, I would like to set up three filtered views: 1. Tracking across one subdomain and one primary domain (example: shop.example.com & example.com) 2. Track only primary domain (example.com) 3. Track only subdomain (shop.example.com) Can this be achieved by using view filters? If so, how do they need to be set? Also, according to this article: https://moz.com/blog/cross-domain-subdomain-tracking-in-google-analytics, with cross domain tracking, I need to ignore self-referrals, which can only be done at the property level. If set up to ignore example.com referrals, will this cause problems with filter 2 and 3?
Reporting & Analytics | | Evan340 -
How to Configure Google Analytic API?
Hello All, I want to implement google analytic api can any one show me the whole process? Regards, Mit
Reporting & Analytics | | mit0 -
On Google Analytics, Pages that were 301 redirected are still being crawled. What's the issue here?
URL that we redirected are being crawled on Google Analytics. Since they dont exist, they have high bounce rates. What can the issue be?
Reporting & Analytics | | prestigeluxuryrentals.com0 -
Google Analytics Not Tracking 100% of Visits?
Hi all, We're having an issue with Analytics where we are getting different figures from what Silver Pop are saying. For example email campaign A sent via Silver Pop, with Google Analytics tracking code show's 50 unique clicks in Silver Pop. Looking at Google Analytics there are only 10 visits from that campaign. So I thought it could be something with the tracking, but there wasn't a significant rise in web visits = either Google Analytics is not recording visits properly or Silver Pop figures are wrong. I'm more inclined to think that it's something to do with Google Analytics. Has anyone come across something similar? Where one system is showing you X amount of visits but the figures on Google Analytics don't add up? A few quick things already covered: Double checked the links have been tracked properly, but this doesn't explain the low increase in web visits generally We've double checked that Google Analytics tracking code is properly installed (and it is / was at the time of send). Any help would be much appreciated! Thanks guys.
Reporting & Analytics | | RKHStaff1 -
Google analytics reality check?
Looking back over a 9 month period tracking analytics with getclicky my site showed a 29% bounce rate, with only about 1/4 of visitors spending 1 minute or less on my site. I've recently implemented GA (removed old clicky code) and although traffic is strong, my site now shows a bounce rate of about 82%. Engagement stats also show that 82% of visitors spend between 0-10 seconds on my site. My site is built on Wordpress and the GA tracking code wasn't placed directly in the footer, my developer built a field in the admin area to insert the UA number which automatically adds the code to all pages. I've checked the code and the tracking seems to appear on all pages. I took a look at AW Stats. It corroborates GA and says that 80% of visitors are spending 0-30 seconds on the site. Potential issues/clues: browser tests show small loading problems in Internet Explorer 7,8,9 (the phone number at the top of the header loads on the wrong side of the page) and major issues in Internet Explorer 6 (site doesn't load at all in IE 6). The thing is no one who uses IE 6 is coming to the site. Second, the site gets a grade of C in YSlow, it's not lightning fast at the moment. GA is showing average page load of 2.4 seconds, but don't think either of these issues should cause an 82% 0-10 seconds engagement number. My site is content rich/focused with very minimal advertising. Content is accessible well above the fold. My question: Does the fact that AW Stats and GA agree mean that those numbers are accurate, or is there a bug I should be looking for? How to explain the clicky numbers?
Reporting & Analytics | | JSOC0 -
Google analytics - help
Hello, What is meant by campaign expiration in Google analytics ? The default value is 6 months. what does it mean ?
Reporting & Analytics | | seoug_20050