Forum Discussion
Google Analytics API returning wrong event counts
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
- One called "% Positive Feedback" with the formula of:
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!