NetScript language reference

35 min

NetScript is the formula language in Optimizely Analytics. Use it to write metrics and calculations, such as revenue by ship mode or orders over time. Each NetScript expression describes a Structured Query Language (SQL) query, and Analytics runs that query against your data warehouse. This article explains how NetScript turns expressions into SQL, so you know what each formula returns.

Before you start, make sure your Analytics app includes the datasets and columns that your formulas reference. Familiarity with SQL GROUP BY clauses and joins helps you follow the examples.

Syntax rules

NetScript follows these syntax rules:

Function names are case-insensitive. For example, countDistinct and countdistinct are synonymous.

Most built-in functions also accept their name without underscores. For example, extract_week and extractweek are synonymous.

Time zone-sensitive functions use Pacific Time (PT) by default. Each Analytics app has its own time zone setting. The extract_* functions do not convert time zones.

Most functions and aggregations accept function syntax, such as round(x), or pipe syntax, such as x | round. Both forms return the same result. Functions that take two inputs, such as percentile, accept only function syntax.

A variable assignment ends with a semicolon (;).

Quads, grains, and measures

Every NetScript expression is a quad. A quad represents a SQL query rather than the data that the query returns. The following terms describe the parts of a quad:

Dimension – A column that groups the results, such as ship mode or order date. A dimension works like a column in a SQL GROUP BY clause.

Grain – The set of dimensions that a quad aggregates at. A quad with an empty grain returns a single row.

Measure – A value that a quad calculates, such as the total order amount. Most quads have one measure.

The examples in this article use the following notation for a quad named P:

P.Query – The SQL query that P represents.

P.Grain – The set of dimensions that P aggregates at. In SQL terms, the grain matches the GROUP BY expressions.

P.Measures – The set of measures that P calculates. In SQL terms, the measures match the aggregated expressions.

Sample schema for the examples

The examples in this article use a sample ecommerce schema with the following tables:

Orders – Each record is an order, with foreign keys that link to Customers and Product.

LineItems – Each record is a line item within an order, with a foreign key that links to Orders.

Customers – Each record is a customer, with account details such as name and address.

Product – Each record is a product, with catalog details such as product name and category.

Build basic quads

The simplest quads return a constant, a dataset, or a column. Combine them with operators to calculate values for each row.

Return a constant

The quad 2 represents the constant 2. It is the simplest quad and corresponds to the following SQL query:

select 2

The measure is the constant 2, and the grain is empty.

Reference a dataset

The quad Orders represents the rows of the Orders dataset. The dataset must exist in your Analytics app, or the quad returns an error.

The quad references the dataset itself rather than a column. As a result, the SQL query returns the primary key of each row in Orders.

This query does not aggregate any data. NetScript treats it as aggregated at the grain of each row, which is the finest possible grain. The following SQL query shows this mapping:

select OrderKey as dim0, OrderKey as measure0 from Orders

Reference a column

The quad Orders.Amount references the Amount column of the Orders dataset. Amount must be a valid column in Orders. The quad maps to the following SQL query:

select OrderKey as dim0, Amount as measure0 from Orders

Like the dataset quad, NetScript treats this query as aggregated at the grain of each row in Orders.

Multiply a column by a constant

The expression 2 * Orders.Amount uses a binary operator. A binary operator such as * represents a join between the subqueries for each operand. NetScript joins the two subqueries on their shared dimensions.

The quad 2 has no dimensions, so the join is a cross join. A cross join pairs every row of one side with every row of the other. The following SQL query shows the unoptimized form, followed by the equivalent optimized form:

with A as select 2 as measure0,
    B as select OrderKey as dim0, Amount as measure0 from Orders
  select B.dim0 as dim0, A.measure0 * B.measure0 as measure0 from A, B

-- Equivalent optimized form
select OrderKey as dim0, 2 * Amount as measure0 from Orders

Multiply two columns

The expression Orders.Amount * Orders.Discount also represents a join between two subqueries. Both subqueries aggregate at the OrderKey grain, so the join is trivial. NetScript merges them into a single select statement, as the following SQL query shows:

with A as select OrderKey as dim0, Amount as measure0 from Orders,
     B as select OrderKey as dim0, Discount as measure0 from Orders
  select A.dim0 as dim0, A.measure0 * B.measure0 as measure0 from A, B
     where A.dim0 = B.dim0

-- Equivalent optimized form
select OrderKey as dim0, Amount * Discount as measure0 from Orders

Slice and aggregate quads

Slicing adds dimensions to a quad, and aggregating combines rows into fewer values. Use these operations to break a metric down, such as revenue by ship mode.

Slice a quad by a dimension

The expression Orders.Amount | by (Orders.ShipMode) slices a quad by a dimension. Every quad has a grain. The by operation takes an aggregated quad and adds the dimension that you supply.

The input Orders.Amount is already at the finest grain, OrderKey, so further slicing does not group the data. The by operation is still valid. It adds the ShipMode column to the set of dimensions.

This article also calls the by operation a slice. For the syntax of by and related operations, go to Query operations.

The quad corresponds to the following SQL query:

select OrderKey as dim0, ShipMode as dim1, Amount as measure0 from Orders

Repeated slices over the same dimension have no effect. In the following identity, P and Q stand for any quads:

P | by (Q)    is equivalent to     P | by (Q) | by (Q)

A slice also has no effect when the dimension is already in the grain of the input quad.

Aggregate by a dimension

The expression Orders.Amount | sum by (Orders.ShipMode) takes row-level order data and totals it for each ship mode. The input query does not include ShipMode, so NetScript first adds that field and then aggregates over it. The following equivalent quad shows these two steps:

Orders.Amount | by (Orders.ShipMode) | sum by (Orders.ShipMode)

The quad maps to the following SQL query:

with A as select OrderKey as dim0, ShipMode as dim1, Amount as measure0
              from Orders
select A.dim1 as dim0, sum(A.measure0) as measure0 from A group by A.dim1

-- Equivalent optimized form
select ShipMode as dim0, sum(Amount) as measure0
    from Orders group by ShipMode

NetScript supports common aggregate functions, such as count, min, max, sum, and avg. For every aggregate function and its syntax, go to Aggregate functions.

Aggregate by time bucket

The expression Orders.Amount | sum by (time_bucket(Orders.OrderDate)) totals order amounts for each time bucket. The time_bucket function groups timestamps into fixed-size intervals called buckets. The quad does not set the bucket size. Instead, Analytics sets the size when the query runs.

The time_bucket function takes an optional second argument that sets the bucket size. This argument defaults to the $TIME_GRAIN parameter, so the previous quad is equivalent to the following quad:

Orders.Amount | sum by (time_bucket(Orders.OrderDate, $TIME_GRAIN))

Run-time parameters such as $TIME_GRAIN let users adjust a formula without editing it. For example, users change the grain or apply filters. This flexibility helps in monitoring, where you view time series data at different grains. Set the grain to 10 seconds for detail or to 1 day for a broader view.

The quad corresponds to the following SQL query:

select time_bucket(OrderDate, $TIME_GRAIN) as dim0,
       sum(Amount) as measure0 from Orders
       group by time_bucket(OrderDate, $TIME_GRAIN)

To fix the bucket size, enter a constant as the second argument of time_bucket, such as 1 day.

Stack operations on a quad

The expression Orders.Amount | sum by (time_bucket(Orders.OrderDate)) | by (Orders.ShipMode) stacks operations on a quad. The second by operation slices the first aggregation by the ShipMode dimension. NetScript adds that field to the group by clause, as the following SQL query shows:

select time_bucket(OrderDate, $TIME_GRAIN) as dim0,
       ShipMode as dim1,
       sum(Amount) as measure0 from Orders
       group by time_bucket(OrderDate, $TIME_GRAIN), ShipMode

Aggregate the entire input

The expression Orders | count aggregates the entire input into a single row, with no dimensions. The following SQL query returns the total number of orders:

with A as select OrderKey as dim0, OrderKey as measure0 from Orders
  select count(A.measure0) as measure0 from A

-- Equivalent optimized form
select count(OrderKey) as measure0 from Orders

Assign quads to variables

Assign a quad to a variable to reuse it in other parts of a NetScript program. End each assignment with a semicolon (;). The following program assigns a quad to the variable amountByTime:

amountByTime = Orders.Amount | sum by (time_bucket(Orders.OrderDate));
amountByTime | by (Orders.ShipMode)

This program is equivalent to the following quad:

Orders.Amount | sum by (time_bucket(Orders.OrderDate)) | by (Orders.ShipMode)

NetScript treats variables as untyped macros. Before it builds the query, NetScript replaces each variable with its quad, like the C preprocessor.

Join quads

NetScript joins datasets and quads automatically, based on your data model and the grain of each quad. Use joins to combine metrics, such as revenue divided by cost.

Query across multiple datasets

NetScript knows the schema of your data warehouse and follows its join paths to generate queries. The following quad uses columns from two datasets:

LineItems.TotalPrice | sum by (Product.Category)

NetScript generates the following SQL query for this quad:

select Category as dim0, sum(TotalPrice) as measure0
    from LineItems, Product where LineItems.ProductId = Product.Id
    group by Category

Join quads with the same grain

The simplest way to join quads is to combine them with a binary operator. The simplest join combines quads that have the same grain, as in the following program:

revenue  = Orders.Amount | sum by (Orders.ShipMode);
cost = Orders.Cost | sum by (Orders.ShipMode);
revenue / cost

Both revenue and cost aggregate at the same grain, Orders.ShipMode. As a result, the / operation acts as a join on the shared ShipMode dimension. The program produces the following SQL query:

with A as select ShipMode as dim0, sum(Amount) as measure0
              from Orders group by ShipMode,
     B as select ShipMode as dim0, sum(Cost) as measure0
              from Orders group by ShipMode
  select A.dim0 as dim0, A.measure0 / B.measure0 as measure0
     from A, B where A.dim0 = B.dim0

Join quads when one grain contains the other

A join is also well defined when one side's dimensions are a subset of the other side's. Consider the following program:

revenue = Orders.Amount | sum by (Orders.Channel, Orders.ShipMode);
cost = Orders.Cost | sum by (Orders.ShipMode);
revenue / cost

The / operator represents a join. The left-hand side (LHS) grain is {Channel, ShipMode}, and the right-hand side (RHS) grain is {ShipMode}. The join condition uses only the shared dimensions, as the following SQL query shows:

with A as select Channel as dim0,
                 ShipMode as dim1,
                 sum(Orders.Amount) as measure0
            from Orders group by Channel, ShipMode,
     B as select ShipMode as dim0, sum(Orders.Cost) as measure0
            from Orders group by ShipMode
select A.dim0 as dim0,
         A.dim1 as dim1,
         A.measure0 / B.measure0 as measure0
    from A, B where A.dim1 = B.dim0

The following program calculates the share of revenue that each product contributes to its category:

revenue = LineItems.Price | sum;
productRevenue = revenue | by (Product);
categoryRevenue = revenue | by (Product.Category);
productRevenue / categoryRevenue

The LHS grain is {Product}, and the RHS grain is {Product.Category}. Neither grain is a subset of the other. One solution is to add the category to the product slice, as in the following program:

revenue = LineItems.Price | sum;
productRevenue = revenue | by (Product, Product.Category);
categoryRevenue = revenue | by (Product.Category);
productRevenue / categoryRevenue

The productRevenue quad already has the {Product} grain. Slicing it further by Product.Category does not change any revenue totals, because each product has only one category. After this change, the subset rule applies, and NetScript generates the SQL query for the join.

This example shows a key idea in how NetScript joins quads. Each product has only one category, so Product determines Product.Category. This relationship is a functional dependency, also called a many-to-one relationship.

Adding the category dimension does not change the grain of productRevenue, and it makes the join possible. NetScript adds these extra dimensions automatically, so you do not need to add them yourself. The original program is well defined and generates the following SQL query:

with A as select Product.ProductKey as dim0,
                 Product.Category as dim1,
                 sum(Price) as measure0
            from Product, LineItems
            where LineItems.ProductKey = Product.ProductKey
            group by Product.ProductKey, Product.Category,
     B as select Product.Category as dim0,
                 sum(Price) as measure0
            from Product, LineItems
            where LineItems.ProductKey = Product.ProductKey
            group by Product.Category
select A.dim0 as dim0,
         A.dim1 as dim1,
         A.measure0 / B.measure0 as measure0
    from A, B where A.dim1 = B.dim0

Show multiple measures with dataframes

Most quads have a single measure. To show several measures side by side, combine quads in a list called a dataframe. NetScript has limited support for dataframes. The following program creates a dataframe with two measures:

revenue = Orders.Amount | sum;
cost = Orders.Cost | sum;
[revenue, cost]

This program produces the following SQL query:

with A as select sum(Amount) as measure0 from Orders,
     B as select sum(Cost) as measure0 from Orders
  select A.measure0 as measure0, B.measure0 as measure1 from A, B

Dataframes have the following limitations:

A dataframe cannot be a dimension of another quad.

A dataframe cannot be a filter condition.

Most aggregate functions do not accept dataframes.

Dataframes work best for showing measures side by side. They support the same joins as other quads. The following program shows product and category revenue side by side:

revenue = LineItems.Price | sum;
productRevenue = revenue | by (Product, Product.Category);
categoryRevenue = revenue | by (Product.Category);
[productRevenue, categoryRevenue]

This program resembles the product and category example in the previous section. It shows both revenues instead of their ratio. The generated SQL query has a similar structure. The top-level select clause returns A.measure0 as measure0 and B.measure0 as measure1 instead of A.measure0 / B.measure0 as measure0.

Aggregate at multiple levels

Some calculations need two levels of aggregation, such as the average revenue across ship modes. A single level of aggregation cannot express this calculation. First calculate the total revenue for each ship mode with sum, and then average those totals:

Orders.Amount | sum by (Orders.ShipMode) | avg

The quad produces the following SQL query:

  with A as select ShipMode as dim0, sum(Amount) as measure0 from Orders
                group by ShipMode
    select avg(A.measure0) as measure0 from A

Slice a joined quad

Slice a quad that results from a join to break the combined metric down further. The following program slices a profit quad by ship mode:

revenue  = Orders.Amount | sum by (Orders.ShipMode);
cost = Orders.Cost | sum by (Orders.ShipMode);
profit = revenue - cost;
profit | by (Orders.ShipMode)

NetScript slices revenue and cost by Orders.ShipMode and then joins the results. As a result, NetScript generates the SQL for the following expression:

(revenue | by (Orders.ShipMode)) - (cost | by (Orders.ShipMode))

This behavior follows a general identity. In the following identity, P, Q, and R stand for any quads, and f stands for a join operator:

f(P, Q) | by (R) = f(P | by R, Q | by R)

The identity does not hold in every case. Sometimes R does not apply to one side of the join. For example, P and Q come from different fact tables, which are tables that record events or transactions. In that case, R applies to only one of them.

NetScript attempts to slice every branch of the join. The result is well defined when the slice succeeds on the finest-grained side of the join.

Filter a quad

Filter a quad with the where keyword to limit a metric to matching rows, such as priority orders. A quad represents a query, not the data that the query returns. As a result, a filter changes the query instead of filtering the returned data.

The expression Orders | count | where (Orders.ShipMode == 'priority') produces the following SQL query:

select count(Orders) from Orders where ShipMode = 'priority'

NetScript filters accept any quad with a single boolean measure, not only simple comparisons. The query compiler interprets the quad and produces the correct SQL query when the required joins exist.

Call data warehouse functions

Call a data warehouse function when NetScript has no equivalent, such as a user-defined function (UDF). NetScript sends any function name that it does not recognize to your data warehouse as written. For example, upper(Orders.ShipMode) runs the upper function of your data warehouse.

Follow these rules when you call a data warehouse function:

Use a function that exists in your data warehouse, with the syntax of your data warehouse. NetScript does not check the function name or its arguments. Your data warehouse reports any errors when the query runs.

Enter parentheses after the function name, even when the function takes no arguments, such as current_date().

Check that no NetScript function has the same name. Otherwise, NetScript runs the NetScript function instead.

Enter the full name of a UDF, such as my_schema.my_function(Orders.Amount). Wrap a name that contains special characters, such as a hyphen, in double quotes. For example, enter "my-project.my_dataset.my_function"(Orders.Amount).

Convert the result with a type conversion function, such as to_float, before you aggregate it. NetScript does not know the data type of the result.