Recommendations for segmentation by external data

Segmentation by external data sources is a powerful tool, but if configured incorrectly, it can significantly slow down segment operation. The following recommendations will help you avoid typical performance issues.

Minimizing data volume

External data sources should be designed to return as little data as possible. The fewer records the platform has to process, the faster the segment will be calculated.

Basic rules:

• Return only the column with the profile identifier used for matching. Additional columns are unnecessary and only increase the amount of transferred data.
• Add basic filtering directly to the query or API URL whenever possible. For example, if you only need active clients, add WHERE is_active = 1 to the SQL query. This will reduce the amount of data the platform receives from the external database and speed up segment calculation.
• For HTTP requests to APIs — the external service should return only a list of identifiers, not full objects with additional fields.
• For uploaded files — the file should contain only the needed column with identifiers.
• Use the result caching parameter when creating a SQL segmentation query. If data changes infrequently, caching will significantly speed up repeated segment calculations.

Combining conditions in a single SQL query

When you need to filter profiles by multiple conditions from one external SQL database, it is better to write one query with combined conditions rather than creating multiple queries and joining them in the segment with "AND".

Each separate segmentation query is a separate call to the external database. The more queries, the longer the segment calculation.

Example. Suppose you need to select clients who have an active contract and are in a specific region.

Inefficient approach — two separate segmentation queries:

1. Query "Contracts": SELECT id FROM contracts WHERE status = 'active'
2. Query "Regions": SELECT id FROM customers WHERE region = 'Moscow'

Then in the segment these two conditions are joined with "AND":

Is in data table (query "Contracts")
AND
Is in data table (query "Regions")

The platform will execute two separate queries to the database, get two lists of identifiers, and intersect them internally.

Efficient approach — one segmentation query with combined conditions:

SELECT customers.id
FROM contracts
JOIN customers ON customers.id = contracts.customer_id
WHERE contracts.status = 'active' AND customers.region = 'Moscow'

In the segment — one condition:

Is in data table (query "Contracts and regions")

The platform executes one query, and the intersection happens on the database side — this is faster.

Recommendations:

• If conditions relate to one external database — combine them into a single SQL query using JOIN, subqueries, or UNION.
• If conditions relate to different databases — it is impossible to combine them into one query, and the platform will execute them sequentially. In this case, try to make each query return as few records as possible.
• Use query parameters to create one universal query instead of several similar ones. For example, one query with parameter {REGION} is better than separate queries for each region.
• Try to make all queries in the segment reference the same profile field (e.g., customer_id). The platform can automatically combine queries only if they are bound to the same profile field and use the same connector.

Automatic query combination

The platform has an optimization that combines multiple segmentation queries to one external database into a single SQL query. Instead of executing each query separately, downloading results, and intersecting them inside the platform, the platform forms one query with INTERSECT (intersection), UNION (union), or EXCEPT (exclusion) operations — and executes it on the external database side.

How it works. If the segment has multiple "Is in data table" conditions that use the same connector and are bound to the same profile field, the platform can combine them:

Is in data table (query "Contracts")
AND
Is in data table (query "Regions")

Instead of two separate queries, the platform will form one:

(SELECT id FROM query1) INTERSECT (SELECT id FROM query2)

If one of the conditions is "Is not in data table", the platform uses EXCEPT:

(SELECT id FROM query1) EXCEPT (SELECT id FROM query2)

info

The optimization is enabled by the platform administrator (parameter SEGMENT_SQL_DATA_GROUP_OPTIMIZATION). More details in the administrator documentation.

Limitations:

• Only queries from the same connector and the same profile field are combined.
• If there are other segment conditions (not from the external database) between queries, the combination may not work. For example, a construct like Query AND (Query OR Condition) cannot be combined because the non-query condition "breaks" the group.

Segment-inside-segment expansion

If a segment condition uses another segment (the "In segment" operator), the platform can expand the nested segment and include its external database queries in the general grouping for combination.

Example. The "VIP" segment contains the condition Is in data table (query "VIP"). The parent segment contains:

Is in data table (query "Active clients")
AND
In segment (segment "VIP")

Without expansion, the platform will execute the "Active clients" query separately, calculate the "VIP" segment separately, and intersect the results.

With expansion, the platform will "see" the "VIP" query from the nested segment and combine both queries into a single SQL query with INTERSECT.

For expansion to work, all conditions must be met:

• The "In segment" operator is used. If "Not in segment" is used, standard expansion will not work (see Not in segment expansion below).
• Expansion works only for dynamic or updatable segments, as they store segmentation conditions. Expansion does not apply to static segments — they contain only a ready-made list of profiles.
• Queries of the nested and parent segments use the same connector and are bound to the same profile field.
• Group condition operators match — if the parent uses "AND", the nested must also use "AND" (similarly for "OR").

info

Segment-inside-segment expansion is enabled by the platform administrator (parameters SEGMENT_SQL_SIS_EXPANSION, SEGMENT_SQL_SIS_MAX_DEPTH). More details in the administrator documentation.

"Not in segment" expansion

A separate optimization is implemented for conditions with the "Not in segment" operator. It allows expanding the nested segment and transforming the negation: each operator inside the nested segment is replaced with the opposite one ("equal" → "not equal", "contains" → "does not contain", "greater than" → "less than or equal", etc.), after which the queries can be combined with the parent segment.

For expansion to work, all conditions must be met:

• The condition uses the "Not in segment" operator.
• The nested segment is not static — dynamic or updatable.
• Queries of the nested and parent segments use the same connector and are bound to the same profile field.
• All operators in the nested segment have an inversion pair (e.g., "equal" ↔ "not equal", "contains" ↔ "does not contain"). If at least one operator has no pair — expansion will not work.

Operators without inversion

Subscription status: "Unsubscribed", "Hardbounced", "Complainer", "Invalid", "Suspended", "Not confirmed", "Does not exist".

Automation scenarios: "was at least once", "was never", "currently in scenario", "currently not in scenario", "exited with error".

Forms: "filled", "did not fill", "filled during selected period", "filled within the last [x] days relative to current date".

Dates: "same as today".

Channel activity: "At least [n] times", "Not a single time".

Campaigns and LCG: "not in LCG".

Loyalty and orders: "in range".

Relations: "Presence / absence of direct relation", "Presence / absence of reverse relation".

info

"Not in segment" expansion is enabled by the platform administrator (parameters SEGMENT_SQL_SIS_NOT_EXPANSION, SEGMENT_GROUP_EXPANSION). More details in the administrator documentation.

"NOT" conditions on external data marts

Segmentation conditions on external data marts with the "NOT" operator (e.g., "Not in data table") lead to scanning the entire profile database. This happens because the system needs to check each profile for absence in the external selection.

Recommendations:

• Minimize the number of "NOT" conditions on external data.
• Whenever possible, replace negative conditions with positive ones. For example, instead of "Not in data table", use "Is in data table" with an inverted query on the external database side.
• If a "NOT" condition is necessary, make sure the external data mart returns the most compact result possible.

For more on the "NOT" operator in segments, see Recommendations for working with segments.