AdWords is now Google Ads. Our new name reflects the full range of advertising options we offer across Search, Display, YouTube, and more. Learn more

Data Studio
2.8K members online now
2.8K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Does Data Studio support SQL in new calculated field?

Follower ✭ ✭ ☆
# 1
Follower ✭ ✭ ☆

Hi,

From what I read here, looks like Data Studio can use SQL in calculated fields:

 

http://stackoverflow.com/questions/40309610/how-to-create-a-new-metric-in-google-data-studio-with-co...

 

But I can make it work. I always get the "Could not parse formula". So, is SQL supported here? I want to exclude some values from a metric field.

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author Endal
November 2016

Does Data Studio support SQL in new calculated field?

Explorer ✭ ✭ ✭
# 2
Explorer ✭ ✭ ✭

You can create custom metrics or dimensions with SQL-esque formulas. You cannot do everything that you can with SQL however. There are really just a handful of formulas that Data Studio supports at this time. 

If you can post your formula or explain what you are trying to do, I might be able to help you write the formula you need...or someone else around here probably can if it's out of my league. 

View solution in original post

Marked as Best Answer.
Solution
Accepted by topic author Endal
November 2016

Does Data Studio support SQL in new calculated field?

Explorer ✭ ✭ ✭
# 2
Explorer ✭ ✭ ✭

You can create custom metrics or dimensions with SQL-esque formulas. You cannot do everything that you can with SQL however. There are really just a handful of formulas that Data Studio supports at this time. 

If you can post your formula or explain what you are trying to do, I might be able to help you write the formula you need...or someone else around here probably can if it's out of my league. 

Does Data Studio support SQL in new calculated field?

Follower ✭ ✭ ☆
# 3
Follower ✭ ✭ ☆

Thanks! I have a field named "Average position" and value range is from 0 to 1000. I want a calculated field that excludes the 0, because average is not accurate when this value equals 0

Does Data Studio support SQL in new calculated field?

[ Edited ]
Explorer ✭ ✭ ✭
# 4
Explorer ✭ ✭ ✭

That's a tough one because Data Studio does not really support the filtering of metrics yet. If you can filter out dimensions with Average Position = 0, however, that may work as an alternative route. You could, for instance, try creating a custom dimension called "Average Position is 0" and the formula:

 

CASE WHEN Average Position = 0 Then TRUE ELSE FALSE END

 

Then you create your score card (or chart or whatever) and apply a filter (located at the bottom of the settings in the Data tab of sidebar menu).

 

Filter settings:
exclude
Average Position is 0
equals
TRUE

 

I think this solution will give you the results you are looking for but I have not tested it so you may need to tweak it a bit if it doesn't work at first.

 

Just remember to use the manual refresh button after making any updates to the data or you may think something is not working when it actually is

Does Data Studio support SQL in new calculated field?

Follower ✭ ✭ ☆
# 5
Follower ✭ ✭ ☆

Thanks, but when I try to create the custom dimension I got:

 

"CASE statements cannot be used with metrics."

Does Data Studio support SQL in new calculated field?

Explorer ✭ ✭ ✭
# 6
Explorer ✭ ✭ ✭

I was worried that might happen. Data Studio's examples for CASE formulas say that metrics can be used but I have not been able to use metrics in a CASE formula successfully in practice. For this reason, I'm not sure if there is a bug or if my formulas need to be tweaking. If we can create a custom dimension for the metric(s) we want or don't want, then it should be easy to add filters for those variables. 

So I will have to try some things out to find the correct answer or maybe someone else in the forum knows the answer, but you might be able to figure it before then if you can just figure out how to create a custom dimension that identifies avg position = 0. Otherwise, we may have to wait for the devs make it possible for us to filter metrics more easily. 

Does Data Studio support SQL in new calculated field?

Follower ✭ ✭ ☆
# 7
Follower ✭ ✭ ☆

Thanks, anyway :-)