Concept: Window Recipe

The Window Recipe lets you make grouped calculations on a dataset, while keeping the structure of the dataset the same. This can be useful when you want to make easy grouped comparisons or generate meaningful features for a machine learning model. For example, let’s say you wanted to calculate the average amount of customer orders. Contrary to the Group Recipe, it outputs as many rows as in the input dataset.

../../_images/window-recipe1.png

Some computations you can make with the Window Recipe include rankings, lag (the number of events that occurred during the n days prior to another event), and moving averages, such as the average quantity per customer order over the last three days.

Let’s find out if each order in our sample dataset is the customer’s first, second, or third order, and so forth.

../../_images/window-dataset-initial1.png

To do this, we first configure the Windows definitions. This constricts the window frame and is optional. Setting this window frame will change our column values. We want to define a window frame for each customer so that we can limit our aggregation calculation to just the subset of rows within each partition. We’ll turn on partitioning and select the customer_id column. Then we’ll order columns by order_date.

../../_images/windows-definition-configuration1.png

Let’s configure the Aggregations. Since we want to know if the order was the customer’s first, second, or third, and we’ve already partitioned by customer and ordered by order date, we’ll select Rank. Then we’ll simply retrieve the information from each column in the dataset.

../../_images/window-aggregations1.png

When we run the recipe we have the option of converting it to a SQL recipe. DSS has created the output dataset. The window is framed by Customer ID and ordered by Order Date as specified in the configuration.

../../_images/window-output2.png