Combining multiple Google Analytics Queries give different results

[Anonymous]

Hi,

I'm fairly new to PowerBI. I am trying to create a dashboard based on Google Analytics data through the GA API. For my report I need more than 10 metrics. I quickly noticed that the limit is set at 10 and thus would make things harder. I try to solve it by creating multiple GA queries and after that I would like to merge them. This seems to be allowed and thus could work. This is the idea:

Query 1: Date, Campaign name, Source/Medium, Sessions, Pageviews, Users, ExitPage, Goal completions, (and some more)

Query 2: Date, Campaign name, Source/Medium, Page, Device Category, Avg time on page, Gender

Query 3: Date, Campaign name, Source/Medium, Latitude, Longitude

I would expect these queries to be of the same lenght (same # rows) as I use several similar dimensions. However, they are all of different sizes. This makes merging the queries impossible as I have a lot of null data. 

I also checked if the big queries display the same numbers as GA itself. It does not. Image below displays on the left a query with only Pageviews and on the right a query combined with other metrics (Query 1):

The number on the left is identical to GA.com but the right is not.

What should I do differently? 

[v-jingzhang]

Hi @Anonymous 

Did you select the first three columns as matching columns when you merged the queries? And which Join Kind did you pick? If one query returns a row number which is identical to GA.com, you could consider putting it in the left side, then select Left Outer to merge additional columns from the second table. If Table1 has more rows than matched rows, it will display null in these additional columns of non-matched rows. You could also try other Join Kind which could meet your need.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[Anonymous]

Thanks for the reply.

I think the merge works as intended. But it goes wrong with the initial load of the seperate GA queries. Right now I used a Left Outer join. 

See, I would expect the seperate queries to be of the same size, which would lead to having 0 null values when merging. I suspect that the GA load leaves out some data depending on what dimensions are being called.

For example: I have 2 GA queries which both contain at least Date, Campaign, Source/Medium AND Sessions (both queries contain more than these). I would expect both queries to be off the same size because both contain date and sessions, however. Query 1 has 2367 rows, while query 2 has 23715 rows. Now when i Merge this it obviously gives me null values, but this shouldnt be the case in my thought.

How can I make sure that both queries are of the same length and thus can be merged?

[v-jingzhang]

Hi @Anonymous 

It seems weird. If you are connecting to the same data source for these separate queries, they should have the same number of rows. How do you create these separate queries? Can you share their M codes from the Advanced Editor? Remove sensitive info in these M codes.

Regards,

Jing

[Anonymous]

Query 1:

let
Source = GoogleAnalytics.Accounts(),
#"X" = Source{[Id="X"]}[Data],
#"UA-X-1" = #"X"{[Id="UA-X-1"]}[Data],
#"X" = #"UA-X-1"{[Id="x"]}[Data],
#"Added Items" = Cube.Transform(#"X",
{
{Cube.AddAndExpandDimensionColumn, "ga:userAgeBracket", {"ga:userAgeBracket"}, {"Age"}},
{Cube.AddAndExpandDimensionColumn, "ga:userGender", {"ga:userGender"}, {"Gender"}},
{Cube.AddAndExpandDimensionColumn, "ga:pagePath", {"ga:pagePath"}, {"Page"}},
{Cube.AddAndExpandDimensionColumn, "ga:deviceCategory", {"ga:deviceCategory"}, {"Device Category"}},
{Cube.AddAndExpandDimensionColumn, "ga:date", {"ga:date"}, {"Date"}},
{Cube.AddAndExpandDimensionColumn, "ga:campaign", {"ga:campaign"}, {"Campaign"}},
{Cube.AddAndExpandDimensionColumn, "ga:sourceMedium", {"ga:sourceMedium"}, {"Source / Medium"}},
{Cube.AddMeasureColumn, "Avg. Time on Page", "ga:avgTimeOnPage"},
{Cube.AddMeasureColumn, "Pageviews", "ga:pageviews"}
}),
#"Collapsed and Removed Columns" = Cube.CollapseAndRemoveColumns(#"Added Items", {"Age"}),
#"Added Items1" = Cube.Transform(#"Collapsed and Removed Columns",
{
{Cube.AddMeasureColumn, "Sessions", "ga:sessions"}
}),
#"Inserted Merged Column" = Table.AddColumn(#"Added Items1", "Date/Campaign", each Text.Combine({Text.From([Date], "nl-NL"), [Campaign]}, "/"), type text),
#"Counted Rows" = Table.RowCount(#"Inserted Merged Column")
in
#"Counted Rows"

 Query 2:

let
Source = GoogleAnalytics.Accounts(),
#"X" = Source{[Id="X"]}[Data],
#"UA-X-1" = #"X"{[Id="UA-X-1"]}[Data],
#"X" = #"UA-X-1"{[Id="X"]}[Data],
#"Added Items" = Cube.Transform(#"X",
{
{Cube.AddAndExpandDimensionColumn, "ga:date", {"ga:date"}, {"Date"}},
{Cube.AddAndExpandDimensionColumn, "ga:campaign", {"ga:campaign"}, {"Campaign"}},
{Cube.AddAndExpandDimensionColumn, "ga:sourceMedium", {"ga:sourceMedium"}, {"Source / Medium"}},
{Cube.AddMeasureColumn, "New Users", "ga:newUsers"},
{Cube.AddMeasureColumn, "Users", "ga:users"}
}),
#"Added Items1" = Cube.Transform(#"Added Items",
{
{Cube.AddMeasureColumn, "Sessions", "ga:sessions"}
}),
#"Inserted Merged Column" = Table.AddColumn(#"Added Items1", "Date/Campaign", each Text.Combine({[Campaign], Text.From([Date], "nl-NL")}, "/"), type text),
#"Counted Rows" = Table.RowCount(#"Inserted Merged Column")
in
#"Counted Rows"

Now I see that I add several dimensions later, could this be problematic?

[v-jingzhang]

Hi @Anonymous , that may be. If you count rows after the #"X" step, will they return the same result?