Static segment synchronization

Description

Data from static segments can be synchronized with external SQL data tables. This integration is utilized when you need to maintain up-to-date information about customer groups in external systems. Data updates in the external system occur based on specific events.

• Adding profiles to a static segment.
• Removing profiles from a static segment.
• Clearing (removing all profiles from) a static segment.
• Creating or updating a static segment.
• Deleting a static segment.

To add synchronization, select Integrations in the left menu. Then click on SQL.

Now click on + Create on the top of the page.

tip

To synchronize within the Altcraft Administrative Panel, you need to create a connector to an external SQL storage. You can find instructions on adding a connector here.

Integration settings

In the main integration settings, enter the Integration name and Integration description. You'll also find a toggle switch here that allows you to activate or pause the integration.

Now set the integration scope. Below in the same section, select the SQL connector for the storage with the table where the data will be exported to.

SQL queries for event synchronization

Below, you need to configure SQL queries that will add or remove data from an external table based on events related to the selected static segments.

tip

Queries can involve additional profile data and logical expressions. The matching of profile fields and data table columns is determined by the WHERE operator.

Adding and removing profiles, segment clearing

caution

Queries for adding and removing profiles are mandatory for integration.

Events of adding or removing customer profiles from a static segment can be exported individually or in batches. When using batches, the number of added profiles (Batch Size) and a timeout (Timeout) are set. After the timeout, synchronization will be triggered even if the required number of profiles hasn't accumulated. Default timeout value — 60 seconds.

Example of data table structure

info

segId — static segment identifier

clientId — unique client identifier in the company's data center

updated — time of profile addition or removal

mysegment

+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| segId    | int(11)      | YES  |     | NULL    |       |
| clientId | varchar(255) | YES  |     | NULL    |       |
| updated  | datetime     | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

Example of a batch profile addition query

info

{segment_id} — segment identifier in the platform

{$lead.CustomID} — custom (additional) profile field.

The batch is configured using the following cycle:

{for $index, $lead = $batch}
    ({segment_id}, "{$lead.CustomID}", NOW()){autocomma} // Here fields for export are specified
  {end}

Altcraft variables and functions

INSERT INTO mysegment (segId, clientId, updated)
  VALUES
  {for $index, $lead = $batch}
    ({segment_id}, "{$lead.CustomID}", NOW()){autocomma}
  {end}
  ON DUPLICATE KEY UPDATE
    updated = NOW();

Example of a batch profile removal query

info

{segment_id} — segment identifier in the platform

{$lead.CustomID} — custom (additional) profile field.

Altcraft variables and functions.

DELETE FROM mysegment
WHERE segment_id = {segment_id}
AND clientId IN ({batch_in "CustomID"})

Example of a query to remove all profiles from a segment

info

{segment_id} — segment identifier in the platform

Altcraft variables and functions.

DELETE FROM mysegment
WHERE segment_id = {segment_id}

Segment Addition/Update and Deletion

Additionally, you can configure the export of events for adding a new segment, updating existing data, and segment deletion. Segment data is exported to a separate SQL table:

Example of data table structure

info

name — name of the static segment

segment_id — unique ID of the segment in Altcraft

updated — event date

segments

+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| name        | varchar(255) | YES  |     | NULL    |       |
| segment_id  | int(11)      | YES  |     | NULL    |       |
| updated     | datetime     | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

Example of a query for adding/updating a segment

INSERT INTO segments (segment_id,name,updated)
  VALUES
     ({segment_id}, "{segment_name}", NOW())
  ON DUPLICATE KEY UPDATE
    name = "{segment_name}",
    updated = NOW();

Example of a query for deleting a segment

info

{segment_id} — segment identifier in the platform

Altcraft variables and functions.

DELETE FROM segments
WHERE segment_id = {segment_id}
LIMIT 1

info

When transferring profile data to an external database using variables, single quotes ' will be escaped. The escape symbols used depend on the type of database:

Database	Escape symbol	Example
MySQL, MSSQL, PostgreSQL, Oracle	'(single quote)	value''s
ClickHouse	\ (backslash)	value\'s

Synchronization Statistics

In the Sync statistics section, information about the number of successful SQL queries within the specified time will be displayed, along with synchronization execution errors.

Using the Start resync button, you can recalculate segment data for export.