This help page will cover various procedures that are necessary to perform when using the Contact Management engine.
At the head of each CRM page are navigation links to the following:
e-Home. Clicking on this link will open the the e-Services home page.
CRM Home. Clicking on this link will open the CRM home page.
Reports. Clicking on this link will open the Reports home page. This will be useful when creating SQL statements for Audience and Content.
Help. Clicking on this link will open the Help pages for the Contact Management engine.
Logout. Clicking on this link will log the user off of the site and open the e-Services home page.
General knowledge of SQL is required to build audiences and create mailing lists. Snippets and Clusters have been developed to simplify and quicken this process. This section will review the basics of SQL statements used for creating audiences and content.
Note: See SQL Editor Form.
SQL statements are used to retrieve desired information from the tables within a database. It is important to know the tables, their structure and content before building a SQL statement. One way to view the available tables is to do the following:
Click on the Report link in the header of the CRM page.
Click on the Data Dictionary link on the Report home page.
On the Data Dictionary page select the XXDPU database from the drop-down list.
Click on View Results.
The drop-down list on the XXDPU database page will give all of the tables available for retrieving information. Select the desired table and click on the View Results. The following table description will be displayed:
Table Name. The Table Name will be used in the SQL statement to select which table to retrieve the information (example: xxad_personmaster_v).
Column Name. The Column Name lists all the information that may be available for each record stored in the table. Column Name will be used to select what information is to be retrieved from the table. It will also be used to determine what information is to be used when determining the criteria for the data (example: person_id).
Data Type. This will list the type of data that this field holds.
Data Length. This is the maximum character length for the field (example: 22).
Data Precision. This is the number of digits following the decimal point in a numeric field.
Nullable. When "N" is in this field, this entry in the Column Name is a requirement. This information will always be available for each record. If "Y" is in the field, then the information is optional. The record may or may not have information in the field.
Description. This will be a brief description of the information contained in this column.
To retrieve the desired information from the tables, a basic SQL statement must have two commands SELECT and FROM. Many times a third command, WHERE, is used to add criteria.
Suggestion: See SQL Reference. In this SQL Reference, the term "field name" can be interchanged with the term "column name".
SELECT. The SELECT command will determine what information (Column Name or cluster) is to be retrieved. One or more Column Names or clusters can be listed. When more than one are used separate them with a comma (example: SELECT {AD Mailing Cluster Excel}, {AD Admission Decision Info Cluster Excel}).
FROM. The FROM command will determine what table this information is to be retrieved from. This can also list one or more tables. When more than one table are used separate them with a comma.
WHERE. The WHERE command, when used, will list any criteria that must be met for this information to be retrieved. This will normally be Column Names or snippets with operators to define the criteria.
Snippets are pre-built SQL WHERE statements that can be used to build SQL statements for audiences. Snippets can be selected from the Audience form drop-down list. The selected snippet name will be added to the SQL statement.
Note: See Audience Form. When a snippet is inserted into a SQL statement the title of the snippet will be in curly brackets (example: {Address}).
The SQL statement for each snippet can be found by clicking on the Report link in the CRM page header. Then click on the AD Snippets link. This page will list the name, SQL statement and expiration date for each Admin snippet.
Clusters are pre-built SQL SELECT statements that can be used to build SQL statements for content. Clusters can be selected from the Content form drop-down list. The selected cluster name will be added to the SQL statement.
Note: See Content Form. When a cluster is inserted into a SQL statement the title of the cluster will be in curly brackets (example: {AD Mailing Cluster Excel}).
The SQL statement for each cluster can be found by clicking on the Report link in the CRM page header. Click on the AD Cluster (HTML) or AD Cluster (Excel) link. The page will list the name, SQL statement and expiration date for each Admin cluster.
Note: The clusters on the AD Cluster (Excel) page are the ones used on the Content form.
The Audience form is used to build the audience for an intended mailing. SQL statements are used to define the audience and retrieve the information from the tables. Snippets have been designed to reduce the amount of time to create the SQL statement.
SELECT. The SELECT command must state what information (Column Name) is to be retrieved. For an audience, this will normally be a person's ID number. The default entry is "person_id".
Attention: In most cases the SELECT command will be "SELECT z.person_id". This command will link the person_id (Column Name) to the xxad_personmaster_v table.
FROM. The FROM command must state what table the information is to be retrieved from. The default command is xxcis_person.
Attention: In most cases the FROM command will be "FROM xxad_personmaster_v z". The space then "z" alias the table to "z". It is important to alias the xxad_personmaster_v table to "z" so that the snippets will operate correctly.
WHERE. The WHERE command is used to specify the audience. Snippets can be used for quick writing of the SQL statement. Select the desired Snippet from the drop-down list. Add the operators as required between the Snippets (example: {Active} AND {Address} AND {Admitted or No Decision} AND {Paid or No Student Decision} AND z.state < 'IN').
Note: See Snippets and Audience Form.
The Content form is used to build the content for an intended mailing. SQL statements are used to define the content and retrieve the information from the tables. Clusters have been designed to reduce the amount of time to create the SQL statement.
SELECT. The SELECT command must state what information (Column Names or clusters) are to be retrieved. Clusters can be used for quick writing of the SQL statement. Select the desired Cluster from the drop-down list. Add a comma between the clusters.
FROM. The FROM command must state what table the information is to be retrieved from. Enter the table that this information is in. Use a comma to separate the tables if more than one are used.
Attention: When using the xxad_personmaster_v table, alias it to "z" by using the following "xxad_personmaster_v z"
WHERE. In most cases the WHERE command will not be used.
Note: See Clusters and Content Form.
From the Regular Mailing Send Menu, Click the Send link corresponding to the Mailing you want to send. At this point, you should have already identified the audience, the content and the send date of the mailing.
(Audience * Content) + Send_Date = Ready_Mailing
Note: See Regular Mail Send Menu and Regular Mail Send Form.
The next screen validates the SQL statement entered into the Content form. If the SQL is not valid, then you will need to return to the content form screen to re-enter the SQL. (Use the Reports Engine to test the SQL statement.)
The screen also gives the count of audience members who will receive the mailing. To adjust the number, go back to the Audience menu and add or take away criteria from the audience filters (WHERE Snippets).
Note: Counting the audience members may take several seconds. Please be patient.
Once your SQLs have been validated, you are ready to send the mailing.
If applicable, you can override the send date by entering the desired date in the send form.
Click the Send button to continue.
What happens next? Behind the scenes, the database logs a recipient row for each member of the audience group. The mailing list is then assembled from the log (not from the live audience, although it's a matter of seconds). Because the log is constant, you can resend the mailing if necessary.
Edit the Excel Spreadsheet, if necessary. Do the Mail Merge. Complete the mailing.
From the Miscellaneous Communications Menu, click on the Add Record link. This will open the Miscellaneous Communications Form.
Note: See Miscellaneous Communication Menu and Miscellaneous Communication Form.
Select the client who is to receive the mailing. Select up to 5 different contents to mail to the client. Finally select the date to be mailed then save the record.
Follow the instructions on the Miscellaneous Mail Send page.
Note: The Miscellaneous Mail can only be sent once a day. See Miscellaneous Mail Send Page.
Miscellaneous Communications Menu