querying API from MS excel

[EddieO]

I'm looking to use the API to pull data into MS excel rather than a
web app. Has anyone taken on something like this yet, and if so could
you offer any tips?
Thanks,
EddieO

[Kirk]

Hey EddioO,

I would just write a script that pulls the data down and saves it as
an Excel file, unless you want to dynamically pull down data into a
spreadsheet you have open.

Kirk

[EddieO]

I'm trying to build a dashboard in Excel that pulls in data from
Analytics, our local database, and other sources. An ODBC connector
is available for our database, but there is no equivalent for
Analytics, so I'm hoping the API could be used in some fashion to
connect live from Excel, similar to an ODBC.

Would it be possible to do something like this, and has anyone thought
about it?

[Nick]

Hi EddieO,

Kirk's idea above is probably the quickest.

Another way would be to build a .NET plugin for Excel that would allow
you to supply valid GA API Query, handle the authentication, pull the
data and update a table in Excel. This latter approach might be a bit
more work, but you can image you could build the plugin in a way that
you could share excel files with non-technically apt people and have
all the updating built in.

-Nick

[chrissky]

ShufflePoint uses Excel Web Queries to accomplish an Excel
integration. Please have a look.
http://www.shufflepoint.com/ExcelWebQuery.aspx

[Renco]

Hi EddieO,

I have built something like that with Visual Studio Tools for Office
(VSTO) and Visual Studio 2008.

I used C# and web services to pull data from Google Analytics directly
into an excel sheet.

Next you can save the information about the queries as meta data into
a seperate sheet. The later will allow you to "refresh" the data
easily for example if you want to select a different time period.

When building the dashboard in Excel you normally set up multiple
layers; connections (odbc, ga, etc) -> transformation (pulling data
from the different connections sheets) -> presentation (dashboard).

If you are interested contact me...

Renco

[EddieO]

Thank you to all for this very good information.

-EddieO

On May 1, 5:05 pm, Renco wrote:
> HiEddieO,

[EddieO]

Hi Renco,
This sounds like a potentially good solution for us. I'd like to find
out more about it. Could you contact me at eddieo <at the domain>
gmx.com

Thanks!

On May 1, 5:05 pm, Renco wrote:

[POP Analytics]

Hi Renco,

I'm also interested in getting more info or code regarding this
solution.

I've been playing with Shufflepoints GA to Excel solution which is
VERY cool, but would like to also check out other options.

Thanks.
Ophir

On May 1, 5:05 pm, Renco wrote:

[POP Analytics]

Forgot to add contact info :)

Please contact me at ophir.prusak <at> gmail

Thanks
Ophir

[reese]

Hey Renco - I'd also be interested in learning more about what you've
set up to do this.

reese

[Chad Summerhill, UPack.com]

Hi Renco,

I am very interested in this method. Any help would be appreciated.

Thanks,

Chad
csumm...@abf.com

On May 1, 4:05 pm, Renco wrote:
> Hi EddieO,
>
> I have built something like that with Visual Studio Tools for Office
> (VSTO) and Visual Studio 2008.
>
> I used C# and web services to pull data from Google Analytics directly
> into anexcelsheet.
>
> Next you can save the information about the queries as meta data into
> a seperate sheet. The later will allow you to "refresh" the data
> easily for example if you want to select a different time period.
>

> When building the dashboard inExcelyou normally set up multiple

> layers; connections (odbc, ga, etc) -> transformation (pulling data
> from the different connections sheets) -> presentation (dashboard).
>
> If you are interested contact me...
>
> Renco
>
> On 27 Apr, 23:12, EddieO wrote:
>

> > I'm looking to use the API to pull data into MSexcelrather than a

[Lars]

Have a look at this: http://excellentanalytics.com/

It's open source.

[Nick]

The concept is fantastic except I can't get it to work.

If you get this working for everybody, I'll send free t-shirts and
write a post on our GA blog highlighting it :)

-Nick

[Mikael Thuneberg]

Hi,

If the t-shirts are still available:

I created two VBA functions to fetch data straight from GA to Excel,
without the need for any addins (I didn't get Excellent Analytics to
work either). You can find those functions here: http://sites.google.com/site/mikaelspage/

They work at least on my PC..

-Mikael

[Steve_T]

Hi Mikael,

That is an excellent script and worked perfectly for me. I have been
looking for this for a while, thanks for sharing!

-Steve

[Nick]

This is a great!

The t-shirts are avaliable, though your code will break if/when we add
and new attributes to the XML Nodes. If you can get this working
irregardless how we modify the response, I'll send you some shirts :)

A simple solution is to use xPath to retrieve the data :
http://msdn.microsoft.com/en-us/library/aa174735(office.11).aspx
Also another approach for the filters is to just URI encode the filter
parameter.

-Nick

On Jun 26, 3:36 am, Mikael Thuneberg wrote:

[Mikael Thuneberg]

Okay, I put some more time into this and there's now a version which
extracts the value by looking for the "aggregates" node, and its
"metric" child node. You probably won't change that structure, right?

- Mikael

[Nick]

Looks great. I'm sending shirts and will follow up off this forum.

2 small edits. Your loops should be getting the "entry" nodes. The
aggregates only show you the totals.
There are more characters to escape as part of the filter. To work for
all regular expressions, you'll want to just uriEncode the entire
filter param.

Nice Job!
-Nick

[Mikael Thuneberg]

Thanks for the comments :)

I don't think there's a built-in function for URI encoding in VBA. I
added some code there (written by someone else) that should handle all
characters, it seems to work.

As for the nodes where the value is extracted: if the query doesn't
have any dimensions (or the dimensions are only used to filter the
data, not to split it), won't the aggregate and entry nodes always be
identical, so it makes no difference from which the value is
extracted?

-Mikael

[Chad Summerhill, UPack.com]

I keep getting "Authentication failed" even though I'm using the
correct username and password.

Would it have something to do with our proxy server? If so, what
should I do to get it to work. Sorry, not very technical.

thanks for any help,

Chad

[Mikael Thuneberg]

Hi Chad,

Don't know if the proxy server could cause this. One thing that comes
to mind is did you put quotation marks around your username and
password? If you write the parameter values into the function, then
they need to be in quotation marks (if they are in other cells and you
use cell references from the function to those cells, quotation marks
are not needed).

-Mikael

[Chad Summerhill, UPack.com]

Hi Mikael,

Yes I did use quotation marks. Just tried it at my home and it works
fine. Must be something to do with the proxy server at work.

Thanks,

-Chad

[Lars]

Check that you have version 1.0.0.40. It should work.
http://excellentanalytics.com/how-to

Remember that Excellent Analytics is open source. If you want to
contribute with your brain and programming skills, contact:
http://www.markred.se/kontakt/

[Lars]

The latest version of Excellent Analytics should work fine.

On Jun 29, 2:54 pm, Mikael Thuneberg wrote:

[Andrew Markou]

Thanks for the programme - which I ran and worked for a while but now
the EA button no longer appears in Excel.

By the way I was also wondering whether the programme allows you to
set it up to pull data on future dates - e.g. to have the data auto
generated for a monthly report?

Many thanks

[Andrew Markou]

A bit more info - I have looked in the Add-Ins and it EA is logged as
a disabled Add In, when i try and manage it and check the box to
enable it, it still doesn't do anything and defaults back to
unchecked.

Any ideas?
Many thanks

[Mikael Thuneberg]

Hi,

EA still doesn't work on my PC..

Meanwhile I made some improvements to the custom function I created to
fetch GA data. It's now possible to add dimensions to the query, which
makes fetching large data sets much more efficient, as they can now be
fetced with a single array formula. The updated functions are
available at http://sites.google.com/site/mikaelspage/

I personally prefer having custom functions to the addin, as I can
share the workbooks I create with other people, and they can modify
and update the queries without needing to install anything. Custom
functions are also more flexible to use, as their parameters can be
outputs from other functions.

-Mikael

[sh...@motionsportgear.com]

Hi EddieO,

I am also looking for a solution to do this. Would you happen to still have this available?

Thanks,

Shawn
sh...@motionsportgear.com

[Mike Sullivan]

Have a look at http://www.analyticsedge.com/simply-free/ -- packaged application is already built and available for free.

[Kushan Shah]

Hi Shawn,

We might have a solution for you. Can you get in touch with me over email?

[MotionSport (Corp)]

Hey Mike!

Thanks a LOT for taking the time out to show me this. 

I work for a company with about 1,800 websites managing the Analytics and I have some ideas on both extending and using an application likes this from a code-base perspective. 

Is the source code for this open and not copyrighted? If not, do you know of a solution that is.

Thanks,

Shawn

Sent from my BlackBerry

--
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/0TS3sqSYalM/unsubscribe.
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/d/optout.

[Mike Sullivan]

Analytics Edge is not open source, sorry -- it is a packaged application. 

If you really want to program, I suggest you check out Linda Lawton's series on http://www.daimto.com/ .

Mike