Forum Discussion

intrasight's avatar
intrasight
Helper IV
10 years ago

Google Analytics connector missing startdate/enddate parameters

  I thought this had been asked before, but I didn't find a thread via search. A GA connection has a query pattern like so: let Source = GoogleAnalytics.Accounts(), account = Source{[Id="6...
Need Help
intrasight's avatar
intrasight
Helper IV
10 years ago

Hi,


For some reason the discussion forum won't let me respond to your most recent post, so I'm responding to this one.


By client-side, I mean that the sum is being calculated by the client - Power BI in this case - as opposed to server-side (in GA). Query folding to GA cannot work if the GA query includes the date dimension, which it still does after adding the filter. You'll see this if you look at the GA API call being made.


The correct answer from GA for my query "users last month" is 893. Power BI is reporting 1034, which is the sum of the users on each of the 31 days. "users" and perhaps half of the other metrics cannot be aggregated client-side, and so Power BI is reporting incorrect values. For query folding to work against GA, it would have to remove the date dimension from the API call - which is clearly not happening.

 

intrasight's avatar
intrasight
Helper IV
10 years ago

 

One last work-around I thought to try

let
    Source = GoogleAnalytics.Accounts(),
    GaAccount = Source{[Id="xxx"]}[Data],
    GaWebProp = GaAccount{[Id="UA-xxx-1"]}[Data],
    GaProfile = GaWebProp{[Id="13844713"]}[Data],
    #"Added Items" = Cube.Transform(GaProfile, {{Cube.AddAndExpandDimensionColumn, "ga:date", {"ga:date"}, {"Date"}}, {Cube.AddAndExpandDimensionColumn, "ga:yearMonth", {"ga:yearMonth"}, {"Month of Year"}}, {Cube.AddMeasureColumn, "Users", "ga:users"}}),
    #"Filtered Rows" = Table.SelectRows(#"Added Items", each [Date] >= #date(2016, 1, 1) and [Date] <= #date(2016, 1, 31)),
    #"Collapsed and Removed Columns" = Cube.CollapseAndRemoveColumns(#"Filtered Rows", {"Date"})
in
    #"Collapsed and Removed Columns"

Which results in these two calls to GA

https://www.googleapis.com/analytics/v3/data/ga?ids=ga:13844713&filters=ga:date==20160101,ga:date==20160102,ga:date==20160103,ga:date==20160104,ga:date==20160105,ga:date==20160106,ga:date==20160107,ga:date==20160108,ga:date==20160109,ga:date==20160110,ga:date==20160111,ga:date==20160112,ga:date==20160113,ga:date==20160114,ga:date==20160115,ga:date==20160116,ga:date==20160117,ga:date==20160118,ga:date==20160119,ga:date==20160120,ga:date==20160121,ga:date==20160122,ga:date==20160123,ga:date==20160124,ga:date==20160125,ga:date==20160126,ga:date==20160127,ga:date==20160128,ga:date==20160129,ga:date==20160130,ga:date==20160131&metrics=ga:users&dimensions=ga:yearMonth&start-date=2016-01-01&end-date=2016-01-31

https://www.googleapis.com/analytics/v3/data/ga?ids=ga:13844713&filters=ga:date==20160101,ga:date==20160102,ga:date==20160103,ga:date==20160104,ga:date==20160105,ga:date==20160106,ga:date==20160107,ga:date==20160108,ga:date==20160109,ga:date==20160110,ga:date==20160111,ga:date==20160112,ga:date==20160113,ga:date==20160114,ga:date==20160115,ga:date==20160116,ga:date==20160117,ga:date==20160118,ga:date==20160119,ga:date==20160120,ga:date==20160121,ga:date==20160122,ga:date==20160123,ga:date==20160124,ga:date==20160125,ga:date==20160126,ga:date==20160127,ga:date==20160128,ga:date==20160129,ga:date==20160130,ga:date==20160131&metrics=ga:users&dimensions=ga:date,ga:yearMonth&start-date=2016-01-01&end-date=2016-01-31

PowerBI still gives the wrong number of 1034, which means that it is unsing the first API call and then doing client-side aggregation.  Again, so close but no cigar. If it had used the first API call, it would have returned the correct result. Why is the connector making that second incorrect call to GA? Does MSFT realize that this bug makes the connector near useless?