"Left join" of Google Analytics Custom Dimensions?

[Anonymous]

Hi,

I've got a GA query as such:

    #"Added Items" = Cube.Transform(#"00000000",
        {
            {Cube.AddAndExpandDimensionColumn, "ga:hostname", {"ga:hostname"}, {"Hostname"}},
            {Cube.AddAndExpandDimensionColumn, "ga:pagePath", {"ga:pagePath"}, {"Page"}},
            {Cube.AddAndExpandDimensionColumn, "ga:source", {"ga:source"}, {"Source"}},
            {Cube.AddAndExpandDimensionColumn, "ga:campaign", {"ga:campaign"}, {"Campaign"}},
            {Cube.AddAndExpandDimensionColumn, "ga:dimension2", {"ga:dimension2"}, {"XYZ 1"}},
            {Cube.AddAndExpandDimensionColumn, "ga:dimension1", {"ga:dimension1"}, {"XYZ 2"}},
            {Cube.AddAndExpandDimensionColumn, "ga:date", {"ga:date"}, {"Date"}},
            {Cube.AddMeasureColumn, "Pageviews", "ga:pageviews"},
            {Cube.AddMeasureColumn, "Users", "ga:users"}
        })

The two custom dimensions are fairly new.

By adding them, the dataset is limited to the timespan since we added them.

Is there any way to have GA return data for all time, making the values for them "(not set)" or something?

I suspect it's a limitation in the GA APIs, but thought it couldn't hurt to ask.

Have a brewing idea that I could make an "append" query limited to all time before we added them...?

Ideas appreciated. 🙂

Lars-Erik

[Stachu · Accepted solution]

it's related to Google API, so if you can create such a query using different tools, then it should also be possible in Power Query. But it's not really M question

As for the append idea - you could do that, but you have to consider that the values will be doubled for the set with custom dimensions (as they are also included in full query). You could work around that by

• filtering the full query to time period before the custom measures were added and appending after that
• using merge instead of append, which will result in adding the dimensions to the full set only where the join criteria is met

[Anonymous]

Thanks,

I managed to make this work with an append. Also learned that M is lazy and smart enough to push time parameters up to the GA queries, so all good! 🙂

Lars-Erik