count distinct of text field in data studio increased when using filter

Question

I want to count the number of sessions with a certain event label in google data studio. I have created a new field in data studio on a google analytics source like this:

COUNT_DISTINCT(CASE WHEN Event Label = "Form Start" THEN Session ID ELSE "" END)

where Session ID is a custom dimension from GA (string). The problem is that when I for example pull this new metric to a scorecard, I get a value of 6, if I then add a filter on this scorecard with Event Label = "Form Start" (the exact same event label as in the case statement of the new field) the metric is increased to 23! (which is the correct number).

Is there some data truncation going on in data studio behind the scenes or why does using the filter increase the distinct count?

Answer 1

The weird numbers you're seeing could be due to sampling. At the bottom of the report in "view" mode, it should indicate if the numbers are sampled or not.

Also, the Unique Events metric should tell you the number of times a specific event happened per session. You might not need to do all that custom work in data studio, just a filter for the label.

Answer 2

I might be missing something that requires the COUNT_DISTINCT function, but would a simpler, different formula work?

CASE
  WHEN Event Label = "Form Start" THEN 1
  ELSE 0
END

This would create a number field that that can be used in the metric element of a Scorecard with multiple aggregation options? The key option being SUM :)

Answer 3

I had a similar problem I think, where I was trying to tally all the pages with a certain category in the meta:

CASE
WHEN REGEXP_MATCH(idio:industry, '.*Agriculture.*') THEN "Agriculture"
else "Others"
END

In your case, I think you would use this:

CASE
WHEN REGEXP_MATCH(Event Label, '.*Form Start.*') THEN Session ID
else "Others"
END