This Readme version updated 5/2004. NOTICE: the Software is available with purchase of the Drilling Down book from any bookseller; instructions on how to download the software are at the top of the first page of the Appendix to the book and at the end of the Introduction to the book. The application runs on Microsoft Access 2000 and requires Microsoft Excel to create a pivot table. The Access 97 version is included in the download "just in case" but can be unreliable depending on your environment. The Drilling Down Software END USER LICENSE AGREEMENT Use of The Drilling Down Software (hereinafter 'SOFTWARE') is contingent on your agreement to the following terms: 1. GRANT OF LICENSE The Drilling Down Project grants you a limited non-exclusive license to use the SOFTWARE free of charge. You are using the SOFTWARE free of charge under the terms of this Agreement, and are not entitled to any support whatsoever. Support may be available from other users at the Drilling Down Software Community: http://www.jimnovo.com/ 2. DISCLAIMER OF WARRANTY THE SOFTWARE IS PROVIDED AS IS WITHOUT WARRANTY OF ANY KIND. TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW, The Drilling Down Project FURTHER DISCLAIMS ALL WARRANTIES, INCLUDING WITHOUT LIMITATION ANY IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, AND NONINFRINGEMENT. THE ENTIRE RISK ARISING OUT OF THE USE OR PERFORMANCE OF THE PRODUCT AND DOCUMENTATION REMAINS WITH RECIPIENT. TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW, IN NO EVENT SHALL The Drilling Down Project BE LIABLE FOR ANY CONSEQUENTIAL, INCIDENTAL, DIRECT, INDIRECT, SPECIAL, PUNITIVE, OR OTHER DAMAGES WHATSOEVER (INCLUDING, WITHOUT LIMITATION, DAMAGES FOR LOSS OF BUSINESS PROFITS, BUSINESS INTERRUPTION, LOSS OF BUSINESS INFORMATION, OR OTHER PECUNIARY LOSS) ARISING OUT OF THIS AGREEMENT OR THE USE OF OR INABILITY TO USE THE PRODUCT, EVEN IF The Drilling Down Project HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. BACK UP YOUR DATA BEFORE INPORTING IT INTO THIS PROGRAM! 3. SCOPE OF GRANT You may not reverse engineer, decompile or disassemble the SOFTWARE. The Drilling Down Project shall retain title and all ownership rights to the SOFTWARE. 4. COPYRIGHT This SOFTWARE is protected by copyright laws and international copyright treaties, as well as other intellectual property laws and treaties. 5. MAINTENANCE The Drilling Down Project is not obligated to provide maintenance or updates for the SOFTWARE. However, any maintenance or updates provided by The Drilling Down Project shall be covered by this Agreement. 6. DISTRIBUTION The SOFTWARE may be freely distributed provided that it is not modified and the original archive remains intact with all accompanying files, and provided that no fee is charged (except for any reasonable fees necessary to cover costs of distribution media). --------------------------------- Drilling Down Software 1.4 ReadMe --------------------------------- NOTE: BACK UP YOUR DATA BEFORE IMPORTING IT INTO THIS PROGRAM! Note: Microsoft Access 2000 (or later) and Microsoft Excel with pivot table capability are required to run this application!! Note: This latest version of the software runs on Access 2000 only. The Access 2000 version can accept any standard keyboard character in the Customer ID field, including "@", "-", "_" and so on, so that e-mail addresses or even URLs can be used as Customer ID. Other characters may be accepted, but have not been tested. An older version running on Access 1997 is available, but this version only accepts numeric characters in the Customer ID field. No improvements, fixes, or changes will be made to this version. The software is provided as a free download, and instructions for this download are on the first page of the Appendix to the Drilling Down book and at the end of the Introduction. Note: There is another file, dao2535.tlb, included with this download package. Your computer may not have this file and the Drilling Down software requires it to run. If you receive a message like "Compile Error. Can't find project or library" when trying to run the program you are missing this file. Copy the file into the directory C:\Program Files\Common Files\Microsoft Shared\Dao If you don't have this directory, you should create it. Note: The software is designed to handle the scoring of larger groups of customers, avoiding the manual method used in the book. The software scoring method may become unstable when used with a customer base of less than 2000 customers, particularly if a large portion of the customers have identical behavior metrics. See the Drilling Down book for more details on this subject. Note: The application has no onboard help. This readme document is the help documentation. Note: This software provides the "base case" behavior analysis you can do with a spreadsheet. If you appreciate the concepts and functionality demonstrated in this software, get the companion book: Drilling Down: Turning Customer Data into Profits with a Spreadsheet The book expands greatly on the types of behavior profiling you can do once you have the "base case" spreadsheet generated by this software. The book is available at: http://www.booklocker.com/jimnovo ----------------- SHORT DESCRIPTION ----------------- Creates RF (Recency/Frequency) Quintile Customer Grids ----------- DESCRIPTION ----------- Drilling Down Software 1.4 provides the capability to score each individual customer in a file of customer transactions against all the other customers based on the Most Recent Date of Activity (Recency) and the Highest Total Activity (Frequency) on a scale from 55 (Highest) to 11 (Lowest). Any activity transaction with a Customer ID, number of units, and date of activity can be scored, for example, orders, visits, items, or page views. Customers with the highest ranking tend to be best customers and have the highest response rate to promotions. Customers with the lowest scores tend to be customers in the process of defecting and have the lowest response rate to promotions. Customers are divided into five equal groups called "quintiles"; a quintile represents 20% of the customer database. The Recency and Frequency attributes of each customer are assigned a score between 1 and 5, where 5 is the highest (top 20%) and 1 is the lowest (bottom 20%). This score or rank assigned represents the customer's Recency and Frequency attributes relative to all the other customers. The combination of the Recency and Frequency scores results in a 2 digit score where Recency is reflected first. For example, the RF (Recency/Frequency) score of 45 means the customer has a 4 Recency, or is in the top 40% of all customers as far as most Recent activity, and a 5 Frequency, or is in the top 20% of all customers, as far as the most total activity. There are 25 possible combinations of Recency (R) and Frequency (F) scores, resulting in a 5 x 5 RF Grid. The application displays the total customer count at each RF score combination within a cell of the RF Grid format, with Recency on the x-axis and Frequency on the y-axis. Totals for each level of Recency and Frequency are provided. Users can then see and export a table of all customer ID's and their individual RF scores in preparation for a marketing campaign. The aggregation of multiple transactions for a unique customer ID is derived by selecting the Most Recent Activity Date (Recency) and totaling the Units of Activity (Frequency) from all the imported activity transactions. ----------------------------------------- PREPARING DATA FOR USE IN THE APPLICATION ----------------------------------------- The Drilling Down application can import Fixed Width, Comma Delimited, Space Delimited, Tab Delimited, and SemiColon Delimited files. If your customer data is not in any of these formats, it needs to be formatted into one that is compatible with the Drilling Down application. THE APPLICATION IMPORT READS IN ONLY 3 DATA FIELDS AND THEY MUST BE IN THIS ORDER: CUSTOMER ID, UNITS OF ACTIVITY, AND DATE OF ACTIVITY. WHEN YOU PREPARE YOUR DATA FOR USE IN THIS APPLICATION, MAKE SURE THESE 3 FIELDS ARE THE ONLY FIELDS IN THE FILE AND THEY ARE ORGANIZED IN THE RIGHT ORDER! In the Access 2000 version, any standard keyboard character is allowed in the Customer ID field. Other characters may be accepted, but have not been tested. In the Access 1997 version, NUMERIC DATA ONLY IS ALLOWED IN CUSTOMER ID FIELD. No improvements, fixes, or changes will be made to this version of the software. NOTE: If your file format is fixed width, field sizes should be set according to the following sizes: Customer ID = 20, Activity Units = 20, Date = mm/dd/yy (time stamp is optional). Even if your Customer ID is only 10 digits long, the field size should still be set to 20. Multiple imports can be performed before the first report is run, or a report can be run after each import. If you have monthly or weekly data and want to see the progress over time, run the report after each import. NOTE: Each time you run a report, refresh the results in the pivot table by clicking on the "!" icon in the floating toolbar. If you do not have a floating toolbar, then choose Data > Refresh from the Access menu. Failure to do this will result in viewing the previous report results. --------------------- USING THE APPLICATION --------------------- Double-click the executable to open the application. The About screen with start button and URL http://www.jimnovo.com for the Drilling Down website appear. Click start button to start. Click OK on the pop-up. On the next page, the sequence of operations is labeled 1 - 4. 1. Locate the folder and file for import. 2. DOUBLE CLICK on the file format being used. 3. Click ONCE to import each file. NOTE: A DOUBLE CLICK MAY CAUSE THE DATA TO BE IMPORTED TWICE. Press OK on the Import Complete pop-up. 4. Click ONCE to run the report. You will see a form with the RF Grid and 4 buttons. To see the whole report, click the "Edit Pivot Table" button, which will open up an Excel pivot table. This spreadsheet can be used to print or export your results. NOTE: You should see the floating toolbar with the "!" symbol at this point. Remember to press this after each report is run to view the most recently imported data. If you do not have a floating toolbar, then choose Data > Refresh from the Access menu. ------- REPORTS ------- Close the pivot table spreadsheet. You will see the report form again, with the "Edit Pivot Table" button and three buttons below it. If you click once on the left-most button, "Customer Scores", you will see a table with each customer ID and the customer's RF score. If you click once on the center button, you will see a table with each customer ID, the number of days between the customer's last Activity date and the current date (system date), and the Recency Quintile Score. If you click once the right-most button, you will see a table with each customer ID, the total units of customer activity, and the Frequency Quintile Score. All 3 of these tables can be printed or saved / exported just like any Access table. NOTE: To save / export a file, choose File > Save As from the Access menu, then enter the name and file type to Save / Export the file. Closing any of these tables brings you back to the form with the "Edit Pivot Table" button and 3 Reporting buttons. For FAQ, Help, Support, and User Community, visit: http://www.jimnovo.com/