I need a PHP script written to generate a keyword level return of investment (ROI) report for my pay per click internet marketing campaigns. There are two third party reports that will be uploaded to the system to analyze and produce the ROI report. There is also a MySQL data source that is touched on below.
First let me explain the process of how everything works now:
1. I advertise on pay per click search engines such as Google Adwords, Yahoo Search Marketing and Microsoft Adcenter. The way it works is I bid on the search keywords that my ad appears on. When the user clicks my ad, they are sent to my website and I have a PHP script that records the keyword (search engine passes it over) into a MySQL database with a unique ID.
2. The visitor is then directed to visit a third party site to complete a related offer or perhaps purchase something. When they travel to the third party (merchant) site, the unique ID that is in my database corresponding to which keyword they clicked through to my site with is passed to the merchant site. If they convert to a sale or lead, the sale is recorded along with that unique ID in a CSV report that I can download. This report also contains the value ($) of each commission I received.
3. I can also download a report from the search engines in CSV format that tells me how many clicks each keyword received (broken down by day), and how much I spent on them.
So, we have the report from the merchant which contains the conversions, their value, and the unique ID stored in my database that corresponds to the keyword that the visitor arrived with. You can see how this data can be matched up with the keyword level spend report from the search engines to tell me my return of investment at the keyword level.
How I want the script to function:
1. There will be a ROI report set up page with fields to upload my two CSV files. One CSV file is the report from the merchant, the other is the report from the search engine.
2. Once executed, the script will interface with the MySQL database, matching up the conversion stats all the way back to the amount I spent on the keyword that initially generated the traffic that converted.
3. An “ROI Report” will be generated and displayed in the browser. I will provide a sample ROI report. The ROI report must be downloadable in either CSV or Excel format. I am not worried about storing any report data by the script at this time.
Initially this script will only need to support exported data from Google and one affiliate network. You must be available in the near future to add additional support for exported data from other networks and search engines. The script will still be looking at the same data, but the outputted CSV files are arranged differently from different sources.
I will have full ownership & rights to the script. You must do a good job of commenting the code so others can quickly determine what is going on in the PHP and where.
I feel this is a pretty simple script and this should not be a difficult job. I look forward to developing a working relationship with you. This script will be expanded very soon to support additional source data, and I will have other projects down the road if this works out.
I expect the script to be delivered in working condition within a few days of accepting the project. I expect us to go back and forth a couple of times to get everything working the way it should be.
You will be provided with a sample dump from the MySQL database that has some of the source data, as well as a sample keyword performance report from Google Adwords and from one of the affiliate networks that I work with.
Thanks for your time!! Let me know if you have any questions or need me to clarify anything.