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
pqian's avatar
pqian
Microsoft Employee
10 years ago

PowerQuery models this with filters:

 

 

    #"Filtered Rows" = Table.SelectRows(gadata, each Date.IsInPreviousWeek([Date]))

 

 

intrasight's avatar
intrasight
Helper IV
10 years ago

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.

  • pqian's avatar
    pqian
    Microsoft Employee
    10 years ago

    I'm not sure what you mean by Cannot aggregate client-side. If the date filter is in the expected range, we will fold that into the start-date and end-date parameter of the request URL. For example, this query:

     

        #"Added Items" = Cube.Transform(cube, {{Cube.AddAndExpandDimensionColumn, "ga:date", {"ga:date"}, {"Date"}}, {Cube.AddMeasureColumn, "Sessions", "ga:sessions"}, {Cube.AddMeasureColumn, "Users", "ga:users"}}),
    #"Filtered Rows" = Table.SelectRows(#"Added Items", each [Date] >= #date(2016, 1, 27) and [Date] <= #date(2016, 2, 4))

    will be folded into

    ?...&metrics=ga:sessions,ga:users&start-date=2016-01-27&end-date=2016-02-04&start-index=1

    If you don't want to see the date dimension, you can collapse it later. The filter stays active.

    = Cube.CollapseAndRemoveColumns(#"Filtered Rows", {"Date"})