Google Analytics

[Anonymous]

Hello,

I am trying to connect Power BI to Google Analytics. I have the connection set up and can bring through tables but I am having trouble finding a good Primary key and foreign key to connect the tables. I am currently using Day, Hour, and Minute (the concatenated version that Google supplies) however this seems to be no good. When I am in Google Analytics I can get to the client ID however when I am trying to load the information to Power BI I cannot find client ID. Can anyone help me to either find the client ID or help me find another key so I can get to exploring my data? 

P.S. I am using the free version of Google Analytics. 

[Anonymous]

Hi @Anonymous ,

It is hard to use single date/time dimension as relationship keys to link different tables.
In my opinion, I'd like to suggest you to add a calendar table to link these tables based on date fields.

For time part, you can extract hour part to build relationships.

Regards,

Xiaoxin Sheng

[Anonymous]

Hello Xiaoxin Sheng,

I am a little confused. Would you mind clarifiying on how you could pull off that relationship with a calendar table?

Thanks!

[Anonymous]

Hi @Anonymous ,

Actually, if you try to create relationship based on date/time, it means you need a whole datetime table, it existed huge amount of records.(time table include 'hour', 'minute', 'second' dimension has 86400 row per day) 

So I suggest you to add calculated columns to these tables to store date part and time part. Then you can create two relationships based on date dimension and time dimension.

Regards,

Xiaoxin Sheng

[Anonymous]

The only flaw I can see is that Google Analytics does not allow me to pull in seconds. I can only pull in Hour and Minute. Do you have any other ideas on how to connect using the Client ID? Right now Google Analytics does not allow me to pull it in but I am wondering if anyone has found a better way to connect their data.

Also, if we only use Hour and Minute we get duplicate values when looking at our primary key.