Linking variables to data

Connecting existing variables to data

If you have the Data column visible, you can use this column to connect (or disconnect) data item/s from variables, and see which Data Items (from which Data Sources) are being used at a glance in the formula bar at the top of the spreadsheet.

If you don't have the Data column visible, hover over the variable name and click on the data icon (the 3 stacked circles).

Connecting new variables to data

You can create a new variable from data, by hitting 'New Variable' and typing in the name of the data item (or data source), and selecting it in the drop-down.

Linking to variables from the data preview

If you open up a data source from the left-hand side bar, the Linked variables column in the data preview shows which variables are linked to each data item (in the current model), and lets you link to existing (or new) variables, and unlink.

Generate a model from a data source in 1 click

If you want to build a model based on underlying data, you can now generate the model structure in 1 click. Head to the Data page, click into your Data Source, and hit Create model.

  • Each variable is automatically named according to the line items, and is automatically connected to the line item in the data source.
  • Use this to quickly generate a skeleton for a model, e.g. a P&L model based on a P&L in Xero/QuickBooks.

Transforming data items

If your data item is broken down by a category you can transform the Data Item by aggregating the category items (if you don't care to see the breakdown), filtering category items (if you just want to pull in particular items), or grouping by another category.

For example, if your Operating Expenses line item from your accounting system is broken down by Region, then you can

  • Filter it to only pull in Operating Expenses for a single region
  • Group it by a different category, e.g. Country (note: this category link must already exist in your Causal model)
  • Aggregate it to pull in the total Operating Expenses as a single line

Blanks in Data

Data Aggregation

The Data Aggregation function on a variable connected to data determines how Causal aggregates transactions within each data item linked to a variable. This is usually always going to be Sum (which is our default), but there are examples where you might want it to be Average, Median, Initial, Final, or Count instead.

For example: If you have daily cumulative data in your data source, and are pulling that into a weekly or monthly model in Causal, Sum wouldn't make sense (as that would be adding multiple cumulative numbers together), so you might choose Final instead.

  • e.g. 2022-12-20 Cumulative User Count 33, 2022-12-21 Cumulative User Count 35. Sum would return 68 whereas Final would return 35 (the true ending users for the week/month).

Multiple data items

When there's multiple data items linked to a variable, Causal always aggregates over them by Sum. If you'd like the averaged values across multiple data items, you can obtain these by using the Count data aggregation.

  • e.g. Employee Voluntary Churn Growth Rates are being pulled in from two different data items, and you'd like the average rate. You'd create 3 variables in total:
    • Total Churn Rates (data aggregation by sum)
    • Churn Rate Transactions Count (data aggregation by count)
    • Average Churn Rate = Total Churn Rates/Churn Rate Transactions Count
Data aggregation settings in a variable's right-click menu