Skip to main content

Segmentation by external SQL tables

Description​

The ability to connect external SQL databases allows you to perform flexible profiles segmentation. This type of data storage has the following features:

  • It is not necessary to store all data in Altcraft MP
  • Regular synchronization is not necessary. Your data will always be actual
  • You can use data types, that were not designed in Altcraft MP
  • It does not matter, what platform and system you use to store information. You only need access to perform SQL queries

Connectors to external databases are created in Altcraft administrative panel.

tip

To connect to your database you would need an account with proper access privileges. The process depends on your version of Altcraft Marketing:

  • If you are a cloud account user — send access credentials to our support — team@altcraft.com.

  • If you have an On-premise installation, platform administrator should create a database connector in Administrative panel — and assign it to your platform account.

Creating query to the database​

You can configure queries both in administrative and user panels. In user panels you can also watch profiles which were selected by your query.

You can restrict editing queries in main.json configuration, if it is necessary. To do this, you need to set parameter: "DISABLE_QUERY_EDITOR": true.

To configure queries, go to Data menu, and select Segmentation queries:

Go to this section, and press Create button.

In General settings specify query name, short name (unique for each query, used in variables and API-requests), result caching time and also groups, tags and description:

For correct query, it is necessary to select column with data, which are already contained in Altcraft MP. For example, if customer identifier is stored in Altcraft, you need to make a query, that has this ID at least in one column:

These queries will be used for segmentation. You can make unlimited number of these queries for segmentation. In every query, you make selection by one or several parameters. Also you can combine your queries using underselection, JOIN and UNION operators.

It is necessary to select the column which will be used to search profiles, and press Update button to get a new columns list for your query:

You can parameters into the query to change it during the segmentation. To do it, use random unique parameter name in braces, like {CITY}:

The form for configuring the parameter will appear, when you set it:

In this form, you can set Type of variable, its Default value, and Title, that will be displayed when you create the segment.

If you want to select field value from the list in the segment, use Set options checkbox. When you toggle it, you can set options list, or select them form SQL database:

If you use identifiers, your SQL query must give two columns — the first one will be used for identification, and the second will be displayed for the user:

If you select Set options manually, you can enter identifier and options, separating them with commas:

For all parameters, you can configure their display order during segmentation. Display order can be changed by dragging the elements. Headers will be displayed separately, for more convenience.

Use preview button to see how the query will be displayed in the segment, and what will be displayed in test selection:

Select database, profile field, and column for selection. In Query parameters section, the form will be displayed as it will be displayed in the segment. Press Apply button to perform the query, and profiles that fit query condition, will be shown:

After saving the query, you can use it in the segments.

Using the query to external database​

In the main menu, go to Data → Segments section.

Create a new segment or edit existing one.

Add a new rule and select identifier field, or field that will be used for profiles search. Then, select the option:

  • Is in data table — if you want to select all profiles that fit the rule
  • Is not in data table — if you want to select all profiles that do not fit the rule

Than, select your query and fill the form with parameters:

Now, you can save the segment.

The rules can be applied more than once in the segment and in groups, combining them with AND and OR operators.

Any conditions can be combined in segments with queries: by Altkarft MP fields, by actions, by goals and others.

Last updated on