When creating a predictive flow in Pecan, you'll write SQL queries that transform your data into an AI-ready dataset. This enables a predictive model to be trained on your data, and enables new records to be fed into the model on an ongoing basis.
To ease the query-creation process, you can use preset variables or create new ones within the Queries Editor.
What is a variable?
In Pecan, a variable is a customizable property that can be injected into an SQL query. Variables contain unique information that affect how your query operates and the data your model is trained (and eventually makes predictions) on.
Variables help formulate your predictive question. They impact how a query functions, and help define model parameters like what you’re trying to predict, for whom, for what period, and with frequency. Each variable is assigned a string or numerical value.
Since they can be reused multiple times within multiple queries, variables allow you to work faster and more efficiently. They also make it easy for colleagues who don’t know SQL to update the premise of a query; all they have to do is duplicate your work and adjust the variables.
Pecan provides preset variables for a number of templates, and you can also create custom variables based on your unique needs or use-case. For a full list of these variables, see below.
How do you use a variable?
When creating a predictive flow in Pecan, you can use the preset variables provided in your template. Here’s how:
From the “Connections” tab, click + New predictive flow and select the template you want to use. This will open the Queries Editor.
In the right-side panel, you’ll see a panel named “Model Variables”:
You can understand each variable by reading its tooltip and checking how the variable is being used in the queries that have been provided by default. (Note: if creating a predictive flow from scratch, you won’t see any default queries or preset variables.) You can also read about each variable in the below table.Define the value of each variable or use the default value that’s been provided.
To define a variable - next to its name, indicate the appropriate string or value to populate the variable with. This will define certain key parameters for your model.
When starting from a template, you’ll find that preset variables have already been injected into the queries. But here’s how to use a variable in a new place or way…
To use a model variable:
Click anywhere within a query and type “{{“.
Then type the name of the variable.
Here’s an example of how this would look for a variable named “frequency”:
Once you’ve completed the above, the variable automatically becomes a part of the query – and its content will affect how the query operates. To see the value of each variable embedded within the query, click the “Compiled” tab at the top of the Editor.
The below image illustrates how variables appear in the editable view and the compiled view:
Note that although you can use variables before they’ve been defined, they’ll have no value in the compiled view, or have any impact when the query is actually run.
What variables are available and what do they mean?
Below are the preset variables that are currently available in Pecan templates:
Variable | Purpose | Type of variable | Relevant template(s) |
frequency | Define how frequently you want to generate a prediction for each customer. | Model | Retention, Churn, Upsell, Engagement Decrease |
active_period | Define how long after an activity a customer is considered to be “active”. This helps Pecan identify whom to make predictions for. | Model | Retention, Churn, Upsell |
prediction_window | Define how far beyond each marker date to make a prediction for. | Model | All |
activity_period | Define a time period prior to the marker date during which a customer may have performed a number of activities. This helps Pecan identify whom to make predictions for. | Model | Engagement Decrease |
days_from_first_activity | Define the desired number of days between a customer’s first activity and the prediction. Activity during this period will provide a basis for the prediction. | Model | Conversion, LTV, HVC |
top_percent | Define the threshold (percentile) at which an entity is considered a High Value Customer. | Model | HVC |
number_past_activities | Define the number of activities or transactions that may have been performed by an entity during their active period. | Model | Engagement Decrease |
percentage_decrease | Define the percentage decrease of activities or transactions that may have occurred for an entity during their prediction window (compared to their active period). | Model | Engagement Decrease |
transaction_table | Select the table that contains customer transactions. It will be used to define customers as active and identify their first activity. | Table | All |
upsell_table | Select the table that indicates whether each customer performed an upsell activity. | Table | Upsell |
converted_customers_table | Select the table that indicates whether each customer had a change in status. | Table | Conversion |
How do you create a new variable?
When creating a predictive flow, you may seee value in creating new variables –particularly if you’re building a use-case that goes beyond the provided templates.
For example, you may create a new model variable in order to formulate a a unique predictive question, or create a table variable in order to query specific sets of data.
To create a model variable:
Click anywhere within a query, type “#{{“, and then type the name of your desired variable.
The new name will appear under “Model Variables” on the right side of the screen. Click + Add variable.
Next, In the blank field that appears, enter the string or value you want to assign to this variable.
Click Save and your variable will now be saved and ready for use.
