cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
intrasight Member
Member

Re: Google Analytics connector missing startdate/enddate parameters

Hi Thomas,

Microsoft did indicate in this tread that they were considering/planning to ad the start/end date parameters. Perhaps someone from Microsoft can chime in. I ended up proxying my requests through a middle-tier service to get what I wanted.

 - Chris

 

Jmenas Member
Member

Re: Google Analytics connector missing startdate/enddate parameters

Hi @intrasight

Thanks for the post, somehow I started to have the same issue just a few days ago. Do you have any news about the fix or the workaround? 


I have the same issue just with dates and E-Commerce data. Only that if I want the correct data as my Analytics UI I have to filter by weeks. Smiley Sad 
I don't know why the folding is so bad for the data for some properties and not for others. 

Let me know if there is an Idea or a Bug issue to vote for. 

Best,
J.

intrasight Member
Member

Re: Google Analytics connector missing startdate/enddate parameters

I've not heard any updates on this. I assume it just not a high-priority item Smiley Sad

SergBI Visitor
Visitor

Re: Google Analytics connector missing startdate/enddate parameters

Hey, Chris!

 

I have also encountered the data sampling in Power BI.

 

It even occurred within small projects because Power BI required data for the whole period.

 

I have found the solution in creating our own connector that would divide queries by days and import them in the SQL Server Database. This way, we managed to solve the problem.

Eventually, our solution escalated into a separate product. It is more convenient to work with one database which contains not only Google Analytics data, but other data as well. Such as data from Google AdWords and CRM systems.

 

You can try to set up integration on our website.

 

I am always there and happy to help if you have any questions.

artemmuntianu Frequent Visitor
Frequent Visitor

Re: Google Analytics connector missing startdate/enddate parameters

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.

artemmuntianu Frequent Visitor
Frequent Visitor

Re: Google Analytics connector missing startdate/enddate parameters

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.

s-in-a-triangle Frequent Visitor
Frequent Visitor

Re: Google Analytics connector missing startdate/enddate parameters

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?

Ali_Afzal Visitor
Visitor

Re: Google Analytics connector missing startdate/enddate parameters

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.

artemmuntianu Frequent Visitor
Frequent Visitor

Re: Google Analytics connector missing startdate/enddate parameters

Hi, all

 

It seems, first you should check are actual GA requests which PBI do.

It is possible to see them in log files. 

Turn logging on -> https://docs.microsoft.com/en-us/power-bi/desktop-troubleshooting-sign-in -> refresh your report -> navigate to the Traces folder on your local computer. Search for text like "https://www.googleapis.com/analytics/v3/data/ga". It will be somewhere in log files.

Compare requests URI with expected from GA query builder. If they are the same, your issue does not relate to GA Connector.

michaelbilling Regular Visitor
Regular Visitor

Re: Google Analytics connector missing startdate/enddate parameters

@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