Google Data Studio with BigQuery Data Source Issue in Calculated Fields and Aggregation

1
votes

I have a Google Data Studio dashboard that loads really slowly since it's using Google Sheets as a Data Source. I migrated the same data to BigQuery then used it as my new Data Source however, I came across an issue:

When creating a calculated field, the new calculated field is not tagged as Auto in the Default Aggregation I still have to select Sum as a Default Aggregation. This causes problems in my report. Also, it's not Blue, where normal fields are shown as green, and calculated fields are shown as Blue.

When I was using Google Sheets, I could do direct computations in the calculated fields.

Example:

Handle Time = Talk Time / Number of calls

I just create a calculated field called Handle Time, then put the formula Talk Time / Number of calls

Now, I need to create 3 separate Calculated Fields:

Calculated Field 1: SUM(Talk Time)

Calculated Field 2: SUM(Number of calls)

Calculated Field 3: Calculated Field 1 / Calculated Field 2

This is even though I already tagged them as Sum in the Default Aggregation. Can anyone help me understand what I'm doing wrong?

2
google-data-studio

2 Answers

2
votes

Solution:
A single calculated field will do the trick; the aggregation of each respective field needs to be stated explicitly in the calculated field:

SUM(Talk Time) / SUM(Number of calls)

Why the Change?
To elaborate, the change was part of the Data Modeling update on 31st October 2020; one of the benefits of explicitly stating the aggregation is that it offers greater flexibility with the ability to aggregate fields as required when creating a calculated field, for example, something like:
MAX(Talk Time) - MIN(Talk Time) / COUNT(Handle Time) * AVG(Handle Time) / COUNT_DISTINCT(Text_Field1) * COUNT(Text_Field2)

Speed
Regarding speed, where the Data Set is large and static (daily updates are fine and real time data is not required), then a Data Extract would be a good option.

-1
votes

Dimensions are shown as green, metrics are shown as blue. Data imported from other sources, particularly from Google sheets tend to show metrics as green but when you add them to a chart or table they get recognised as metrics and change to blue.