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
  • combining data from Custom and System Data Tables

Creating a SQL Query

To create a query, follow these steps:

  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
Click to copy

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
Click to copy

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 Las 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.

SELECT Query options:

  • 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. 

UPDATE or INSERT or DELETE Query options:

  • 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.

Operations

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'
Click to copy

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
Click to copy

Empty “myTable”

DELETE FROM 1001_cdt.myTable
Click to copy

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 = ''
Click to copy

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)
Click to copy