Forum Discussion
Google Analytics connector missing startdate/enddate parameters
PowerQuery models this with filters:
#"Filtered Rows" = Table.SelectRows(gadata, each Date.IsInPreviousWeek([Date]))
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.
Here's the specifics. Here's the query:
let Source = GoogleAnalytics.Accounts(), GaAccount = Source{[Id="xxxx"]}[Data], GaWebProp = GaAccount{[Id="UA-xxxx-1"]}[Data], Profile = GaWebProp{[Id="13844713"]}[Data], #"Added Items" = Cube.Transform(Profile, {{Cube.AddAndExpandDimensionColumn, "ga:date", {"ga:date"}, {"Date"}}, {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"And here's the three GA API queries made:
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&start-date=2016-01-01&end-date=2016-01-31&start-index=1&max-results=1000 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&start-date=2016-01-01&end-date=2016-01-31&start-index=1&max-results=1000 https://www.googleapis.com/analytics/v3/data/ga?ids=ga:13844713&metrics=ga:users&dimensions=ga:date&start-date=2009-01-01&end-date=2016-02-04&start-index=1&max-results=1000
The first filters in all the dates (which is redundant due to start-date and end-date, and has no date dimension. This query if run in GA returns users=893
The second again lists all dates but also has date dimension. This query if run in GA returns users=1034
The third doesn't list dates but has current date as end-date. I don't know why it runs this query - I didn't check GA directly.
In Power BI, the value returned is users=1034. The correct value is users=893.
This post SHOULD be chronologically after the "Here's the specifics. " post. But the discussion software is buggy and won't allow a response to the last post. So I'm responding out of order. Hopefully it will sort display chronologically.
I thought of a potential workaround. It sort of works but with caveats that I'll describe. Rather than use ga:date, which doesn't fold properly, I did this:
let Source = GoogleAnalytics.Accounts(), GaAccount = Source{[Id="xxx"]}[Data], GaWebProp = GaAccount{[Id="UA-xxx-1"]}[Data], GaProfile = GaWebProp{[Id="13844713"]}[Data], GaData = Cube.Transform(GaProfile, {{Cube.AddAndExpandDimensionColumn, "ga:yearMonth", {"ga:yearMonth"}, {"Month of Year"}}, {Cube.AddMeasureColumn, "Users", "ga:users"}}), #"Filtered Rows" = Table.SelectRows(GaData, each ([Month of Year] = "201601")), #"Collapsed and Removed Columns" = Cube.CollapseAndRemoveColumns(#"Filtered Rows", {"Month of Year"}) in #"Collapsed and Removed Columns"It give the corrent value users=893. But it doesn't send a start-date or end-date back to GA. Here are the GA calls:
https://www.googleapis.com/analytics/v3/data/ga?ids=ga%3a13844713&filters=ga%3ayearMonth%3d%3d201601&metrics=ga%3ausers&start-date=2009-01-01&end-date=2016-02-05 https://www.googleapis.com/analytics/v3/data/ga?ids=ga%3a13844713&filters=ga%3ayearMonth%3d%3d201601&metrics=ga%3ausers&dimensions=ga%3ayearMonth&start-date=2009-01-01&end-date=2016-02-05 https://www.googleapis.com/analytics/v3/data/ga?ids=ga%3a13844713&metrics=ga%3ausers&dimensions=ga%3ayearMonth&start-date=2009-01-01&end-date=2016-02-05
So GA has to deliver data for every month since the site was created (2009). Not very smart or efficient. I want data for just one month. But at least it gives the right value.
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?
intrasight - have you found a solution to client vs. server side calculation yet?
If not, take a look at Scitylana
Then you will be able to import un-aggregated Google Analytics data into PBI or even choose to have it in a database and run direct query.
You can try it here, www.scitylana.com
