BBPCS #1: Country Breakdown per Ad Partner

**Great beginner project!** I need a report which breaks down the top countries for clicks sent by each of our ad partners. I'll then want to play with the data once it's in your database, based on what's learned from that initial report. This is pretty easy, since I'll show you exactly how each step should be done: The **BBPCS** database will start with two tables: The **Countries** table is a trivial import from a public source. The **Clicks** table will be imported (with some very minor tweaking) from a _~35 meg CSV file_ which I will send you. You will use existing software to identify each click's country. The report itself is probably best done on the MySQL server, especially since we're dealing with hundreds of thousands of rows. I'll then want access to the database on your server, via your website, for at least a month, and possibly up to three. ## Deliverables The deliverables include the code used to create and populate the database, the code used to generate the report, the report itself, and access to the database via the Web. Create **[login to view URL]** with columns **Code**, a unique two-character identifier, and **Name**, the English short country name, from the official [ISO 3166][1] page. Get the software and data for [MaxMind's GeoLite Country][2]. Any language there is fine, however, I do *not* recommend the SQL database option, as you will be looking up the country data once per IP address as you import the clicks, and you will *not* be doing this on-the-fly when generating reports. I will send you a ~35 meg file in CSV format. Import this data into **[login to view URL]** as follows: The input file's column 2 becomes the **AdPartnerCode**. Column 6 becomes the **Keyword**. Column 7 becomes the **SourceURL**. Column 13 is the click's time, expressed in the traditional Unix fashion of seconds since midnight of 01/01/70. Use a *standard* function to convert this to a DateTime column called **ClickTime**. (Heck, MySQL may even allow you to send the seconds directly, i.e., conversion may not even be needed!) Column 15 becomes the **SaleAmount** with up to two decimal places. Store 0 (not NULL) when the input says, "NULL". Column 16 becomes the **IPAddress**. Use GeoLite Country to store the **CountryCode** of the IPAddress, *once* upon importing each input line. The report will then output, *for each ad partner as well as for a grand total,* the twenty most popular countries (by number of clicks), shown in decreasing level of popularity, plus an intermediate total (which includes all countries, of course, not just the top twenty, for each ad partner). Each line will show percentage of the intermediate total clicks (format 12.3%), number of clicks (format 1,234,567), country code, country name, percentage of the intermediate total dollar amount of sales (format 12.3%), number of sales (i.e., number of rows where SaleAmount > 0) (format 1,234), and dollar amount of sales (format $1,234.56). The intermediate totals for each ad partner as well as the grand total will of course show 100.0% in two columns. Your bid will include my ability to access my data in a ***password-protected*** area on your server, using a web-based interface such as [phpMyAdmin][3], for a period of one month, with an option to extend this to at least three months. The interface should allow me to access the data using arbitrary SQL and possibly other methods. * * *I'm pretty flexible and easy to work with. I tried to make sure that this job is as completely specified as humanly possible. If you have **any** questions, *please* ask them! I have found that it is far better to risk a bit of embarrassment than to spend time working on an assumption which later proves to be wrong. Do not worry that a question may seem silly; just *let me know* and I will be very happy to answer you! We've been a Buyer here since July '05, with a rating of 9.9 ("Excellent") out of 10, with over 75 ratings, and we've now paid on over 95 jobs. We've completed both security verifications, we have a non-action ratio under 5% ("very good"), and we've lost 0 out of 10 mediations / arbitrations. | ![][4] | PGP is **not** required for this project. | | ![][5] | An NDA is **not** required for this project. | We will retain full legal rights to the code and/or data upon completion. All code and/or data is and becomes *our* intellectual property! You will *not* have any rights to the code and/or data in any fashion. **Thank you for your consideration and for bidding through RentACoder!** * * *Copyright © 2007, RentACoder Buyer [notecom][6]. **All Rights Reserved.** Reproduction without permission is strictly prohibited. * * * 1) Complete and fully-functional working program(s) in executable form as well as complete source code of all work done. 2) Deliverables must be in ready-to-run condition, as follows (depending on the nature of the deliverables): a) For web sites or other server-side deliverables intended to only ever exist in one place in the Buyer's environment--Deliverables must be installed by the Seller in ready-to-run condition in the Buyer's environment. b) For all others including desktop software or software the buyer intends to distribute: A software installation package that will install the software in ready-to-run condition on the platform(s) specified in this bid request. 3) All deliverables will be considered "work made for hire" under U.S. Copyright law. Buyer will receive exclusive and complete copyrights to all work purchased. (No GPL, GNU, 3rd party components, etc. unless all copyright ramifications are explained AND AGREED TO by the buyer on the site per the coder's Seller Legal Agreement). ## Platform MySQL preferred but not required. PHP preferred but not required. I'm open to alternatives; please discuss with me, rather than make assumptions.