Skip to content

Clean and transform

This page does not apply to the environment you selected. It covers: Web app.

Between the sources and the Datama block, action blocks shape the data: pick an action from the menu of the block you want to transform.

Action Use it to
Clean Reorder, rename, retype or remove columns; add calculated or clustering fields.
Append Stack two sources one below the other.
Join Add the columns of a source to another, on a common column.
Filter Keep only the rows you need.
Pivot and unpivot Turn values into columns, or columns into values.
Aggregate Group rows by keys, like GROUP BY in SQL.
What if Add a simulated scenario to the data.

Clean lists the columns coming from the previous block. Use it to make two sources match before you append them, or to put the dimensions before the metrics.

  • Drag and drop a column to move it.
  • Edit its name to rename it.
  • Change its type. If a value cannot be converted, for example a product name to a date, the block shows a warning.
  • Click × to remove it.

Create a calculated field adds a column computed from the others, with the operators +, -, *, / and the functions below. Click Ok to create it.

New fields are added at the end of the columns. If the new field is a dimension, add another Clean block to move it before the metrics.

Create a clustering field groups the values of a column into a new column:

  • Column name: name of the new column;
  • Column: the column to group;
  • Clustering Method and its settings: see Clustering;
  • Metric used for clustering: the metric that weighs the values.

Append merges two flows into one, by putting one table below the other. It is not a join.

Columns with the same name are merged, case included. The columns found in one source only are left empty (null) in the rows of the other.

When two sources do not have the same dimensions, Fill in null values spreads the metrics of one source over the dimensions of the other.

Datama pairs the metrics of the two sources, for example Traffic IT with Sessions GA: Traffic IT is then spread over the countries and devices found in Google Analytics, in proportion of Sessions GA. Edit, add or remove pairs as needed. The total of each metric does not change.

Distribute missing segments (off by default) also spreads a segment found in one source only, evenly over the rows to fill.

Join adds the columns of a table to another when they share at least one column. Append adds rows; join adds columns. The join is an inner join by default; you can choose another type.

Click New filter, choose a column and the type of filter, then the values. Text, number and date columns have their own filter types.

Inside a filter, the values are combined with OR. Chain several filters to combine them with AND.

When a column has too many unique values, the filter does not suggest them; type the values instead.

  • Pivot groups the rows by one or more categories, and aggregates the values (sum, average or other statistics).
  • Unpivot turns the values of a column into columns. For example, unpivot Gender with Sessions as aggregated column to get Sessions.female and Sessions.male.

Aggregate reduces the size of a dataset by grouping rows, like GROUP BY in SQL.

  • Keys: the columns to group by. The result has one row per combination of their values.
  • Aggregation: the columns to compute for each group, and the function of each one. The default is sum for numbers and count for other types.
  • Click × to remove a column.

Move a column between the two sections by drag and drop or with the arrows.

The What if block adds simulated data to the dataset: a Baseline (for example last year) and the Impacted KPI(s), with a relative (+10%) or absolute (+0.01) change on a scope. Datama adds a Scenario column with the baseline, the scenario, and the out of scope data, ready to compare in Compare.

The simulation is linear: doubling a price doubles the revenue, with no elasticity. Keep it for sizing or small changes. To simulate inside the analysis instead, see What-if scenarios.

Column names are written between brackets: [revenue].

Function Description Example
SUM([col1], [col2]…) Sum of two or more columns, row by row. SUM([revenue], [bonus]): 10 and 30 give 40.
SUM_COLUMN([col]) Total of the column, on every row. SUM_COLUMN([revenue]): 10, 20, 30 give 60, 60, 60.
MEAN([col1], [col2]…) Average of two or more columns, row by row. MEAN([revenue], [bonus]): 10 and 30 give 20.
Function Description Example
IF(condition, then [, otherwise]) A value depending on a condition. IF([income] < 0, 'in-debt', 'not-in-debt')
AND(condition1, condition2) True when both conditions are true. AND([name] = 'john', [income] > [average])
OR(condition1, condition2) True when one of the conditions is true. OR([name] = 'kenny', [revenue] > [average])
NOT([col]) The opposite of a true/false value. NOT([boolean]): true gives false.
IS_NULL([col]) True when the value is empty. IS_NULL('Value') gives false.
CASE(condition1, value1, …, else) The value of the first true condition, else the last value (empty by default). CASE([Value] > 150, 'Very good', [Value] > 100, 'Good', [Value] < 50, 'Bad', 'Normal')
Function Description Example
CONCAT([col1], [col2]) Joins two texts. Also written [col1] & [col2]. CONCAT([firstName], [lastName])
REGEX_REPLACE([col], regex, 'replacement') Replaces the parts that match the regex. REGEX_REPLACE([text], '\\s+', ' ')
REGEX_EXTRACT([col], '(pattern)') Extracts the part that matches, or null. REGEX_EXTRACT([names], '(star)')
REGEX_CONTAINS([col], 'pattern') True when the text contains the pattern. REGEX_CONTAINS([names], 'star')
Function Description Example
DATE_FORMAT([col], 'format') Formats a date. DATE_FORMAT([Date], '%Y-%m-%d')
YEAR([col]) Year of a date. YEAR('2024-10-22') gives 2024.
MONTH([col]) Month of a date. MONTH('2024-10-22') gives 10.
DAY([col]) Day of the month. DAY('2024-10-22') gives 22.
DAY_OF_WEEK([col]) Day of the week, from 1 to 7. DAY_OF_WEEK('2024-10-22') gives 3.
WEEK([col]) Week of the year, weeks starting on Sunday. WEEK('2024-10-22') gives 42.
ISO_WEEK([col]) ISO 8601 week. ISO_WEEK('2024-10-22') gives 43.

With BigQuery, Snowflake and Amazon Redshift, Table connect reads a table of your warehouse without a query. Datama only downloads the data your analysis needs, and the filters you set later in the analysis are applied in the warehouse.

  1. Connect the source, choose the project, then click Use Table Connect Instead.
  2. Choose the dimensions and metrics of the table. Double-click a pill to switch it between dimension and metric.
  3. Define the KPI and its market equation.
  4. Set the filters: date range, comparison… Data is downloaded only once they are set.
  5. Click Done: the analysis opens. You can change what you compare from the analysis, or from the block.

When the first comparison dimension is a date, Group dates into periods is on by default: each compared range comes back as one period label instead of every date, which transfers much less data. Turn it off when the analysis needs every date, for example in Detect.