cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jdejonge Frequent Visitor
Frequent Visitor

Google Analytics Reporting API, Connector and Sampling

Hi Community,

 

Like more people within this community, I am struggling with the Google Analytics Reporting API connector. It still looks like we have some kind of sampling in the data, but I cannot verify whether or not this is actually sampling or something wrong with my query. We use the Table.SelectRows method described in this post: https://community.powerbi.com/t5/Desktop/Dynamic-value-in-datatime-QueryFilter/m-p/360343#M162637, to pull in data from 1 week. Sampling in the dataset should not be able to occur for one week worth of data. We do however see a discrepancy between the dataset and the reporting interface for all metrics.

 

When I try to verify the data in the Google Analytics interface with the data I can pull from the Query Explorer (https://ga-dev-tools.appspot.com/query-explorer/), I see the data matches 100%. Hence, there seems to be something wrong with the connector and/or my query.

 

My query looks like this:

 

 let
    Source = GoogleAnalytics.Accounts(),
    #"1" = Source{[Id="XXXXXX"]}[Data],
    #"2" = #"1"{[Id="UA-XXXXXX"]}[Data],
    #"3" = #"2"{[Id="XXXXXX"]}[Data],
    #"Added Items" = Cube.Transform(#"3",
        {
            {Cube.AddAndExpandDimensionColumn, "ga:date", {"ga:date"}, {"Date"}},
            {Cube.AddAndExpandDimensionColumn, "ga:hostname", {"ga:hostname"}, {"Domain"}},
            {Cube.AddAndExpandDimensionColumn, "ga:userType", {"ga:userType"}, {"User Type"}},
            {Cube.AddMeasureColumn, "Users", "ga:users"},
            {Cube.AddMeasureColumn, "Sessions", "ga:sessions"},
            {Cube.AddMeasureColumn, "Pageviews", "gaSmiley Tongueageviews"},
            {Cube.AddMeasureColumn, "Bounces", "ga:bounces"},
            {Cube.AddMeasureColumn, "Session Duration", "ga:sessionDuration"}
        }),
 #"combinedData" = Table.Combine({
Table.SelectRows(#"Added Items", each (Date.Year([Date]) = 2019 and Date.WeekOfYear([Date]) = 13))
 })
  in
  #"combinedData"

 

Hope someone is able to help me out!

2 REPLIES 2
Community Support Team
Community Support Team

Re: Google Analytics Reporting API, Connector and Sampling

Hi @jdejonge ,

If I understand your scenario correctly that you want to filter rows before you load the data from GoogleAnalytics?

If it is,  I'm still a little confused about your question. Could you load the data from GoogleAnalytics with your M query successfully?

If you have errors, could you share the error message so that we could help further on it?

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jdejonge Frequent Visitor
Frequent Visitor

Re: Google Analytics Reporting API, Connector and Sampling

Hi @v-piga-msft (Cherry),

 

Thank you for your reply!

 

I am able to load the data succesfully with my M query and I am able to do so one week at a time. But even when I load one week of data, I see that my data does not match with the data in the Google Analytics interface. When I ask for the same data with the Query API Explorer tool from Google, I see that the data does match with the Google Analytics interface.

 

It is not possible to trigger sampling for one weeks worth of data. At most this would be 60.000 sessions. Would you know which other reasons would exist, why the data from my M query would not match the Google Analytics interface / Query API Explorer. 

 

We also know it is not due to Power Query M weeknumbers following the sunday - saturday date format instead of the monday - sunday date format.

 

All help would be appreciated!