BBPCS #2: 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 appear. Please also note that my specifications are very exact for good reasons; I will expect you to please follow every part of this spec that is shown here. (You have more freedom on *how* to do the job, i.e., "connecting the dots".)

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 **[url removed, login to view]** 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/method there is fine, _with one exception_. I *strongly* recommend that you do *not* use 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 [Comma-Separated Value][3] (CSV) format. I urge you to use an existing, standard method of parsing this CSV. If you feel you must write your own parser, at least be very aware of how to handle exception cases such as commas and quotes. Import this data into **[url removed, login to view]** 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 (such as [FROM_UNIXTIME][4]) 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.

If a column of data is not mentioned above, it may safely be ignored for this project.

The report should be written in MySQL, i.e., built by the data server side. Yes, I realize that it's a bit complex, and might be easier if intermediate information was manipulated on the client side, but I am going to be using this report as a basis for some other work in the future.

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). As a rough example:

Ad Partner ABC:

Most popular country

Second-most popular country



20th most popular country

(remaining countries not shown)

Intermediate total of all countries

Ad Partner DEF:

Most popular country

Second-most popular country



20th most popular country

(remaining countries not shown)

Intermediate total of all countries

Grand Total:

Most popular country

Second-most popular country



20th most popular country

(remaining countries not shown)

Intermediate total of all countries

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][5], (strongly preferred!) 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.

| ![][6] | PGP is **not** required for this project. |

| ![][7] | 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][8]. **All Rights Reserved.** Reproduction without permission is strictly prohibited.

* * *

## Platform

MySQL preferred but not required.

PHP preferred but not required.

phpMyAdmin preferred but not required.

I'm open to alternatives; please discuss with me, rather than make assumptions.

Навыки: Техника, MySQL, PHP, Архитектура ПО, Тестирование ПО

Показать больше: what's wrong with my website, what are the best sales jobs, what are sales jobs, want to create my fashion website, use cases include extend, unix jobs, unique sales jobs, under the table jobs, top software sales jobs, top sales jobs, top paid sales jobs, top paid jobs, top best jobs, top 10 sales jobs, top 10 best jobs, top 100 jobs to work for, top 100 jobs, the best sales jobs, standard nda language, sql data conversion, software sales jobs, short project fashion, s&e partners, security sales jobs, sales software jobs

О работодателе:
( 137 отзыв(-а, -ов) ) Wyoming, United States

ID проекта: #3224074