Modelling data sources: Writing formulas using results references

 

Our recent redesign simplifies the data transfer experience by combining raw and modelled data sources into a single object - data feeds.
If you see Data Feeds in the left navigation sidebar, go to this article.
These new features are being released gradually. If you don't see them in your account yet - no worries - they’re coming soon!

 

You can use results references when modelling data sources. Results references enable you to refer to columns within the modeller after date properties and formulas are applied to them. To use a results reference, type & into the formula bar and select from the list that displays.

This article includes the following examples of using results references when modelling data sources for custom metrics or Klips:

Adding column data together to create a total column

In the modeller, you can use simple math (addition) and results references in your formula to add columns of data together to create a single, totalled column.

The data source to be modelled includes separate columns for sales in January, February, and March:

The following example uses simple math (addition) and results references to add three columns of data together to create a single totalled column.

To add data from separate columns together into one totalled column:

  1. In the left navigation sidebar, click Data Sources and select the modelled data source you want to open.

If you don’t see the above option, go to the bottom of the left navigation sidebar and click your Account Name > Data Sources instead.

  1. On the About this Modelled Data Source page, click Edit Model.
  2. Click the + column. (It displays after the last column in your modelled data source.)

This adds a new, empty column and opens the formula bar.

  1. Click in the new column’s header and enter a new name “Q1 Sales”.
  2. With the new column selected, enter the formula (&Jan Sales+&Feb Sales+Mar Sales) into the formula bar, and click Enter.

Your new column is populated with the consolidated data, according to the formula you entered.

  1. Click Save.

Consolidating related data

When modelling a data source that you will use to build a metric or a Klip, you can use the CONCAT function and a results reference in your formula to consolidate related data from separate columns into a single column.

The data source to be modelled includes separate columns for sales rep names and sales rep IDs:

The following example uses the CONCAT function and a results reference to combine sales rep names (from one column) and sales rep ID numbers (from a different column) into a single, consolidated column that includes the sales rep names alongside their ID numbers.

To consolidate data using CONCAT and a results reference:

  1. In the left navigation sidebar, click Data Sources and select the modelled data source you want to open.

If you don’t see the above option, go to the bottom of the left navigation sidebar and click your Account Name > Data Sources instead.

  1. On the About this Modelled Data Source page, click Edit Model.
  2. Click the + column. (It displays after the last column in your modelled data source.)

This adds a new, empty column and opens the formula bar.

  1. Click in the new column’s header and enter a new name “Name and ID Number”.
  2. With the new column selected, enter the formula CONCAT(&Name," ",&ID Number) into the formula bar, and click Enter.

Your new column is populated with the consolidated data, according to the formula you entered.

  1. Click Save.

Calculating the percentage of change

The following example describes how to use results references to calculate the percentage of change between two periods of time. 

Note: This method works well if you're going to use the modelled data source for Klips. However, if you're going to use it for custom metrics that include a rate or percentage calculation (for example, Lifetime Value to Cost of Acquisition ratio), due to differences in aggregation methods for calculations, you should perform those calculations by building a new, calculated metric instead of using results references in the modeller.

The data source to be modelled includes separate columns for sales in January and February:

In this example, a column is added to the data source that includes the percentage of change between sales in January and sales in February.

To calculate the percentage of change:

  1. In the left navigation sidebar, click Data Sources and select the modelled data source you want to open.

If you don’t see the above option, go to the bottom of the left navigation sidebar and click your Account Name > Data Sources instead.

  1. On the About this Modelled Data Source page, click Edit Model.
  2. Click the + column. (It displays after the last column in your modelled data source.)

This adds a new, empty column and opens the formula bar.

  1. Click in the new column’s header and enter a new name “% Change”.
  2. With the new column selected, enter the formula (&February Sales-&January Sales)/&January Sales into the formula bar, and click Enter.

Your new column is populated with the percentage of change data, according to the formula you entered.

  1. Click Save.

Related articles

Have more questions? Submit a request