Different behavior of Google Analytics and BigQuery data sources

[VincentMazet]

Hi, I'm looking for help using custom metrics with the SUM() function and a data source connected to Google Analytics.

I don't understand why my formula returns 1 and not the sum of all events named “screen_view” : SUM(IF(Event name=“screen_view”, 1, 0))

My issue is that, when I use a BigQuery data source with the same function, I get the right result :

Is there some way I can make it work with a Google Analytics Data source?

[Mehdi_Oudjida · Accepted solution]

Bonjour @VincentMazet Vincent,

It is one of the differences between nice raw and not aggregated data from BigQuery and aggregated data from an API like GA4 one.

If you need to apply the same logic, you have to un-aggregate data with fake blending with only 1 table (no real blending).

- Create a blending with one table including Event Name and Event Count
- Use this blending as the data source of your chart
- Apply the formula looking like that SUM(IF(Event Name = 'screen_view' , Event Count, 0)) on the chart level

Thanks to the blending, You will see the field "Event Count" becoming green, and no more blue, so without aggregation, and the formula will work. In this case, the blending helped you also to create a data set with rows and separate event names, contrary to a scorecard asking just 1 row with one already summed metric.

I hope it helps.