I had been looking for a way to create Revenue Targets in Google Data Studio, and tested out a method using CASE statements that works. So thought I’d share it!

So in this example I’m using the Enhanced Ecommerce data from the Google Merchandise Store demo Google Analytics account.

So let’s take a look at the Product Revenue for each of the Product Categories over the past month.

Now let’s say we want to set some targets for each of these categories.

We want to set the following revenue targets for our categories;

Apparel = 2000
Accessories, Uncategorized Items, New, Office = 300
Drinkware, Writing Instruments, Stationery, Clearance, Lifestyle = 100
Small Goods, Shop by Brand, Google,Bags ,(not set), Fun, Campus Collection = 50

To do this we write a CASE statement. CASE statements are really useful, and I’ll link to some interesting articles about them at the end of this post.

1. Create the Target Revenue CASE statement

To create a CASE statement in Data Studio, click Resources > Manage added data sources.

When you see the data source you want to edit, simply click Edit.

Then click “ADD A FIELD” in the top right hand corner of the screen.

We then create a new dimension using a CASE statement, as shown below.

CASE
WHEN Product Category (Enhanced Ecommerce) IN ("Apparel")THEN "2000" 
WHEN Product Category (Enhanced Ecommerce) IN ("Accessories","Uncategorized Items","New","Office")THEN "300"
WHEN Product Category (Enhanced Ecommerce) IN ("Drinkware","Writing Instruments","Stationery","Clearance","Lifestyle")THEN "100"
WHEN Product Category (Enhanced Ecommerce) IN ("Small Goods","Shop by Brand","Google","Bags","(not set)","Fun","Campus Collection")THEN "50"
ELSE "0"
END

Here is what your CASE statement should look like once it’s done.

2. Change the new Field’s Type to Currency

Once we’ve created our new field using a CASE statement, the default Type will be “Text”. We need to change this to Currency.

Choose whichever currency is appropriate for you from the list provided. In this case I’ve used USD because that is the currency the Google Merchandise Store’s Product Revenue is listed in.

You must also change the ‘Default Aggregation’ to “Sum” before we can use it in our dashboard.

3. Add the Target Revenue into the table with your Product Revenue

Now that we’ve created our Target Revenue field, and changed the Type to Currency and the default aggregation to Sum we can add it to our Data Studio report.

We now have two columns in our table; Product Revenue from Google Analytics and our “Target – Product Revenue” data we’ve just created.

We can see each of our Product Categories has an amount of Product Revenue and a Target.

The next step is to create a column displaying the difference between the target revenue and the actual revenue.

4. Create a Custom Field showing the Difference from the Target

So to create a column showing the difference between target revenue and actual revenue, we need to create a Custom Field.

To do this you can write the following formula;

SUM(Product Revenue) – SUM(Target – Product Revenue)

We can also select the type as Currency.

Notice that it displays an alert saying that it is an Invalid formula.

In our Data tab for the Table, it also displays the field in red, indicating that there is a problem with it.

But nevertheless, despite the warnings it seems to work just fine!

So now we have our column with our new calculated field showing the difference between the target and actual revenue. But it’s a bit difficult to see which categories achieved their targets, and which did not. So it’s time to add in some Conditional Formatting!

5. Adding Conditional Formatting to see which Categories achieved their Targets

To highlight which categories achieved their targets, and which did not, we are going to add some Conditional Formatting rules to the table.

To do this, open the Style tab of your table and click on “Add” under Conditional Formatting.

The first rule sets any value greater or equal to zero as Green. The second rule sets any value lower than zero to pink.

We now have our table with the Difference from Target column displaying green if the Product Revenue exceeded the target or red if it did not.

And below we can look at the final report!

6. Viewing the final Google Data Studio report

Below we have the final Google Data Studio report. I’ve also added in a bar chart showing the amount that categories exceeded or missed their targets.

Useful Articles about Targets, CASE Statements, and Calculated Fields in Data Studio

I wanted to share some other great articles from other websites that will be helpful if you’re figuring out how to use Data Studio to achieve your goals.

• How to create CASE statements in Google Data Studio – Seer Interactive
• Reporting Against Targets in Google Data Studio – Data Runs Deep
• Setting Monthly Targets in Google Data Studio – Data Runs Deep
• 5 Calculated Fields for Google Data Studio – ClickInsight
• 5 More Calculated Fields for Data Studio – ClickInsight

My Other Blog Posts

If you found this post about creating Revenue Targets in Google Data Studio useful, you might enjoy my other blog posts.