Retrieve data from Google Analytics by calculating a dimension

[Giuseppe89]

Hi all,

In PowerBI Desktop I would like to retrieve some web traffic data from Google Analytics (GA4).

The data I need have the following structure:

YearMonth	Category	TotalUsers
202401	A	10
202401	B	7
202402	A	9

where the dimension Category is calculated from the GA dimension page path, more precisely: if the user's page path contains the substring category_a, then Category = A (the same for the category B).

In order to retrieve these data I have tried the GA connector, but it does not seem possibile to calculate the dimension Category querying directily Google Analytics.

Does anyone have any ideas to solve the issue?

Is it possible to retrieve GA4 data using the PowerBI connector WEB (executing some GET/POST calls) and defining the field I need?

Note:

• I don't want to load all GA4 data in PowerBI because I have >10M data

Thanks in advance

[ReportGuru]

Hi @Giuseppe89 as a workaround, maybe you can try to test your connection with a 3rd party tool, which pulls data directly from the API and would not require any coding on your side. I've tried windsor.ai, supemetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the GA4 connector in the data sources list:

After that, just grant access to your GA4 account using your credentials, then on preview and destination page you will see a preview of your GA4 fields:

There just select the fields you need. Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url. 

[v-zhengdxu-msft · Accepted solution]

Hi @Giuseppe89 

If you don't want to import your data. 
You can use the REST API in Power Query, which is a viable way to do it at the moment:

Google Analytics Data API  |  Google for Developers

You can use the API to get data from data source and write mcode to get the output data you want.

Here is a sample for your reference, and this code needs to be modified:

let
    Source = Web.Page(Web.Contents("YOUR_API_ENDPOINT", [Headers=[Authorization="Bearer YOUR_ACCESS_TOKEN"]])),
    Data = Source{0}[content],
    JsonData = Json.Document(Data),
    #"Converted to Table" = Record.ToTable(JsonData),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Category", each if Text.Contains([PagePath], "category_a") then "A" else if Text.Contains([PagePath], "category_b") then "B" else "Other"),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"YearMonth", "Category"}, {{"TotalUsers", each List.Sum([Users]), type number}})
in
    #"Grouped Rows"

Here for your reference:

javascript - Google Analytics Reporting API V4, User does not have sufficient permissions for this p...

The above is an implementation that requires you to have a deep understanding of Power Query M code and understand the relevant knowledge of API.

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.