Segmentation by External SQL tables

The ability to connect external SQL databases allows for flexible profile segmentation:

• You don't need to store all data in Altcraft MP
• No regular synchronization is needed — data is always up to date
• You can use data for entities not available in Altcraft MP
• It works regardless of the platform or system you use to store data; all you need is access to execute SQL queries

Database connectors for external databases are created in the Altcraft administrative panel.

tip

If you are using a cloud solution — send the database connection credentials to our support service: support@altcraft.com. You can also clarify questions related to connecting to other external services and databases there.

Creating a database query

Queries can be configured both in the administrative panel and in the user panel. The difference is that in the user panel you can immediately view the profiles that were selected by your query.

Editing queries in the user panel can be disabled by configuration setting in main.json if necessary. To do this, set the parameter "DISABLE_QUERY_EDITOR": true.

Queries are configured in the Data —> Segmentation Queries section. Go to the section and press the + Create button.

In the main settings, specify the query name, short name (unique for each query, used in platform variables and API requests), result caching period, as well as the query group, tags, and description:

info

Note: the result caching period only works when calculating segments. Automation scenarios use a separate caching mechanism with its own settings.

For the segmentation query to work correctly, it must select a column with data that already exists in Altcraft MP. For example, if Altcraft has a customer identifier in the profile database, you need to form a query so that this identifier is returned in at least one of the columns:

These queries will be used inside the segment. You can create an unlimited number of such queries to refine the segment. In each query you can select by one or several parameters, combining queries, using subqueries, JOINs, UNIONs, etc.

Be sure to select the column that will be used to search for profiles; use the update button to get a new list of columns for your query:

You can add parameters to the query so that it changes when used inside the segment. To do this, use an arbitrary but unique parameter name in curly braces, for example {CITY}:

Once you specify it in the query, a form with parameter settings will appear:

Here you set the field type, its default value, and the title that will be displayed when creating the segment.

If we want the segment field to be selected from a list, we use the Set options setting. When enabled, we choose whether to enumerate the list of options or also select it from SQL:

If identifiers are used, the SQL query must return two columns: the first is used as the identifier, and the second will be displayed to the user:

With a manually specified list, the identifier and options can be listed separated by commas:

For all parameters, the order of their display during segment construction is configured. The order can be changed by simply dragging elements. Headers are positioned separately for more flexible display.

Use the database selection preview button to see how the query form will look in the segment and what the test selection will look like:

Specify the database and profile field for selection, as well as the column if there are several. In the Query Parameters section, the form will be generated as it will appear in the segment. Pressing the Apply button will run the query and immediately show the profiles that match the query condition:

After saving the query, it can be used in segments.

Variable Types

When creating a query parameter, you can choose one of the types. The chosen type determines how the value is substituted into the SQL query.

String

The value is substituted as text.

SELECT email FROM users WHERE city = '{CITY}'

For example, to find profiles from Moscow, set the {CITY} parameter to Moscow when configuring the segment. You can also set a descriptive parameter title, such as "City". The following query will be sent to the database:

SELECT email FROM users WHERE city = 'Moscow'

As a result, profiles with Moscow specified in the city field will fall into the segment.

Integer and Floating Point Number

The value is substituted as an integer if the variable type is "integer", or as a fractional number if the variable type is "Floating Point Number":

SELECT email FROM orders WHERE order_amount >= '{AMOUNT}'

For example, to find profiles with an order amount of at least 1500, set the {AMOUNT} parameter to 1500. You can also set a descriptive parameter title, such as "Minimum Order Amount". The following query will be sent to the database:

SELECT email FROM orders WHERE order_amount >= 1500

Profiles with an order amount greater than or equal to the specified amount will fall into the segment.

Boolean

The value is substituted as true (True), false (False), or null (Not selected).

SELECT email FROM orders WHERE is_vip = {IS_VIP}

For example, to find VIP clients, set the {IS_VIP} parameter to True. You can also set a descriptive parameter title, such as "VIP Client". The following query will be sent to the database:

SELECT email FROM orders WHERE is_vip = true

As a result, profiles with VIP status will fall into the segment.

Array

Used for working with array-type columns in PostgreSQL. Values are substituted as an array.

SELECT email FROM orders WHERE statuses && ARRAY[{status_list}]

The variable {status_list} has the type "Array", Array Element Type — "String". When configuring the segment, we select the needed values.

For example, the orders table has a statuses column of type text[], which contains arrays of order statuses. We need to find profiles that have at least one of the statuses completed or pending in the array. In the segment, we select both values. The following query will be sent to the database:

SELECT email FROM orders WHERE statuses && ARRAY['completed', 'pending']

As a result, profiles that have completed or pending in the statuses array will fall into the segment.

info

The "Array" type only works for PostgreSQL and requires that the column in the external table has an array type (e.g., text[], integer[]). This type is not supported for MySQL and ClickHouse.

To save the query, you must set at least one default value for the parameter.

Date

Used for filtering by dates. The value is substituted in the format that your database expects.

SELECT email FROM orders WHERE order_date >= '{date_from}'

The variable {date_from} has the type "Date". When configuring the segment, we select a date in the calendar.

For example, we need to find profiles that placed an order after a certain date. In the segment, we select a date, for example 28.05.2024. The following query will be sent to the database:

SELECT email FROM orders WHERE order_date >= '2024-05-28'

As a result, profiles with orders from the selected date and later will fall into the segment.

Month

Used for filtering by month from a date. The month number is substituted into the query.

SELECT email FROM orders WHERE MONTH(order_date) = '{month}'

The variable {month} has the type "Month". When configuring the segment, we select a month from the dropdown list.

For example, we need to find profiles that placed an order in May. In the segment, we select "May". The following query will be sent to the database:

SELECT email FROM orders WHERE MONTH(order_date) = 5

As a result, profiles with orders in May will fall into the segment.

Year

Used for filtering by year from a date. The year is substituted into the query.

SELECT email FROM orders WHERE YEAR(order_date) = '{year}'

The variable {year} has the type "Year". When configuring the segment, we select a year.

For example, we need to find profiles that placed an order in 2025. In the segment, we select 2025. The following query will be sent to the database:

SELECT email FROM orders WHERE YEAR(order_date) = 2025

As a result, all profiles with orders in 2025 will fall into the segment.

Weekday

Used for filtering by day of the week. The day number is substituted into the query using the platform's numbering.

SELECT customer_email FROM customer_orders WHERE WEEKDAY(order_date) = '{weekday}'

The variable {weekday} has the type "Weekday". When configuring the segment, we select a day of the week from the dropdown list.

Correspondence of days of the week and values substituted into the query:

Day of week	Value
Sunday	1
Monday	2
Tuesday	3
Wednesday	4
Thursday	5
Friday	6
Saturday	7

For example, we need to find profiles that placed an order on Tuesday. In the segment, we select "Tuesday". The following query will be sent to the database:

SELECT customer_email FROM customer_orders WHERE WEEKDAY(order_date) = 3

info

If your database uses a different day-of-week numbering, convert the value using the CASE operator. For example, in MySQL WEEKDAY() returns 0 for Monday, 6 for Sunday. To map the platform numbering to MySQL numbering, use the following construct:

SELECT customer_email FROM customer_orders
WHERE WEEKDAY(order_date) = CASE '{weekday}'
    WHEN 1 THEN 6   -- sunday
    WHEN 2 THEN 0   -- monday
    WHEN 3 THEN 1   -- tuesday
    WHEN 4 THEN 2   -- wednesday
    WHEN 5 THEN 3   -- thursday
    WHEN 6 THEN 4   -- friday
    WHEN 7 THEN 5   -- saturday
END

Comparison Operator

A comparison operator is substituted into the query. Quotes are not used for the "Comparison Operator" variable type in the query.

SELECT email FROM orders WHERE order_amount {operator} '{MIN_AMOUNT}'

The variable {operator} has the type "Comparison Operator". When configuring the segment, it allows selecting an operator from the dropdown list.

Available operators:

Operator	Description
=	Equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
<>	Not equal

For example, we need to find profiles with an order amount greater than 1000. In the segment, we select the operator >, specify the value 1000. The following query will be sent to the database:

SELECT email FROM orders WHERE order_amount > 1000

Logical Operator

When selecting this variable type, the user will be offered to choose an operator from a list when configuring the segmentation query. Quotes are not used.

SELECT email FROM orders WHERE status = '{STATUS}' {logical} city = '{CITY}'

The variable {logical} has the type "Logical Operator". When configuring the segment, we select an operator from the dropdown list.

Available operators:

Operator	Description	Usage Example
AND	Both conditions must be true	status = 'active' AND city = 'Moscow'
OR	At least one condition is true	status = 'active' OR city = 'Moscow'
NOT	Negation of condition	NOT (status = 'active')
IN	Value is in the list	city IN ('Moscow', 'Kazan')
NOT IN	Value is not in the list	city NOT IN ('Moscow', 'Kazan')
LIKE	Contains	city LIKE '%Mos%'
NOT LIKE	Does not contain	city NOT LIKE '%Mos%'
BETWEEN	In range (including boundaries)	amount BETWEEN 1000 AND 5000
NOT BETWEEN	Outside range	amount NOT BETWEEN 1000 AND 5000
EXISTS	Exists	EXISTS (SELECT 1 FROM orders WHERE user_id = id)
NOT EXISTS	Does not exist	NOT EXISTS (SELECT 1 FROM orders WHERE user_id = id)
ANY / SOME	Comparison with at least one value from subquery	amount > ANY (SELECT amount FROM orders)
ALL	Comparison with all values from subquery	amount > ALL (SELECT amount FROM orders)

For example, we need to find active profiles from Moscow. In the segment, we select the operator AND, specify the city Moscow. The following query will be sent to the database:

SELECT email FROM orders WHERE status = 'active' AND city = 'Moscow'

info

All parameters, except "Comparison Operator" and "Logical Operator", must be enclosed in single quotes — '{PARAMETER}'. Comparison operators and logical operators are written without quotes.

Using an external database query in a segment

Go to the Data — Segments section. Create a new segment or open an existing one for editing.

Add a new rule and select the identifier field or the field by which the profile is searched by the query. Then select one of the options:

• Is in data table — if you want to include everyone who fits this condition.
• Is not in data table — if you want to include everyone who does not fit this condition.

Then select your query to the table and fill in the query parameters form:

The conditions is in data table / is not in data table can be applied repeatedly in the segment, including inside groups, combining them with AND if you want each of them to satisfy the conditions, or with OR if you want any of them to satisfy the conditions.

Now you can save the segment.