1. Introduction
What are computed fields in Octolis?
- Very useful metrics to get more insights about your data.
- They can be simple, such as the contact “lifetime value”, or advanced, such as the contact “RFM segment”.
- They can be created either in no code or in SQL.
- Below is a brief overview of the different methods available to create your computed fields in Octolis.
Method | Definition | When to use it |
No code builder | Creation of simple calculated fields, in no-code, from a dataset's Transformations menu. This involves adding a column based on data from another dataset. | Default method to be used as soon as possible. |
Custom SQL “simple”
Using one dataset columns | Creation of calculated fields using a SQL query. This involves adding a column to a dataset based on the data contained in that dataset. | Method to be used to create a calculated field in a dataset based on columns from the same dataset. |
Custom SQL “advanced”
Using several datasets | Creation of calculated fields via a SQL script that generates a new dataset containing our calculated fields. | Method to be used for calculated fields too complex to be handled by the no-code builder. |
Scoring Library [Available Soon] | [Available Soon] | [Available Soon] |
2. No code Builder
For most fields, you can directly use the computed fields no code builder.
Let's imagine for example that you want to add a column: "Total number of items purchased by the customer" to your Customers dataset, containing all the info I got about your clients.
To do so, you can either follow the step-by-step guide below or consult the demo video hereafter.
Demo Video :
Step 1 - Select the dataset you want to add a computed field to
After selecting your dataset in the "Dataset" menu, you have to go into the "Transform" section and select "add computed fields" in the item banner at the right of the interface.
Step 2 - Select the complementary dataset needed for your calculated field
You now have to select on which Dataset should your Computed Fields be based.
Regarding our example, we need to extract the information about the number of orders made by customers from your "Orders Dataset”, so you have to select this one.
You must also specify which is the key column and which name it has in each dataset. It has to be a column shared by the two datasets enabling to identify independently each record. This column will allow ensuring the joining between the two datasets.
In our case, it is the “user_id” column, included in both datasets (under the name “contact_id” in the “Orders Dataset”).
Then you can click on “Add new Computed Field”.
Step 3 - Add the computed field
Now, a window invites you to select the column containing the data you want to use and the function to apply to it to create your calculated field.
In our case, the source column from the “Orders Dataset” will be the “number of items” and the function we need to apply to it is simply the “count” function.
It is also possible to apply advanced filters to modify records using other columns from the two datasets. For instance, you can filter the records by date period.
Step 4 - Check the update of your dataset
By going into your dataset, you can see that the computed field has been added under the name “number_of_count_alltime”.
Here we can see the number of orders made by each customer (only 0 or 1 order per customer for the displayed data part).
3. Custom SQL “simple” (Using one dataset columns)
I you want to add a column in a dataset based on other columns of this dataset, you may use our “Custom SQL” builder.
You only have to select the data type you want for your new column, its name, and the SQL formula you want to get the result from.
Example of a custom SQL field:
4. Custom SQL “advanced” (Using several datasets)
For more advanced computed fields, you can use SQL scripts to create a new dataset containing the computed column you want to add, in the SQL dataset builder.
You can see below an example of a construction of calculated columns (columns corresponding to an RFM segmentation of customers) from an SQL script.
5. Use our Scoring Library [Available Soon]
You can find below a non-exhaustive review of possible computed fields that could be added to your dataset.
All you have to do is choose the ones that are relevant to your business.
Octolis Scoring Library [Available Soon]: