Custom Spreadsheet And Analysis Software to be built

In marketing, there is a concept called RFM analysis that allows you to determine your best customers based on: 1) R - Recency - How recently they last purchased? 2) F - Frequency - How often do they purchase? 3) M - Monetary - How much do they purchase? The power, however, comes from taking the top 20% of each category and knowing who they are. The problem with trying to do this is it's kind of difficult with excel. I want to create a software that takes this raw data and makes it easy to come up with best candidates. ## Deliverables RFM Analyzer ============ In marketing, there is a concept called RFM analysis that allows you to determine your best customers based on: 1) R - Recency - How recently they last purchased? 2) F - Frequency - How often do they purchase? 3) M - Monetary - How much do they purchase? The power, however, comes from taking the top 20% of each category and knowing who they are. The problem with trying to do this is it's kind of difficult with excel. I want to create a software that makes this easy. Here's exactly what it should do, point by point. 1) Similar to excel it should have a basic spreadsheet mode that accepts unlimited columns and rows (or whatever maximum amount of rows typical memory will allow; it's not uncommon for me to have a csv/xml file with 100,000 rows). This should be taken into consideration. 2) Accept any type of excel/data format including, but not limited to: csv, tsp, xml, els, xlsx, xlsm, xlt, txt, and any others you can think of. 3) Allows header row for the labeling / relabeling of each column name. 4) SORT FUNCTION: Very very important as we would like to take a column of data and sort by: a) Date - Should accept all popular date formats: DD/MM/YYYY D/M/YY YY/M/D YYYY/MM/DD MM/YY MM/YYYY DD-MM MM DD *Should accept "/" or "-" in date format b) Monetary: Pretty simple? 10.00 to 100000.00 c) Numerical: Like how many times they've purchased: 1, 2, 6, 20, 24 5) Multiple sheets: Because the data to determine best RFM group may come from different data sources / files, there needs to be a way to manage multiple files at once. 6) ANALYZE mode: This is where the magic should happen. Here's exactly what should happen: a) Program should ask: "Which column would you like to analyze?" Drop down menu to select column. b) Program should ask: "Would you like to sort column in descending or ascending order" Drop down menu to select: Recency (Date): Sort by most recent date on top Recency (Date): Sort by most oldest date on top Monetary: Sort by highest amount on top Monetary: Sort by lowest amount on top Frequency (Numerical): Sort by highest number on top Frequency (Numerical): Sort by lowest number on top c) Program should ask: "How many groups would you like to separate customers into?" Should allow anywhere from 3 to 8 groups. 5 groups should be selected as default. If user picks 5, that's the same as dividing customers into top 20%, next top 20%, next 20%, next 20%, lowest 20%. IF user picks 4, that's the same as dividing customers into top 25%, next 25%, next 25%, and lowest 25%. And so on. d) Program should create input fields for user to label each group. By default, it should have numbers already in the fields. So if user chooses to separate customers into default 5 groups, then 5 input fields should appear with the top one populated as 5, then the next one 4, then the next one 3, then the next one 2, then 1. User can change this to anything they want: Superior, Great, Average, Lower Than Average, Bottom. e) Upon pressing "ANALYZE" the program will sort accordingly, create additional column, and apply the label ("5", "4", "3" or "Superior, Great, Average") to each row starting from top. EXAMPLE SCENARIO: I upload a csv file of 100 customers. This file simply has their name and how much they've spent. I go to ANALYZE mode and choose "total spent" column in step 1. I then choose "Monetary: Sort by highest amount on top". I choose to split into 5 groups. I keep the default 5-4-3-2-1 labels. Upon pressing analyze, everything should now be sorted and I should have an additional column that has the number 5 next to the top 20 customers (top 20%) on the list. There should be a 4 next to 20 customers. Then a 3 for the next 20 customers. Then a 2. Then a 1. 7) MERGE MODE: User should be able to do ANALYZE mode with as many files as they want (or for memory purposes, you can cap at 7 files). The reason, again, for different files is because one file may have "total spent"? another file may have "dates" ? another file may have "how many times they've bought". The power of the software comes from combining the results of the file. For example, if I have ANALYZED 3 files, I now have 3 separate files sorted in the manner I've selected with 5-4-3-2-1 appended in the last column. In MERGE MODE, we can ask the program to give us people who appear in all 3 files (or X files) with ALL 5's. These are our best customers. Or we can ask for people with at least one 5. Or at least two 5's. Or as I said, ALL 5's. Here's what should happen: a) Program should have option that says: OUTPUT ALL ROWS THAT MEET THESE CRITERIA b) In the CRITERIA area, there should be a number of drop downs. The first drop down contain the names of columns that have been analyzed already. They are able to select which column to work with. The second drop down will contain [EQUALS, GREATER THAN, LESS THAN]. The third column will contain the labels they've created in the ANALYZE MODE. If they've kept the defaults, it will show 5, 4, 3, 2, 1. Then there will be a button that says: "ADD ANOTHER CRITERIA" which will cause another identical row to appear where they can add more criteria from other columns. EXAMPLE: I can choose column "Total spent" ? then [GREATER THAN] ? then "4." That means it will output any rows that are greater than 4. But If I click "ADD ANOTHER CRITERIA" and I choose another column named "Last purchase," then [EQUALS]? then 5, it will only output those with "4 or 5" in the total spent column who must also have EXACTLY a "5" in the "Last Purchase" column. If I make another criteria and add "Number of Purchases" column is [GREATER THAN] 3, then all criteria must be met. NOTE: Obviously, there must be an identifier so they need to have a column like name or e-mail (more reliable) that will tie these different files together. That can be asked somewhere on this screen as well before hitting the "MERGE" button. Essentially, if the same e-mail exists in all 3 files and meets all 3 criteria, this record will be in the final file. 8) DE-DUPING: Since e-mail or name will most likely be record IDENTIFIER, there needs to be a deducing process to make sure more than one do not appear in file. The problem is, it needs to be a SMART de-duper. If columns deal with dates, it can simply take the latest date as that is usually want someone wants to know in RFM analysis (i.e. - "recency") If column deals with total spent (MONETARY), these rows can't simply be de-duped but they need to be added. So if someone with the e-mail of johndoe@[login to view URL] exists in the file 5 times, and has separate purchase amounts of $100, $200, $300, $400, $450, the system should add up total amount ($1450) of all duplicate rows and place the sum total in the HIGHEST row available, deleting all others below it. If column deals with number of purchases (FREQUENCY), it needs to do the same thing. It needs to add up orders, and output whole number in the highest row, deleting all others below it. 9) SPECIAL FEATURES: a) Calculate Frequency: Many times, someone will not have a csv file that simply states how many times someone has ordered. But they will have a csv of 1000 orders. Similar to the deducing feature, the program should take these orders, and using a RECORD IDENTIFIER (like e-mail or account number; column selected by user), it can simply add up each order and create a column called "Frequency" (or custom labeled by user). This is a simple counter. If out of 1000 records, the same e-mail johndoe@[login to view URL] appears 20 times, the program would have determined this and entered 20 in the FREQUENCY column. b) Determine Latest Action: This is pretty much the same as the DE-DUPER for dates but perhaps someone wants to use JUST this feature and copy and paste results back to excel. This will take a file of any size or the current file they are working on and reduce it down to ONE unique identifier per file, based on latest date in the specified column. So if johndoe@[login to view URL] has a date-related column and appears 20 times , the program would determined latest date (like 02-10-2012) and erase all other instances of johndoe@[login to view URL] in the file. This can be useful in preparing the file for ANALYZE mode so that it doesn't take into account duplicates when it is labeling and separating rows into X groups. 10) EXPORT: of course, I would like to be very liberal with exporting. I would like exporting to happen at all levels. IF they've deduced, it would be great to be able to export. IF they've made a simple change to spread sheet, they should be able to export new results. If they've done the "Determine last action "special feature, they should be able to export results. If they've determined frequency (special features), they should be able to export. And of course, after ANALYZE and MERGE mode they should be able to export each individually with the new labels in the added column. Of course, MERGE MODE is the most important export as it will contain only the BEST customers with all existing data in tact (if address, phone, city, state are included). The purpose of this is to be able to then do a mail merge in microsoft office or give to a department to then contact these BEST customers. That's the WHOLE goal of this program. To take raw data and pull out best customers. To make this process easier. That's it. I have been very verbal in explaining because I want it to be very clear but really this is a very straightforward "data/spreadsheet/functions" project. If you have experience in this regard, I look forward to your bid.