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:

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.

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:

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:

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:

 

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

 

 

Read

12 input records

 

Skipped

1 deleted input records 

 

Rejected

3 duplicate input records 

 

Merged

1 existing customer records 

 

Created

7 new customer records 

 

Created

8 prospect records 

 

 

0 errors found 

 

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.

The import will left justify and space fill strings.

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 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 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.

 

ID1

String

Length 6

ID2

String

Length 8

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.

 

ID3

String

Length 15

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

 

PREFIX

String

Length 12

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

 

FNAME

String

Length 20

The prospect's first name or names.

 

LNAME

String

Length 30

The prospect's last name.

 

SUFFIX

String

Length 12

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

 

SALUTATION

String

Length 15

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.

 

SEX

Code

Length 1

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.

 

TITLE

String

Length 40

The prospect's title or position.

 

COMPANY

String

Length 40

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.

 

DEPT

String

Length 40

The department within the prospect's company.

 

STREET1

String

Length 40

STREET2

String

Length 40

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

 

CITY

String

Length 26

The city for the prospect's address.

 

STATE

Code

Length 2

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

 

ZIP

String

Length 10

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.

 

ZIP2

String

Length 2

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

 

CARRT

String

Length 4

The carrier route code.

 

CNTRYCODE

String

Length 4

COUNTRY

String

Length 24

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.

 

TAXJR

Code

Length 3

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.

 

PHONE

String

Length 20

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.

 

CELLPHONE

String

Length 20

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.

 

FAX

String

Length 20

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.

 

EMAIL

String

Length 60

EMAIL2

String

Length 60

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

 

USERID

String

Length 15

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

 

PASSWORD

String

Length 15

Enter the prospect's password.

 

TAXABLE

Code

Length 1

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

 

RENTPOSTAL

Code

Length 1

RENTEMAIL

Code

Length 1

RENTPHONE

Code

Length 1

RENTFAX

Code

Length 1

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:

Y

- Al uses allowed

I

- In-house use allowed

R

- Renewal notices only

N

- No use allowed

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

 

BADSEED

Code

Length 1

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

DEMCODEA (Demo Code A)

Numeric

Length 4

DEMCODEB (Demo Code B)

Numeric

Length 4

DEMCODEC (Demo Code C)

Numeric

Length 4

DEMCODED (Demo Code D)

Numeric

Length 4

 

DEMDATA1 (Demo Data 1)

String

Length 19

DEMDATA2 (Demo Data 2)

String

Length 19

 

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.

 

EXTCUSTID

String

Length 12

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.

 

SELFLAGS

String

Length 8

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.

 

CASSDATE (CASS Certification Date)

Date

Length 10

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.

 

CUSTMISC (Miscellaneous info)

String

Length 60

Use this field to hold any miscellaneous data about the prospect  that you wish to maintain.  This data will be included in the exports and can be accessed using ODBC.

 

LIST

Code

Length 12

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 list, namely the one specified on the import filter screen at the time the import is run.

 

PROSPDATE (Prospect date)

Date

Length 10

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.

 

PROSRQTYPE (Prospect Request Type)

Code

Length 3

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

 

PDW

Personal direct written

PDT

Personal direct telemarketing

PDI

Personal direct Internet/E-mail

CDW

Company direct written

CDT

Company direct telemarketing

CDI

Company direct Internet/E-mail

MI

Membership individual

MO

Membership organization

NRW

Non-request written

NRT

Non-request telecommunication

NRI

Non-request Internet/E-mail

AR

Association roster

BD

Business directory

FR

Field reports

LIC

Licensees

LIS

Lists

OTH

Other sources

 

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

 

PUB (Publication)

Code

Length 12

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.

 

QDATE or QUALDATE
(Qualification Data Date)

Date

Length 10

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.

 

QBATCHDATE (Qualification Batch Date)

Date

Length 10

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

 

QBATCHNUM (Qual. Batch Number)

Numeric

Length 4

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.

 

PERSID

String

Length 20

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

 

Qualification Data

String

Length varies based on number of responses

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.

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.

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.

 

 

See Also

 

ERROR MESSAGES

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

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

Both the country name and the country code are blank.

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.

The country code field is blank.

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

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.

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).

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).

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

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

The country code is not defined in the country table.

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.

The prospect date field contains an invalid date.

The qualification batch date field contains an invalid date.

The qualification batch field contains non-numeric data.

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

The request type field contains an invalid request type.

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.)

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.

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.

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

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.

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).

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).

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

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.

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.

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

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.

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.

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

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.