Google Analytics connector missing startdate/enddate parameters

Please correct me if I'm wrong, but I don't think this can be done with filter semantics.

Let me give a more realistic example

let
    Source = GoogleAnalytics.Accounts(),
    account = Source{[Id="654321"]}[Data],
    webprop = account{[Id="UA-654321-1"]}[Data],
    profile = webprop{[Id="123456"]}[Data],
    gadata = Cube.Transform(
        profile,
        {
           {
              Cube.AddMeasureColumn, 
              "Users", 
              "ga:users"
           }
        }
    ),
in
  gadata

As you see, there is no date dimension in the query. Because I want the value of the metrics over a date range. I need metrics (such as ga:users) which cannot be aggregated client-side.  So I need the connector to support the missing start and end date parameters.

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.

It's the same reason that it would be wrong in MDX - you can't aggregate client-side. Have someone who knows MDX look at the GA integration and I'm sure they can straighten it out. Or have them contact me.

I did see the additional parameters in SAP and AS, and thought "that's the solution".

Thanks for investigating.

Thank you for the above code. This works to circumvent the sampling issue!

I use a code like this:

let
    Source = GoogleAnalytics.Accounts(),
    #"1" = Source{[Id="999"]}[Data],
    #"2" = #"1"{[Id="999"]}[Data],
    #"3" = #"2"{[Id="999"]}[Data],
    #"Added Items" = Cube.Transform(#"3",
        {
            {Cube.AddAndExpandDimensionColumn, "ga:channelGrouping", {"ga:channelGrouping"}, {"Default Channel Grouping"}},
            {Cube.AddAndExpandDimensionColumn, "ga:customfield", {"ga:customfield"}, {"Customfield"}},
            {Cube.AddAndExpandDimensionColumn, "ga:yearMonth", {"ga:yearMonth"}, {"Month of Year"}},
            {Cube.AddMeasureColumn, "Revenue", "ga:transactionRevenue"},
            {Cube.AddMeasureColumn, "Transactions", "ga:transactions"}
        }),

 #"combinedData" = Table.Combine({
Table.SelectRows(#"Added Items", each ([Month of Year] = "201803")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201804")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201805")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201806")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201807")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201808")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201809")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201810")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201811")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201812")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201801")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201802")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201903")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201904")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201905")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201906")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201907")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201908")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201909")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201910")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201911")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201912")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201901")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201902")),
Table.SelectRows(#"Added Items", each Text.Contains([Month of Year], "2017"))
 })
in
    combinedData

This works, but for performance reasons and scalability I'm looking for an improvement over this as I now query again and again also the historical data. Ideally I would like to create a combined datasource that contains all data up till and including last month. And then have a seperate connection for the data for this month. And combine the two. That way I don't have to send all seperate monthly requests for the historic data anymore, which saves on GA requests. That historic datasource should of course update as soon as a new month starts, so for instance when August starts my historic dataset should automatically contain data up till and inclusing July 2018 (as I aim to avoid manual tasks).

Is this possible in the query editor? Anybody any ideas?