Google Analytics API returning wrong event counts

Bane313 — Thu, 07 Dec 2023 14:53:52 GMT

Hello everyone.

I'm using the Google Analytics API (v2.0 beta) to build some reports in PowerBI Desktop. I'm still pretty new to PowerBI, so I'm sure the error is on my end, but I'd appreciate any help I can get.

I have 3 tables in my report. One is a dimension table that includes the "hostname" dimension, the "eventname" dimension (text filtered to include the word "helpful" which should return the events "helpful" and "not_helpful"), and the event count metric. Another dimension table that includes "hostname", "month", "year", and "bouncerate", "newusers","totalusers", and "userengagementduration". The final table is a fact table that includes "hostname". The relationship from both of the dimension tables to the fact table is "hostname".

In the Helpful/Nothelpful table, I have 3 custom measures:

one called "feedback total" with the formula of

feedback total = calculate(sum('Helpful/not helpful'[eventCount]),'Helpful/not helpful'[Helpful event]="helpful"||'Helpful/not helpful'[Helpful event]="not_helpful")

One called "helpful total" with the formula of

helpful total = calculate(sum('Helpful/not helpful'[eventCount]),'Helpful/not helpful'[Helpful event]="helpful")

One called "% Positive Feedback" with the formula of:

% Positive Feedback = divide('Helpful/not helpful'[helpful total], [feedback total])

When I try to make the Matrix with the following options, my event count for the "helpful" and "nothelpful" events are way off.

I'd imagine that the error is coming at the table level for my "helpful/nothelpful" table. My query looks like this:

let
Source = GoogleAnalytics.Accounts([Implementation="2.0"]),
#"accounts/XXXXXXXXX" = Source{[Id="accounts/XXXXXXXX"]}[Data],
#"properties/XXXXXXXX" = #"accounts/XXXXXXXX"{[Id="properties/XXXXXXXX"]}[Data],
#"properties/1" = #"properties/XXXXXXXXX"{[Id="properties/XXXXXXXX"]}[Data],
#"Added Items" = Cube.Transform(#"properties/1",
{
{Cube.AddAndExpandDimensionColumn, "eventName", {"eventName"}, {"eventName"}},
{Cube.AddAndExpandDimensionColumn, "hostName", {"hostName"}, {"hostName"}},
{Cube.AddMeasureColumn, "eventCount", "eventCount"}
}),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Items", {"eventName"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Errors", each Text.Contains([eventName], "helpful")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"eventName", "Helpful event"}})
in
#"Renamed Columns"

What am I doing wrong? Any help would be immensely appreciated!

topic Google Analytics API returning wrong event counts in DAX Commands and Tips

Google Analytics API returning wrong event counts