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
artemmuntianu's avatar
artemmuntianu
Frequent Visitor
8 years ago

Our solution to un-sampling is to break a month/year query to many daily queries, and aggregate the data later on the client-side.

Here is a code

let
    Source = GoogleAnalytics.Accounts(),
    #"1" = Source{[Id="999"]}[Data],
    #"2" = #"1"{[Id="999"]}[Data],
    #"3" = #"2"{[Id="999"]}[Data],
    #"newCube" = Cube.Transform(#"3",
        {
            {Cube.AddAndExpandDimensionColumn, "ga:date", {"ga:date"}, {"Date"}},
            {Cube.AddAndExpandDimensionColumn, "ga:deviceCategory", {"ga:deviceCategory"}, {"Device Category"}},
            {Cube.AddAndExpandDimensionColumn, "ga:userAgeBracket", {"ga:userAgeBracket"}, {"Age"}},
            {Cube.AddMeasureColumn, "Sessions", "ga:sessions"}
        }),
    combinedData = Table.Combine({
        Table.SelectRows(#"newCube", each ([Date] = #date(2017, 1, 1))),
        Table.SelectRows(#"newCube", each ([Date] = #date(2017, 1, 2))),
... {days in between} ...
        Table.SelectRows(#"newCube", each ([Date] = #date(2018, 3, 4))),
        Table.SelectRows(#"newCube", each ([Date] = #date(2018, 3, 5)))
    })
in
    combinedData

When we run that code it makes PowerBI to send 429 GA requests. One request per day.

So, Power BI is actually requests GA with proper startdate and enddate params.

One problem still remains - when you combine data on the client side it becomes a bit inaccurate in terms of unique counters.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi all,

       

      I just wanted to see if there has been any updates on this topic or if anyone found a way around this?

       

      I am getting the correct figures by applying the above mentioned steps for date and using collapse however I have to filter my data which contains a model name in all the page paths. This works really well on the GA query builder but when I add the "contains" filter in Power BI I don't get the correct visitors numbers. It would allow me to collapse and remove as it says that it is not a cube.