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

Create custom metric similar to SUMIF

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hi everyone, 

 

I'm using a Data Studio report to show campaign AdWords data, and since I want to group campaigns together I created a new dimension called 'market' using the formula SUBSTR(Campaign, 1, 2). 

 

In my table I now need to show metrics like CTR, CPC, Conversion rate, cost per conversion and search impression share. Unfortunately, at the moment these standard metrics don't work. So I guess I need to calculate new ones creating a custom metric. I'm looking for something similar to a sum if on excel that allows me to for example sum impressions and cliks only when they are from specif campaigns. On excel my CTR formula would be =sumif(market,"UK",clicks)/sumif(market,"UK",impressions).

 

 

I've tried to use a CASE statement, but didn't have much success. Can someone please help?

 

Thank you so much!

Robin

1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author Roberta R
May 2017

Create custom metric similar to SUMIF

Follower ✭ ☆ ☆
# 2
Follower ✭ ☆ ☆

Hi Robin,

 

"Unfortunately, at the moment these standard metrics don't work." - Are you shure?

 

Anyway, I used to group AdWords campaigns by building a Custom Dimension using the CASE Statement, and it's still working.

 

Specific Example: CASE WHEN REGEXP_MATCH(Campaign, "(.*?)DE.*") THEN "DE" WHEN REGEXP_MATCH(Campaign, "(.*?)US.*") THEN "US" ELSE "Other" END

 

I also needed to setup some percent-type custom metrics for the average values for CTR (Clicks/Impressions), Conv.-Rate (Conversions/Interactions) and Cost/Conv. (Cost/Conversions) and then bring it all together.

 

Maybe that works for you, too.


Best regards,

 

Malte

View solution in original post

Marked as Best Answer.
Solution
Accepted by topic author Roberta R
May 2017

Create custom metric similar to SUMIF

Follower ✭ ☆ ☆
# 2
Follower ✭ ☆ ☆

Hi Robin,

 

"Unfortunately, at the moment these standard metrics don't work." - Are you shure?

 

Anyway, I used to group AdWords campaigns by building a Custom Dimension using the CASE Statement, and it's still working.

 

Specific Example: CASE WHEN REGEXP_MATCH(Campaign, "(.*?)DE.*") THEN "DE" WHEN REGEXP_MATCH(Campaign, "(.*?)US.*") THEN "US" ELSE "Other" END

 

I also needed to setup some percent-type custom metrics for the average values for CTR (Clicks/Impressions), Conv.-Rate (Conversions/Interactions) and Cost/Conv. (Cost/Conversions) and then bring it all together.

 

Maybe that works for you, too.


Best regards,

 

Malte

Create custom metric similar to SUMIF

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Hi Malte,

 

Thanks a lot for your reply!

 

Yes, the standard CTR, CPC and conversion rate don't work based on the custom dimension I created. The table greys out and gives the error "Unable to aggregate ratio metrics in the request. Please select another metric. Error ID: 21756fdb".

 

Thanks for the example of the CASE Statement. I've tried to apply it to my table, but I wasn't too lucky. Could you please share the formulas of the percent-type custom metrics you created? Perhaps if I see those, I can find a way to adapt them to my table.

 

Thanks again,

Robin 

Create custom metric similar to SUMIF

Follower ✭ ☆ ☆
# 4
Follower ✭ ☆ ☆

Hi Robin, 

 

I just did that already ;-) -> CTR (Clicks/Impressions), Conv.-Rate (Conversions/Interactions) and Cost/Conv. (Cost/Conversions)

 

Best regards, 

Malte

Create custom metric similar to SUMIF

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

Thanks Malte, 

 

but how did you make sure that for example the clicks and impressions in your CTR formula where the ones with 'DE' in the campaign name? Shouldn't there be another formula that aggregates the metrics you need to find out CTR from?

 

Best regards,

Robin

Create custom metric similar to SUMIF

Follower ✭ ☆ ☆
# 6
Follower ✭ ☆ ☆

Hi Robin,

 

yes, it seems to be a little bit too simple, but I compared it to the corresponding data in the Google AdWords Interface and it's equal. Looks like the Custom Dimension is "doing the math".

 

Best regards,

Malte 

Create custom metric similar to SUMIF

Visitor ✭ ✭ ✭
# 7
Visitor ✭ ✭ ✭

I got what you mean. It does seems too simple!

 

I finally managed to do the same. And it works. Smiley Happy

 

Thanks a lot, 

Robin

Create custom metric similar to SUMIF

Visitor ✭ ✭ ✭
# 8
Visitor ✭ ✭ ✭

Hi Malte,

 

maybe you can help to me also Smiley Happy

 

I need this excel formula: sumifs(sales;year;YYYY;month;MM).