Possible to Track Organic Visits to Specific URLs via Excel VBA/Google API?

[Dan Kern]

Hi Everyone, our company tracks Google Analytics stats for hundreds of websites into an Excel Spreadsheet using the VBA formulas connecting to the Google Analytics API. We're familiar with the basics like tracking visits, unique visitors, organic visits, referral visits, etc.

We need a separate report to track a few hundred specific URLs, individually, but in one spreadsheet. So, we want to have each URL on it's own row, and have columns for each month...and track organic monthly search visitors for each page. This will allow us to see how each page is trending month to month, quarter to quarter, year to year, etc.

Can anyone offer any advice on the formulas we'd need to use to accomplish this?

Thanks so much!

Dan

[chris@shufflepoint]

Are the URLs for different GA account and web properties, or for a single web property?

[Dan Kern]

We have @ 5-50 different URLs to track across @ 25 websites (each)...so, several hundred URLs total. I figured if there's a way to do this, we could have different worksheets in one spreadsheet for each website...and use the profile ID to tell the Google Analytics API which site each URL is for. If we need to use separate spreadsheets, then we'll do that...but not ideal.

--
You received this message because you are subscribed to a topic in the Google Groups "Google Analytics Reporting API" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-analytics-data-export-api/A0qCmuD5NfM/unsubscribe?hl=en.
To unsubscribe from this group and all its topics, send an email to google-analytics-data-...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

--

Dan Kern

www.kernmedia.com
www.sixstringsoul.com
513.404.7196

[chris@shufflepoint]

Hi Dan,
Are the specific URLs completely arbitrary? Or can they be identified using an advanced segment? How many URLs total on these sites?  If less than ~1000 then I would query the whole website and then in Excel do vlookups to get the information for the ones that you've identified.

To unsubscribe from this group and all its topics, send an email to google-analytics-data-export-api+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Dan Kern]

Chris, thank you for your reply. Much appreciated. Here's a specific example. In March of last year, we had 153,000 organic search visits to one our websites that we need this report for. 6,580 different URLs drove those visits. We have maybe 40-50 different URLs that we need to track monthly organic search traffic for. Using an Advanced Segment would defeat the purpose since we'd have to lump them all together. We want to track them all individually and then use an Excel formula (in the spreadsheet) to tally up the results. How does this affect your suggestion?

To unsubscribe from this group and all its topics, send an email to google-analytics-data-...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

[chris@shufflepoint]

The advanced segment would act as a filter. It would identify the subset but not lump them together. For example if the ones you were interested all had some character sequence - or even one of several such sequences, then you could create an advanced segment to select those pages.  You could also do it with a dynamic segment in the API.  Or, like I said, select all pages and then filter in Excel. How many pages/URLs?

I didn't understand your statement "6,580 different URLs drove those visits". 

[Dan Kern]

Hi Chris, there is no unique character set in the URLs. They are just root level. I understand what you were getting at, though.

6,580 different URLs drove 153,000 visits last March indicates that if we were to pull all of the URLs into Excel that drove organic visits, that would be 6,580...which is many more than 1,000.

I would think there has to be a way to query the Google API for a specific URL just like how you can query the API to pull organic search visits, or keywords, right?

To unsubscribe from this group and all its topics, send an email to google-analytics-data-...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

[chris@shufflepoint]

For any specific web site there are probably <1000 URLs, so I'd still be inclined to get all the pages (ga:pagePath) and then filter in excel using VLOOKUP or equivalent.

[Dan Kern]

Most websites have less than 1,000 URLs, but not ours. Here is a screenshot showing that we have 6,580 different landing pages that drove traffic in March of 2012. Much more than 1,000: http://screencast.com/t/k8u6yxMMU4e 

Is it possible to further define (ga:pagePath) to additionally query specific URLs? I'm afraid that pulling nearly 7,000 URLs in order to sculpt it down to 40-50 URLs is going to 1) make the report too slow, and 2) provide too much extra work in refining the list of URLs to the 40-50 that we need.

Thanks again for your brainstorming efforts. Much appreciated.

To unsubscribe from this group and all its topics, send an email to google-analytics-data-...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

[chris@shufflepoint]

Hi Dan,
Ok. I was assuming that the 6580 was split across ~25 sites, which would mean an average of ~300 per site.
But responding to your concerns, I think getting all and filtering will be faster, and in any solution you will have the work of refining the list to the 50 of interest. GA allows you to get up to 10,000 records in a single call.

[Dan Kern]

Okay that is a good option then, thank you. I still would prefer to see if there's a way to run a Google API Query via Excel VBA formulas for a specific URL. Hopefully someone can chime in with a solution, otherwise we can pull all and refine from there per your suggestion. Thanks again :)

To unsubscribe from this group and all its topics, send an email to google-analytics-data-...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

[chris@shufflepoint]

You can certainly retrieve the metrics for individual URLs using a ga:pagePath filter. But I think it would slower since you'd need to make a few hundred calls to the GA API.

[Dan Kern]

So would the formula/code be like this?

ga:/specific-page-url-here

To unsubscribe from this group and all its topics, send an email to google-analytics-data-...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

[chris@shufflepoint]

Filters are documented here:
https://developers.google.com/analytics/devguides/reporting/core/v3/reference#filters

[MT]

You can use my VBA functions for this: http://www.automateanalytics.com/p/google-analytics-vba-functions.html

To get data for a specific URL, type "pagepath==x" as the filter parameter, where x is the URL. As Chris suggested, the other option is to fetch data for all URLs and do the filtering in Excel; for this, type "pagepath" as the dimension parameter.

[Shivam Gupta]

Hi Dan,

Can you please let me know how can we use VBA to extract data (unique visitors, daily pages vies etc) from Google Analytics.

Any help would be really appreciable.

Regards

Shivam