Overview

Skill Level: Intermediate

Prerequisites

A query can search for values within database columns, relational table columns, and also filter by mailing activity.

Note: Updates were made to this call to allow users to use the below criteria in the CreateQuery call.

Step-by-step

  1. Syntax of the request and response XML for CreateQuery

    Operation: <CreateQuery>
    Elements Description  
    QUERY_NAME The name of the new query.  
    PARENT_LIST_ID The ID of the database being queried.  
    PARENT_FOLDER_ID (Optional) The ID of the folder within which to save the query. If not specified, it is stored in the root of Shared or Private based on VISIBILITY.  
    SELECT_COLUMNS (Optional) A pipe-separated list of fields available for personalization. ONLY HONORED BY CLASSIC QUERIES
    ALLOW_FIELD_CHANGE (Optional) Defines whether to allow users to change the fields available for personalization when editing or creating a new query from this query. If not specified, the default is 1 (Allow Changes).Valid values are: 0 – Do not allow changes 1 – Allow Changes. ONLY HONORED BY CLASSIC QUERIES
    Child Elements Description  
    CRITERIA Describes the expressions to perform on one or more columns in the database.  
    EXPRESSION Describes a single expression to perform on a column. One or more EXPRESSION elements can exist within the CRITERIA element. <EXPRESSION criteria_type=’behavior‘>
    <EXPRESSION criteria_type=’relational_table‘>
    <EXPRESSION criteria_type=’profile‘>
    <EXPRESSION criteria_type=’rt_profile’>
    LOCKED_EXPRESSION (Optional) Only one of these elements can exist. <LOCKED_EXPRESSION criteria_type=’profile’>
    <OPERATOR>is in query </OPERATOR>
    <ID>[another query id]</ID> </LOCKED_EXPRESSION>
    COLUMN (Optional) Column name of the database or relational table. Required for ‘profile’ and ‘rt_profile’ criteria. Attribute name for universal behavior elements  
    OPERATOR (Required)
    • is blank
    • is not blank
    • anniversary is
    • today is
    • is after
    • is not after
    • is before
    • is not before
    • contains
    • does not contain
    • starts with
    • does not start with
    • ends with
    • does not end with
    • is equal to
    • is not equal to
    • is less than
    • is not less than or equal to
    • is greater than
    • is greater than or equal to
    • is after
    • is before
    • year is equal to
    • month is equal to
    • day is equal to
    • is equal to one of the following
    • is not equal to one of the following
    • contains one of the following
    • does not contain one of the following
    • starts with one of the following
    • does not start with one of the following
    • is between
    • is within the last
    • is not within the last
    • is within the next
    • is not within the next
    • is exactly
    • is not exactly
    • is within
    • is not within
    • is within the last range of
    • is in contact list
    • is not in contact list
    • is in query
    • is not in query

    Available operators for ‘relational_table’ criteria type:

    • match
    • not match

    Available operators for ‘behavior’ criteria type:

    • has submitted web form
    • has not submitted web form
    • has visited web site
    • has not visited web site
    • have visited web page
    • has not visited web page
    • has triggered custom tracking event
    • has not triggered custom tracking event
    • has been sent email
    • has not been sent email
    • has clicked link in email
    • has not clicked link in email
    • has opened email
    • has not opened email
    • has attained ub event
    • has not attained ub event
    • has downloaded file
    • has not downloaded file
    • has been in program
    • has not been in program
     
    VALUE (Optional) Required when using one of the following operators:

    • contains
    • does not contain
    • starts with
    • does not start with
    • ends with
    • does not end with
    • is equal to
    • is not equal to
    • is less than
    • is less than or equal to
    • is greater than
    • is greater than or equal to
    • is after
    • is before
    • year is equal to
    • month is equal to
    • day is equal to
     
    TIMEFRAME (Optional) These are the following timeframes that can be used:

    • at any time
    • on today
    • before today
    • before today on
    • before
    • after
    • between
    • within
    • the last
    • exactly
    • more than
    • less than
    These are the following operators that can use timeframes:

    • has submitted web form
    • has not submitted web form
    • has visited web site
    • has not visited web site
    • has visited web page
    • has not visited web page
    • has triggered custom tracking event
    • has not triggered custom tracking event
    • has been sent email
    • has not been sent email
    • has clicked link in email
    • has not clicked link in email
    • has opened email
    • has not opened email
    • has attained ub event
    • has not attained ub event
    • has downloaded file
    • has not downloaded file
    • is in query
    • is not in query
    • has been in program
    • has not been in program
    • is in stages
    • is not in stages
    TIME_UNIT (Optional)

    These are the following time units that can be used:

    • days
    • weeks
    • months

    Can be used with these operators:

    • is within the last
    • is not within the last
    • is within the next
    • is not within the next
    • is exactly
    • is not exactly
    • anniversary is
    • is within
    • is not within
    • is within the last range of

    Can be used with these time frames:

    • within the last
    • exactly
    • more than
    • less than
    UNIT
    • ago
    • away

    Used with TIME_UNIT and the following operators:

    • is exactly
    • is not exactly
    • anniversary is

    And the following time frames:

    • exactly
    • more than
    • less than
    PARENS (Optional) Used to specify one or more left/right parentheses when relating more than one expression.  
    CONJUNCTION (Optional) Used to specify AND or OR when relating multiple expressions. <CONJUNCTION>AND/OR</CONJUNCTION>
    RT_EXPRESSIONS (Optional) Child element to an EXPRESSION element when criteria_type=’relational_table’. This element itself can have child EXPRESSION elements with criteria_type=’rt_profile’

    Example RT expression:

    <EXPRESSION criteria_type=’relational_table’>
    <OPERATOR>match/not match</OPERATOR>
    <ID>[rt id]</ID>
    <RT_EXPRESSIONS>
    <EXPRESSION criteria_type=’rt_profile’> [any valid elements for
    profile criteria]
    </EXPRESSION>
    </RT_EXPRESSIONS>
    </EXPRESSION>

    CHANNEL (Optional) Channel Type: PUSH SMS EMAIL Used with the following operators: NEVER-JOINED OPTED-OUT OPTED-IN <EXPRESSION criteria_type=’profile’>
    <OPERATOR>NEVER-JOINED/OPTED-OUT/OPTED-IN</OPERATOR>
    <CHANNEL>SMS/PUSH</CHANNEL>
    <VALUES>
    <VALUE>[channel qualifier 1]</VALUE>
    <VALUE>[channel qualifier 2]</VALUE>
    or
    <ANY>true</ANY>
    </VALUES>
    </EXPRESSION>

    UB_EXPRESSIONS (Optional) Child element to an EXPRESSION element when criteria_type=’behavior’ and operator is UB operator. This element itself can have child EXPRESSION elements with criteria_type=’behavior’ <EXPRESSION criteria_type=’behavior’>
    <OPERATOR>has attained ub event/has not attained ub event</OPERATOR>
    <ID></ID>
    <TIMEFRAME>is exactly</TIMEFRAME>
    <VALUE>10</VALUE>
    <TIME_UNIT>days</TIME_UNIT>
    <UNIT>ago</UNIT>
    <UB_EXPRESSIONS>
    <EXPRESSION criteria_type=’profile’>
    <OPERATOR>[any profile operator]</OPERATOR>
    <COLUMN>[attribute name]</COLUMN>
    </EXPRESSION>
    </UB_EXPRESSIONS>
    </EXPRESSION>

    EMAIL_LINK (Optional) Used with behavior criteria and the following operators:

    • has clicked link in email
    • has not clicked link in email
    <EXPRESSION criteria_type=’behavior’>
    <OPERATOR>has clicked link in email</OPERATOR>
    <ID>[mailing id]</ID>
    <EMAIL_LINK>[link name]</EMAIL_LINK>
    <TIMEFRAME>is within the last</TIMEFRAME>
    <VALUE>10</VALUE>
    <TIME_UNIT>weeks</TIME_UNIT>
    </EXPRESSION>

    WEB_FORM_NAME (Optional)

    Used with behavior criteria and the following operators:

    • has submitted web form
    • has not submitted web form
    <EXPRESSION criteria_type=’behavior’>
    <OPERATOR>has submitted web form</OPERATOR>
    <WEB_FORM_NAME>[web form name]</WEB_FORM_NAME>
    <TIMEFRAME>
    on today/TIMEFRAME>
    </EXPRESSION>

    WEB_SITE (Optional)

    Used with behavior criteria and the following operators:

    • has visited web site
    • has not visited web site
    <EXPRESSION criteria_type=’behavior’>
    <OPERATOR>has submitted web form</OPERATOR>
    <WEB_SITE>[website/domain name]</WEB_SITE>
    <TIMEFRAME>at any time</TIMEFRAME>
    </EXPRESSION>

    WEB_PAGE (Optional)

    Used with behavior criteria and the following operators:

    • has visited web page
    • has not visited web page
    <EXPRESSION criteria_type=’behavior’>
    <OPERATOR>has visited web page</OPERATOR>
    <WEB_PAGE>[web page]</WEB_PAGE>
    <TIMEFRAME>at any time</TIMEFRAME>
    </EXPRESSION>

    FILE (Optional)

    Used with behavior criteria and the following operators:

    • has downloaded file
    • Has not downloaded file
     
    <EXPRESSION criteria_type=’behavior’>
    <OPERATOR>has downloaded file</OPERATOR>
    <FILE>[file path]</FILE>
    <TIMEFRAME>at any time</TIMEFRAME>
    </EXPRESSION>

    EVENT_NAME (Optional)

    Used with behavior criteria and the following operators:

    • has triggered custom tracking event
    • has not triggered custom tracking event
    <EXPRESSION criteria_type=’behavior’>
    <OPERATOR>has triggered custom tracking event</OPERATOR>
    <EVENT_NAME>[file path]</EVENT_NAME>
    <FRIENDLY_NAME>[friendly name] or ANY element</FRIENDLY_NAME>
    <TIMEFRAME>at any time</TIMEFRAME>
    </EXPRESSION>

    FRIENDLY_NAME (Optional)

    Used with behavior criteria and the following operators:

    • has triggered custom tracking event
    • has not triggered custom tracking event
    <EXPRESSION criteria_type=’behavior’>
    <OPERATOR>has triggered custom tracking event</OPERATOR>
    <EVENT_NAME>[file path]</EVENT_NAME>
    <FRIENDLY_NAME>[friendly name] or ANY element</FRIENDLY_NAME>
    <TIMEFRAME>at any time</TIMEFRAME>
    </EXPRESSION>

    ANY (Optional) Can be used as child element of the following elements: FRIENDLY_NAME VALUES <ANY>true/false</ANY>
    ID (Optional)

    Used with the following operators:

    • is in contact list
    • is not in contact list
    • is in query
    • is not in query
    • has been sent email
    • has not been sent email
    • has clicked link in email
    • has not opened email
    • has opened email
    • has attained ub event
    • has no attained ub event
    • has been in program
    • has not been in program
    • match
    • not match
    <EXPRESSION criteria_type=’behavior’>
    <OPERATOR>has been in program</OPERATOR>
    <ID>[program id]</ID>
    <TIMEFRAME>at any time</TIMEFRAME>
    </EXPRESSION>
    <EXPRESSION criteria_type=’profile’>
    <OPERATOR>is in query</OPERATOR>
    <ID>[query id]</ID>
    <TIMEFRAME>at any time</TIMEFRAME>
    </EXPRESSION>
    <EXPRESSION criteria_type=’profile’>
    <OPERATOR>is in contact list</OPERATOR>
    <ID>[contact list id]</ID>
    </EXPRESSION>
    <EXPRESSION criteria_type=’behavior’>
    <OPERATOR>has been sent email/has opened email</OPERATOR>
    <ID>[mailing id]</ID>
    <TIMEFRAME>at any time</TIMEFRAME>
    </EXPRESSION>

    TABLE_ID (Optional) Used for child relational table expressions  
    Response: <RESULT>
    SUCCESS TRUE if successful <Envelope>
    <Body>
    <RESULT>
    <SUCCESS>TRUE</SUCCESS>
    <ListId>25874</ListId>
    </RESULT>
    </Body>
    </Envelope>

     

    This interface supports programmatically creating a Classic query of a Watson Campaign Automation database

    Operation <CreateQuery>
    Elements QUERY_NAME The name of the new query.
      PARENT_LIST_ID The ID of the database being queried.
      VISIBILITY Defining the visibility of the new query. Valid values are:

    • 0 – Private
    • 1 – Shared
      PARENT_FOLDER_ID Optional The ID of the folder within which to save the query. If not specified, it is stored in the root of Shared or Private based on VISIBILITY.
      SELECT_COLUMNS Optional A pipe-separated list of fields available for personalization.
      ALLOW_FIELD_CHANGE Optional Defines whether to allow users to change the fields available for personalization when editing or creating a new query from this query. If not specified, the default is 1 (Allow Changes).Valid values are:

    • 0 – Do not allow changes.
    • 1 – Allow Changes
      Child Element EXPRESSION Describes the expressions to perform on one or more columns in the database.
        CRITERIA Specifies if criteria are locked or editable. Valid values are:

    • Locked
    • Editable.

    If not specified, the default is marked Editable.

    Child Element Optional EXPRESSION Describes a single expression to perform on a column. One or more EXPRESSION elements can exist within the CRITERIA element.
      Optional TYPE The type of evaluation. Valid values are: NE – Numeric DE – Date/Time TE – Text:

    • NE – Numeric
    • DE – Date/Time
    • TE – Text
    Note: TYPE is required when defining database expressions but not specified for relational table expressions.
    Element COLUMN_NAME Optional The name of the database field.

    Note: COLUMN_NAME is required when defining database expressions but not specified for relational table expressions.
      OPERATORS Optional The operator that is used for comparison. Valid values are: != < > = IS NOT null IS null LIKE NOT like IN NOT in BETWEENOPERATORS is required when defining database expressions but not specified for relational table expressions.

    Note: OPERATORS is required when defining database expressions but not specified for relational table expressions.
      VALUES Optional The value being compared to the Watson Campaign Automation database field. This can include text or a numeric or date value. It can also include a list of values or the name of a Column. If specifying a database column, surround with square brackets, such as: [My Text Column]. If specifying a list of values, use the pipe character ( | ) to separate values in a list. Required for all operators other than IS NOT null and IS null.

    Note: VALUES is not specified for relational table expressions.
      TABLE_ID Optional The ID of the Relational Table when one or more relational table expressions are being defined.
      LEFT_PARENS Optional Used to specify one or more left parentheses when relating more than one expression. For example, two left parentheses would be indicated as: ((
      RIGHT_PARENS Optional Used to specify one or more right parentheses when relating more than one expression. For example, two right parentheses would be indicated as: ))
      AND_OR Optional Used to specify AND or OR when relating multiple expressions.
      RT_EXPRESSION Optional

    Describes a single expression to perform on a relational table column. One or more RT_EXPRESSION elements can exist within the EXPRESSION element.

    Note: The RT_EXPRESSION child elements are the same for database criteria and relational table criteria. See EXPRESSION section within the CRITERIA section for definition.
      ChildElements TYPE
    The type of evaluation. Valid values are:

    • NE – Numeric
    • DE – Date/Time
    • TE – Text
        COLUMN_NAME The name of the relational table field.
        OPERATORS The operator that is used for comparison. Valid values are: != < > = IS NOT null IS null LIKE NOT like IN NOT in BETWEEN
        VALUES Optional The value being compared to the Watson Campaign Automation relational table field. This can include text or a numeric or date value. It can also include a list of values or the name of a Column. If specifying a Column, surround with square brackets, such as: [My Text Column]. If specifying a list of values, use the pipe character ( | ) to separate values in a list. Required for all operators other than IS NOT null and IS null.
        LEFT_PARENS Optional Used to specify one or more left parentheses when relating more than one expression. For example, two left parentheses would be indicated as: ((
        RIGHT_PARENS Optional Used to specify one or more right parentheses when relating more than one expression. For example, two right parentheses would be indicated as: ))
        AND_OR Optional Used to specify AND or OR when relating multiple expressions.
        BEHAVIOR Optional Optional; defines the Behavior section of the query. Filters mailing contacts by their activity.
        OPTIONAL_OPERATOR Specifies the operation or activity for which you are searching. Valid values are: 100 – Opened 101 – Clicked 102 – Bounced 103 – No Activity
        TYPE_OPERATOR Specifies whether to search a particular mailing (or all mailings) for the database. Valid values are: 110 – For Any Mailing (only available if query is on the database that is designated to store Automated Behavior Updates) 111 – For One Mailing
        MAILING_ID Optional The ID of the mailing within which to search for contact activity.
        REPORT_ID Optional Required when using the MAILING_ID parameter. The Report ID that identifies the mailing instance within which to search for contact activity.
        LINK_NAME Optional The name of a link in a mailing. Used to include only contacts who clicked a particular link in a mailing.
        WHERE_OPERATOR Optional Specifies whether to compare to an activity count, date of activity, or number of days since activity. Valid values are: 120 – Days Since Activity 121 – Activity Date 122 – Activity Count Only used when you specify For One Mailing and you do NOTinclude a No Activity parameter.
        CRITERIA_OPERATOR Optional The operator that is used for comparison. Required if WHERE_OPERATOR is included. Valid values are: != < > = >= <= BETWEEN
        VALUES Optional The value that you are comparing to the contact activity count, date of activity, or number of days since activity. Might be numeric or date value. If operator is BETWEEN, two values must be provided delimited by a pipe. Required if WHERE_OPERATOR is included.
    Example
    <Envelope>
      <Body>
        <CreateQuery>
          <QUERY_NAME>Has_Mobile_30_Zip</QUERY_NAME>
          <PARENT_LIST_ID>17607</PARENT_LIST_ID>
          <VISIBILITY>0</VISIBILITY>
          <PARENT_FOLDER_ID>9512</PARENT_FOLDER_ID>
          <ALLOW_FIELD_CHANGE>0</ALLOW_FIELD_CHANGE>
          <SELECT_COLUMNS>Last_Name|First_Name</SELECT_COLUMNS>
          <CRITERIA>
            <TYPE>editable</TYPE>
            <EXPRESSION>
              <TYPE>TE</TYPE>
              <COLUMN_NAME>Zip</COLUMN_NAME>
              <OPERATORS>
                <![CDATA[BETWEEN]]>
              </OPERATORS>
              <VALUES>
                <![CDATA[30000|30999]]>
              </VALUES>
              <LEFT_PARENS>(</LEFT_PARENS>
            </EXPRESSION>
            <EXPRESSION>
              <AND_OR>AND</AND_OR>
              <TYPE>TE</TYPE>
              <COLUMN_NAME>Mobile</COLUMN_NAME>
              <OPERATORS>
                <![CDATA[IS NOT null]]>
              </OPERATORS>
              <RIGHT_PARENS>)</RIGHT_PARENS>
            </EXPRESSION>
            <EXPRESSION>
              <TABLE_ID>4645</TABLE_ID>
              <
              AND_OR>AND</AND_OR>
              <LEFT_PARENS>(</LEFT_PARENS>
              <RT_EXPRESSION>
                <TYPE>TE</TYPE>
                <COLUMN_NAME>Purchase Type</COLUMN_NAME>
                <OPERATORS>
                  <![CDATA[=]]>
                </OPERATORS>
                <VALUES>
                  <![CDATA[New]]>
                </VALUES>
                <LEFT_PARENS>(</LEFT_PARENS>
              </RT_EXPRESSION>
              <RT_EXPRESSION>
                <AND_OR>AND</AND_OR>
                <TYPE>DE</TYPE>
                <COLUMN_NAME>Purchase Date</COLUMN_NAME>
                <OPERATORS>
                  <![CDATA[>]]>
                </OPERATORS>
                <VALUES>
                  <![CDATA[[Current Date]-3]]>
                </VALUES>
                <RIGHT_PARENS>)</RIGHT_PARENS>
              </RT_EXPRESSION>
            </EXPRESSION>
            <EXPRESSION>
              <TABLE_ID>8581</TABLE_ID>
              <AND_OR>OR</AND_OR>
              <RIGHT_PARENS>)</RIGHT_PARENS>
              <RT_EXPRESSION>
                <TYPE>TE</TYPE>
                <COLUMN_NAME>Expired</COLUMN_NAME>
                <OPERATORS>
                  <![CDATA[=]]>
                </OPERATORS>
                <VALUES>
                  <![CDATA[true]]>
                </VALUES>
              </RT_EXPRESSION>
            </EXPRESSION>
          </CRITERIA>
          <BEHAVIOR>
            <OPTION_OPERATOR>103</OPTION_OPERATOR>
            <TYPE_OPERATOR>111</TYPE_OPERATOR>
            <MAILING_ID>669614</MAILING_ID>
            <REPORT_ID>502993</REPORT_ID>
          </BEHAVIOR>
        </CreateQuery>
      </Body>
    </Envelope>
    Response <RESULT>
    Elements SUCCESS TRUE if successful
      ListID The ID of the query that was created.
    Example
    <Envelope>
      <Body>
        <RESULT>
          <SUCCESS>TRUE</SUCCESS>
          <ListId>25874</ListId>
        </RESULT>
      </Body>
    </Envelope>

2 comments on"Create a query of a database"

  1. Hello,

    Is there a way to edit, or delete a query through the XML api?

    Thank you
    Vincent

    • Hello Vincent,
      You can use the (Purge Data) – used to delete all records from a Watson Campaign Automation database, suppression, seed, test or contact list (target) based on the contacts that exist in a specific database, contact list, or query (source). – Note that this will work for a Classic Query only at this time.

      Thank you
      Jeri

Join The Discussion

Your email address will not be published. Required fields are marked *