Derived Columns

3. Select a Dataset for the new Derived Column in the Column of field.
4. Then, select if you want the derived column to be a Measure or Attribute.
5. Set the Default Aggregation.
6. Using blocks, you can define the computation for your derived column.

7. As you work on the definition of the Derived Column, click Run Derived Column to preview the computed values. You can also choose a different view for your visualization from the chart drop-down.
8. Specify a name and description for the Derived Column.
9. Click Save to save the Derived Column's definition in the Catalog.
10. The new Derived Column can now be used anywhere, e.g. inside exploration templates, block editor, etc.

Types of Derived Columns​

Similar to Cohorts, there are also a bunch of templates under Derived Columns that allow you to get started easily and quickly. Choosing a template is the first step in creating a new derived column and you can change this at any time without losing any work. Let's look at how each of these templates can be configured.

This template allows you to calculate an aggregate quantity for each actor. As an example, let us create a derived column to calculate the sign-up date.

1. Navigate to + on the left navigation panel, click Derived Column and choose Aggregation/First/Last.
2. For the Column of field, choose a dataset from the drop-down. Here, we choose the Users dataset.
3. Then, choose if this will be a Measure or an Attribute. Here, we choose Measure.
4. Next, choose the default aggregation from the drop-down. Here, we choose avg.
5. Under Blocks, choose the Aggregator for the aggregate block and also select a value from the drop-down. For the example, let us choose min as the aggregator and set event_ts as the value. Also, group the result by Users.
6. Click Run Derived Column and your result will be displayed in tabular form. You can also change the chart type from the drop-down.
7. Give your derived column a name and add a description (optional).
8. Click Save to save all your changes.

Conditional on Property​

This template allows you to assign values based on logical conditions. As an example, let us create a derived column to determine if users like/dislike the content of the website.

1. Navigate to + on the left navigation panel, click Derived Column and choose Conditional on Property.
2. For the Column of field, choose a dataset from the drop-down. Here, we choose the Content dataset.
3. Next, choose the default aggregation from the drop-down. Here, we choose avg.
4. Then, we configure the blocks. We have an If/Then/Else block in our example here - we set the condition : if vote_avg >= 7.5 then, the value is Thumbs Up else the value will be Thumbs Down.
5. Click Run Derived Column and your result will be displayed in the form of a pie chart. You can also change the chart type from the drop-down.
6. Give your derived column a name and add a description (optional).
7. Click Save to save all your changes.

Formula​

This template allows you to write custom logic using the NetScript forumla language. Follow the steps below to create a template of this type.

1. Navigate to + on the left navigation panel, click Derived Column and choose Formula.
2. For the Column of field, choose a dataset from the drop-down. Here, we choose the Campaigns dataset.
3. Choose Attribute in the Measure or Attribute field.
4. Next, choose the default aggregation from the drop-down. Here, we choose none.
5. As the first step, let us enter the formula to calculate Numeric ID:

   id = Data.Campaigns.id;
   if not(isnull(id) or strlen(id) == 0) then to_int(id) end

6. Next, add a bin block to categorize the types. Choose Numeric ID as the value. Let us add 3 bins:
   • Bin 1 : Social Media where x < 151.
   • Bin 2 : Organic Search where 151 <= x < 501
   • Bin 3 : Paid Search where x >= 501
7. Enable the output toggle for this block.
8. Click Run Derived Column and your result will be displayed. You can also change the chart type from the drop-down.
9. Give your derived column a name and add a description (optional).
10. Click Save to save all your changes.

Custom​

This template allows you to calculate values by composing any number of blocks. As an example, let us create a derived column to determine Incremental Campaign Revenue.

1. Navigate to + on the left navigation panel, click Derived Column and choose Custom.
2. For the Column of field, choose a dataset from the drop-down. Here, we choose the Product Events dataset.
3. Choose Measure in the Measure or Attribute field.
4. Next, choose the default aggregation from the drop-down. Here, we choose avg.
5. The next step would be adding our custom blocks. As the first step, let us add an aggregate block to calculate the number of Ads Viewed. Choose count as the aggregator and select the Product Events dataset. Let us also group the result by Campaigns.
6. Next, add a formula block to calculate campaign revenue. The formula is as follows:

   "Ads Viewed" * Data.Campaigns.cpm / 1000

7. Click Run Derived Column and your result will be displayed. You can also change the chart type from the drop-down.
8. Give your derived column a name and add a description (optional).
9. Click Save to save all your changes.

Conditional on aggregate​

This template allows you to calculate an aggregate quantity per actor, and assign each actor to a bucket based on the value of the aggregate.

1. Navigate to + on the left navigation panel, click Derived Column and choose Conditional on aggregate.
2. For the Column of field, choose a dataset from the drop-down. Here, we choose the Users dataset.
3. Choose Measure in the Measure or Attribute field.
4. Next, choose the default aggregation from the drop-down. Here, we choose avg.
5. As an example, let us determine the number of high value users of the app. High value users are the ones that engage actively by performing actions such as making a purchase or playing content on the app.
6. First, let us use the If/Then/Else block to categorize High Value Users. Set the condition as follows: where event_type in Play Content, Make Purchase then High Value User.
7. Next, we add an Aggregate block to determine the number of high value users. Choose count as the aggregator and the previously created If/Then/Else block as the value.
8. Click Run Derived Column and your result will be displayed. You can also change the chart type from the drop-down.
9. Give your derived column a name and add a description (optional).
10. Click Save to save all your changes.

Previous/Next Value​

This template allows you to look up the value of the previous or next event for each actor, optionally including only certain events.

1. Navigate to + on the left navigation panel, click Derived Column and choose Previous/Next Value.
2. For the Column of field, choose a dataset from the drop-down. Here, we choose the Product Events dataset.
3. Choose Attribute in the Measure or Attribute field.
4. Next, set the default aggregation to none.
5. As an example, let us consider that we want to determine the previous value of the Last Campaign Type in the Product Events time series.
6. In the Previous Value Lookup block, choose previous from the drop-down and choose Product Events as the value. For 'output specific property, choose Type (a custom derived column) as the value. You can also add for each conditions if necessary - here, let us add the Users dataset.
7. Click Run Derived Column and your result will be displayed. You can also change the chart type from the drop-down.
8. Give your derived column a name and add a description (optional).
9. Click Save to save all your changes.

Time Since​

This template allows you to calculate how long ago a timestamp property is.

1. Navigate to + on the left navigation panel, click Derived Column and choose Time Since.
2. For the Column of field, choose a dataset from the drop-down. Here, we choose the Users dataset.
3. Choose Measure in the Measure or Attribute field.
4. Next, choose the default aggregation from the drop-down. Here, we choose avg.
5. As an example, let us calculate the time since the last signup. In the Timestamp block, choose the Sign Up Date derived column using the drop-down.
6. Then, enter the logic in the formula block. Here, we use now - Timestamp.
7. Click Run Derived Column and your result will be displayed. You can also change the chart type from the drop-down.
8. Give your derived column a name and add a description (optional).
9. Click Save to save all your changes.

View Derived Columns​

Derived Columns Page​

1. In the Left Navigation Panel, select the icon which says Derived Columns.
2. The resulting page lists a set of templates at the top for defining new derived columns. Underneath that is a search bar to find individual derived columns by name. Below that is a paginated listing of all Derived Columns in the system, pooled across all datasets.
3. Each Derived Column entry in the listing contains the following information:
   1. Name
   2. Description (if any)
   3. Type (this refers to the template used when defining the Derived Column).
   4. Updated By
   5. Updated At

Dataset Page​

Alternatively, all derived columns for a given dataset can also be accessed from inside the dataset page by clicking on the "Derived Columns" tab there.