Skip to content
English
  • There are no suggestions because the search field is empty.

SQL Queries

A SQL query allows you to retrieve custom data table or system data table information to perform complex operations, such as data manipulation on Custom Data Tables, populating new Custom Data Tables, and combining data from Custom and System Data Tables

Creating a SQL Query

To create a query:

  1. Click SQL Queries located under Contacts in the Content Creation menu.
  2. Enter the SQL Query Name in the Create New SQL Query field.
  3. Click Create.

Properties

The table below lists the SQL Query configuration properties with their descriptions.

Field

Description

Name

The name of the SQL query.

Description

A description explaining the nature of the SQL query. This field is optional.

Folder Location

This field shows the folder in which the SQL query is stored. You can change the folder by clicking Select Folder.

Editor

When you create a SQL Query, you write the SQL statement that defines the query. You can write the query against a Custom Data Table or System Data Table provided in the system.

System Data Tables

System Data Tables contain information about your contact’s activities and other events in your iPost account.

Information contained in the System Data Tables can be viewed in the Attributes panel. Clicking on the System Data Table name will show the list of fields contained within. You can drag and drop the fields into the SQL Query Editor.

Here is a list of System Data Tables that are available:

  • Demographics – Use this data system table to view contacts and their database fields.
  • Orders - Us this system table to view orders generated from conversions.
  • Order_Details - Use this system table to view order details from conversions.
  • Sends – Query this data system table to find the contact’s sent emails.
  • Opens – Use this data system table to find email open data for your iPost account.
  • Clicks - This data system table can be queried to find email click data for your iPost account.
  • Optouts – Use this data system table to find opt-outs from lists.
  • Bounces - Query this data system table to find bounce data for emails from your iPost account.
  • Listmemberships – Query this data system table to find contacts on lists.
  • Journey_membership – This table can be used to find data on contacts in your journeys.
  • Categories - Query this data system table to find data on categories.
  • Platform_views – This table contains the type of device or operating system a contact used when interacting with a particular email.
  • Contact_latest – Query this data system table to find the most recent activities for your contacts.
  • Automations – Query this data system table to find information about your iPost automations.
  • Tickets – This data system table can be queried to find data on your iPost email tickets.

System Data Tables are preceded by <CLIENT_ID>_imm.

Example:

SELECT *
FROM 1001_imm.sends

Your CLIENT_ID is an integer that will be given to you when the SQL Editor is enabled in your account.

Custom Data Tables

You can write your query against a Custom Data Table in your iPost account.

In the Attribute panel, click the Select Custom Data Tables button to select your Customer Data Table(s). You can view fields within your Custom Data tables by clicking the table name and dragging and dropping the fields into the SQL Query Editor.

Every Custom Data Table being used in your query must be preceded by <CLIENT_ID>_cdt.

Example:

SELECT *
FROM 1001_CDT.myTable

Your CLIENT_ID is an integer that will be given to you when the SQL Editor is enabled in your account.

SQL Query Templates

A syntax template can be added to your query by selecting a statement found in the SQL Query Templates panel. Drag and drop the statement into the SQL Query Editor to use.

There are 3 SQL Query Templates:

  1. SELECT – this statement is used to select data from the database.
  2. DELETE – this statement is used to delete existing records in a table.
  3. UPDATE – this statement is used to modify records in a table.

SQL Query Library

Sample queries are available in the SQL Query Library. Drag and drop the query into the SQL Query Editor to use. Be sure to change out the data table place holder before validating the query.

Query

Definition

Domain Selection

Select a specific domain from any data table

Data Field Value Update

Update a value for a data field with no value in any data table

Orders From Last 6 Months

Find all orders placed in the last 6 months

Insert Contacts into a Data Table

Select records from a data table to be inserted into a destination data table while skipping all duplicates

Empty a Data Table

Empty all records from any data tables

Opens within Last Month

Select all contacts from an existing data table that opened any email in the last month

Add/update Records from Last Day

Add/update a data table with records imported in the last day

Select Contacts by Attribute

Select contacts who have a demographic with a specific value

Select Contacts Sent an Email

Select all contacts who have been sent an email in the last 90 days

90 Day Openers

Select all contacts who have opened any email in the last 90 days

90 Day Opt-Outs

Select contacts who have opted out in the last 90 days

Select Contacts from a List

Select all contacts from any list using the ID for that list

30 Days Openers With a Data Table

Select all contacts from a data table who have opened an email in the last 30 days

Contacts Sent An Email

Select all contacts sent a specific email using the email ID

Contacts Who Opened an Email (Email ID)

Select all contacts who opened a specific email using the email ID

Contacts Who Opened an Email (Mailing ID)

Select all contacts who opened a specific email using the mailing ID

Contacts Ejected from a Journey

Select all contacts ejected from a journey using the journey ID

Contact's Most Recent Events

Select a contact's most recent events

Non-Openers

Select all contacts who have never opened an email

Global Status

Select all contacts with their current global status

Bounced Out Contacts

Select all contacts who are opted out due to meeting the bounce threshold for the account

List Membership

Select all contacts with a status of opt in for a specific list using the list ID

Contacts Added in the Last Day

Select all contacts added to the account in the last day

Topic Membership

Select all contacts who are members of a topic

Top 10 Click Links Excluding URL String

Select the top 10 links by click from the last week excluding a string found in specific URLs

Validation

The SQL Query Editor provides a validation tool to check the syntax of your SQL. You must validate your query in order to save, run, or export data.

Once you have completed building your SQL Query, click Validate Query. A pop-up window will appear letting you know if your query was successfully validated.

If an error is found, a message with the error description will be displayed.

If the query is valid, you will be provided a different option based on the statement being used.

Options for SELECT Queries:

  • Cancel: Closes the pop-up window.
  • Preview: Executes the query and displays a sample set of resulting records at the bottom of the Email Editor page.
  • Export: A results file will be downloaded.
  • Update Table: Choose a table where the resulting records will be sent to. The options are:
    • Add records.
    • Add new & update existing records. This option can only be used if the Custom Data Table has a Primary Key.
    • Only update existing records. This option can only be selected if the table has a Primary Key.
    • Overwrite existing records.
  • Save Query: Saves the query. Please notice that if you exit the query without saving your information, the system will not save your changes.
  • Schedule: Allows you to schedule the query to run.

Options for UPDATE or INSERT or DELETE Queries:

  • Cancel: Closes the validation pop-up window.
  • Run: Runs the query. Once the query has completed running the results (error description or performed operations) will appear in the pop-up window.
  • Save Query: Saves the query. Please notice that if you exit the query without saving your information, the system will not save your changes.
  • Schedule: Allows you to schedule the query to run.

Syntax

The iPost SQL Editor is based on MySQL.

The primary web site for MySQL documentation is https://dev.mysql.com/doc/, but only a limited set of operations is allowed in the iPost platform.

Supported Operations:

  • SELECT
  • SELECT COUNT(*)
  • UPDATE TABLE
  • INSERT INTO
  • INSERT ... ON DUPLICATE KEY / INSERT IGNORE
  • DELETE FROM

Denied Operations:

  • DROP TABLE
  • ALTER TABLE
  • CREATE TABLE

Operations on System Data Tables:

  • SELECT ... INTO OUTFILE
  • FLUSH
  • KILL
  • SHOW
  • SET
  • Multiple Statements

SQL Examples

Select Gmail email addresses from “master” table:

SELECT *
FROM 1001_cdt.master
WHERE emailaddress LIKE '%gmail.com'

Select email from “source_table” and Insert into “destination_table” (skipping duplicates):

INSERT IGNORE 1001_cdt.destination_table
SELECT
1001_cdt.source_table.emailaddress
FROM 1001_cdt.source_table

Empty “myTable”:

DELETE FROM 1001_cdt.myTable

Update “myTable” to have a value of 1 for records with myID empty or NULL:

UPDATE 1001_cdt.myTable
SET myID = 1
WHERE myID IS NULL OR myID = ''

Select Contacts from “master” who placed an order in the last 6 months:

SELECT m.*
FROM 1001_cdt.master m
JOIN 1001_cdt.orders o ON o.emailaddress = m.emailaddress
WHERE o.orderdate > date_add(CURDATE(),INTERVAL - 6 MONTH)