Different results with same query using Google Analytics Built-In Connector

[Lino]

I created three different queries that extract data from Google Analytics using the Google Analytics built-in connector:

1. The first one extracts only the metric ga:sessions. The sum of the sessions is equal to 82'153.
   
   

   let
       Source = GoogleAnalytics.Accounts(),
       #"Step 1" = Source{[Id="XXXXXXX"]}[Data],
       #"Step 2" = #"Step 1"{[Id="XX-XXXXXXX-XX"]}[Data],
       Data = #"Step 2"{[Id="XXXXXXXXX"]}[Data],
       Result = Cube.Transform(Data,
           {
               {Cube.AddMeasureColumn, "Sessions", "ga:sessions"}
           })
   in
       Result

   
   
   
2. The second one extracts the metric ga:sessions and the dimension ga:channelGrouping. The sum of the sessions is equal to 82'173. The difference compared to the previous one is +20.
   
   

   let
       Source = GoogleAnalytics.Accounts(),
       #"Step 1" = Source{[Id="XXXXXXX"]}[Data],
       #"Step 2" = #"Step 1"{[Id="XX-XXXXXXX-XX"]}[Data],
       Data = #"Step 2"{[Id="XXXXXXXXX"]}[Data],
       Result = Cube.Transform(Data,
           {
               {Cube.AddAndExpandDimensionColumn, "ga:channelGrouping", {"ga:channelGrouping"}, {"Channel"}},
               {Cube.AddMeasureColumn, "Sessions", "ga:sessions"}
           })
   in
       Result

   
   
   
3. The third one extracts the metric ga:sessions and the dimensions ga:channelGrouping and ga:eventCategory. The sum of the sessions is equal to 8'412. The difference compared to the previous one is -73'761.
   
   

   let
       Source = GoogleAnalytics.Accounts(),
       #"Step 1" = Source{[Id="XXXXXXX"]}[Data],
       #"Step 2" = #"Step 1"{[Id="XX-XXXXXXX-XX"]}[Data],
       Data = #"Step 2"{[Id="XXXXXXXXX"]}[Data],
       Result = Cube.Transform(Data,
           {
               {Cube.AddAndExpandDimensionColumn, "ga:channelGrouping", {"ga:channelGrouping"}, {"Channel"}},
               {Cube.AddAndExpandDimensionColumn, "ga:eventCategory", {"ga:eventCategory"}, {"Interaction"}},
               {Cube.AddMeasureColumn, "Sessions", "ga:sessions"}
           })
   in
       Result

Every query returns a different number, but the source is always the same and there are no applied filters, since this connector does not provide a filtering system.

I think this is a very important issue to solve.

Regards

Lino

[v-qiuyu-msft]

Hi @Lino,

Have you tried use Google Analytics API to retrieve data based on the same dimensions and measures?

I would suggest you check if the issue is related to Power BI desktop actually. Please do the same steps on Google Analytics side, if you have trouble in this section, you can get help here. Then try get data in the latest Power BI desktop version as well, compare those two results.  

Best Regards,
Qiuyun Yu

[Lino]

Hello @v-qiuyu-msft

Thanks for your reply.

Using this endpoint, the data are right, but the main problem is that I did not find a way to get dynamically the Access Token. I have to put it manually into a Power Query parameter and this is not absolutely an option.

I tried to get automatically the Access Token, but I encountered two different issues:

1. The first one is when I try to get the Auth Code using the following code. I do not know if it is possible getting Auth Code since it is passed as query string parameter. Using OAuth 2.0 Playground, I get it manually.
   
   

   let
       Source = Web.Page(Web.Contents("https://accounts.google.com/o/oauth2/auth?client_id=12345-1234567890.apps.googleusercontent.com&redirect_uri=https://oauth.powerbi.com/views/oauthredirect.html&scope=https://www.googleapis.com/auth/analytics.readonly&access_type=offline&prompt=none&response_type=code&login_hint=username@domain.com"))
   in
       Source

2. The second one is when I try to get the Access Token using the following code. I get always an error 400 (Bad Request).
   
   

   let
       Source = Web.Contents("https://www.googleapis.com/oauth2/v3/token", [Headers=[ContentType="application/x-www-form-urlencoded; charset=utf-8"], Content=Text.ToBinary("code=" & authCode & "&redirect_uri=https://oauth.powerbi.com/views/oauthredirect.html&client_id=" & client_id & "&client_secret=" & client_secret & "&grant_type=authorization_code")])
   in
       Source

   
   

   DataSource.Error: Web.Contents failed to get contents from 'https://www.googleapis.com/oauth2/v3/token' (400): Bad Request
   Details:
       DataSourceKind=Web
       DataSourcePath=https://www.googleapis.com/oauth2/v3/token
       Url=https://www.googleapis.com/oauth2/v3/token

Do you know how to solve these issues?

Regards

Lino

[Lino]

Hi @v-qiuyu-msft

I implemented a workaround.

I got manually the Access Token and the Refresh Token using Google OAuth 2.0 Playground. I saved them in Power BI as parameters.

Then I created a function that returns the Access Token using the Refresh Token. In this way, I don't need to retrieve the Auth Code and the Access Token.

When the query is updated, the Access Token is refreshed by the refresh function using the Refresh Token.

Now, I hope that:

• when the Access Token reachs zero as expire time, it will be able to be refreshed using the Refresh Token.
• the Refresh Token never expires.

If both these points are true, I can retrieve always data from Google Analytics using a single Refresh Token.

What do you think? Could it work always?

Regards

Lino