How to import prospects

This section describes the methods you can use to convert prospects into the QuickFill system. It also provides technical documentation for QuickFill's prospect import found on the Other menu.

There are two approaches to converting prospects into QuickFill:

• You can key the names using the 'Prospect' transaction found on the QuickFill transaction menu. Usually you will only do this when you have a limited number of names to enter.

• You can use the prospect import option found under Imports on the Other submenu of QuickFill's main menu.

To import prospects into QuickFill the data must be in a dBASE file or a comma-delimited text file or a tab-delimited text file. The file must have  the name and address arranged into fields with specified filed names. The QFConvert program can assist you greatly in getting your data into that form.

USING QFCONVERT

Included with QuickFill is a separate application program named QFConvert that can help you reformat your data. QFConvert will accept a wide variety of input file formats including Excel, Access, comma delimited files and convert them into the format that QuickFill needs for importing. QFConvert contains a powerful set of address-processing functions that can split full names into separate first and last names, and identify address lines as company names, titles or departments. These functions allow you to accept files containing name and address data in almost any format and generate clean output files with each part of the name and address in its own field. QFConvert can also perform address certification in conjunction with ZP4.

Click here for more information on QFConvert.

The following procedure shows the steps required to convert prospects by using QFConvert:

1. Extract the data from your existing system into a file.

2. Use QFConvert to reformat the data in the file to conform to QuickFill's prospect file specification (see below).

3. Optional step:
   Review the data using Microsoft Excel or a dBASE database program and make any necessary minor corrections. If major changes are required go back to the second step.

4. Use the QuickFill system to define the lists that will be used to identify the prospect lists that you are importing. You do this by using the Lists option found under Marketing on the Main menu.

5. Make a backup copy of the QuickFill database.

6. Run the prospect import.

7. Review the error messages produced by the prospect import. If necessary go back to the second step, third step, or fourth step after restoring the QuickFill database from the backup copy made prior to running the prospect import.

8. Optional step:
   Process the rejected duplicate customers file. This will only be necessary if you have opted to use the "de-duping" capabilities (explained below) of the prospect import.

REKEYING YOUR DATA

If you have a good supply of patience you can create the file by keying in all of the names, addresses and other data using a standard database program. Suitable programs include Microsoft Access, Microsoft FoxPro, Microsoft Excel and White Town Software's DBF Editor and Viewer.

The following procedure shows the steps required to convert a set of prospects using this method:

1. Create an empty file with the correct structure. Use the prospect export in QuickFill to export a dBASE file. If your publications are audited also export the audit fields. Set the maximum number of customers desired to one so that only a single record is exported.

2. Using your chosen database program to open the file that you just created in QuickFill.

3. Erase or overwrite the single record that QuickFill exported.

4. Continue entering new records to end of the file using the record format that QuickFill created for you.

5. Use the QuickFill system to define the lists that will be used to identify the prospect lists that you are importing. You do this by using the "Lists" option found under "Marketing" on the Main menu.

6. Make a backup copy of the QuickFill database.

7. Run the prospect import.

8. Review the error messages produced by the prospect import. If the errors are few and/or minor you can probably correct them using QuickFill transactions. Otherwise restore the database from the backup copy made prior to running the prospect import, then return either to the first or second step.

9. Optional step:
   Process the rejected duplicate customers file. This will only be necessary if you have opted to use the "de-duping" capabilities (explained below) of the prospect import.

MULTIPLE PROSPECT LISTS

If you have more than one prospect list you may either create separate files for each list or create a single file for multiple lists (see the "List" field below). It is important to note that if you choose to create a single file for multiple lists and you have individuals that are members of more than one list you must create separate records for each list membership. If you choose to create a separate file for each list simply run the prospect import once for each list.

You can also import a single list in stages. For example you can have several people rekeying prospect names from the same list into different files. You can then import each of these files into the same prospect list by running the import once for each file.

PRE-IMPORT REQUIREMENTS

Once you have created a prospect file the next step is to use the QuickFill system to define the list(s) that you are importing. Click here for information on defining a list.

In addition to defining the list(s) you should also make sure that you have defined the following in the QuickFill database prior to running the Prospect import:

• Foreign country code definitions for all of the foreign countries referenced by the CNTRYCODE field.

• Audit Users Only: if you are importing prospects with audit qualification data you must also have define your audited publication(s) before running the import.

Once you have defined all of these codes, make a backup copy of the QuickFill database. It is important that you have a backup copy of the database made prior to importing the prospects because it is possible that the import will uncover some problems which you will want to correct by first fixing the file and then re-running the prospect import.

DUPLICATES

You probably have duplicate customers in your existing data. This may be because you have multiple prospect lists or simply because your current system does not recognize duplicates.

The QuickFill prospect import recognizes possible duplicate customers as falling into two categories -- near and exact matches. The import utilizes the same duplicate detection strategy as the QuickFill transactions and the Merge duplicates update. Click here for a complete explanation of 'Address matching' and the duplicate detection strategy.

When you run the prospect import you specify the scoring cutoffs to be used for determining near and exact matches. You also specify how the import is to process each incoming record once it has been identified as being an exact match, a near match or a non-match. Click here for a complete explanation of the options when importing prospects.

THE PROSPECT IMPORT

The Prospect import is on the Other menu. You must specify the name of the input file and of a reject file on the Import filter. The importer will write any records that rejected because they are duplicates to the reject file using the same format as the input file. Having the reject file be in the same format as the input file make it convenient to use the reject file as an input file in a subsequent run of the importer.

You are required to supply a list code on the import filter. Any records in the input file with a blank list field will be attached to the list you supply on the filter.

The Import filter also allows you to specify the treatment for exact matches, near matches and non-matches. For both exact and near matches you have five choices:

• Merge, retaining the existing address

• Merge, replacing the existing data with the incoming data

• Copy the incoming data to the reject file

• Create a new customer record

• Ask me

If you select "Ask me" the import will display matches as it locates them and will allow you to decide what you would like to do with the possible duplicates on a case-by-case basis. This option requires you to be present while the import is running. If you have a large file or suspect that there will be a considerable pool of possible duplicates consider using the "Copy to the reject file" option instead of "Ask me". Then you will know how many possible duplicates there are and can set aside the appropriate amount of time to run the import using the reject file as the input file.

For non-matches you have two options:

• Create a new customer record

• Copy the incoming record to the reject file.

The import produces a report when it has completed processing your input file. The summary should look something like this:

The "Skipped deleted" category show the number of records in your input file that were marked as deleted. These records were not imported.

The "Rejected" category shows the number of records rejected as dupes either automatically by the import (if you have used the "Copy to the reject file" option) or by you (if you have used the "Ask me" option).

The "Merged" category shows the number of records in your input file for customers who already existed in your database (or in a prior imported record in the input file). "Merged" customer records are created if you use either of the "merge" options.

In the sample above the input file contained twelve records. One was rejected because it had been marked as deleted in the original input file. Three were rejected because of duplicate addresses. Of the remaining eight, there was one record for a customer already in your database.

The seven new customers are the result of: 

 12(original) - 4(skipped/rejected) - 1(merged) = 7.

The eight new prospect records are the result of:

 12(original) - 4(skipped/rejected) = 8.

Remember the merged customers still have new prospect records.

The final line "x errors found" is a count of the number of individual error messages printed in the Import summary report.

THE PROSPECT IMPORT FILE RECORD FORMAT

We now present a field-by-field description of the fields in the file that are used by the prospect import.

Your file does not need to have all the file fields. If a field is missing, then it is treated as if it were blank. Be very careful that the fields you want imported have the correct field name or they will be ignored. The fields do not have to be in the order listed below nor do they have to have the same lengths. Leading spaces will be stripped. If the field is still too long then characters or digits on the end will be dropped. Additional fields in the file whose names are not listed below will be ignored.

Field types

There are four basic field types: strings, codes, numeric and dates.

• Strings

The import will left justify and space fill strings.

• Codes

The import will left justify and space fill code fields like string fields, and also convert them to upper case. Generally, you should define code fields in the QuickFill database before running the import. If the import finds a code that you have not defined in the database it will produce an error message. The import will define the code and then import the record containing it. Subsequent occurrences of the code will use the definition created by the import. It is unlikely that the definition created for you by the import will contain the data you want. Be sure that you check and correct all codes created by the import.

• Numeric

Numeric fields are expressed using ASCII digits. Numeric fields may contain leading or trailing spaces which will be ignored by the import. If the prospect import finds an error in a numeric field it produces an error message and treats it as though it were blank (blank numeric fields are treated as though they contained '0').

• Dates

Dates can be entered either in YYYYMMDD format with no separators or in MM/DD/YY format with separators. The latter actually matches the standard format as specified on the QuickFill Preferences screen which is typically MM/DD/YY, but can be changed to DD/MM/YY or DD.MM.YY to suit your local custom. When entering dates with separators you can use either two or four digit years. When using two digit years, a year less than 70 will be treated as being in the twenty-first century. Years greater than or equal to 70 are assumed to be in the twentieth century. The month and day can either have leading zeroes or not.

Date fields in dBASE files (type code 'D') are always stored in YYYYMMDD format although you may see them in various other formats on the screen depending on which application you use to view the dBASE file. Character fields in dBASE files (type code 'C') can also be used to enter dates using either the YYYYMMDD or MM/DD/YY format. For character fields the format you see on the screen will always match what is in the file.

The format of date fields in Excel is controlled by the date format of the cell or column, which is typically M/D/YYYY (four digit years, no leading zeroes on the month and day), but may vary depending on the regional settings of the Windows system.

Field description of the fields that are used by the prospect import

The following is a field-by-field description of the fields in the input file that are used by the prospect import.

The import will include these two fields when it creates an error message. This is a good place to put your previous system's id number. Another good use for one of these fields is a sequential record number. In case you ever sort the file you can restore it to its original order by resorting on this field. The contents of these fields are NOT imported into your QuickFill database.

This field is not used by the prospect import. It is listed here because it is produced by QuickFill's subscription export.

The prefix for the prospect, such as Mr., Mrs., Ms. or Dr.

The prospect's first name or names.

The prospect's last name.

The suffix for the prospect's name, such as Jr., Sr. or Esq.

The salutation with which the prospect should be addressed on any correspondence. The name that you place in this field will appear on any form letters that you create using the Customer export command on the Other menu. If you leave the salutation blank then the prefix and last name will be used instead.

Enter the person's sex, if you wish. QuickFill does not restrict you to the codes 'M' or 'F' so you can actually use this field to encode additional information about the prospect that may be useful for special selections. If your old system coded "company only" addresses use this field to capture that data. We suggest that you use 'N' for company only addresses and 'U' where the sex is unknown.

The prospect's title or position.

The prospect's company name. If both the prospect's last name and this field are left blank the import will produce a warning message when it imports the record.

The department within the prospect's company.

Two lines are available for entering street address data. If only one line of data exists use STREET1.

The city for the prospect's address.

Enter the post office code for the state or the Canadian province. For foreign countries other than Canada enter a code of "ZZ".

For U.S. addresses enter the zip code using either the common 5-digit format or the newer Zip+4 format. For Zip+4 you must include the "dash" (nnnnn-nnnn).

For Canadian addresses enter the postal code in the form "ANA NAN" where the A's are letters and the N's are numbers (the space in the middle is optional).

U.S. and Canadian zip codes are checked against the state/province code that you entered. If they do not match the import will display an error message.

For other foreign addresses enter the postal code.

The last two digits of the Zip code in 5+4+2 format. These digits are used to create Delivery Point Barcodes.

The carrier route code.

These fields should be filled in for foreign (non-USA non-Canadian) addresses only. For USA and Canadian addresses (as identified by the STATE field above) data contained in these fields will be ignored.

The CNTRYCODE should be defined in the "Foreign countries" table in QuickFill.

If you fill in the COUNTRY field, it should match the Country found in the Foreign country table listed under the CNTRYCODE.

For all foreign addresses (STATE="ZZ"):

If you leave the CNTRYCODE blank the import will produce an error message.

If the CNTRYCODE is entered the import will look it up in the QuickFill Foreign country table. If it is not found the import will produce an error message. If it is found but the COUNTRY name in the table does not match the one entered in the file the import will produce an error message.

In any of the above cases the data entered in these fields will be imported into the QuickFill database.

Tax jurisdiction. If your publications are not taxable in any state or province, or taxable only in states/provinces that have a single tax rate and a single reporting requirement, then you may leave this field blank. If you have defined a Tax jurisdictions table in QuickFill, the import will use that table to assign the jurisdiction that corresponds to the prospects country and postal code. If you have not defined a Tax jurisdictions table, the import will assign the prospects state/province code as the jurisdiction.

If the prospect is located in a city or county that has a tax rate different from the state/province-wide rate and you have not defined a tax jurisdictions table, or if the prospect is located in a taxable foreign country, then you should enter the jurisdiction code for that city, county, or country. You define the jurisdiction codes in your QuickFill database on the "Tax jurisdictions" screen under Definitions on the main menu.

Enter the prospect's telephone number, with area code if available. If the phone number is not in QuickFill's conventional (999)999-9999 format, the import will attempt to convert it to that format. For example 123-456-7890 will be converted to (123)456-7890.

Enter the prospect's cell phone number, with area code if available. If the phone number is not in QuickFill's conventional (999)999-9999 format, the import will attempt to convert it to that format. For example 123-456-7890 will be converted to (123)456-7890.

Enter the prospect's fax number, with area code if available. If the phone number is not in QuickFill's conventional (999)999-9999 format, the import will attempt to convert it to that format. For example 123-456-7890 will be converted to (123)456-7890.

Enter the prospect's primary or secondary E-Mail address.

Enter the prospect's user ID. The user ID should be unique for each prospect.

Enter the prospect's password.

Enter 'N' if the prospect is a tax-exempt organization. If the field is blank a value of 'Y' is assumed.

• Important: Do not make the mistake of assuming that you should enter an 'N' if the prospect lives in a state for which you do not collect sales tax. If the prospect subsequently moves to a taxable state and then becomes a subscriber you want to start charging sales tax on future orders. QuickFill will handle this automatically when you change the state code providing you have not marked the prospect as tax-exempt.

These fields control how you use the prospects name and address for postal, E-mail, telephone, and fax correspondence.  Enter one of these four codes:

These codes are used in the selection of promotional labels.  If a field is blank, a value of 'Y' is assumed.

This field is used to specify whether or not a customer is a bad debt customer or a seed.  Enter "B" for a bad debt customer, "S" for a seed, or a space for a normal customer.  Youll use these codes when you create promotional labels.

Marking a name as a "Bad debt", causes QuickFill to show you that name whenever you enter a subscription with the same zip code and street address. This is useful for handling persons that repeatedly order subscriptions under assumed names but never pay for them.

Seed names are used for policing usage of any names that you may rent to other publishers. If a name is marked as a seed it will be included in any selection of promotional labels regardless of whether it meets the selection criteria or not (there is an override to exclude seed names if you so desire).

DEMOGRAPHIC FIELDS

There are four numeric fields and two alphanumeric fields you use to encode special information about the customer. If any of the four numeric are blank, then they will be set to zero. You can use these fields for whatever purpose you wish. The Promotional labels, Customer directory, Subscription statistics, Blanket renewals, Customer export, Prospect export, and the Subscription export filters provide a means of selecting customers based on these six codes.

QuickFill assigns its own customer numbers to all of the customer records it creates. If you need another identifier or number to link the customer to an external system you can use this field for that purpose.

There are eight optional true/false flags that can be used to identify groups of customers. To set one of these flags include the corresponding flag number in this field. For example, if you want to set the third, fifth and eighth flag, enter "358" in this field.

You may use this field to specify the most recent date on which the prospects' addresses were CASS certified. Although this field is not a required one, if you know the CASS certification date for your prospects' addresses you should include it in your file. A number of the report filters and all of the export filters in QuickFill provide the ability to select customers based upon their CASS certification date.

If the file you are creating contains prospect records for more than one prospect list you may use this fields to specify the list code. If the List field is left blank the import will assume that all the records in the file are for the same listnamely the one specified on the import filter screen at the time the import is run.

You may use this field to specify the date for the prospect record. If this date is left blank the import will supply the system date for this field.

• The remaining fields apply to the audit system only.

The means by which you received the prospect name. Use one of the codes from the following table:

If this field is blank, then the import will assume a value of OTH (Other sources).

If you are entering qualification data this field is required. The publication must have already been defined in the QuickFill database. All prospects for the same publication do not need to be together in the file.

The date the prospect completed the qualification form. If this field is blank, then the import will use the prospect date. We strongly recommend that you do not leave this field blank if you are entering qualification data. The field name may be either QDATE or QUALDATE. Only one of these fields is needed. If you have both QDATE and QUALDATE fields the QDATE field will take precedence.

The date you recorded the qualification data. If this field is blank, then the import will use the current date.

The batch number used to record the qualification data. If this field is blank, then the import will use the batch number of the import.

The response to the personally identifying question which was asked when qualifying the subscriber by telephone or over the Internet.

The audit qualification data fields apply to the qualification form. The field names should match your qualification form questions on the publication definition 'Audit' tab. For example, the response(s) for question 1 should be in a field named Q1, the response(s) for question 1A should be in a field named Q1A, etc. A question can have up to three alphanumeric characters.

The number of qualification data fields and their lengths will vary depending on the number of questions in your qualification form(s) and the number of responses allowed for each question. For questions that allow multiple responses, all responses should be put into the field that matches the question number and should be separated by commas. For example, lets say that the possible responses for question 1 are the numbers 1 through 10, you allow multiple responses, and one of your customers responded with 1, 3, 4, and 5. Your import file should have a field named Q1 that contains 1,3,4,5 for that customer.

The qualification data fields should be defined as character fields if the allowed responses are letters or numbers or as numeric fields if the allowed responses are numbers and the corresponding Code field on the publication definition 'Audit' tab in QuickFill contains "#". The response(s) to questions can have up to three alphanumeric characters for character fields and up to nine digits for numeric fields.

• Note: importing prospects for multiple audited publications

When importing prospects that include qualification data and are for multiple publications with different qualification forms, the import file should contain a separate field for each question that is unique. For example, if the qualification form for publication A has questions 1, 2, and 3 and the qualification form for publication B has questions 1, 1A, and 1B, the import file should have qualification fields named 1, 1A, 1B, 2, and 3.

• Note: limit on number of qualification data fields

The number of qualification data fields that your import file can have is limited according to the maximum number of fields allowed for dBASE files. The field limit is 255 fields.

ERROR MESSAGES

The following is an alphabetical list of error messages generated by the prospect import.

• Both last name and company are missing

Every prospect should have a person's name or a company name.

• Country code and country name missing

Both the country name and the country code are blank.

• Country code does not match country name

The country code has been found in the country table but the country name in the table does not match the one in the data being imported.

• Country code missing

The country code field is blank.

• Demographic code X contains non-numeric data

The four demo code fields (A, B, C and D) must be numeric. Use the demo data fields for non-numeric demographic data.

• Duplicate User ID new ID assigned

User IDs should be unique for each prospect. When the ID of a prospect matches that of another customer, QuickFill assigns an alternate ID to the prospect you are importing.

• Email address rental code invalid

The 'E-mail' allowed usage field (RENTEMAIL) contains an invalid code.  This field should contain Y (all uses allowed), I (in-house use only), R (renewal notices only), or N (no use allowed).

• Fax rental code invalid

The 'Fax' allowed usage field (RENTFAX) contains an invalid code.  This field should contain Y (all uses allowed), I (in-house use only), R (renewal notices only), or N (no use allowed).

• Future qualification date

The qualification date field contains an invalid date sometime after today. It is important to enter a correct date for audited publications.

• Invalid Canadian postal code format

Canadian postal codes should have the format ANA NAN where 'A' is an alphabetic character and 'N' is a number.

• Invalid country code

The country code is not defined in the country table.

• Invalid code: X in response to Question X.

Response X is not a valid response for question X on your qualification form. Either the response is not one of the responses defined in your qualification form (on the Audit tab of the publication definition) or, if the response is a letter, it is not in the same case (uppercase/lowercase) as it is in your publication definition.

• Invalid prospect date

The prospect date field contains an invalid date.

• Invalid qualification batch date

The qualification batch date field contains an invalid date.

• Invalid qualification batch number

The qualification batch field contains non-numeric data.

• Invalid qualification date

The qualification date field contains an invalid date or is missing. It is important to enter a correct date for audited publications.

• Invalid request type

The request type field contains an invalid request type.

• Invalid state or province code

The state field did not contain a valid U.S. state code, Canadian province code, or the code 'ZZ' for a foreign country. (Click here for U.S. state codes or here for a list of Canadian province codes.)

• Invalid zip code format

The incoming record is for a USA address and the zip code is either non-numeric, its length is not 5 or 10 (zip plus4) or its length is 10 but there is no hyphen.

• Leave blank for normal records, or enter S for seeds and B for bad debts

The customer 'Type' field (BADSEED) contains an invalid entry.  This field should contain S for prospects you wish to mark as seeds, B for prospects you wish to mark as a bad debt customer, or nothing for normal prospect records.

• Match code is blank probably the whole address is blank

The company, zip, and last name fields are all blank.

• Only one code may be entered for Question X.

The incoming record contains more than one response for question X, but question X is defined (in the qualification form on the Audit tab of the publication definition) as allowing only one response.

• Phone rental code invalid

The 'Phone' allowed usage field (RENTPHONE) contains an invalid code.  This field should contain Y (all uses allowed), I (in-house use only), R (renewal notices only), or N (no use allowed).

• Postal address rental code invalid

The 'Postal' allowed usage field (RENTPOSTAL) contains an invalid code.  This field should contain Y (all uses allowed), I (in-house use only), R (renewal notices only), or N (no use allowed).

• Publication code missing

The publication field is blank. This record is processed as a non-qualified prospect. Any data in the qualification fields is ignored.

• Publication not defined in the database

The publication code is not in the database. This record is processed as a non-qualified prospect. Data in publication field and the qualification fields is ignored.

• Qualification batch date is blank.

The incoming record does not contain a qualification batch date, but is classified as being qualified and contains a qualification date and/or qualification data.

• Qualification date is blank.

The incoming record does not contain a qualification date, but is classified as being qualified and contains qualification data.

• Question X is required.

The incoming record does not contain a response for question X, but question X is defined (in the qualification form on the Audit tab of the publication definition) as being required.

• Question X requires a numeric response.

The incoming record does not contain a numeric response for question X, but question X is defined (in the qualification form on the Audit tab of the publication definition) as having a numeric response.

• Too many digits in response to Question X

The numeric response for qualification question X, in the incoming record, contains too many digits.

• Zip code incorrect for the state

The first three digits of the zip code do not match the state code. Click here for a list of valid zip code ranges for each state.