BPA and VAC conduct "computer-to-computer" audits. In order to participate in this type of audit, you must provide them with a galley data file and an addition and removal list.
When you run the 'Issue labels' update for an audit issue QuickFill creates a galley data file containing data for all of the qualified subscribers who were served that issue. This is one of the files that you must send to the auditor. Even though you send a copy of the galley file to the auditor you must still leave it in your database sub-directory. QuickFill uses the galley data file for several purposes including compiling information for the 'Publisher's statement' and 'Audit issue galley' reports.
The auditor must be informed the galley data file is a dBASE file (even though we have given it a file extension of "QFDAT"). The galley data file's name is made up of the database name, publication code, and issue number. For example, if your audit issue for publication code WIDGET in the standard QuickFill database (that is the database files are named QFWDB*.QFDAT) was issue number 144, the galley data file would be named QFWDB_WIDGET_0144.QFDAT. The galley data file is located in the database directory.
dBASE files contain a header which describes the data structure of the file. The auditor will be able to access this information. You can do so too by opening the database in Microsoft Excel, provided there are no more than 65,000 records in the file. We have tried to make the file as self-explanatory as possible. In most cases the field names are sufficient.
Here is the structure of the galley data file:
|
Field name |
Type |
Width |
Description |
|
UPDTDATE06 |
Date |
8 |
Date the label update was run |
|
UPDTTIME |
Character |
4 |
Time the label update was run |
|
CUSTNUM |
Numeric |
9 |
Customer number |
|
ORDERNUM |
Numeric |
9 |
Order number |
|
SUBDA |
Numeric |
9 |
Database address of the subscription record |
|
PREFIX |
Character |
12 |
Prefix |
|
FNAME |
Character |
20 |
First name |
|
LNAME |
Character |
30 |
Last name |
|
SUFFIX |
Character |
5 |
Suffix |
|
TITLE |
Character |
40 |
Title |
|
COMPANY |
Character |
40 |
Company |
|
DEPT |
Character |
40 |
Department |
|
STREET1 |
Character |
40 |
Street 1 |
|
STREET2 |
Character |
40 |
Street 2 |
|
CITY |
Character |
26 |
City |
|
STATE |
Character |
2 |
State |
|
ZIP |
Character |
10 |
Zip code |
|
COUNTRY |
Character |
24 |
Country name |
|
CNTRYCODE |
Character |
4 |
Country code |
|
ADDRTYPE |
Character |
1 |
Address type |
|
|
Character |
60 |
Email address |
|
QDATADATE |
Date |
8 |
Date the qual. data was entered |
|
QDATABATCH |
Numeric |
4 |
Batch in which qual. data was entered |
|
QUALDATE |
Date |
8 |
Qualification date |
|
QUALBATCH |
Numeric |
4 |
Batch from which the subscription became qualified |
|
QUANTITY |
Numeric |
4 |
Number of copies |
|
SUBDATE |
Date |
8 |
Original order date of the subscription |
|
ORDDATE |
Date |
8 |
Date of the order from which the issue was served |
|
PAYDATE |
Date |
8 |
Last payment date for the order from which the issue was served |
|
PAYAMT |
Numeric |
9 |
Total amount paid (including 2 decimal places) for the order from which the issue was served |
|
PRICE |
Numeric |
9 |
Price of the order, not including tax or shipping. |
|
SERVICE |
Character |
2 |
Service code for the order from which the issue was served |
|
SHIPCLASS |
Character |
1 |
Shipping class (this field may be blank for group subscriptions in galley files upgraded from Version 1.20 to Version 1.30 of QuickFill) |
|
PERSID |
Character |
20 |
Response to personally identifying question |
|
SUBCLASS |
Character |
3 |
Subscription classification |
|
REQTYPE |
Character |
3 |
Request type |
|
TERM |
Numeric |
5 |
Term of the order, in issues |
|
EXPISS |
Numeric |
5 |
Expire issue |
|
TRACK |
Character |
6 |
Tracking code |
|
SOURCE |
Character |
6 |
Order source for Paragraph 3b (this field used to be named PAR3BSRCE) |
|
Q1 |
Character/Numeric |
3/9 |
Qualification data |
|
Q2_1 |
Character/Numeric |
3/9 |
Qualification data |
|
Q2_2 |
Character/Numeric |
3/9 |
Qualification data |
|
Q2_3 |
Character/Numeric |
3/9 |
Qualification data |
|
Q3 |
Character/Numeric |
3/9 |
Qualification data |
The field names for the qualification data all begin with "Q" and are followed by the question name you assigned in QuickFill. For questions which allow multiple answers there will be one field for each possible answer (the maximum number of responses allowed is defined in QuickFill). In the example above the Qualification form had three questions (named "1", "2", and "3"), questions "1" and "3" allowed only one answer and question "2" allowed a maximum of three answers.
You also need to send the auditor a list of the customer names that were added to or removed from your circulation for each issue within the audit period. To obtain this list you should run the 'Addition and removal list' report that can be found on the 'Audit reports' menu. After running the report, select the 'Print...' command from the Reports menu and locate the report file that you just ran. Look for the name 'Additions and Removals' with the most recent date and time. Select the report by clicking on it, then click the 'Export to Excel' button on the bottom left of the screen. This will produce an Excel spreadsheet which you should send to the auditor.
If you have a large publication, or you have a lot of turnover in your circulation it is possible for the 'Addition and Removals' report to contain more than 65,000 lines, exceeding the maximum length of an Excel spreadsheet. In that case you should run the report separately for each issue in the period, thus producing several smaller lists instead of one large one. Export each of the smaller reports to Excel and send them all to the auditor.