The aim of this guide is to help you use messageREACH Subscription Management services. It is organized by topic as follows:
Introduction
Subscription Databases
Subscription Database Queries
Build a Query
- Field Name
- Selected Query Criteria
- Operators
- Values
- Logical Operators
- Keyword Mapping
Query Results
- Query Criteria
- Query Name
- Total xx of xx Records Displayed
Download
Database Properties
Subscription Management is a service that will allow you to sign up recipients (single or double opt-in) and manage one or multiple subscription lists. Subscribers may be solicited via direct e-mail or invited to sign up via a link on your website. The subscription form may include any number of data fields that you establish. Subscribers opting in are stored in a database on the messageREACH system. Once having established a subscription database, you may run queries against the database and use the results as a delivery list. You may also download the result set for data analysis.
Subscription Management databases are created by messageREACH customer service personnel. A messageREACH customer service representative will work with you to determine what types of information need to be collected in the database to support your application. For example, a financial newsletter may collect information about the subscriber's age and/or investment strategies. Once your application requirements have been ascertained, the messageREACH customer service representative will create the necessary database and ancillary web pages required to complete the application.
Subscription databases are stored as subsections of a Sybase database. They are free format and are created by messageREACH customer service representatives.
Database types:
Fields that are stored in the database:
Internationalization:
This box lists the names of all available subscription databases that belong to the customer. Names beginning with a '#' prefix are databases that may be shared by all user ID's for that customer. Names beginning with a '!' prefix are databases that are used only by your user ID.
Build a New Query
The Build a Query page will be loaded.
Get Available Query Names
The Available Queries list box will be loaded with all the queries you have under the database you selected above.
This box lists all available queries in your subscription database under your user ID, including customer-level (with '#' prefix) and user-level (with '!' prefix) queries.
A system default query called "Every Record" will be available for selecting the entire database without any conditions. The database SQL statement for this query is equivalent to "SELECT * FROM [your-database]".
Use this optional field on the Job Submission page to select queries (stored at messageREACH) so that you may send a message to one or more customized lists of e-mail addresses assembled by the queries. Click Browse (located to the right of the Subscription Database Queries field). This brings you to the Query Selector window.
Work in the Query Selector window as follows:
The Query Selector window displays the names of your registered queries in the column on the left.
Select a query, then click Add. The query name is copied to the column on the right.
Continue this process until the names of all the queries you wish to use are copied to the column on the right. (Select a query in the column on the right and click Remove if you need to remove a query from that column.)
When you have finished selecting lists, click OK.
The selected query names, separated by commas, are now displayed in the Subscription Database Queries field on the form. The '#' or '!' symbol that precedes your query names is explained below in the Query Names Preceded by "!"or "#" section.
In the Subscription Database Queries field, type the names of any query you
wish to use, choosing from among your queries that are stored at messageREACH.
Separate each query name with a comma. Be sure to accurately type your query
names.
The Build a Query page is a wizard-like page that provides you with a quick
and easy way to build your subscription database query. (Access this page from the Subscription Databases page by selecting a database name and clicking Create New Query.) There is an "Every Record" checkbox on this page. This is a convenient feature that lets you get all records from the database. It will override all the conditions that were added in the Selected Query Criteria list box.
The drop-down list box contains all the parameters of the database that you just selected on the previous page. These parameters are your subscription database table's field names. Queries are created using these names.
The information in this list box will be sent to the messageREACH server for query processing.
Follow these steps to build your query condition:
If you want to add more search conditions, select a Logical Operator ('AND' or 'OR') and repeat steps 1 to 4. The additional condition(s) will be added to the Selected Query Criteria list box.
Follow these steps to remove a query condition from the Selected Query Criteria list box:
When you are finished building your list of search conditions, click the Search button. The search engine will be started to process your request and the query results will be displayed.
Queries are built using the database parameter names and operators. The Subscription Management service supports the following comparison operators:
| Operator | Usage | Example |
| = (equals sign) | Requires exact match For all data types. |
LAST_NAME="Smith" |
| != (not equal) | For all data types. | LAST_NAME!="Smith" |
| < (less-than) | For numeric and date/time data types. | BIRTHDATE<"3/18/1965" |
| > (greater-than) | For numeric and date/time data types. | FAMILYINCOME>100000.00 |
| <= (less-than or equal) | For numeric and date/time data types. | FAMILYINCOME<=70000.00 |
| >= (greater-than or equal) | For numeric and date/time data types. | date_created>="1/12/2001 23:59:00" |
| like | For matching character strings. | FIRST_NAME like "Ch*" |
| is blank | For all data types. | ADDRESS_2 is blank |
| is not blank | For all data types. | ADDRESS_2 is not blank |
If you use an operator that is not supported for the data type you try to compare, for example, LAST_NAME > "Smith", you will get a system error. Also, the default operator is always '='. Remember to pick the desired operator for your query statement.
Many SQL queries will operate with the subscription management database.
You use wildcard characters as placeholders for other characters when you are specifying a value you want to find when using the "like" operator:
In the Subscription Management service, you can use the following characters in queries using the 'like' operator to find matches.
| Character | Usage | Example |
|---|---|---|
| * (asterisk) | Matches any number of characters. | wh* finds what, white, and why *@aol.com finds any string ending in the AOL domain |
| . (full stop/period) | Matches any single character. | B.ll finds ball, bell, and bill |
Values are the variable contents of the fields in your subscription database queries; these match values in the database itself. There are three locations where you can enter values for the queries. They are the drop-down list box, the text-edit field and the date/time fields. The value-input fields (locations 1, 2, 3 in the picture below) are enabled or disabled depending on the parameter's data type.
TheSubscription Management service supports the following data types:
| Data Type | Description | HTML Representation |
| String | A sequence of 255 characters or less | Text Box (2) |
| Numeric | A real number (e.g., 2.37) or an integer (e.g., 125) | Text Box (2) |
| Date/Time | Includes month, day, year, hours and minutes | Drop-down list boxes (3) |
| Money | Currency | Text Box (2) |
| Boolean | True or False | Drop-down list box (1) |
| Enumerator | Custom list of strings from which only a single value can be selected | Drop-down list box (1) |
For example, if you selected NAME from the Field Name list box, the text box (2) is enabled and drop-down list boxes (1) and (3) are disabled because the browser knows that NAME is a string data type.
The Subscription Management service supports the following logical operators:
| Operator | Usage | Example |
| AND | Returns data records if all of its arguments are TRUE. | LAST_NAME="Smith" AND STATE="NY" |
| OR | Returns data records if any argument is TRUE. | HAPPY is false OR FINCOME<12000.00 |
Each parameter may be mapped to a maximum of two messageREACH reserved header keywords. This mapping allows you to refer to data using names that you prefer, while providing the mapping necessary to activate such messageREACH system features as INS substitutions.
Supported mappings include TO, REF, EFORMAT, INSERT1-999 and Password.
The "TO" field is used to identify the parameter that contains the e-mail address. Each subscription database may have only one parameter mapped to the header keyword "TO".
The "EFORMAT" field is used to map to the e-mail format selection keyword. This keyword currently supports values of
html (most modern e-mail clients, AOL version 6 and higher, Compuserve version 6 and higher)
html-lite (limited HTML, such as AOL version 5 and lower, Compuserve version 5 and lower)
text (Plain ASCII text)
default (uses multipart/alternative containing plain text and html)
INSERT[#]
By using inserts, each recipient gets a copy of the message with data inserts that were specifically tailored to that person. Insert numbers correspond to the numbered INSERT[#] data in an associated address list which are generated by the messageREACH system based on you query. You can repeat insert numbers within the same message if you wish to position the associated text in multiple places.
Example
Suppose you have a subscription database that has the following messageREACH keyword mappings:
FNAME mapped to INSERT[3]
LNAME mapped to INSERT[4], and
SALARY mapped to INSERT[5]
Enclose the insert number-preceded by the uppercase letter I (for "Insert")-in parentheses, as in the following example:
I personalized this message to you, (I3) (I4), by using inserts. I am pleased to announce your 40% raise; your new salary is (I5). Congratulations!
You can add inserts wherever each insert is desired. messageREACH replaces the inserts with the corresponding data in the database. For example, the recipient named Joy Baker might get this version of your message:
I personalized this is message to you, Joy Baker, by using inserts. I am pleased to announce your 40% raise; your new salary is $150,000. Congratulations!
The results of a
query are displayed in a row-and-column format. The first row shows the parameter
names that come from your subscription database. Up to 100 rows are
displayed. The total number of records that match the query is shown in the bar below the last row.
You can use the messageREACH prefixes '!' and '#' to distinguish
between user-level and customer-level queries. For example, '!mytest' and '#mytest'
are legal names. However, 'my!test', or 'mytest#' are illegal names. The query
name 'mytest' is equivalent to '!mytest'.
You can download search results as a CSV (comma-separated values) file to your PC:
After you upload queries to messageREACH, you will notice
that when you choose queries from the Available Queries list, an additional
character may precede the query name. messageREACH adds the character to distinguish between two types of queries: user-level and customer-level queries.
User-level query names are preceded by an exclamation point. For example,
"!myquery" indicates that "myquery" is a user-level query. Customer-level
lists are distinguished by a pound sign ("#") preceding the query name. For
example, "#myOtherQuery" indicates that "myOtherQuery" is a customer-level
list.
The subscription database properties page will allow you to display the details and layout of the selected database. This information will be helpful during query creation.
Click the Printable Version button on the bottom of the page to switch to a printable version.
This section provides information regarding your subscription database, and includes:
These parameters are your subscription database table's field names displayed in a row-and-column format. The first row shows the parameter's attributes:
A form set is a collection of all the HTML forms and email confirmation messages that are required for a subscription application. Each subscription application must have at least one form set but could have as many form sets as required.