Create Revenue Targets in Google Data Studio using CASE statements

Create Revenue Targets in Google Data Studio using CASE statements

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.

Two columns showing Product Category enhanced ecommerce and product revenue

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.

A case statement in google data studio

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.

changing the field type to currency in google data studio

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.

changed filed type to currency in data source section

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.

Two columns showing Product Category enhanced ecommerce and product revenue and now the 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.

a calculated field in google data studio showing the difference between actual and target amounts

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

a data tab in data studio

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

Two columns showing Product Category enhanced ecommerce and product revenue and difference from target in google data studio

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.

conditional formatting in google data studio

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.

a table in google data studio showing ecommerce product revenue , target and difference from target

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.

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.

I’m Michael


Iā€™m a digital analyst with seven years experience in digital marketing. I’m skilled in web analytics, tag management and data visualization.

Get in touch with me!
LinkedIn
Twitter
Email

I'm Michael
Michael

Author Michael

More posts by Michael

Join the discussion 8 Comments

  • John says:

    Hey Michael
    awesome post! What about if the monthly targets differ or add up? Is there a possibility in GDS?

  • Giannis says:

    Great post Michael.

    The question is if GDS allows you to create dynamic targets based on year over year performance of a Product Category or even a Brand.

    For example if the Product Category is Apparel the Target Revenue to be calculated as 2018ā€™s product revenue * 1,2.

    • Michael says:

      Great question Giannis! I’ve thought about that but haven’t taken the time to work out how to calculate that. Will have a think and see if it’s possible to do in a future post. šŸ™‚

  • Julian says:

    Great stuff Michael. Will share in my newsletter.
    Cheers,
    Julian

    • Michael says:

      Awesome- thank you! Coming from you that is big praise. I always watch your videos and have learnt so much from you over the years. šŸ™‚

  • Kia Street says:

    WOW simple & easy plus majorly effective? What a smart way to analyze pacing to targets! Thanks for the tips šŸ”„

    BTW glad you enjoyed my Data Studio post on the Seer Interactive blog! Just released another one with more examples of custom fields here: https://seer.is/2PbQJFO. Let me know what ya think!

    • Michael says:

      Thanks so much Kia! šŸ™‚
      Your new article looks great! Think it will be super useful for me (and lots of other people!)
      Especially like the Removing Trailing Slashes section. Had been trying to figure out something similar a while back.

Leave a Reply