You might notice me using the term Formula a lot.... I only later on noticed it's actually called Functions in Data Studio... oh well! Enjoy the video and let me know which functions you use often. 0:00 - Introduction 1:05 - Create a New Customized Field 1:40 - #1 LOWER/UPPER Function 3:05 - #2 CONCAT Function 4:35 - #3 REGEX_EXTRACT Function 6:05 - #4 REGEX_MATCH Function 9:30 - #5 CASE Function 13:20 - Summary
For search term extraction at around the 6 minutes mark consider q=([^&]+) - this should prevent capturing extra query parameters' values after the q, e.g. in q=xxx&, xxx will be captured, but & will be ignored. For What/When extraction around 7:15, consider the regex \\b(what|when|etc...)\\b - the \\b part says 'word limit', so 'when' is a match, but 'whenever' is not. Remember that calculated fields in Data Studio use two slashes as the escape character.
This is great. Thanks for creating and posting it. One use case comes to mind for us: calculate branded searches out of Google Search Console data. Maybe even filter and separate them to show the power of SEO on non-branded searches.
is there a way to use IF or CASE as a filter. Lets say a score card with conversion that only shows the conversion when the event name = form_submit? I know I can use regular filters but sometimes it would make things much easier to apply filter directly to the metric.
But when you cancel the transformed variable how you can set an aggregate function for the metric variable? I don't understand what datastudio does in default, average? min? max?choose a random value?
Thanks for the video, Can you suggest how to apply a filter in Average Position(GSC). I used CASA when Calculated Field but every time its showing an error.
Looking for function to remove text before and after a character....example: Trigger-EM|AbandonCategoryBrowse-PartMakeModelYear - the output should be 'AbandonCategoryBrowse', so everything before (and including) | and - - removed.
Great video!!! Much of what you have explained helped me immediately, but I have this issue that is giving me gray hair. I have two tables that come from the same field, but each of them has a different filter, one is by date of entry (sum all that came in on the day) and the other by date of exit(sum all that left work on the day), how do I do the operation so that I add both the amounts of both tables to have the total of inputs and outputs in a single report? thanks for your help
Hey Julian - I want to remove the Values of one field from another if their values are similar, How to put that logic. Example: My Event category has button click and Document clicked, and I want if values of document click =button click, true then remove or filter those
hi, thank you very much for your videos. Please, I see if you can help me. I don't want DataStudio to round values, coming from Bigquery, please, as I do for datastudio not to round values after the comma
I want to create a score card for number of rejections. The data has rejections as a column . Where there are no rejections, it's mentioned as 0. While creating scorecard, it's calculating even the 0 rejections. Can you help
Hi, if we want to pull only parts of data from the String how do we use the extract function ? Like for example I have data like: Tier1|data1 Tier2|data1 Tier1|data2 From the above I want to extract only tiers and copy them to a new calculated dimension .
Hi Julian, I used Google Analytics as a data source to create a Google Data Studio report and found the numbers of same parameter(user, session etc) do not matching any more after applying a (filter) segment. Is this a common problem?
CASE WHEN effective = "January" then 1 WHEN effective = "February" then 2 END (still my ouput is 0) can you help me about this. when month is january then it shows the field 1 and etc...
Do you know in data studio how can I generate a filter with a case, where contain text could be two options and set the same label? I thought something like this but doesn't work CASE WHEN CONTAINS_TEXT(Página de destino, "/blog") THEN "blog" WHEN CONTAINS_TEXT(Página de destino, "(/|/es | /en)") THEN "Home" ELSE "Otros" END
Hi! i´ve got 2 questions: 1. is any way to extract the hostname from an url? I´ve been trying with regexp_extract but it seems not to work..... 2. how could convert "av. time on page" in a range distribution? (So, % within less than 10 seconds, between 10 and 30 seconds, etc...) and then use it as dimension... thanks!
Hello I am trying to calculate an indicator in Data Studio called WMape = (Abs (Sale-Forcast)) (Sale / Sum (Sale)). The problem I have is to want to dynamically weigh the calculation. This weighted is based on the sale. Anyone who can help me with this calculation please !? Thank you!
How to solve the problem the number of transactions for the period with the status 1, 2, 3 ... 5 = A the number of transactions for the period with the status 6, 7, 8 = B A-B = C (A + B) / A = X
Hey Julian I have a question! Do you know how to extract Image Organic Traffic with Google Data Studio?Is there a way to do this? You know that in Google Search Console there is a section which allows you to see the Image Organic Clicks which comes from Image Google Results! And Google Data Studio doesn't have Search Type as dimension.
@@Getfoundmadison I can't extract Image Organic Traffic from Google Analytics! In Google Search Console there is a filter which allows you to see the organic traffic which comes from Image Organic Section from Google Image Results. And I asked Julian if there is a way to extract in Google Data Studio the clicks/impressions from the that section! Thanks for replay!
Hi. I tried to create a calculate field to take only Sessions with Medium = ads. Does anyone has any idea how to do it? I tried this one but doesn't give me the sessions. I don't want the dimensions. I need the metrics. CASE WHEN Mídia = 'ads' THEN 'Facebook Ads' ELSE 'Null' END
CASE WHEN avg time on screen = 2 THEN "good performance" END getting an error "Only numeric literals are allowed if aggregated fields are used in CASE." I don't know what is wrong
Hey Julian, First of all, I am a big fan of you, I have one situation that is related to data studio blending feature. Hope you will solve it. The situation is that I have a blog that has so many posts and I want to see metrics of one category blog posts. For that I have list out post URLs in Google sheets and connect with data studio. All going good, but I don't able to see metrics of dynamic URLs that contains ? mark. Because Data studio only matches non dynamic URLs with Google sheets URLs and showing data. Hope you understand my problem. If not, so I will share screenshots with you through mail.
Hi Julian, Thanks for this video and all previous ones, so interestings. I'm actually trying to analyse datas from multiple choice question survey with Google forms and Google Data Studio. The data obtain are words separate with coma depending how many choices the person has choosen. ex : Construction, Design, Innovation Design Design, Innovation Construction, Design I'm using the REGEXP_MATCH formula to extract the word "Design" and count how many times it appears in the results. After that, I'm using the words count with the formula as data sources for graph. But I'm not able to have the correct regular expression to extract all the data because the word can be located anywhere (beginning, middle or ending) Here is the formula I use, can you help me with the regular expression I should use ? SUM(CASE WHEN REGEXP_MATCH(Affaires et industries, ' .*Design.*' ) THEN 1 ELSE 0 END) Thanks for your help ...
Update, I modify the formula and it seems working ... What do you think with this new version ? SUM(CASE WHEN REGEXP_MATCH(Affaires et industries, 'Design' ) THEN 1 WHEN REGEXP_MATCH(Affaires et industries, '.*Design' ) THEN 1 WHEN REGEXP_MATCH(Affaires et industries, 'Design.*' ) THEN 1 WHEN REGEXP_MATCH(Affaires et industries, '.*Design.*' ) THEN 1 ELSE 0 END)
Hi, do you have any tutorials on how to make this kind of table? Name | Date1 | Date2 | Date3 | Date4 | Date5 | john | ok | ok |not ok | ok | not ok | james | not ok| not ok | ok | ok | ok | David | not ok| not ok | ok | ok | ok | I am hoping you can help me with this. Thank you!
CASE WHEN Order Date>= '2014-01-01' and Order Date< '2015-01-01' then 30000 WHEN Order Date>= '2015-01-01' and Order Date< '2016-01-01' then 30000 ELSE 50000 END * invalid formula - Operator ">=" doesn't support DATE >= TEXT. Operator ">=" supports ANY >= ANY... Help me,, i want to make methode agregasi (conditional function)
4 роки тому
Great tutorial. It helps me a lot. I am an issue with this formula: CASE WHEN VencimentosMes = 13 THEN (CONCAT(VencimentosAno, "/", 12)) WHEN VencimentosMes < 10 THEN (CONCAT(VencimentosAno, "/0", VencimentosMes)) ELSE (CONCAT(VencimentosAno, "/", VencimentosMes)) END I receive this message "Invalid formula - Invalid input expression. - Failed to parse CASE statement". Why? I coudn´t find the issue. Thanks.
You might notice me using the term Formula a lot.... I only later on noticed it's actually called Functions in Data Studio... oh well! Enjoy the video and let me know which functions you use often.
0:00 - Introduction
1:05 - Create a New Customized Field
1:40 - #1 LOWER/UPPER Function
3:05 - #2 CONCAT Function
4:35 - #3 REGEX_EXTRACT Function
6:05 - #4 REGEX_MATCH Function
9:30 - #5 CASE Function
13:20 - Summary
For search term extraction at around the 6 minutes mark consider q=([^&]+) - this should prevent capturing extra query parameters' values after the q, e.g. in q=xxx&, xxx will be captured, but & will be ignored.
For What/When extraction around 7:15, consider the regex \\b(what|when|etc...)\\b - the \\b part says 'word limit', so 'when' is a match, but 'whenever' is not. Remember that calculated fields in Data Studio use two slashes as the escape character.
Wonderful video! Thanks a lot. I found everything I needed for my project in just a few minutes. Excellent!
This is great. Thanks for creating and posting it. One use case comes to mind for us: calculate branded searches out of Google Search Console data. Maybe even filter and separate them to show the power of SEO on non-branded searches.
Can you make a video on this - I am interested in seeing my branded and non branded searches as percentages
You're amazing! THANK YOU!
Very useful tips. Thanks!
is there a way to use IF or CASE as a filter. Lets say a score card with conversion that only shows the conversion when the event name = form_submit? I know I can use regular filters but sometimes it would make things much easier to apply filter directly to the metric.
Very helpful video - The last 2 functions
How can I make a field in Data Studio for a specific conversion type (e.g. Leads) ?
Great video. Thank you!
Great video and great explanation.
But when you cancel the transformed variable how you can set an aggregate function for the metric variable? I don't understand what datastudio does in default, average? min? max?choose a random value?
Thanks for the video, Can you suggest how to apply a filter in Average Position(GSC). I used CASA when Calculated Field but every time its showing an error.
Looking for function to remove text before and after a character....example: Trigger-EM|AbandonCategoryBrowse-PartMakeModelYear
- the output should be 'AbandonCategoryBrowse', so everything before (and including) | and - - removed.
Hi, I need to merge all the row with the same Action Event (I have multiple "transaction" rows with different labels) ? Thank you
Great video!!! Much of what you have explained helped me immediately, but I have this issue that is giving me gray hair. I have two tables that come from the same field, but each of them has a different filter, one is by date of entry (sum all that came in on the day) and the other by date of exit(sum all that left work on the day), how do I do the operation so that I add both the amounts of both tables to have the total of inputs and outputs in a single report? thanks for your help
Fantastic man, thanks a bunch! Keep em coming!
Hey Julian - I want to remove the Values of one field from another if their values are similar, How to put that logic. Example: My Event category has button click and Document clicked, and I want if values of document click =button click, true then remove or filter those
hi, thank you very much for your videos.
Please, I see if you can help me.
I don't want DataStudio to round values, coming from Bigquery, please, as I do for datastudio not to round values after the comma
Really helpful video thanks!
nice content sir!
I want to create a score card for number of rejections. The data has rejections as a column . Where there are no rejections, it's mentioned as 0. While creating scorecard, it's calculating even the 0 rejections. Can you help
Hi all,
Any have idea how will be use sumproduct function in data studio. If any alternative suggestion for this.
Hi, if we want to pull only parts of data from the String how do we use the extract function ? Like for example I have data like:
Tier1|data1
Tier2|data1
Tier1|data2
From the above I want to extract only tiers and copy them to a new calculated dimension .
=REGEXEXTRACT(FIELD,"(Tier.)\|")
Hi Julian, I used Google Analytics as a data source to create a Google Data Studio report and found the numbers of same parameter(user, session etc) do not matching any more after applying a (filter) segment.
Is this a common problem?
Awesome tips : thanks !
Is there a way to get a YOY function over a sum of values?
Hey Julian - that was really helpful, how can we do data validation on google data studio, could you please help - thank you in advance.
Nice Ibanez, do you also shred?
sometimes
Can you do calculated IF statements in GDS?
CASE
WHEN effective = "January" then 1
WHEN effective = "February" then 2
END
(still my ouput is 0) can you help me about this. when month is january then it shows the field 1 and etc...
is the "else" clause optional?
Hi :)
Thanks for your video, very interesting.
Is there a video only on the "case" formula ? 😇
Do you know in data studio how can I generate a filter with a case, where contain text could be two options and set the same label?
I thought something like this but doesn't work
CASE
WHEN CONTAINS_TEXT(Página de destino, "/blog") THEN "blog"
WHEN CONTAINS_TEXT(Página de destino, "(/|/es | /en)") THEN "Home"
ELSE "Otros"
END
Hi! i´ve got 2 questions:
1. is any way to extract the hostname from an url? I´ve been trying with regexp_extract but it seems not to work.....
2. how could convert "av. time on page" in a range distribution? (So, % within less than 10 seconds, between 10 and 30 seconds, etc...) and then use it as dimension...
thanks!
how about this, apples when = 5, raffle entry is 1 ;and apples exceeds 5 another raffle enrty, how can i do that?
Hi Sir , How can i use correl formula in data studio ??
how can i measure the number of users registered in my website?
Hello
I am trying to calculate an indicator in Data Studio called WMape = (Abs (Sale-Forcast)) (Sale / Sum (Sale)). The problem I have is to want to dynamically weigh the calculation. This weighted is based on the sale. Anyone who can help me with this calculation please !?
Thank you!
Great Video
How to solve the problem
the number of transactions for the period with the status 1, 2, 3 ... 5 = A
the number of transactions for the period with the status 6, 7, 8 = B
A-B = C
(A + B) / A = X
Do u know any function like fixed in Tabelau for DataSatudio?
no, sorry
wow✌
CASE
WHEN YRINST = "02-.*" THEN sum(premium)
END ------is this possible?, how can i create case if the condition is true, it total my field
not sure. would need to work with your data
@@MeasureSchool I alreay made it :). i did it in pivot and it works. Thank You
Hey Julian I have a question! Do you know how to extract Image Organic Traffic with Google Data Studio?Is there a way to do this? You know that in Google Search Console there is a section which allows you to see the Image Organic Clicks which comes from Image Google Results! And Google Data Studio doesn't have Search Type as dimension.
Not sure what you mean by extract, but you can filter by Default Channel Grouping from Google Analytics data.
@@Getfoundmadison I can't extract Image Organic Traffic from Google Analytics! In Google Search Console there is a filter which allows you to see the organic traffic which comes from Image Organic Section from Google Image Results. And I asked Julian if there is a way to extract in Google Data Studio the clicks/impressions from the that section! Thanks for replay!
@@Katallinu1 did you integrate search console with your Google Analytics?
@@9038158250 I extracted the image traffic with Supermetrics connector!
Hi. I tried to create a calculate field to take only Sessions with Medium = ads. Does anyone has any idea how to do it?
I tried this one but doesn't give me the sessions. I don't want the dimensions. I need the metrics.
CASE
WHEN Mídia = 'ads' THEN 'Facebook Ads'
ELSE 'Null'
END
This should work:
CASE
WHEN Mídia IN ("ads") THEN "Facebook Ads"
ELSE "Null"
END
Can we display the Google User Name in the report page ?
no
CASE WHEN avg time on screen = 2 THEN "good performance" END
getting an error "Only numeric literals are allowed if aggregated fields are used in CASE."
I don't know what is wrong
Maybe "time on screen" is not correctly set as a number.
Wow SQL in Datastudio? Amazing lol
Hey Julian,
First of all, I am a big fan of you, I have one situation that is related to data studio blending feature. Hope you will solve it.
The situation is that I have a blog that has so many posts and I want to see metrics of one category blog posts. For that I have list out post URLs in Google sheets and connect with data studio.
All going good, but I don't able to see metrics of dynamic URLs that contains ? mark. Because Data studio only matches non dynamic URLs with Google sheets URLs and showing data.
Hope you understand my problem. If not, so I will share screenshots with you through mail.
Hi I am sales person agri based and would like correct product names miss typed clean it and also want to see the sales growth YOY growth %
6:47 am I the only person who cares about the search term on line 6?
Data studio got weakness,.it can not support combine If and Sum command
Hi Julian,
Thanks for this video and all previous ones, so interestings.
I'm actually trying to analyse datas from multiple choice question survey with Google forms and Google Data Studio.
The data obtain are words separate with coma depending how many choices the person has choosen.
ex :
Construction, Design, Innovation
Design
Design, Innovation
Construction, Design
I'm using the REGEXP_MATCH formula to extract the word "Design" and count how many times it appears in the results.
After that, I'm using the words count with the formula as data sources for graph.
But I'm not able to have the correct regular expression to extract all the data because the word can be located anywhere (beginning, middle or ending)
Here is the formula I use, can you help me with the regular expression I should use ?
SUM(CASE
WHEN REGEXP_MATCH(Affaires et industries, ' .*Design.*' ) THEN 1
ELSE 0 END)
Thanks for your help ...
Update, I modify the formula and it seems working ... What do you think with this new version ?
SUM(CASE
WHEN REGEXP_MATCH(Affaires et industries, 'Design' ) THEN 1
WHEN REGEXP_MATCH(Affaires et industries, '.*Design' ) THEN 1
WHEN REGEXP_MATCH(Affaires et industries, 'Design.*' ) THEN 1
WHEN REGEXP_MATCH(Affaires et industries, '.*Design.*' ) THEN 1
ELSE 0 END)
@@ryokosan7 Pourquoi ça n'a pas marché avec l'autre expression régulière? Ça aurait dû normalement.
Hi, do you have any tutorials on how to make this kind of table?
Name | Date1 | Date2 | Date3 | Date4 | Date5 |
john | ok | ok |not ok | ok | not ok |
james | not ok| not ok | ok | ok | ok |
David | not ok| not ok | ok | ok | ok |
I am hoping you can help me with this.
Thank you!
no
How to change null in a table?
CASE
WHEN Order Date>= '2014-01-01' and Order Date< '2015-01-01' then 30000
WHEN Order Date>= '2015-01-01' and Order Date< '2016-01-01' then 30000
ELSE 50000
END
* invalid formula - Operator ">=" doesn't support DATE >= TEXT. Operator ">=" supports ANY >= ANY...
Help me,, i want to make methode agregasi (conditional function)
Great tutorial. It helps me a lot.
I am an issue with this formula:
CASE
WHEN VencimentosMes = 13 THEN (CONCAT(VencimentosAno, "/", 12))
WHEN VencimentosMes < 10 THEN (CONCAT(VencimentosAno, "/0", VencimentosMes))
ELSE (CONCAT(VencimentosAno, "/", VencimentosMes))
END
I receive this message "Invalid formula - Invalid input expression. - Failed to parse CASE statement".
Why? I coudn´t find the issue.
Thanks.
hard to say. Need to keep trying
🤯