Google Analytics

Summary

Release State: General Availability

Products: Power BI Desktop

Authentication Types Supported: Google Account

Function Reference Documentation: GoogleAnalytics.Accounts

Prerequisites

Before you can sign in to Google Analytics, you must have an Google Analytics account (username/password).

Capabilities Supported

  • Import

Connect to Google Analytics data

To connect to Google Analytics data:

  1. Select Get Data from the Home ribbon in Power BI Desktop. Select Online Services from the categories on the left, select Google Analytics, and then select Connect.

    Get Data from Google Analytics

  2. If this is the first time you're getting data through the Google Analytics connector, a third party notice will be displayed. Select Don't warn me again with this connector if you don't want this message to be displayed again, and then select Continue.

  3. To sign in to your Google Analytics account, select Sign in.

    Select sign in button

  4. In the Sign in with Google window that appears, provide your credentials to sign in to your Google Analytics account. You can either supply an email address or phone number. Then select Next.

    Sign in to Adobe Analytics

  5. Enter your Google Analytics password and select Next.

    Enter your password

  6. When asked if you want Power BI Desktop to access your Google account, select Allow.

    Allow access to your Google account

  7. Once you have successfully signed in, select Connect.

    Signed in and ready to connect

Once the connection is established, you’ll see a list of the accounts you have access to. Drill through the account, properties, and views to see a selection of values, categorized in display folders.

You can Load the selected table, which brings the entire table into Power BI Desktop, or you can select Transform Data to edit the query, which opens Power Query Editor. You can then filter and refine the set of data you want to use, and then load that refined set of data into Power BI Desktop.

Load or transform data

Limitations and issues

You should be aware of the following limitations and issues associated with accessing Adobe Analytics data.

Google Analytics quota limits for Power BI

The standard limitations and quotas for Google Analytics AP requests is documented in Limits and Quotas on API Requests. However, Power Query Desktop and Power Query Service allow you to use the following enhanced number of queries.

Power BI Desktop:

  • Queries per day—250,000
  • Queries per 100 seconds—2,000

Power BI Service:

  • Queries per day—1,500,000
  • Queries per 100 seconds—4,000

Troubleshooting

Validating Unexpected Data

When date ranges are very large, Google Analytics will return only a subset of values. You can use the process described in this section to understand what dates are being retrieved, and manually edit them. If you need more data, you can append multiple queries with different date ranges. If you're not sure you're getting back the data you expect to see, you can also use Data Profiling to get a quick look at what's being returned.

To make sure that the data you're seeing is the same as you would get from Google Analytics, you can execute the query yourself in Google's interactive tool. To understand what data Power Query is retrieving, you can use Query Diagnostics to understand what query parameters are being sent to Google Analytics.

If you follow the instructions for Query Diagnostics and run Diagnose Step on any Added Items, you can see the generated results in the Diagnostics Data Source Query column. We recommend running this with as few additional operations as possible on top of your initial connection to Google Analytics, to make sure you're not losing data in a Power Query transform rather than what's being retrieved from Google Analytics.

Depending on your query, the row containing the emitted API call to Google Analytics may not be in the same place. But for a simple Google Analytics only query, you'll generally see it as the last row that has content in that column.

In the Data Source Query column, you'll find a record with the following pattern:

Request:
GET https://www.googleapis.com/analytics/v3/data/ga?ids=ga:<GA Id>&metrics=ga:users&dimensions=ga:source&start-date=2009-03-12&end-date=2020-08-11&start-index=1&max-results=1000&quotaUser=<User>%40gmail.com HTTP/1.1

<Content placeholder>

Response:
HTTP/1.1 200 OK
Content-Length: -1

<Content placeholder>

From this record, you can see you have your Analytics view (profile) ID, your list of metrics (in this case, just ga:users), your list of dimensions (in this case, just referral source), the start-date and end-date, the start-index, max-results (set to 1000 for the editor by default), and the quotaUser.

You can copy these values into the Google Analytics Query Explorer to validate that the same data you're seeing returned by your query is also being returned by the API.

If your error is around a date range, you can easily fix it. Go into the Advanced Editor. You'll have an M query that looks something like this (at a minimum—there may be other transforms on top of it).

let
    Source = GoogleAnalytics.Accounts(),
    #"<ID>" = Source{[Id="<ID>"]}[Data],
    #"UA-<ID>-1" = #"<ID>"{[Id="UA-<ID>-1"]}[Data],
    #"<View ID>" = #"UA-<ID>-1"{[Id="<View ID>"]}[Data],
    #"Added Items" = Cube.Transform(#"<View ID>",
        {
            {Cube.AddAndExpandDimensionColumn, "ga:source", {"ga:source"}, {"Source"}},
            {Cube.AddMeasureColumn, "Users", "ga:users"}
        })
in
    #"Added Items"

You can do one of two things. If you have a Date column, you can filter on the Date. This is the easier option. If you don't care about breaking it up by date, you can Group afterwards.

If you don't have a Date column, you can manually manipulate the query in the Advanced Editor to add one and filter on it. For example.

   let
      Source = GoogleAnalytics.Accounts(),
      #"<ID>" = Source{[Id="<ID>"]}[Data],
      #"UA-<ID>-1" = #"<ID>"{[Id="UA-<ID>-1"]}[Data],
      #"<View ID>" = #"UA-<ID>-1"{[Id="<View ID>"]}[Data],
      #"Added Items" = Cube.Transform(#"<View ID>",
          {
              {Cube.AddAndExpandDimensionColumn, "ga:date", {"ga:date"}, {"Date"}},
              {Cube.AddAndExpandDimensionColumn, "ga:source", {"ga:source"}, {"Source"}},
              {Cube.AddMeasureColumn, "Organic Searches", "ga:organicSearches"}            
         }),
      #"Filtered Rows" = Table.SelectRows(#"Added Items", each [Date] >= #date(2019, 9, 1) and [Date] <= #date(2019, 9, 30))
   in
      #"Filtered Rows"

Next steps