Google Data Studio: Creating Ranking Dimension

Question

I would like to create a ranking dimension in one of the Google Data Studio reports.

The data source for the report is [GSC Site] which has information on [Query] (a dimension) and [Average Ranking] (a metric). Trying to use the CASE formula to create a new field called "Ranking" as follows:

CASE
    WHEN Average Position >= 0 AND Average Position <= 5 THEN "Top 5"
    WHEN Average Position > 5 AND Average Position <= 10 THEN "Top 10"
    ELSE "Not Ranked"
END

and get the message "Only numeric literals are allowed if aggregated fields are used in CASE."

As I understand, the field "Average Ranking" is metric with default aggregation "Auto" which prevents me from using the CASE formula.

What would be a possible solution where a new field is created as (i.e. dimension) with default aggregation "None" and populated with the above-ranking distribution values (Top 5, Top 10 and Not Ranked).

Answer 1

I was trying to do the same thing, and the solution I came up with was to blend GSC data with itself. With a blend, you don't have the same auto-aggregation limitation if you add a custom field to a specific table or chart. Here is what the blend looks like:

Then use the blend as the datasource for your chart, and click to add a dimension and select Create Field. My custom field used a case statement almost identical to the one in your question.