Hyperlinks in data
You can display clickable links in your data using a table. There are 2 ways to get these links:
- Directly from your data set, using the URL field type
- Generating the link using the HYPERLINK function
URL field type
When you create a data source, Data Studio will detect valid URLs in the data set and assign the URL field type to that dimension. (If Data Studio doesn't detect the URLs automatically, you can set the field type to URL manually.)
URL fields display the full link in charts. In tables, this link is clickable.
The HYPERLINK function lets you construct links in calculated fields. The HYPERLINK function takes a URL and a link label as input. The output is a clickable link when displayed in a table (in other charts, the link text is not clickable).
Only certain protocols are supported in both URL fields and the HYPERLINK function. See the HYPERLINK article for details.
What does the "Unable to aggregate ratio metrics" error mean?
At a glance
Have you seen this error message?
Unable to aggregate ratio metrics in the request. Please select another metric.
This error means you've asked Data Studio to do something with an already aggregated ratio metric that it can't do. For example, you've applied a filter based on a calculated field to a chart that contains a ratio metric.
The solution is to select a non-ratio field in the chart (e.g., use Impressions instead of CTR), or remove or change the filter.In this article:
Ratio metrics show the relative sizes of two or more values. For instance, the Google Ads metric , e.g. Clickthrough rate (CTR) is the number of clicks that your ad receives divided by the number of times your ad is shown. In Google Analytics, Bounce Rate is single-page sessions divided by all sessions, while Entrances / Pageviews calculates the ratio of visitors entering your site and beginning a new session compared to the number of pageviews.
In Data Studio, you get the error above when you filter a chart that includes ratio metrics in a way that requires the product to recalculate the ratios.
Here are 3 things to help you understand this issue:
1) Data from data sets such as Google Analytics, Google Ads, YouTube, and Google Marketing Platforms products is already aggregated by the time it gets to Data Studio. For example, when Data Studio requests a Google Ads metric such as CTR (click-through rate), the data is already processed into the appropriate aggregation type.
2) Because of the above, calculated field functions are applied to your data post-aggregation. It's not possible (nor would it generally make sense) to go back into the raw data and look at every unique instance of that metric. For example, if you try to create a calculated field with the formula
SUM(Impressions)in a Google Ads data source, you'll get the error:
Re-aggregating metrics is not supported.
That's because Impressions is already aggregated (and its aggregation type, Auto, can't be changed).Note that this isn't necessarily the case with data sources such as Google Sheets, MySQL, or BigQuery, where you are able to send non-aggregated data to Data Studio. For example, if you had raw impression data in a Sheet, you could use the SUM function to add it all up, the AVG function to generate the average, etc.
3) For consistency, all calculated field functions in Data Studio are available for use with all data source types, even if the underlying system doesn't natively support that function. For example, you can use the CONCAT function to join multiple values in any data source, even if the underlying system doesn't have a CONCAT function of its own. Instead of "pushing down" the CONCAT function to the underlying system, Data Studio requests the data and performs the concatenation itself.
Aggregation failure example
So what does all that have to do with the "
Unable to aggregate ratio metrics" error? Let's go a little deeper with the CONCAT example to find out.
Suppose you create a calculated field called Campaign : Click Type in a Google Ads data source, using the following formula:
CONCAT(Campaign, " : ", Click Type)
Data Studio issues queries for Campaign and Click Type individually, and then performs the concatenation. The results are grouped, so there is no duplication of records.
You can now use that concatenated field in your charts, and the metrics you include are aggregated properly. For example, we could use Campaign : Click Type as the dimension and CTR as the ratio metric in a table:
But now, let's say you apply a filter to show only those records where the Click Type is
This will break the chart:
Why it breaks
The filter asks Data Studio to include each record returned by the Campaign : Click Type if it contains "Headline." That field is a concatenation of 2 dimensions: to fulfill this request, Data Studio has to refetch those dimensions, and then apply the filter. The problem is the presence in the table of the ratio metric, CTR. Google Ads ratio metrics are computed before Data Studio requests them. Data Studio has no way to access the raw data and recompute the new ratios for the records that just contain "Headline" in the concatenated field.
The solution in cases like this depends on the data you are trying to show. In this example, you could either replace the CTR metric with a non-ratio metric, such as Impressions. Or, instead of filtering on the concatenated Campaign : Click Type field, put the filter on just the standard Click Type field, which would achieve the same result.
The chart now works:
Example reportsExample reports:
- Welcome to Data Studio! (Start here)
- Filter Control Example
- Data coloring example
- Design reports using the layout grid
- Running calculations
- Understand report-level component position
- Reverse Axes
- Data blending example
- Lat, Long, and City Name in Geo Charts
- HYPERLINK Example
- Image Link Example
- Drill Down Table Example
Welcome to Data Studio! (Start here)
Learn how to view, edit and create a Data Studio report.
Filter Control Example
This report illustrates various styles of filter controls. The data comes from two sources, Google Sheets and Google Analytics . The filter controls at the top of the report show how various filter control configurations work.
Data coloring example
This report demonstrates 2 ways to color your data:
- Color by the dimension value. E.g., "France" is always represented in red. This is the default behavior for new reports.
- Color by the order of the dimension data. E.g., the first series is always blue.
Design reports using the layout grid
This report illustrates using adjustable grid sizes to achieve a more standardized report design.
The minimum canvas grid size is 10 px. You can adjust this with the Grid Settings options. Increasing the grid size makes it easier to layout your charts, controls, and other components.This report includes a Google Analytics data control. You can use it as a template for your Analytics data.
Running calculations compute summary results for each record of data, helping you see how each contributes to the whole picture.
Understand report-level component position
Why do my report-level components disappear?
Change the Report-level Component Position property to "Top."
This report demonstrates controlling the X and Y axis direction in Cartesian charts.
Data blending example
Blending lets you create charts based on multiple data sources. For example, you can blend two different Google Analytics data sources to measure the performance of your app and website in a single visualization.
Lat, Long, and City Name in Geo Charts
How can I use latitude and longitude in a geo map to provide maximum resolution in my data, but still see city names in the chart?
Use a calculated field to combine latitude and longitude with the city name. Example: CONCAT(Lat Long , "(", City,")")
This report demonstrates the Hyperlink field. Hyperlinks create clickable links in tables.
To create a Hyperlink field, use the
HYPERLINKfunction, and provide a URL as the first parameter, and a Text field, or formula that returns Text, as the second
Image Link Example
This report demonstrates the Image Link field. Image Links create clickable images in tables.
To create an Image Link field, use the
HYPERLINKfunction, and provide a URL as the first parameter, and an Image field, or an IMAGE function with a valid link to an image, as the second
Drill Down Table Example
Use a custom bookmark to create drill down fields in a table.
"Drilling down" into your data lets you go from a broader or more general view to a greater level of granularity.
Although this isn't a native feature of Data Studio, you can create simple drill down behavior by combining custom bookmarks with the
HYPERLINKfunction. Learn more.
If your reports or data sources aren't showing all the information you expect, check the following:
Missing data in reports
Is the data cached?
If the report is missing some recently added data, you can refresh the cache by editing your report and in the upper right, clicking . You must be an editor of the report to use this feature.
Is the data filtered?
Edit the report, then click Resources > Manage Added Filters to see if there are any filter properties in the report. If there are, check their setup to make sure that's not the cause of the missing data.
Missing fields in data sources
Is the data source out of sync with the data set?
If the data source is missing some recently added fields (columns), you can add them by editing the data source, then in the lower left, clicking REFRESH FIELDS. Learn more.
Is your Google Sheets data source missing rows or column?
If the rows (data) or columns (fields) of your Sheets data source are still not appearing after refreshing the fields as described above, make sure the data source connection includes the proper range and options. You must be the owner of the data source to do this.
- Edit the data source.
- On the left, click EDIT CONNECTION.
- Review the connection options, on the right. Be sure any specified range includes all your data, and that you are including hidden and filtered fields, if appropriate.
Sheets connector options.
Is it a connector limitation?
Connectors based on fixed schemas, which includes many of the Google product connectors, may not deliver all the fields of the underlying data set. If your data source appears to be missing fields that you know are in the original product, it's possible that field is not supported in Data Studio. You can check the issue tracker to see if the field has already been requested, or if not, file a feature request.
Graph negative numbers
As of 2019-01-16, the default value for Axis min is (auto), so you'll only need to change this for older charts.
Previously, the default Axis min setting was "0."
To display metrics with negative values in a chart, set the Axis min option to (auto):
- Edit your report.
- Select the chart.
- Select the STYLE panel.
- Locate the main axis settings: for default charts, this will be the left axis, but could also be right or bottom, depending on how you've customized your chart.
- Change the Axis min value to (auto) by deleting the current setting.
Here's an example of how charts look with Axis min 0 versus Axis min (auto):
- Data in the top charts contains mixed negative and positive values.
- Charts on the left are set to use Axis min: 0.
- Charts on the right use Axis min: (auto).
- Note how the last two dimension values ("Green" and "Blue") appear in the charts.
- Data in the middle charts only contains positive values. In this case, there is no difference between 0 and (auto) settings.
- Data in the bottom charts only contains negative values. Here, using the (auto) setting on the right provides the correct visualization.
Since its initial release, Data Studio has used Google Drive to store and share reports, data sources, and explorations. To enable a number of product improvements, we will be migrating all Data Studio files away from Drive.
No action required
You do not need to take any action.
You’ll be able to keep using Data Studio the same way you are today, with the same sharing and real-time collaboration features you’re used to.
For consumers, your existing Data Studio files will be migrated in the next few weeks. Anyone who currently has access to reports or data sources you’ve created will continue to have access.
For G Suite users, your Data Studio files will not be migrated yet. G Suite admins will receive further instructions about how to migrate their users.
What’s changing and why
Once the migration is completed, files you create in Data Studio will no longer be accessible through Google Drive. This change will facilitate product improvements, like better search, a simplified process for connecting to data, and improved admin and file management capabilities. If you’ve been using Drive to organize your Data Studio reports, you’ll soon be able to do this directly in Data Studio.
Revoke data source credentials
We’ve made it easier to control how data sources work with your credentials. When you create a data source with Owner’s credentials, that data source will be able to retrieve data using your credentials until you choose otherwise. Learn more about data source credentials.
New sharing dialog
You can still share new and migrated files in the same ways as in Drive, but once your files are migrated, you'll notice some differences in how you manage sharing settings. Here's the new Share with others dialog:
- The Add people tab lets you enter individual email addresses or Google Group names to share the report with.
- The default permission is Can View. To allow other people to edit your report, change this to Can Edit.
- Click Send to share the report and notify your viewers or editors. To share without sending a notification, uncheck Notify people.
- The Manage access tab lets you configure advanced sharing settings.
- Use the link sharing options to share your report more widely than using individual addresses or groups.
- You can specify different access options for individual people or groups.
- Use these options to prevent other editors from changing access, adding people, or prevent viewers from downloading, printing, or copying your file.
Learn more about how to share reports and data sources.
Dimension and metric improvements
We recently (October 2019) made improvements to how fields in your data sources are defined and aggregated by default. These changes make it easier to model your data, and make calculated fields more robust.
You don't need to take any action. Charts and calculated fields used in your reports will work as before the upgrade.
We've made the following improvements:
Refined the definitions of "dimension" and "metric"
Dimension -- A set of unaggregated values you can group your data by. As before, dimensions in your data source appear as green fields.
Metric -- A specific aggregation that you can apply to a set of values. Because a metric itself has no defined set of values, you can’t group by it. As before, metrics in your data source appear as blue fields.
Introduced default aggregation
We've changed the "Aggregation" column in a data source to "Default aggregation". This is the aggregation method that is used when you include that field in a chart in a Data Studio report, unless you override it.
For data sources based on flexible schema data sets, such as Google Sheets, BigQuery, CSV file upload, etc., fields containing unaggregated numeric data appear as dimensions with a default aggregation of Sum. You can use these fields as either dimensions or a metrics in your charts. If used as a metric, the values are summed, however, you can still change the aggregation method in the chart itself.
Metrics in your data source always have a default aggregation of Auto, which can't be changed. This includes already-aggregated data from fixed schema data sources, such Google Analytics and Ads, as well as calculated fields you create that include a specific aggregation method.Because of these changes, you may notice that your data source now has more green fields than blue fields. This has no effect on your existing reports, and is actually a benefit, as it allows you to more easily use these fields as either a dimension or a metric.
Made calculated fields more robust
If you create calculated fields without specifying an aggregation function in the formula, the result is an unaggregated dimension. To create an aggregated metric, include the desired aggregation function. For example:
Profit / Revenueresults in a numeric dimension. You can set the field's aggregation manually in the data source, or in charts that use this field.
SUM(Profit) / SUM(Revenue)results in a metric. The aggregation is Auto, and won't change even if the underlying fields' default aggregation changes.
Learn more about calculated fields.
Why do I see "deprecated" fields in my data source?
A small number of older data sources may contain fields marked as "deprecated." These are unaggregated numeric fields that have been copied and converted to dimensions with a default aggregation of Sum. The original fields still exist in the data source and still appear as before the upgrade in any charts or controls, but you won't be able to add them to new components.We recommend you NOT delete any deprecated fields unless you're certain they aren't used in a calculated field. If the field is used, you'll need to edit the calculated field formula to use the new upgraded fields and specify any aggregation functions required to achieve the correct result.
Example of an upgraded data source.
In the example above, the original Price and Qty Sold fields have been deprecated (rows 8 and 11). Their original aggregation of Sum has been set to Auto (Sum). New, upgraded versions of those fields have been added to the data source (rows 7 and 10). These appear as numeric dimensions (green) with default aggregations of Sum.
The Order Total calculated field (row 9) still references the deprecated fields and still works as before. New calculations based on Price or Qty Sold should use the new fields, not the deprecated versions.