Problem with Google Analytics data

[andrescaso]

Hi all, I connected GA to the desktop version of Power BI, but the data (in days, users, and channel) in BI doesn't match with GA reports. After reading a lot of post, I was filter between dates in Power Query and achieve the data match in both systems, but the problem is the maximun range of date is 2 month, I need years. Somebody knows a workaround better than this to retrieve data of the GA api? or if the Power Bi team it´s working in fix this issue?

[Kalpit_Shah]

Hi,

My data in the Power BI desktop is higher than the numbers showing on the Google Analytics site. I have checked all the filters, tried setting a new connection and also verified the data, but I cannot figure out the problem. 

Please help. 

[andrescaso]

Try to filter your table in power query by dates, at some point your data in power bi is the same than google analytics

Regards

[CmdrKeene]

You'd think so, but it's not.  I think the blame rests with Google, but I don't know what to do about it.

In the very first step the data is already wrong because Google tries to send all data without filters, and if there are more rows than the return limit it just truncates the rest, and the data is never correct in PowerBI.

[v-eachen-msft]

Hi @andrescaso ,

You could vote for this similar idea.
You could also come up a new idea about that and add your comments there to improve Power BI and make this feature coming sooner.

https://ideas.powerbi.com/forums/265200-power-bi-ideas

[andrescaso]

Thanks @v-eachen-msft 

I found and vote in this post https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/39361177-data-credential-issues-i... with the same problem

[andrescaso · Accepted solution]

Thanks @v-eachen-msft , By the way, I find a brief job to fix this problem, in the advanced editor you must add this code: #"samplingLevel" = HIGHER_PRECISION in each GA table,

meanwhile vote in this post https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/39361177-data-credential-issues-i... with the same problem

[CmdrKeene]

Thanks for posting this @andrescaso - I'm also trying to resolve some similar Google Analytics issues.

Another post had mentioned adding this:

    samplingLevel=  "LARGE",    

While yours shos to add:

    #"samplingLevel" = HIGHER_PRECISION

Which one is the proper syntax?  Do quote marks belong around the "samplingLevel" parameter name, or the value (large or higher_precision)?

Also do you know where the values are documented?  What's the difference between large and higher_precision?

[andrescaso]

Hi @CmdrKeene 

you can find the info in the support or GA, in this URL https://developers.google.com/analytics/devguides/reporting/core/v3/reference?hl=en#samplingLevel

mmm the value "LARGE" a think is not valid value

I copy for you the explanation of this parameter:

samplingLevel

samplingLevel=DEFAULTOptional.Use this parameter to set the sampling level (i.e. the number of sessions used to calculate the result) for a reporting query. The allowed values are consistent with the web interface and include:

• DEFAULT — Returns response with a sample size that balances speed and accuracy.
• FASTER — Returns a fast response with a smaller sample size.
• HIGHER_PRECISION — Returns a more accurate response using a large sample size, but this may result in the response being slower.

If not supplied, the DEFAULT sampling level will be used.See the Sampling section for details on how to calculate the percentage of sessions that were used for a query.

Regards

[CmdrKeene]

I so far can't seem to retrieve valid results (matching the Google Analytis website) no matter what I try.  I think I must just have too much data to query in this way.

[andrescaso]

Start simple, maybe only with users and date only, remember GA have a limit of 10.000 rows in his API

put a date filter and compare yours numbre in PBI with GA

this is my code in the advance editor for your help

let
Source = GoogleAnalytics.Accounts(),
#"xxxxxxxx" = Source{[Id="xxxxxxxx"]}[Data],
#"UA-xxxxxxxx-1" = #"xxxxxxxx"{[Id="UA-xxxxxxxx-1"]}[Data],
#"yyyyyyyy" = #"UA-xxxxxxxx-1"{[Id="yyyyyyyy"]}[Data],
#"samplingLevel" = HIGHER_PRECISION,
#"Added Items" = Cube.Transform(#"81471935",
{
{Cube.AddAndExpandDimensionColumn, "ga:date", {"ga:date"}, {"Date"}},
{Cube.AddMeasureColumn, "Users", "ga:users"}
}),
#"Filtered Rows" = Table.SelectRows(#"Added Items", each [Date] >= #date(2017, 1, 1) and [Date] < #date(2030, 1, 1))
in
#"Filtered Rows"

Regards

[CmdrKeene]

I didn't know about that limit, but a 10,000 row return limit is definitely going to mean I can't use this for my implimentation of PowerBI directly.

I'll need to have our team gather date and store it in our own data warehouse, then have PowerBI reference that location instead.  Here's why:

My first goal was to get the daily number of sessions, broken down default channel grouping, and broken down by a custom variable dimension we have. Our Google Analytics gathers data from around 8,000 domains into one UA tracking code, and that custom variable dimension is the website ID that lets us actually see the data for a particular site.  But this means even a single day's of data is going to return nearly the limit (round 8k rows).  And I wanted to get at least a year of this data into PowerBI so I could show a trend (so let's assume around 8000 X 365 = nearly 3 million records returned.

[andrescaso]

look, a find this post maybe to help you

https://www.ryanpraski.com/google-analytics-sheets-add-on-unsampled-data-more-than-10000-rows/

Regards

[v-eachen-msft]

Hi @andrescaso ,

I'm glad that you could find an idea that meets your needs. You could accept your own reply to top this idea, so that more people in need can vote more easily.