Here are just some of the options for doing Data Mining Operations in CP SQL.
1. From within CP SQL.
Use any standard CP SQL report that has most of the data and fields that you already need.
The selection criteria can be narrowed down using the "customize" option on the Tabs to limit (filter) the type of data being returned. This report can then be output to an Adobe Acrobat PDF file or to an Excel spreadsheet. Adobe PDF files can be emailed at you leisure. If data is output to Excel, you can manipulate it as desired.
2. Using Microsoft SQL Management Studio or Microsoft SQL Management Studio Express.
Simple to complex SQL scripts can be written to query-up specific data. The query can be saved for later re-use.
Example Task: I’m looking to get a list of all customers residing in a specific zip code that we have email addresses for.
Here’s an example of the SQL Syntax:
Select ZIP_COD, EMAIL_ADRS_1 from AR_CUST WHERE ZIP_COD LIKE ‘%98105%’
AND EMAIL_ADRS_1 IS NOT NULL;
This above example would return a list of all customer email addresses within a specific zip code range, but would ignore selecting any customers with blank email addresses. With the zip code wild-carded like above, it would pick any customers in zip code "98105" including those with Zip + 4 codes.
Example Output:
ZIP_COD EMAIL_ADDRS_1
98105 camcmaster@verizon.net
98105-3412 jsmith@msn.com
If you just wanted the email addresses, you could simplify eliminate the "ZIP_COD" column from the first part of the select statement. The output could also be redirected to a file.
3. External Software programs.
Any software application that can read an MS SQL ODBC DSN can be used to query data from the database, and output to a report or file. Examples of this are products such as MS Access, Crystal Report Writer, Gold Mine, ACT, etc.
As an example, you could use an MS Query with MS Excel to find specific data similar to the one shown in #2 above, that extracts to a spreadsheet and could then be converted to an ASCII CSV file for use in bulk emailing projects.
4. Some other examples of the kinds of data that you could mine are:
-
A list of customer names and phone numbers of all customers who purchased a specific item with in a specific data range. – Helpful for selling add-on products, notifying customers about a product recall, or determining Spiffs for sales people.
-
A list of discontinued items, with no quantities on hand, that are in a specific category and have a Last Sale Date that is earlier than a specific cut-off date.
-
Outputting detailed customer name and address info for use in a doing a catalog or a direct mailing project.
If you would like more information on the above examples and/or if you need assistance with doing something similar to the above, please contact the CCS Retail Systems Support Department.