cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

google analytics

Hello! 

 

First off, thanks for reading and sharing your expertise. I'm a new Power BI user and running into some trouble with Google Analytics.

 

I connected GA to the desktop version of Power BI pulling in: 

  • % Exit
  • Avg. time on Page
  • Date
  • Default Channel Grouping
  • Page
  • Page Title
  • Unique Pageviews

 

I did a quick test comparing unique page views for the month of September. The data in BI doesn't match GA. What could possibly be causing this error? 

 

 

 

The other issue I'm running into is with the date hierarchy. The date from GA includes the day, month, date, and year. When I add the date column as a filter and select date hierarchy, my only option is year. Why is month not also included? Is there a work around to add this an option? 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Hi @carlydugger,

 

The connector in Power BI Desktop rely on the Google Analytics Core Reporting API. Although we attempt to release updates in accordance with any changes, the API may change in a way that affects the results of the queries we generate. Due to this dependency we cannot guarantee the results of your queries when using this connector.

 

In your scenario, please try to update the Power BI Desktop to the latest version 2.39.4526.362 and connect to Google Analytics. If issue persists, according to this article, this should be a better place for you mainly discussing questions related to google-analytics-api. 

 

In addition, we are not able to place a date hierarchy in Visual /Page /Report level filter currently. To work around the issue, you can use the custom visual HierarchySlicer.

 

q3.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Community Support
Community Support

Hi @carlydugger,

 

The connector in Power BI Desktop rely on the Google Analytics Core Reporting API. Although we attempt to release updates in accordance with any changes, the API may change in a way that affects the results of the queries we generate. Due to this dependency we cannot guarantee the results of your queries when using this connector.

 

In your scenario, please try to update the Power BI Desktop to the latest version 2.39.4526.362 and connect to Google Analytics. If issue persists, according to this article, this should be a better place for you mainly discussing questions related to google-analytics-api. 

 

In addition, we are not able to place a date hierarchy in Visual /Page /Report level filter currently. To work around the issue, you can use the custom visual HierarchySlicer.

 

q3.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Thank you! The custom slicer is wonderful. I'll continue to search for API solution elsewhere. 

Regular Visitor

I ran into the same problems. 

Connecting to the GA API via Power BI gave me wrong numbers while using the GA API  via SEO Tools for Excel was ok.

This may be linked to the amount of data imported.

 

I have tried to modify the query using the advanced editor.

I added a line with samplingLevel=  "LARGE", as indicated in the documentation  

let
    Source = GoogleAnalytics.Accounts(),
    Account = Source{[Id=#"GA Account Id"]}[Data],
    Property = Account{[Id=#"GA Property Id"]}[Data],
    View = Property{[Id=#"GA View Id"]}[Data],
    samplingLevel=  "LARGE",    
    #"Éléments ajoutés" = Cube.Transform(View, {{Cube.AddAndExpandDimensionColumn, "ga:yearMonth", {"ga:yearMonth"}, {"Month of Year"}}, {Cube.AddAndExpandDimensionColumn, "ga:medium", {"ga:medium"}, {"Medium"}}, {Cube.AddMeasureColumn, "Sessions", "ga:sessions"}})
in
    #"Éléments ajoutés"

It does not return errors, but seems to solve the issues as the figures are the same as in GA.

However, another solution would be to trim the imported data to a selection of dates.

With the GA API, you have to set a start and end date

In Power BI, you always seem to import everything.

 

I have tried to add dateranges to my query as per this guide

 

    dateRanges= {[startDate="2014-11-01", endDate="2014-11-30"]},

It does not throw any error, but the date ranges are not applied either.

 

If this is possible, what is the right syntax?

 

Regular Visitor

I ran into the same problems. 

Connecting to the GA API via Power BI gave me wrong numbers while using the GA API  via SEO Tools for Excel was ok.

This may be linked to the amount of data imported.

 

I have tried to modify the query using the advanced editor.

I added a line with samplingLevel=  "LARGE", as indicated in the documentation  

 

let
    Source = GoogleAnalytics.Accounts(),
    Account = Source{[Id=#"GA Account Id"]}[Data],
    Property = Account{[Id=#"GA Property Id"]}[Data],
    View = Property{[Id=#"GA View Id"]}[Data],
    samplingLevel=  "LARGE",    
    #"Éléments ajoutés" = Cube.Transform(View, {{Cube.AddAndExpandDimensionColumn, "ga:yearMonth", {"ga:yearMonth"}, {"Month of Year"}}, {Cube.AddAndExpandDimensionColumn, "ga:medium", {"ga:medium"}, {"Medium"}}, {Cube.AddMeasureColumn, "Sessions", "ga:sessions"}})
in
    #"Éléments ajoutés"

It does not return errors, but seems to solve the issues as the figures are the same as in GA.

 

However, another solution would be to trim the imported data to a selection of dates.

With the GA API, you have to set a start and end date

In Power BI, you always seem to import everything.

 

I have tried to add dateranges to my query as per this guide

 

 

    dateRanges= {[startDate="2014-11-01", endDate="2014-11-30"]},

 

It does not throw any error, but the date ranges are not applied either.

 

If this is possible, what is the right syntax?

 

Hi @offrench,

I imagine is because you have to add the samplingLevel on the cube request, not outside. As you can see in the documentation that you posted. 

POST https://analyticsreporting.googleapis.com/v4/reports:batchGet
{
  "reportRequests":
  [
    {
      "viewId": "XXXX",
      "dimensions": [{"name": "ga:medium"}],
      "metrics": [{"expression": "ga:sessions"}],
      "samplingLevel":  "LARGE"
    }
  ]
}


Still, somehow the cube request still not work because it doesn't have the same request, maybe you will have to do some work around. 

Best,

Jorge

@carlydugger, I have the same problem with the data of GA, It´s something related with the API of GA and the quantity of data.
The workaround for that is, filter by dates periods in Power Query until your data match in PBI and GA.

And we will wait to Power BI team fix this problem

 

 

 

Is that code block in your reply the correct syntax or the example of what not to do?

I've seen a few conflicting answers, such as these:

samplingLevel=  "LARGE", 
#"samplingLevel" = HIGHER_PRECISION
"samplingLevel" = "LARGE"

As you can see, some advise to put quotes around the parameter name, others to put quote around the value, or both. The "Higher_Precision" doesn't seem to be mentioned in Google's API docs at all, so right now I believe "large" is probably the right one to use.

But I am still not sure where to place it in my PowerQuery's code.  Your post looks like a json rest request, but PowerQuery's advanced editor is a bit different.

Would you let me know if this is the proper placement for this parameter?  I replaced the account numbers and such with placeholders, but otherwise this is my exact query right now.

 

let
    Source = GoogleAnalytics.Accounts(),
    #"1234567" = Source{[Id="1234567"]}[Data],
    #"UA-1234567-1" = #"1234567"{[Id="UA-1234567-1"]}[Data],
    #"987654321" = #"UA-1234567-1"{[Id="987654321"]}[Data],
    #"samplingLevel" = "LARGE",
    #"Added Items" = Cube.Transform(#"987654321",
        {
            {Cube.AddAndExpandDimensionColumn, "ga:dimension4", {"ga:dimension4"}, {"CustomID"}},
            {Cube.AddAndExpandDimensionColumn, "ga:date", {"ga:date"}, {"Date"}},
            {Cube.AddAndExpandDimensionColumn, "ga:channelGrouping", {"ga:channelGrouping"}, {"Default Channel Grouping"}},
            {Cube.AddMeasureColumn, "Sessions", "ga:sessions"}
        })
in
    #"Added Items"

 


CmdrKeene

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors