locked
Google Analytics Data RRS feed

  • Question

  • Hi

    If I define a google analytics query here http://ga-dev-tools.appspot.com/explorer/, do I have a way to set the URL as a Web data source? It will need to somehow authenticate using Google credentials. I guess it's not possible, but given there's a 'Web API' option when connecting to web datasources I wonder if there was a way to achieve it.

    Thanks

    Thursday, February 28, 2013 3:31 AM

Answers

  • Hi Andres. I think there's probably some work to do to really make this data source a good option, but I was able to get the data into Data Explorer.

    1. From the Google Analytics Query Explorer, I set the parameters of my query and clicked Get Data
    2. That created a button on the right side that says Excel TSV. I right clicked on that button and copied the shortcut URL.
    3. From Data Explorer, click From File > From Text (the From CSV option assumes too much to make it useful in this specific case.) In the open file dialog, I pasted the URL that I copied in step 2. That landed the data in the Data Explorer preview. From there I can filter it and reshape it to get my fields into a nice table.
    4. Right click on the column header and choose To Table. Choose Tab as the delimiter and choose "Accumulate into the last column" as the way to handle extra columns.
    5. Whether or not you have any extra columns will depend on how many metrics you picked back in step 1, but let's assume you have more than two. Click the little icon on the right side of the Column 3 header. This will expand the list of extra values that were accumulated when this was converted to a table. (The first row only has one tab in it so we create a two column table. If any row has more than two columns then those fields get dropped into the List that was in Column 3. We've now expanded that to make sure we can see all our fields.)
    6. Click the Table menu in the top left corner of the table and choose "Keep Range of Rows." In my dataset I needed to keep rows 14 and 15 so I set this task to start with row 14 and keep 2 rows.
    7. Click that Table menu in the top left corner again and choose "Use First Row as Headers."
    8. And finally I multi-selected all the columns (shift-click or ctrl-click to multi-select), right clicked on one of the headers, and chose Change Type > Number.
    9. Click Done

    Now the data is in Excel and should be backed by the query to Google Analytics.

    Thursday, February 28, 2013 7:29 AM
  • I think we can get it into two columns. Let's start with that URL in your last post.

    1. From Web > Paste the URL here
    2. That doesn't parse it as JSON, but we can change that manually. Click the Settings button at the bottom of the window and choose Show Formula Bar. This let's you see the formula that's being generated through the UI.
    3. The outer method call is Web.Page. Change that to Json.Document so it looks something like this:
      = Json.Document(Web.Contents(https://www.googleapis.com/analytics/v3/data/ga?ids=ga%3A105413&dimensions=ga%3Adate&metrics=ga%3Avisitors&start-date=2013-02-14&end-date=2013-02-28&max-results=50&access_token=<accesstoken>))
      By the way, you can see the full formula reference at this link in the help documents.
    4. The very last row of the record is rows. Click on "List" in that last row.
    5. Now we're at your list of lists. (Maybe you had an easier way of getting here?) Right click on the column header and choose To Table. Accept the defaults and click Apply.
    6. Expanding that column will give you everything in a single column which isn't what we want so we need to make a couple custom columns. Right click on the column header and choose Insert Column > Custom.
    7. The formula you want is =[Column1]{0}  That means that for each row, it will pull out the first element of the list.
    8. Right click on the column header again, Insert Column > Custom, and this time we'll grab the second value: =[Column1]{1}
    9. Now we can right click on the first column header and choose Hide and we're done!

    Friday, March 1, 2013 7:18 AM

All replies

  • Hi Andres. I think there's probably some work to do to really make this data source a good option, but I was able to get the data into Data Explorer.

    1. From the Google Analytics Query Explorer, I set the parameters of my query and clicked Get Data
    2. That created a button on the right side that says Excel TSV. I right clicked on that button and copied the shortcut URL.
    3. From Data Explorer, click From File > From Text (the From CSV option assumes too much to make it useful in this specific case.) In the open file dialog, I pasted the URL that I copied in step 2. That landed the data in the Data Explorer preview. From there I can filter it and reshape it to get my fields into a nice table.
    4. Right click on the column header and choose To Table. Choose Tab as the delimiter and choose "Accumulate into the last column" as the way to handle extra columns.
    5. Whether or not you have any extra columns will depend on how many metrics you picked back in step 1, but let's assume you have more than two. Click the little icon on the right side of the Column 3 header. This will expand the list of extra values that were accumulated when this was converted to a table. (The first row only has one tab in it so we create a two column table. If any row has more than two columns then those fields get dropped into the List that was in Column 3. We've now expanded that to make sure we can see all our fields.)
    6. Click the Table menu in the top left corner of the table and choose "Keep Range of Rows." In my dataset I needed to keep rows 14 and 15 so I set this task to start with row 14 and keep 2 rows.
    7. Click that Table menu in the top left corner again and choose "Use First Row as Headers."
    8. And finally I multi-selected all the columns (shift-click or ctrl-click to multi-select), right clicked on one of the headers, and chose Change Type > Number.
    9. Click Done

    Now the data is in Excel and should be backed by the query to Google Analytics.

    Thursday, February 28, 2013 7:29 AM
  • Miguel mentions in his post here:

    http://blogs.msdn.com/b/mllopis/archive/2013/02/28/get-microsoft-quot-data-explorer-quot-preview-for-excel-today.aspx

    that there is support for XML/JSON APIs, and I've seen (when looking at columns in a query) something that mentions XML and JSON. Is the support actually there yet? If so, how do we use it?

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Thursday, February 28, 2013 9:42 AM
  • I think that there are two different aspects of Data Explorer at work here.

    First, Data Explorer does support JSON and XML data, and should recognize data as such and parse it into a useful shape.  For instance, if you use the "From Web" option to point to a URL whose return value is an XML or JSON document, you should see the result as a structured value or table instead of a raw binary value.

    Second, Data Explorer has some limited support for interacting with Web or REST-like APIs.  Whenever DE connects to a Web source, it uses the function Web.Contents.  Web.Contents usually only takes one argument - the URL of the web source - but it can also take a second optional record argument.  Here is the syntax (with each of the record fields being optional):

    Web.Contents("http://myurl.com",

    [ ApiKeyName = "Bacon",
    Content = "SomeHexOrText",
    Query = [ Data1 = "A", Data2 = "B" ]])

    As for those three record parameters, here is what they do:

    Query: This parameter allows you to programmatically add query parameters to the URL without having to worry about escaping.  In the example above, the URL would be appended with ?Data1=A&Data2=B.
    ApiKeyName:
    If your target site has some notion of an API key, this parameter allows you to specify the name (not the value) of the key parameter that must be used in the URL.  You specify the value of the Api key using the "Web API" credential option, so that you can send your document to someone else, and they would be able to use their own API key.  Technically, you can use the Query parameter to effect the same behavior, but then the API key and value would travel with the document.
    Content: Specifying this value changes the web request from a GET to a POST, using the value of the Content field as the content of the POST.

    I hope this information helps!

    James

    Friday, March 1, 2013 12:40 AM
  • Thanks a lot!

    I looked at it a little more, and it seems that if I append the access_token that is in the link to the TSV file to the query, I get a JSON response:

    https://www.googleapis.com/analytics/v3/data/ga?ids=ga%3A3784649&dimensions=ga%3Adate&metrics=ga%3Avisitors&start-date=2013-02-14&end-date=2013-02-28&max-results=50&access_token=<youtoken>

    However, I could not find a way to transform that JSON response in something tabular. I get a list of lists, where each element has the value for each metric, and I could not find a way to turn a list of lists with two elements into a list with two columns.

    Thanks!

    Friday, March 1, 2013 1:47 AM
  • I think we can get it into two columns. Let's start with that URL in your last post.

    1. From Web > Paste the URL here
    2. That doesn't parse it as JSON, but we can change that manually. Click the Settings button at the bottom of the window and choose Show Formula Bar. This let's you see the formula that's being generated through the UI.
    3. The outer method call is Web.Page. Change that to Json.Document so it looks something like this:
      = Json.Document(Web.Contents(https://www.googleapis.com/analytics/v3/data/ga?ids=ga%3A105413&dimensions=ga%3Adate&metrics=ga%3Avisitors&start-date=2013-02-14&end-date=2013-02-28&max-results=50&access_token=<accesstoken>))
      By the way, you can see the full formula reference at this link in the help documents.
    4. The very last row of the record is rows. Click on "List" in that last row.
    5. Now we're at your list of lists. (Maybe you had an easier way of getting here?) Right click on the column header and choose To Table. Accept the defaults and click Apply.
    6. Expanding that column will give you everything in a single column which isn't what we want so we need to make a couple custom columns. Right click on the column header and choose Insert Column > Custom.
    7. The formula you want is =[Column1]{0}  That means that for each row, it will pull out the first element of the list.
    8. Right click on the column header again, Insert Column > Custom, and this time we'll grab the second value: =[Column1]{1}
    9. Now we can right click on the first column header and choose Hide and we're done!

    Friday, March 1, 2013 7:18 AM
  • That's great! Thanks!


    Friday, March 1, 2013 5:00 PM
  • Friday, March 1, 2013 5:02 PM