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
-
How To Stop Google's "Fetch & Render" From Showing Up In Google Analytics
Hi all, Within Google's "Fetch & Render" (found in Google Search Console) is the ability to index certain pages from my website on-demand. Unfortunately, every time I ask Google to index a page, it registers as a bounce in Google Analytics. Also, if it means anything, my website (www.knowtro.com) is a single-page application, functioning similarly to Google. If you guys know of any solution to this problem, please help! I originally thought that Google would know to block its own Fetch & Render crawler from Google Analytics but that doesn't seem to be the case. Thanks, Austin
Reporting & Analytics | | A_Krauss0 -
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? 9BH70RO
Reporting & Analytics | | VanguardCommunications0 -
How to get a specific keyword count from a particular country to a particular page in google analytics
We are trying to get the keyword count of a particular keyword from a particular country to a particular page. eg: (keyword)Green shoes from (country)United states on (particular page)one of our blog posts page Any help will be really appreciated
Reporting & Analytics | | Nobody15870501745820 -
Google Analytics (not provided)
Hi A greater and greater % of clicks is showing as "not provided" - does any one know of any good external tracking software which will show this information? Thanks
Reporting & Analytics | | jj34340 -
Multiple domains to one payment sub-domain in Google Analytics
Hi Mozzers, I have a series of sites (~25) that are all regional e-commerce sites with the same URL structure eg. www.[region]-product.com as well as a 'master' site (eg www.product.com). I also have a single Adwords PPC account which point to all of the regional sites as well as the master site. As part of the payment/checkout process you only ever get taken to a sub domain of the master site (eg payment.product.com). My question is: Is it wise to use the single PPC Adwords account to set up an Analytics account under which I list all of the regional sites and the master site? Once that's done I'm not entirely sure how I can track sales from a regional site to the payment process of the master site and have sales correctly assigned to the right domain. On the payment process I can only use one site ID tracking code so I'm not even sure I can correctly assign a transaction to a single site if multiple sites use the same payment pages. Example: Visit to region-product.com > Quote form submitted to payment.product.com > Payment completed on payment.product.com > Sale is assigned to which domain? Can anyone who understands what I'm trying to achieve comment on the plausibility of this? Many many thanks!
Reporting & Analytics | | panini0 -
Google Analytics Tracking Code Queries
Hello, I have taken on a new client who has Google Analytics installed. The tracking code is set to 'single domain'. Recently they added a mobile site using a sub-domain (m.website.com) which means that Google Analytics is not picking up this traffic. I want to revise the account so that I have a master account (raw data) and then profiles for the mobile site, main domain (www.website.com) and one other for a sub-domain that they are using. I am aware that there is mobile specific tracking code however I thought it would be easier (re conversions/goals/eCommerce tracking) to not use this and by changing the account to 'multiple domains' we could also get data for another sub-domain that they are using . My questions are: Am I right to want to use individual profiles over web properties. If not please explain why. When installing the tracking code (where the profile number is changing) I believe that I need to add that code with the changing profile number to the sub-domain sections. So my question is a) is that correct, and b) if I use a profile number on a sub-domain section will the master account still gather the data for the main URL as well as all sub-domains. If I change the master account from using 'single domain' tracking code to 'multiple domain' tracking code will this affect historical data? Will I lose the data? When changing from 'single domain' tracking to 'multiple domain' tracking does this affect eCommerce tracking? Or do we only need to be adding the additional lines of tracking code that allow sub-domains to be tracked? The web developers are using asynchronous code however half is in the and the other half is at the bottom of the source code. Given that traffic is being reported in the Google Analytics account should I have any concerns that the code is split? I have done a lot of reading but seem to be going around in circles, so your help is much appreciated! Thanks,
Reporting & Analytics | | Unity
Dinny0 -
301 Tracking with Google Analytics hurts SEO?
Hey guys, I have an old-domain.com and a new-domain.com. old-domain.com has reputation and through a 301 redirect it's handling it over to new-domain.com. I want to know how many people still visit old-domain.com and track this with Google Analyitcs. I read the best solution for this would be a Google Campaign URL. My question: Does a URL like "http://www.new-domain.com /?utm_source=&utm_medium=redirect&utm_campaign=" hurt the effectiveness of the 301 redirect?
Reporting & Analytics | | optimiert-es0 -
Nofollow page is being reported as a landing page for organic search in Google Analytics
One of my client's websites includes a series of pages for an enrollment process. All of these pages are blocked by robots.txt. In Google Analytics these pages are showing data as landing pages for organic search traffic, and have been for quite some time. There was recently a surge of organic search traffic landing on one of these pages, coming from multiple search engines. The pages appear to be blocked and I'm not finding any of them in the search results for the keywords that are being reported in GA or by searching for the url. Does anyone have any insights into why this might be happening?
Reporting & Analytics | | rgibson1000