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.7K members online now
2.7K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Trying to put contition on value of a data

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Here is my issue,

 

I have a source which contains calls, one line for each.

Depending on the duration, the billing has to be different.

 

I am trying to get a report where the billing displays like this:

 

- over 6 minutes calls                        XXXX€

- under 6 minutes calls                      XXXX€

 

But I can't find the correct way to play with dimension and metrics directly in DataStudio.

I don't want to put this information in my source, because depending on the report, times and values are not the same.

 

Thanks for the help

 

Regards

 

Pierre Damien 

1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author Pierre-Damien D
July 2017

Trying to put contition on value of a data

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Hi Thanks for you reply!

 

These data are from Big Query.

As I was saying earlier, I don't want to touch my source. As a matter of fact, this source contains data for multiple companies.

Each one will get its own DataStudio report, and rules are never the same from a company to another. 

 

I just found a solution, it is quite tricky but effective:

 - First create a dimension "time_cat" based on the duration (CASE WHEN duration>360 then "+6min" ELSE "-6min" END)

 - Then create a metric "val_cat" containing the value and set it to average (CASE WHEN duration>360 then 14 ELSE 7 END)

 - Then create a second metric with this formula COUNT(time_cat)*val_cat

 

And it will do the job. This way you won't have to create a column per rule in your source.

 

Regards

View solution in original post

Trying to put contition on value of a data

Participant ✭ ✭ ☆
# 2
Participant ✭ ✭ ☆

Where is your data coming from?

 

If it's coming from Google Sheets, then you can add a column with a formula which categorizes the call as over/under 6 minutes, and use that to create the cost in another column, then import those into Data Studio. 

 

If it's coming from Google Analytics (or another direct link), then you likely need a CASE statement. I'm not well versed in those, so if that's what you need, perhaps someone else can chime in. 

Marked as Best Answer.
Solution
Accepted by topic author Pierre-Damien D
July 2017

Trying to put contition on value of a data

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Hi Thanks for you reply!

 

These data are from Big Query.

As I was saying earlier, I don't want to touch my source. As a matter of fact, this source contains data for multiple companies.

Each one will get its own DataStudio report, and rules are never the same from a company to another. 

 

I just found a solution, it is quite tricky but effective:

 - First create a dimension "time_cat" based on the duration (CASE WHEN duration>360 then "+6min" ELSE "-6min" END)

 - Then create a metric "val_cat" containing the value and set it to average (CASE WHEN duration>360 then 14 ELSE 7 END)

 - Then create a second metric with this formula COUNT(time_cat)*val_cat

 

And it will do the job. This way you won't have to create a column per rule in your source.

 

Regards