none
Leveraging Google Analytics and Power Query

    Question

  • We recently started leveraging Power Query as a "self service" BI tool for our startup Navinum

    What we really love is to have the option to connect different data sources together (fairly) easily. 

    We have started doing combined analysis across our Google Analytics data and our data from our PostgreSQL database. A typical example would be a view of fraudulent transactions broken down by marketing channel. 

    This can already be achieved by leveraging e.g. the Excel Plugin NextAnalytics, that connects to the Google API. With that we populate worksheets that are then the data source for Power Query. 

    However, I think this kind of integration would be useful for almost all companies - and it would make a lot of sense to have Google Analaytics connectivity as a standard data source for Power Query. 

    Can someone comment on if this is planned or not - and maybe pass it on for the roadmap? 

    Friday, October 25, 2013 1:07 PM

Answers

  • Thanks for the suggestion! I'll definitely pass this along.

    I'm sure you noticed that you can export to CSV from Google Analytics, but of course this doesn't really help much since you can't refresh and get newer data.

    Friday, October 25, 2013 10:15 PM
    Moderator

All replies

  • Thanks for the suggestion! I'll definitely pass this along.

    I'm sure you noticed that you can export to CSV from Google Analytics, but of course this doesn't really help much since you can't refresh and get newer data.

    Friday, October 25, 2013 10:15 PM
    Moderator
  • Has this been proposed and when do you expect to support Google Analytics data through Power Query?
    Wednesday, March 26, 2014 5:23 PM
  • Just my 2 cents: it would be *very* interesting - we have a system running for that, but since we cannot schedule a direct refresh, we receive a weekly mail with data we want. Stiil an incomplete system, it would be much better integrating it in Power Query (but, at that point the ability of writing from Power Query into SQL Azure or SQL Server would be very welcome...)


    Marco Russo http://www.sqlbi.com http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo

    Thursday, March 27, 2014 5:40 AM
  • toulou01: Yes this is on our backlog but there is no expected date of completion yet (nor has work been started.) We'll have to prioritize it with our other backlog items. Feedback like this thread help to bump up the priority so please keep the feedback coming!

    Marco: Who knows how our direction will change in the future, but right now we are 100% focused on getting data INTO Excel, not writing it back out to other sources. That being said, there are goofy things you can do with the Native SQL box in the From SQL dialog and I've also seen some people create queries that output the text of a SQL query which they then parse and run with sqlcmd. But this type of scenario isn't something we're focused on as a product.

    Friday, March 28, 2014 9:09 PM
    Moderator
  • Ben,

    it would be interesting to investigate possible development once Power Query will run on the cloud as part of an incremental update. In that case, writing a partition into a data model would be similar to writing into another "adapter" that can push data somewhere. Not something for Tomorrow, I agree :)

    Marco


    Marco Russo http://www.sqlbi.com http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo

    Friday, March 28, 2014 11:03 PM
  • Hey, even we are looking the data source available in Power Query which can pull the data from Google Analytic.

    Regards,

    Ranjeet.

    Wednesday, April 30, 2014 8:23 PM
  • Has this started or has anyone got a solution to the problem of automating the data from GA to Excel?

    cheers

    tony

    Friday, December 12, 2014 10:15 AM
  • We have nothing new to report on this topic.
    Friday, December 12, 2014 3:06 PM
    Moderator
  • Hi,

    You can find a step by step how-to on http://www.kohera.be/insights/detail/how-to-get-google-analytics-data-in-power-query

    grts,

    Frederik

    Monday, February 02, 2015 11:37 AM
  • Hi there!

    I found the following simple solution.

    (1) I login to Google Sheet, install Add-on 'Google Analytics'.

    (2) In 'Report Configuration' sheet I configure GA profile and query parameters (result sheet name, dates, metrics, dimensions and filters).

    (3) Then I run GA-query via menu 'Add-ons / Google Analytics / Run reports'. Result of GA-query is put to result sheet defined on previous step.

    (4) Then I go to menu 'Add-ons / Google Analytics / Schedule reports' and define refresh frequency (hour).

    (5) Then I publish the result sheet via menu 'File / Publish to the web...' and copy published URL to clipboard.

    From now, I have auto-updated report from GA, which I can easily access via Power Query connecting to URL, which I copied on previous step.  Having done simple cleaning and I can load figures to Power Pivot and combine with metrics from on-premises systems. 

    If you have any questions, let me know.

    -- Paul

    Thursday, February 12, 2015 2:58 PM
  • That's a really smart approach, Paul! Thanks for sharing it.
    Thursday, February 12, 2015 3:06 PM