5 Data Studio Functions for Calculated Fields you should know

Рет қаралды 135,901

Күн бұрын

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

Пікірлер: 77

@DirkSchornstein 2 жыл бұрын

Wonderful video! Thanks a lot. I found everything I needed for my project in just a few minutes. Excellent!

@peternelson1061 3 жыл бұрын

Fantastic man, thanks a bunch! Keep em coming!

@Getfoundmadison 5 жыл бұрын

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.

@AdamLundquist 4 жыл бұрын

Can you make a video on this - I am interested in seeing my branded and non branded searches as percentages

@ChristopherGuillou 5 жыл бұрын

Awesome tips : thanks !

@MeasureSchool 5 жыл бұрын

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

@brinleyrebstein 4 жыл бұрын

Very helpful video - The last 2 functions

@tranlenhan6422 3 жыл бұрын

Very useful tips. Thanks!

@liamkholmes 3 жыл бұрын

Great video. Thank you!

@SofiaPotdevin 2 жыл бұрын

You're amazing! THANK YOU!

@SapphosSails 3 жыл бұрын

Really helpful video thanks!

@erasmomiguel5663 3 жыл бұрын

Great video and great explanation.

@seodaweb2759 2 жыл бұрын

Hi :) Thanks for your video, very interesting. Is there a video only on the "case" formula ? 😇

@EliaAntoldi 3 жыл бұрын

Hi, I need to merge all the row with the same Action Event (I have multiple "transaction" rows with different labels) ? Thank you

@maharshichoubisa2547 3 жыл бұрын

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.

@gauravsrivasttava33 3 жыл бұрын

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

@muhammadnauman1454 5 жыл бұрын

Great Video

@SaiRamsairamyadavavula 3 жыл бұрын

Hey Julian - that was really helpful, how can we do data validation on google data studio, could you please help - thank you in advance.

@niklasjohansson4526 11 ай бұрын

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.

@topefu 4 жыл бұрын

How can I make a field in Data Studio for a specific conversion type (e.g. Leads) ?

@PhatNguyen-by7lu 3 жыл бұрын

nice content sir!

@nataliadacostaperez5641 4 жыл бұрын

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!

@vdchagas 3 жыл бұрын

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

@jiezhang5000 4 жыл бұрын

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?

@juanjoge Жыл бұрын

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

@marcogelsomini7655 2 жыл бұрын

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?

@Acheiropoietos 3 жыл бұрын

Can you do calculated IF statements in GDS?

@siddharthapulijala 5 жыл бұрын

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 .

@MeasureSchool 5 жыл бұрын

=REGEXEXTRACT(FIELD,"(Tier.)\|")

@cruzjovanyduranflores9952 4 жыл бұрын

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!

@kunalborge5474 3 жыл бұрын

wow✌

@bnlovesyou2241 4 жыл бұрын

how about this, apples when = 5, raffle entry is 1 ;and apples exceeds 5 another raffle enrty, how can i do that?

@abhishekshrivastav2141 3 жыл бұрын

Hi all, Any have idea how will be use sumproduct function in data studio. If any alternative suggestion for this.

@MohitHarjai 5 жыл бұрын

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.

@romank1280 3 жыл бұрын

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.

@lemuelcarinote5674 3 жыл бұрын

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.

@gonzalogarcialabat9385 4 жыл бұрын

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

@FarhanKhan-zo8wp 2 жыл бұрын

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

@stephenong137 4 жыл бұрын

Wow SQL in Datastudio? Amazing lol

@prakashsharma5225 3 жыл бұрын

Hi Sir , How can i use correl formula in data studio ??

@miguelribeiro1182 4 жыл бұрын

Is there a way to get a YOY function over a sum of values?

@diegofabianmoralara421 4 жыл бұрын

Do u know any function like fixed in Tabelau for DataSatudio?

@MeasureSchool 4 жыл бұрын

no, sorry

@bnlovesyou2241 4 жыл бұрын

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...

@landrynoulawe1565 3 жыл бұрын

is the "else" clause optional?

@FernandoOka 4 жыл бұрын

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

@mauriciocortes9736 4 жыл бұрын

This should work: CASE WHEN Mídia IN ("ads") THEN "Facebook Ads" ELSE "Null" END

@Katallinu1 5 жыл бұрын

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 5 жыл бұрын

Not sure what you mean by extract, but you can filter by Default Channel Grouping from Google Analytics data.

@Katallinu1 5 жыл бұрын

@@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!

@9038158250 4 жыл бұрын

@@Katallinu1 did you integrate search console with your Google Analytics?

@Katallinu1 4 жыл бұрын

@@9038158250 I extracted the image traffic with Supermetrics connector!

@bnlovesyou2241 4 жыл бұрын

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

@MeasureSchool 4 жыл бұрын

not sure. would need to work with your data

@bnlovesyou2241 4 жыл бұрын

@@MeasureSchool I alreay made it :). i did it in pivot and it works. Thank You

@shreddingninja4019 3 жыл бұрын

Nice Ibanez, do you also shred?

@MeasureSchool 3 жыл бұрын

sometimes

@mohammadnorouzi162 2 жыл бұрын

how can i measure the number of users registered in my website?

@ryokosan7 5 жыл бұрын

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 ...

@ryokosan7 5 жыл бұрын

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)

@landrynoulawe1565 3 жыл бұрын

@@ryokosan7 Pourquoi ça n'a pas marché avec l'autre expression régulière? Ça aurait dû normalement.

@dpitpulilan3712 4 жыл бұрын

How to change null in a table?

@ivanshipilov4265 2 жыл бұрын

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

@gauravtripathi5123 4 жыл бұрын

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

@henrywillian6427 4 жыл бұрын

Maybe "time on screen" is not correctly set as a number.

@pratapcabm123 3 жыл бұрын

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 %

@josiahclagett1105 2 жыл бұрын

🤯

@muzamilshafeek6450 4 жыл бұрын

Can we display the Google User Name in the report page ?

@MeasureSchool 4 жыл бұрын

@jasonjason6856 Жыл бұрын

Data studio got weakness,.it can not support combine If and Sum command

@hendryns2419 3 жыл бұрын

6:47 am I the only person who cares about the search term on line 6?

@jacksonsolomon2444 4 жыл бұрын

@MeasureSchool 4 жыл бұрын

@yopiibrahim979 3 жыл бұрын

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.

@MeasureSchool 4 жыл бұрын

hard to say. Need to keep trying