Model your data

Manipulating data to support your business objectives is known as data modeling.

In Looker Studio, the fields in a data source have certain properties, such as name, data type, and aggregation. These properties are provided by the connector that was used when the data source was created.

This article describes these data source field properties and how you can customize them to ensure that your reports display the data the way you want.

Before you begin, you should be familiar with the following topics:

• How to edit a data source
• How to configure report components

In this article:

• Types of fields
• Dimensions and metrics in your data source
• Field properties
• Calculated fields, type, and aggregation
• Dimensions and metrics in reports
• Overriding default type and aggregation in charts

Types of fields

A data source can contain the following field types:

• Dimensions describe or categorize your data. Dimensions contain unaggregated data. Dimensions appear as green fields in the data source editor and report properties panel.
  
  Adding dimensions to a chart groups the data by those dimensions. Campaign name, Product ID, and Country are all examples of dimensions that you might use to group the information in a chart. Note that any type of data can be a dimension, including a column of unaggregated numbers.
  
  Learn more about dimensions.
• Metrics measure your dimensions. Metrics contain aggregated data. Metrics appear as blue fields in the data source editor and report properties panel.
  
  A metric is the result of applying an aggregation to a set of values,. That aggregation could come from the underlying data set or could be the result of implicitly or explicitly applying an aggregation function, such as COUNT(), SUM(), or AVG().
  
  Learn more about metrics.
• Calculated fields are fields that you create by applying functions, operators, and/or branching logic to your data. A calculated field appears as a metric or a dimension, depending on the output of the formula.
  
  Learn more about calculated fields.
• Parameters store user-defined data. You can use parameters to customize or personalize your reports and data sources based on user input or variables that are defined in the underlying data set, such as a BigQuery custom query parameter.
  
  Learn more about parameters.

Field properties

All fields have the following properties that are displayed on the data source page for a data set. These properties are derived from the underlying data set, and are listed in the following subsections.

Field

The Field column displays the name of the fields in a data set. The name appears in any chart that uses a field. If the name is too long to display completely, hover over it until the full name appears.

To change a field's name, click the field name and type in a new name.

Type

The Type column displays a field's data type. The data type tells Looker Studio what kind of data to expect when processing the field. Data type determines how the data appears in your reports along with which operations are allowed for it and which are not. For example, you can't apply an arithmetic function to a Text field or use a Number field as the date range dimension in a report.

Use the drop-down menu to change a field's data type.

Learn more about data types.

Default Aggregation

Aggregation is the method by which a field's data is summarized. You can see a field's default aggregation method in the data source Default Aggregation column. Default aggregation is applied when you use a field as a metric in a chart. If you use a field as a dimension, then you'll group a chart by that field instead. Report editors can change this at the chart level.

There are three possible default aggregations, depending on where the data comes from and how it's defined in the underlying data set:

1. None. This is the default aggregation for all dimensions that contain non-numeric data. Fields with an aggregation of None are considered dimensions in Looker Studio.

You can create metrics from dimensions by applying an aggregation function in a calculated field. For example, COUNT(Customer Name) returns the number of non-unique customers in your data. You can't, however, apply math functions to non-numeric dimensions: SUM(Customer Name) returns an error.

2. Sum. This is the default aggregation for all dimensions that contain unaggregated, numeric data. Generally, this applies to number columns from tabular data sets, such as Google Sheets, CSV files, and BigQuery tables. Fields with an aggregation of Sum can be used either as dimensions or as metrics in Looker Studio.
3. Auto. This is the default aggregation for all metrics that come from the data set, the connector, or a calculated field. Auto means that field's aggregation method is fixed and can't be changed. Auto fields are always metrics in Looker Studio.

Learn more about aggregation.

Description

Some data sources provide annotations, such as the underlying field name or a description of the field. The Description column displays these descriptions. You can edit or add a description by clicking the text box in the Description column. For Looker and Search Ads 360 connections, the description is automatically populated from the data source.

You can display descriptions in table charts by enabling the Show field descriptions style option. Show field descriptions is automatically enabled for charts that are connected to a Looker or Search Ads 360 data source.  

Calculated fields, type, and aggregation

Calculated fields let you create new fields that are derived from your data. Calculated fields appear in the field list with an fx symbol.

The formula that you use to create the field determines the data type and whether the result is an unaggregated dimension or an aggregated metric. For example, a formula that uses CONCAT() to build a URL from product codes creates a dimension of type URL with an aggregation of None.

Or, to create a field that displays a profit margin, you could use the formula SUM(Profit) / SUM(Sales). The field that is created would be a type Number wiith an aggregation of Auto.

Learn more about calculated fields.

Dimensions and metrics in reports

Adding a data source to a report lets anyone who can edit the report use the data source fields to build charts and configure controls.

Dimensions and metrics in charts

Dimensions group data, with each dimension in a chart providing more and more granular detail. Metrics in charts are always aggregated numbers: the level of aggregation depends on the dimensions present, if any.

Learn how to add, replace, or remove fields.

For example, a table with a Country dimension and a single metric groups that metric by country. Adding the City dimension provides a further breakdown, first by country, then by city.

Charts with no dimensions, such as the scorecard or a table with all dimensions removed, display the ungrouped value of a metric.

Overriding default type and aggregation in charts

You can change default field definitions at the chart level in the ways described in the following subsections.

Use a dimension as a metric

• Using a non-numeric dimension field as a metric applies the Count Distinct (CTD) aggregation method to that data.
• Using a numeric dimension field as a metric applies the default aggregation that was specified in the data source, or Sum, if no default aggregation was specified.

Manually edit the field

In the properties panel, click a field's edit pencil to modify that field's definition. You can change the data type and aggregation method (for non-Auto aggregated fields), and apply comparison and running calculations.

To prevent report editors from changing these defaults, edit the data source and turn off Field Editing in Reports. Learn more about editing fields.