Google Data Studio has long given report makers the ability to create their own “calculated fields”. Recently, blended data sources were also made available. What was missing was the ability to combine values from blended sources using calculated fields. This capability has just been released and is available now in data studio!
Example: Total Google Reach – Google My Business and Search Console
This could be very useful for small businesses with local locations. You can watch the YouTube video to see the whole process, or follow the detailed walk through below.
Step By Step
Here are step by step instructions for using the new feature. You can find also check out the example report here.
1. Add data source for Google My Business (GMB) listing to the report. Note: I recommend the free Jepto connector or the premium Supermetrics connector for this data. Create a time series graph for total views metric.
2. Add Google Search Console (GSC) ‘Site’ data to the report.
Create a time series graph for total impressions metric.
3. Combine the charts to create a new chart and a new “Blended Data Source”.
Select a chart, then use CNTRL button and click on the other chart to select both. Right-Click on the selected charts to drop down the select menu and select “Blend Data”. Your new blended data source and chart will be created.
4. Create a new field called “Total Google Reach
The new metric will be added to the blended chart. Create the new field by adding Impressions + Total Views.
- Click “Add Metric” in the metrics section.
- Add the new name and the formula “Impressions + Total Views” and click the “Apply” button.
- A detail screen will appear and the new metric will be added to the graph. Simply click on the page to make the detail screen go away!
5. Remove extra source metrics (optional)
You can safely remove the extra metrics if desired so only the new Total Google Reach field shows in the chart. Congratulations, you just created your first blended calculated metric!
What are the implications of calculated fields with blending?
Data Studio creators have been anticipating this feature since blending was released. Google seems to have waited a bit to release this feature – it was exposed before release in an official Google YouTube video with Krista Seiden and Mary Pishny. “Data Blending in Data Studio” appeared October 1, 2018.
This ability to create new fields from blended data greatly extends the power and reach of Data Studio in 3 major ways:
- Simplifying current reporting processes. Now you can work with values between metrics from different data sources directly in the data studio editing interface. Before this, new fields had to be created from multiple sources outside of data studio then brought in as a data source. This required a lot of thinking ahead and setup.
- Faster data reconciles between sources with different values. If you want to provide consistent dimension values across data sources this is now possible. Applying a case statement on a new field should allow us to create consistent values for dimensions across data sources.
- Faster discovery and analysis. The ability to create new metrics will encourage more exploration of the data and the connections between different sets of data.
I will add new examples and links to new ideas
Great feature!
Lot’s more updates this weekend including interactive chart filtering! Follow my twitter posts at @helpfullee to find out more and see discussions on the new features .
Very nice feature. i had it fully working for me in a dashboard however since last week (approx 12-11) it stopped working for me. The already created calculated fields are marked as faulty, and the button to “+ Add metric” is completely gone.. Anyone else who has this problem?
I haven’t been seeing this, but I know there have been some issues reported. Did the issue go away?
I’m still having the same issue with anything other than the most basic calculations.
Addition of two data sources is ok, but trying to then divide them against another calculated field causes issues.
E.g. data1 + data2 / data1 + data2
More info: https://www.en.advertisercommunity.com/t5/Data-Studio/Basic-Math-Calculated-Fields-With-Blended-Data/m-p/1871196
Just a follow up on this – Still an issue as far as I know – See @Jonny_J_ post on his blog here about the issue http://bit.ly/2CMLtB3 .
Hi,
Did you check that the “field editing in reports” is on for the different data sources you’re using?
You can check that going to Resource > Manage added data sources > edit (any source) > switch the “field editing in reports” on in the top right corner.
You need to make sure it’s on for all the sources you blend together.
Mat
Thanks Mat, sorry for the delayed reply! Yes, it’s a good idea to check that, but I believe it is set correctly by default. I have had cases where it seemed to flip for some reason. It is pretty rare, but worth mentioning.
Hi there
Having some issues with this feature – the tutorial was helpful but my data does not seem to be summing as expected.
I am adding together the session data from two views. I have followed these steps carefully but when it comes to adding the two metric values ‘Sessions’, only one shows in the drop down so I can only add it to itself.
Is there a difficulty adding two metrics with the same name? How can I get around this?
Thanks in advance
Good Day! I think I know the problem , but let me know if I am right…
You join the data sources and you want to show the data from each source in a separate column? Like
Date | Sessions Site ! | Sessions Site 2 | Total Sessions
1/15 | 2750 | 2000 | 4750
The trick is in the blended data source setup – if you edit the blended source you can change the name of Sessions for each source there. This makes it MUCH easier to work with them! I suggest calling them something like Site1Sessions Site2Sessions . It is a bit ugly , but you wont have any problems if you need to use them in calculated fields – I have heard of issues using a ‘-‘ in the name because it might be interpreted as a minus symbol. You can always change the name on the lables if you actually use the fields.
Let me know how it goes or we can set up a Google Hangout to take a look at the issue …
Hi Hurst, I also have the problem when I sum up rows after calculating new fields for blended data
Hi,
Very useful this post, congrats!
But I’m trying to add a Calculated field as a dimension, no as a metric.
And Data Studio allows me to choose between numeric, text, date, boolean, geo, currency and URL type, but I need an Image (to show thumbnails from an URL)!
Do you why?
Many thanks!
Hi Joan, You need to use the Image function- here’s the documentation https://support.google.com/datastudio/answer/7570489?hl=en
It will be classified as a URL when you use it .
Please see my other post on how to capture thumbnails for your data studio automatically! I think it may cover exactly what you are looking for and save you some time . https://helpfullee.com/add-previews-data-studio/
Let me know how it goes!
Hi Lee, and thanks for your quickly answer!
Yes, for that Calculated field I’m using the Image function as follows:
IMAGE(Image URL, “thumbnail”)
Where “Image URL” is a path like: https://example.com/media/catalog/product/839.png
At first, I set up the dimension “Image URL” as an Image, but I also tried with a “Text” type, but I’m still having seeing the issue:
/ This data source was improperly configured.
/ Invalid argument type.
Do you know why?
Many thanks again!
Hi Joan, Yes I see your problem. Thought maybe issue was png , but was able to get this one
IMAGE(“https://i.imgur.com/a4GM6wt.png”,”test”)
The problem is that your test image IS NOT AN IMAGE!
When you hit that page there is no image even though the URL ends in .png
You can tell because you can highlight the text. Try some other actual image URLs!
Thanks for reading my posts!
I know, Lee.
I posted this example of URL image because I cannot show the domain, but believe me it’s an image and I can show correctly in my browser, not in Data Studio.
I’ll keep trying…
Thanks!
Hi Joan, the image tag will only work with links to images, it does not create thumbnails from actual pages. I sense we have a disconnect here. The URL you provided has ‘png’ at the end but it is not an image. Several ways to tell – you can use the mouse to highlight the text, you can view the source and there is no image tag, and if you right click there is no way to save the image. If you want to display that page you need to render it as an image as explained in my post that I referenced.
Here is a formula that will work for that page
IMAGE(CONCAT(‘https://api.letsvalidate.com/v1/thumbs?url=’,”https://example.com/media/catalog/product/839.png”),”Thumbnail” )
This will work because the sample URL is not an image, it is a page. This test case may be confusing issues and you should try a different URL to an image.
There is another case that might be causing the issue. If the image is not publicly accessible, it will not show in data studio. This is true even if you are not sharing publicly and everyone in your network can see the image on their browser.
I believe the Data Studio service that pulls images is outside your security network. This means that when it tries to pull the image it is blocked and thus cannot pull the image to format and display it.
You can check an image by using incognito mode in chrome to check the image URL, or better yet, check the image URL on your phone. If it can’t display there Data Studio cant render it. So if the image urls are on a server behind a firewall you will have issues.
I have had this issue on Google Drive – Images that I can see in the browser if I am logged in to the company network but cannot be viewed because they are not world viewable. To make matters worse, our network does not allow you to make the files or folders world viewable. I have figured out a way around this, but it requires using a non-company external account. I may write a post on it.
Let me know if any of this resonates with you – Happy to dig in deeper and help you debug if necessary as I’m sure there is a solution here.
Hi Lee! I want to calculate the ROI with data from Ads and Analytics. The joint key is Campaign ID and Google Ads Campaign ID (should be a match right?) And the get cost from Ads and Revenue from the Analytics, as metrics.
The formula (Revenue-Cost)/(Cost)=Numeric Value doesnt work 🙁 Any tricks??!!
Hi Louisa – I will check it out. I did an analysis for some similar things in DS before blending came out. I had to export to sheets to make some of the aggregated values non-aggregated to get the formulas to work. It was effective for what I needed at the time, but haven’t tried since blending and calculated fields became available. I will take a look. Please note there are still some bugs in the system – Jonathan Jones @Jonny_J_ has a good recent post that you might find helpful! http://bit.ly/2CMLtB3 One note – I always found it really helpful to integrate goals with values and eCommerce back to Adwords. It makes all the data available in both places. This won’t help you much now, but you might want to consider it.
HI Louisa, Sorry for the late reply and hope you got this fixed. There were some bugs when using division in blended calculated fields but they should be all fixed now. I have seen the suggestion that you modify that a little to something like this
(Sum (Revenue) – Sum (cost))/ Sum (cost)
it seems it works better using the sum function explicitly in the formula.
Is there a way to take two scorecard numbers and subtract one from the other? Ie: Total Revenue taken from product data (Google Analytics) subtracted by Total Google Ads Spend (taken from ‘cost’ metric from Google Ads) ??? Been struggling with this for a while now!
Thx.
Yes, it’s just not as clear. I did this with different data sources but the principle should be the same. I created a scorecard for Supermetrics Search Console clicks , then I created one for Google Search console clicks. Use CNTRL-click to select both, right mouse click for menu and select join. This will select one of the metrics for the scorecard. Then click on the metric , and in the selection popup you should be able to create a new field. Here I was able to subtract one from the other and get a difference metric.
One note: Both data sources have to have field editing turned on – for some reason my older data source did not have this selected.
Another way to do this is to create a table with a single metric column from each source, blend them as above to get the new metric , then delete the unneeded source metrics and change the style from table to scorecard.
Hope that helps – let me know!
Hi, tried to add together 2 metrics of different data source once I have the data correctly blended, but it didn’t work.
I checked out the same operation with the “+” but for 2 metrics from the same data source once I have the data blended but didn’t work either.
Do you know why I cannot do this?
Many thanks Lee!
Could be many reasons for this – usually the issue is trying to blend on the wrong dimension, or too many dimensions. The most foolproof way I have found so far is to actually chart out the metrics from each source then blend them as shown here. Data Studio is pretty good at getting it correct when you blend this way! Are you still having issues?
Hi!
I have a problem with blending data. My Join Key is a custom Channel defined in both data sources. My metric is sessions. I made a calculated blended field called total sessions to sum both sessions metrics.
now the problem is that some channels in second data source doesn’t have any sessions and as a result of that data studio show null instead of zero and when data is blended the amounts of total sessions for those channels becomes null. How can I make data studio to show zero numbers too so that i don’t get a null for those channels in total sessions column. (As far as I know making the table to show zero instead of null for missing data doesn’t help at all. it just makes the final number in the total sessions column to be zero instead of null!)
Hi Ali! Yes , this is a common problem. The blend is an “outer left join” , which is a fancy way of saying it will only join on keys it finds in the left data source. You can usually get what you want by using a different join key like just the date. ad adding sessions from each source. The rule of thumb here is only join on what is absolutely needed and only include fields you need. It can get confusing , so I would suggest creating a table for each data source with the metrics and values you want in the final table. Then do the join on those tables. Each source will have its own session metric and it is a good idea to name them a bit differently in the blended data source definition. Then try creating the new field in the blended data source, or directly in the chart. I have found this much easier in most cases until you are very used to working with blends.
Thanks so much for this post, it was driving me mad how to get a scorecard to add up the total sessions from two different websites, google’s tutorials are very basic!
I’m having a problem with the comparison data though. So I’ve created a calculated field which adds site1 sessions and site2 sessions to give me total sessions, and it should show the year-on-year comparison at the bottom, but I get null back for this once I blend the data. Site 1 has sessions for last year, site 2 doesn’t, but I was still expecting it to show the site 1 data because x + 0 should still be x – what am I doing wrong?!
Hi Rebecca, Hope you have this solved now! Just for the people who might be following along, I suspect that you used the date for your join for that chart. Whichever one is on the left side of the blended data source would control the date – since the other site does not have dates in the same year there is no match and thus it does not show. That is the most likely problem, but there could be others. You might want to join on Month of year, or ISO Week , or even ISO Day of year. This should get you what you need although it is not ideal. It would be nice if you could just lump them together, but the rules of Data Studio blending are based on “outer left join”. Sorry I missed this one when you posted it! Hit me up on twitter if possible @helpfullee if you need more help here.
Great article, thanks. I’ve been trying to combine two GA properties so that i can report on combined stats. I’ve tried doing a data blend, and adding “page views” as a metric on both GA data sources but I can’t get a sum of those together when I try integrate them into a Scorecard, even trying to create a custom field that sums them together, only one “page views” appears in the dropdown. I’d ideally like to just combine them together without having to manually combine each metric but if even that’s possible it would be a start. Thanks!
Dear Lee, please help. I have created blended data source from GA + Adwords and I would like to calculate the cost per lead. It looks easy, but it is not:/
For real example – in blended data source table I have GA metric Leads for campaign 1= 41, AdWords campaign 1 costs = 10 918 CZK. In that table I created new calculated metric Cost/Leads and the results is 266 CZK. I am not even able to fihure out from where this number come from:(
Can we blend data from tables created from already blended data ?
Or can we save tables data as new data sources in data studio ?
is there a way to create another dimension from blended data?
I’m trying to show the breakdown b/w FB and AW data for different campaign types, but my formula doesn’t seem to work.
CASE
WHEN CONTAINS_TEXT(Campaign objective,”BRAND_”)THEN “BRAND”
WHEN CONTAINS_TEXT(Campaign objective,”LINK_”)THEN “TRAFFIC”
WHEN CONTAINS_TEXT(Campaign type,”Search”)THEN “SEARCH”
WHEN CONTAINS_TEXT(Campaign type,”Video”)THEN “VIDEO”
ELSE “Other”
END
Hmm, this is a good question! I am really late following up on this and wondering if you got it solved. I believe I have seen some case statements in the calculated fields , but not sure why this would not work for you. Of course, both of the fields have to be in the blend to start with, but I dont see any major issue here. My suggestion would be to build the table without the new field first so you can double check the data. Problems usually come form assumptions that are not valid and overlooked.