5 Data Studio Functions for Calculated Fields you should know
ฝัง
- เผยแพร่เมื่อ 8 มิ.ย. 2024
- Calculated Fields in Google Data Studio let you create and add custom metrics or dimensions to your data set. You can utilize Formulas to calculate your fields just like in Google Sheets. Today I’m going to show you 5 Functions you should know.
🔗 Links mentioned in the video 🔗
📄 Read this Video’s Guide: measureschool.com/calculated-...
All Functions: support.google.com/datastudio...
📚📚 Courses and Resources ⏬⏬
From Zero to Measure Hero 🦸♀️🦸♂️ bit.ly/measuremasters
GTM, GA4, and GDS FREE Courses 💸 bit.ly/measureschool-courses
Need Personalized Help? Contact Us 📞 bit.ly/measureschool-services
👍 FOLLOW US 👍
LinkedIn: / measureschool
Twitter: / measureschool
Instagram: / measure.school
Facebook: / measureschool
OUTLINE:
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 - วิทยาศาสตร์และเทคโนโลยี
Wonderful video! Thanks a lot. I found everything I needed for my project in just a few minutes. Excellent!
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
Fantastic man, thanks a bunch! Keep em coming!
Awesome tips : thanks !
Very helpful video - The last 2 functions
Very useful tips. Thanks!
Great video. Thank you!
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!
Great video and great explanation.
Really helpful video thanks!
Great Video
Hi :)
Thanks for your video, very interesting.
Is there a video only on the "case" formula ? 😇
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.
nice content sir!
Hi, I need to merge all the row with the same Action Event (I have multiple "transaction" rows with different labels) ? Thank you
Hey Julian - that was really helpful, how can we do data validation on google data studio, could you please help - thank you in advance.
How can I make a field in Data Studio for a specific conversion type (e.g. Leads) ?
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.
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
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
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
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?
wow✌
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!
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.
Can you do calculated IF statements in GDS?
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?
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.)\|")
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.
how about this, apples when = 5, raffle entry is 1 ;and apples exceeds 5 another raffle enrty, how can i do that?
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 all,
Any have idea how will be use sumproduct function in data studio. If any alternative suggestion for this.
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!
Wow SQL in Datastudio? Amazing lol
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
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
Nice Ibanez, do you also shred?
sometimes
Hi Sir , How can i use correl formula in data studio ??
Do u know any function like fixed in Tabelau for DataSatudio?
no, sorry
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?
Is there a way to get a YOY function over a sum of values?
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!
how can i measure the number of users registered in my website?
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.
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
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
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
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.
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 %
How to change null in a table?
🤯
Can we display the Google User Name in the report page ?
no
Data studio got weakness,.it can not support combine If and Sum command
6:47 am I the only person who cares about the search term on line 6?
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
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
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)