Google Analytics Multiple Site and Power BI

Today i have use for the first time Google Analytics Connector to extract Some info about different sites of the company i work. It’w very simple to use it, we have two different solution:

  1. Connect direct online with power bi
  2. Use Power Bi Desktop

In both cases we have to insert our Google Credential that we use on Google Analytics. In the first situation power bi in automatic create some Reports and Dashboard base on Google Analytics:

Immagine 128.png

But if we use this Connector we can only see information about one single Site. I want to compare the different sites. We can use power bi desktop.

Unfortunately If we use the connector we notice that Google analytics not create a simple table like CRM Online or Excel connector. As you can see it’s like a multidimensional cube.

Immagine 129.png

We can select only one Site and :

  • Only 8 attribute dimensions
  • Only 10 different Measures

We have to create different table and dataset bu it works. Now the problem is tha same, and if i have Different sites and i want to compare them?

No problem we can use power bi and in particular power Query to resolve this problem.

The first operation is create your google analytics query, it’s very simple.

  1. Open new power bi desktop blank report
  2. Select as data source google analytics
  3. Select one of your site (it’s indifferent what you select)
  4. Select attribute and measure that you want to analyze and import data.

After this first steps if you go to Modify query –> visualize –> advance editor you have this result ( or similar)

let

Source = GoogleAnalytics.Accounts(),

#”55130307″ = Source{[Id=”46372169″]}[Data],

#”UA-55130307-1″ = #”55130307″{[Id=”UA-55130307-1″]}[Data],

#”91607070″ = #”UA-55130307-1″{[Id=”91607070″]}[Data],

#”Added Items” = Cube.Transform(#”91607070″, {{Cube.AddAndExpandDimensionColumn, “ga:city”, {“ga:city”}, {“City”}}, {Cube.AddAndExpandDimensionColumn, “ga:country”, {“ga:country”}, {“Country”}}, {Cube.AddAndExpandDimensionColumn, “ga:region”, {“ga:region”}, {“Region”}}, {Cube.AddAndExpandDimensionColumn, “ga:pageTitle”, {“ga:pageTitle”}, {“Page Title”}}, {Cube.AddAndExpandDimensionColumn, “ga:browser”, {“ga:browser”}, {“Browser”}}, {Cube.AddAndExpandDimensionColumn, “ga:deviceCategory”, {“ga:deviceCategory”}, {“Device Category”}}, {Cube.AddAndExpandDimensionColumn, “ga:date”, {“ga:date”}, {“Date”}}, {Cube.AddMeasureColumn, “Avg. Time on Page”, “ga:avgTimeOnPage”}, {Cube.AddMeasureColumn, “Exits”, “ga:exits”}, {Cube.AddMeasureColumn, “Page Value”, “ga:pageValue”}, {Cube.AddMeasureColumn, “Pages / Session”, “ga:pageviewsPerSession”}, {Cube.AddMeasureColumn, “Pageviews”, “ga:pageviews”}, {Cube.AddMeasureColumn, “Time on Page”, “ga:timeOnPage”}, {Cube.AddMeasureColumn, “Unique Pageviews”, “ga:uniquePageviews”}, {Cube.AddMeasureColumn, “New Users”, “ga:newUsers”}, {Cube.AddMeasureColumn, “Users”, “ga:users”}})

in

#”Added Items”

Added items is not important for use in this moment, the query and structure can be different but we focus on this 3 lines:

#”55130307″ = Source{[Id=”55130307″]}[Data],

#”UA-55130307-1″ = #”55130307″{[Id=”UA-55130307-1″]}[Data],

#”91607070″ = #”UA-55130307-1″{[Id=”91607070″]}[Data],

We can have different values , in fact To connect to a specific site we need three different ID. It’s not important the first part #”55130307″ because it’s only the name of a specific passage on power by the important part is the 3 number in after the ID=””. We have to create a table that contain these 3 ids for all our sites

How can we can recover this info?

  • First passage is go to home tab and select Modify Query (modifica query in the image) :

immagine-023

 

Create a blank query from button import data. IN the new window go to Visualize Tab and select adnvance editor:

immagine-024

 

And insert this query:

let
Source = GoogleAnalytics.Accounts()
in
Source

The result is the complete list of Your sites:

Immagine 130

If we Expand the column Data 2 Times we can found all the information. You can also use this query to have the direct result:

let
Source = GoogleAnalytics.Accounts(),
#”Expanded Data” = Table.ExpandTableColumn(Source, “Data”, {“Data”, “Id”, “Kind”, “Name”}, {“Data.Data”, “Data.Id”, “Data.Kind”, “Data.Name”}),
#”Expanded Data.Data” = Table.ExpandTableColumn(#”Expanded Data”, “Data.Data”, {“Id”, “Name”, “Data”, “Kind”}, {“Data.Data.Id”, “Data.Data.Name”, “Data.Data.Data”, “Data.Data.Kind”}),
#”Removed Columns” = Table.RemoveColumns(#”Expanded Data.Data”,{“Data.Data.Name”, “Data.Data.Data”, “Data.Data.Kind”, “Data.Kind”, “Kind”, “Data.Name”}),
#”Renamed Columns” = Table.RenameColumns(#”Removed Columns”,{{“Data.Id”, “2ID”}, {“Data.Data.Id”, “3ID”}})

in
#”Renamed Columns”

You have to obtain a table with this structure:

Immagine 131

Now we have all the information that we need. now we have to come back to the first query, the query with the result that we have created using import data from google analytics. We have to convert it in a function like that:

let Multiple =(id1 as text,id2 as text,id3 as text)=>
let
Source = GoogleAnalytics.Accounts(),
#”55130307″ = Source{[Id=id1]}[Data],
#”UA-55130307-1″ = #”55130307″{[Id=id2]}[Data],
#”91607070″ = #”UA-55130307-1″{[Id=id3]}[Data],
#”Added Items” = Cube.Transform(#”91607070″, {{Cube.AddAndExpandDimensionColumn, “ga:city”, {“ga:city”}, {“City”}}, {Cube.AddAndExpandDimensionColumn, “ga:country”, {“ga:country”}, {“Country”}}, {Cube.AddAndExpandDimensionColumn, “ga:region”, {“ga:region”}, {“Region”}}, {Cube.AddAndExpandDimensionColumn, “ga:pageTitle”, {“ga:pageTitle”}, {“Page Title”}}, {Cube.AddAndExpandDimensionColumn, “ga:browser”, {“ga:browser”}, {“Browser”}}, {Cube.AddAndExpandDimensionColumn, “ga:deviceCategory”, {“ga:deviceCategory”}, {“Device Category”}}, {Cube.AddAndExpandDimensionColumn, “ga:date”, {“ga:date”}, {“Date”}}, {Cube.AddMeasureColumn, “Avg. Time on Page”, “ga:avgTimeOnPage”}, {Cube.AddMeasureColumn, “Exits”, “ga:exits”}, {Cube.AddMeasureColumn, “Page Value”, “ga:pageValue”}, {Cube.AddMeasureColumn, “Pages / Session”, “ga:pageviewsPerSession”}, {Cube.AddMeasureColumn, “Pageviews”, “ga:pageviews”}, {Cube.AddMeasureColumn, “Time on Page”, “ga:timeOnPage”}, {Cube.AddMeasureColumn, “Unique Pageviews”, “ga:uniquePageviews”}, {Cube.AddMeasureColumn, “New Users”, “ga:newUsers”}, {Cube.AddMeasureColumn, “Users”, “ga:users”}})
in
#”Added Items”
in Multiple
To do that you have to:
  1. Select the query
  2. Go to visualize tab and select advance editor
  3. Open the query and insert before  let

let Multiple =(id1 as text,id2 as text,id3 as text)=>

and add after all the query

in Multiple

Replace the id Number that we have seen before with the 3 differents a parameters. ( the value in bold). Save and close. I

if you have done all the passages correctly you should have converted the table in a function. If you want you can try it and insert the 3 ids of your other table (the query with sites list) and execute the function. The query show your statistics about your site.

Now go to your list of Sites Query
you have to create a new column :
=NameofYouFunction([Id],[2ID],[3ID])

NameofYouFunction is the name of your other query. The query that you have converted in a function. you have to write every name exactly as you have written in your other query. Expand the new column and now you have all the info for all the different sites that you can monitor. Here the result:

Immagine 132.png

 

 

 

 

Annunci

8 pensieri su “Google Analytics Multiple Site and Power BI

  1. Hi,

    Thanks for this when I try to replicate it though on the third step I get this error.

    Expression.Error: We cannot convert a value of type Table to type Function.
    Details:
    Value=Table
    Type=Type

    Thanks

    Mi piace

  2. How do I run the scripts? ‘Advanced editor’ can hold just one script. Do I have to use R?
    I can expand the collumns manually thought.

    But then, how to get dates en months? I got a list of websites like your screenshot but want to make a dashboard with visitors per month per site etc..

    Thanks!

    Mi piace

      • I see the problem was the quote signs didn’t copy ok.

        Now the last step gives error after I put in the 3 id’s (id1,id2,id3):

        An error occurred in the ‘Invoked Function (3)’ query. Expression.Error: The key didn’t match any rows in the table.
        Details:
        Key=Record
        Table=Table

        Hope you can help me!

        Mi piace

  3. No luck…still error. I use this function now:

    let Multiple =(id1 as text,id2 as text,id3 as text)=>
    let
    Source = GoogleAnalytics.Accounts(),
    #”11733962″ = Source{[aaa=id1]}[Data],
    #”UA-11733962-1″ = #”11733962″{[bbb=id2]}[Data],
    #”23741817″ = #”UA-11733962-1″{[ccc=id3]}[Data],
    #”Added Items” = Cube.Transform(#”23741817″, {{Cube.AddAndExpandDimensionColumn, “ga:city”, {“ga:city”}, {“City”}}, {Cube.AddAndExpandDimensionColumn, “ga:country”, {“ga:country”}, {“Country”}}, {Cube.AddAndExpandDimensionColumn, “ga:region”, {“ga:region”}, {“Region”}}, {Cube.AddAndExpandDimensionColumn, “ga:pageTitle”, {“ga:pageTitle”}, {“Page Title”}}, {Cube.AddAndExpandDimensionColumn, “ga:browser”, {“ga:browser”}, {“Browser”}}, {Cube.AddAndExpandDimensionColumn, “ga:deviceCategory”, {“ga:deviceCategory”}, {“Device Category”}}, {Cube.AddAndExpandDimensionColumn, “ga:date”, {“ga:date”}, {“Date”}}, {Cube.AddMeasureColumn, “Avg. Time on Page”, “ga:avgTimeOnPage”}, {Cube.AddMeasureColumn, “Exits”, “ga:exits”}, {Cube.AddMeasureColumn, “Page Value”, “ga:pageValue”}, {Cube.AddMeasureColumn, “Pages / Session”, “ga:pageviewsPerSession”}, {Cube.AddMeasureColumn, “Pageviews”, “ga:pageviews”}, {Cube.AddMeasureColumn, “Time on Page”, “ga:timeOnPage”}, {Cube.AddMeasureColumn, “Unique Pageviews”, “ga:uniquePageviews”}, {Cube.AddMeasureColumn, “New Users”, “ga:newUsers”}, {Cube.AddMeasureColumn, “Users”, “ga:users”}})
    in
    #”Added Items”
    in Multiple

    Gives error:

    An error occurred in the ‘Websites’ query. Expression.Error: The key didn’t match any rows in the table.
    Details:
    Key=Record
    Table=Table

    Mi piace

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...