Once you have read Understanding ETA (Entity, Target, Attribute), you should have a general understanding of the role these components in play in building a predictive model.
This article aims to provide you with a basic understanding of how to build SQL queries in the Queries Editor so you can generate AI-ready tables (on the basis of data that’s been imported into Pecan.) These queries enable data to be interpreted by Pecan’s AutoML so your model can be trained, and so predictions can be made for new datasets.
This is all done within the Queries Editor.
Entity queries
Your Entity query is where you will define your entities – for whom, and when, you want to generate a prediction.
For example, you may want to generate predictions on the first of each month for all active customers.
Below is a simplified example of how this query may look in the Queries Editor. It is simplified because:
The content of your query will vary depending on the type of model you wish to create (e.g. upsell, churn, lifetime value, etc.)
You will require additional entries that manipulate your data on a more granular level (e.g. to define when customers should be considered “active”, to filter out certain types of entries, etc.)
As per the above example, your dataset would need to include:
A “helpers_repeat” table that contains a single column of marker dates and dictates your desired sampling frequency.
A “customers_first_activity” table that provides the “customer_id” property for all active customers
Target queries
Your Target query is where you will define the prediction you wish to generate.
For example, you may wish to predict whether each active customer (as defined in the Entity query) is likely to upgrade their customer status churn within a given time period.
Here’s an example of how your query may appear in the Queries Editor:
In the above example, the target is represented in the “demodata.vip_customers” table. Whether or not the customer had a change in status will be denoted by either a 0 (no) or 1 (yes).
Attribute queries
Your Attribute query is where you'll define the various attributes that may contribute to your predictions. In other words, it enriches your model by providing a range of potentially important information from your Attribute tables – such as demographic data, transaction details, support interactions, user behavior, etc.
In this query, you'll add relevant lines of SQL for each column that exists in your dataset (e.g. “age”, “last_purchase”, “campaign_id”, etc.) Here’s an example of how a query may appear in the Queries Editor:
In the above example, each attribute (e.g. order_type) is represented by a particular column within a table named “demodata.transactions”.
If querying attributes from multiple tables, you’ll need to create a separate query for each table. To do so, click + Add attribute in the left-most panel in the Queries Editor, and provide it with a distinct name.
