May 7th, 2009

Prosper202 Mod: LP Clickthrough for Text Ads/Keywords

**[EDIT: 7/6/09] Here’s an update to the code: Prosper202 LP CTR Version 2 **

Since Prosper202 went open-source, I decided to poke around in the source code.  One feature that I wanted was to be able to know which keywords and text ads clicked through from the landing page to the merchant’s offer.  Before that, I was using Google Analytics and looking at the bounce rate to determine the effectiveness of a keyword.

Now that Prosper202 is open-source, I created my own modifications for it.  If you notice at the top, I added links for “Keywords LP CTR” and “Text Ad LP CTR”.

p202_1.gif

The next thing I did was I added two columns, one for the number of clickthroughs and one for the clickthrough percentage of clicks into the LP and clicks out to the offer.

Keywords:
p202_3.gif

Text Ads:
p202_2.gif

Here’s the download for it.  Take a look at the source code, I put a bunch of comments so that anyone can understand the changes that I did.

Keyword-Text Ads LP CTR Mod

Here are the instructions from the included txt file:

1.) Make a backup of top.php in the tracking202/_config folder
2.) Copy top.php to the tracking202/_config folder
3.) Make a backup of index.php in the tracking202/analyze folder
4.) Copy index.php to the tracking202/analyze folder
5.) Copy contents of tracking202/ajax and tracking202/analyze to the corresponding folders (no backups are required since it doesn't overwrite anything)
6.) Make sure to add the tables below using the SQL tab in PHPMYADMIN in your webhost.

// Create Table: 202_sort_keywords_lpctr

CREATE TABLE IF NOT EXISTS `202_sort_keywords_lpctr` (
`sort_keyword_id` int(10) unsigned NOT NULL auto_increment,
`user_id` mediumint(8) unsigned NOT NULL,
`keyword_id` bigint(20) unsigned NOT NULL,
`sort_keyword_clicks` mediumint(8) unsigned NOT NULL,
`sort_keyword_click_throughs` mediumint(8) unsigned NOT NULL,
`sort_keyword_ctr` decimal(10,2) NOT NULL,
`sort_keyword_leads` mediumint(8) unsigned NOT NULL,
`sort_keyword_su_ratio` decimal(10,2) NOT NULL,
`sort_keyword_payout` decimal(6,2) NOT NULL,
`sort_keyword_epc` decimal(10,2) NOT NULL,
`sort_keyword_avg_cpc` decimal(7,5) NOT NULL,
`sort_keyword_income` decimal(10,2) NOT NULL,
`sort_keyword_cost` decimal(13,5) NOT NULL,
`sort_keyword_net` decimal(13,5) NOT NULL,
`sort_keyword_roi` decimal(10,2) NOT NULL,
PRIMARY KEY (`sort_keyword_id`),
KEY `user_id` (`user_id`),
KEY `keyword_id` (`keyword_id`),
KEY `sort_keyword_clicks` (`sort_keyword_clicks`)
) ENGINE=MyISAM AUTO_INCREMENT=1 ;

// Create Table: 202_sort_text_ads_lpctr

CREATE TABLE IF NOT EXISTS `202_sort_text_ads_lpctr` (
`sort_text_ad_id` int(10) unsigned NOT NULL auto_increment,
`user_id` mediumint(8) unsigned NOT NULL,
`text_ad_id` mediumint(8) unsigned NOT NULL,
`sort_text_ad_clicks` mediumint(8) unsigned NOT NULL,
`sort_text_ad_click_throughs` mediumint(8) unsigned NOT NULL,
`sort_text_ad_ctr` decimal(10,2) NOT NULL,
`sort_text_ad_leads` mediumint(8) unsigned NOT NULL,
`sort_text_ad_su_ratio` decimal(10,2) NOT NULL,
`sort_text_ad_payout` decimal(6,2) NOT NULL,
`sort_text_ad_epc` decimal(10,2) NOT NULL,
`sort_text_ad_avg_cpc` decimal(7,5) NOT NULL,
`sort_text_ad_income` decimal(10,2) NOT NULL,
`sort_text_ad_cost` decimal(13,5) NOT NULL,
`sort_text_ad_net` decimal(13,5) NOT NULL,
`sort_text_ad_roi` decimal(10,2) NOT NULL,
PRIMARY KEY (`sort_text_ad_id`),
KEY `user_id` (`user_id`),
KEY `keyword_id` (`text_ad_id`),
KEY `sort_keyword_clicks` (`sort_text_ad_clicks`),
KEY `sort_keyword_leads` (`sort_text_ad_leads`),
KEY `sort_keyword_signup_ratio` (`sort_text_ad_su_ratio`),
KEY `sort_keyword_payout` (`sort_text_ad_payout`),
KEY `sort_keyword_epc` (`sort_text_ad_epc`),
KEY `sort_keyword_cpc` (`sort_text_ad_avg_cpc`),
KEY `sort_keyword_income` (`sort_text_ad_income`),
KEY `sort_keyword_cost` (`sort_text_ad_cost`),
KEY `sort_keyword_net` (`sort_text_ad_net`),
KEY `sort_keyword_roi` (`sort_text_ad_roi`)
) ENGINE=MyISAM AUTO_INCREMENT=1 ;

Be Sociable, Share!

51 Responses to “Prosper202 Mod: LP Clickthrough for Text Ads/Keywords”

Steve Howe says:
May 7th, 2009 at 6:19 am

Nice Post Jasper! Thanks for sharing your modification to 202! I am definitely going to point this little GEM out to some affiliates =)

Ian Fernando says:
May 7th, 2009 at 7:54 am

damn this is a nice mod – I thought my p202 had some good mods to it already.

Affbuzz says:
May 7th, 2009 at 8:23 am

Very sweet, looks like I now have another item to add to my to-do list today.

Logan says:
May 7th, 2009 at 8:30 am

Sweet mod! That is awesome. Nice job Jasper.

Tim says:
May 7th, 2009 at 11:17 am

This is awesome, thanks so much for releasing this Jasper. Great comments in the code as well, very helpful! 😀

JasperP says:
May 7th, 2009 at 11:18 am

Thanks for the feedback!

Anxiolytic says:
May 7th, 2009 at 2:45 pm

I recently began adding LP CTR to 202 as well. Nice to see others modifying this app. You have it hosted anywhere? If not, why not add your modifications to the unofficial public repo? I put it up on github ..

http://github.com/lsd/prosper202/tree/master

James Seligman says:
May 7th, 2009 at 4:54 pm

Very nice mod Jasper! I am going to add it to my P202 tonight. Thanks for sharing it.

James Seligman’s last blog post..My Own Weight Loss Challenge to Myself

Indo Contest says:
May 7th, 2009 at 9:43 pm

I’m gonna try this for my P202!!.
Thanks a lot Nerdy…

zalds says:
May 8th, 2009 at 10:35 pm

nice one.

I used to plot this data to excel manually :)now I can let the 202 gather it automatically – really a sweet mod

Thank you for sharing kabayan.

zalds’s last blog post..The Google Ad Auction

Daehee says:
May 9th, 2009 at 7:34 am

Awesome, install was a breeze. Thanks!

Daehee’s last blog post..Former Kairos Society Officer Arrested for Ponzi Scheme

ImagesAndWords says:
May 10th, 2009 at 10:51 am

This will definitely come in handy, thanks. :)

ImagesAndWords’s last blog post..How To Split-Test Landing Pages with Tracking202

huge says:
May 11th, 2009 at 5:33 pm

awesome mod thanks!

huge’s last blog post..top secret formula for easy money huhu

Jake says:
May 11th, 2009 at 7:57 pm

Thanks Nerdy!

Jake

Lars says:
May 11th, 2009 at 10:01 pm

Thanks very much! JUST what I’ve been wanting but had no idea how to code.

2 minute install (and this from someone downright dangerous in phpmyadmin).

MarcusFunbox says:
May 11th, 2009 at 10:10 pm

Thanks Jasper! Thats a cool mod :)

Scott says:
May 12th, 2009 at 7:44 am

Jasper .. Thank You!! I was actually about to do this manually … You are awesome!

I have a request .. I saw that you are doing a Yahoo test.

What about CTR by referer?

Since you can’t target just yahoo.com, I think knowing this would really help your ROI.

In one niche I’m in I get 70% of my conversions from Yahoo.com, but less than half of my actual clicks.

JasperP says:
May 12th, 2009 at 10:01 am

that’s a good question.

I’ll look into it.

Feri says:
May 12th, 2009 at 1:12 pm

Hi,
I upgraded according to the readme file, and I get this error:

Parse error: syntax error, unexpected ‘}’ in XXXXXX/tracking202/_config/top.php on line 70

When I look into the sourcecode this is on line 70:

I am not a php guru, any advice?
thanks.

JasperP says:
May 12th, 2009 at 1:17 pm

That’s strange. top.php only adds the 2 links “Keyword LP CTR” and “Text Ads LP CTR” to the Analyze tab.

I’m looking at line 70 and I’m not seeing anything significant. If it doesn’t work, just put the top.php back.

Take a screenshot if you can.

Feri says:
May 12th, 2009 at 1:20 pm

Problem solved. Didn’t work when I simply overwrote the top.php and index.php files. When I deleted the files (after backup), then simply COPIED the new ones, worked fine… Try this way!

and thanks for the mod!

JasperP says:
May 12th, 2009 at 1:23 pm

nice work.

Wes (MasterlessSamurai.com) says:
May 13th, 2009 at 11:33 pm

Sweet update man! I’m using Pro now mostly, but for a few non search campaigns, I still need Prosper. This will definitely help me continue to optimize my landing pages.

Thanks for helping the 202 community!

Wes (MasterlessSamurai.com)’s last blog post..Speed202: How to Integrate Tracking202 & SpeedPPC

opinijus says:
May 14th, 2009 at 2:22 am

Hey,

Thanks for the MOD.

There is an error when creating tables: “There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem

ERROR: Unknown Punctuation String @ 1
STR: //
SQL: // Create Table: 202_sort_keywords_lpctr”

I go to SQL query window in the PHPMYADMIN. paste code from .txt and click go. This is the proper way to install tables?

opinijus says:
May 14th, 2009 at 2:31 am

Oops, i copied the code with “//”. Everything is fine now. Thanks man for awesome tool again! :)

AmYourPusher says:
May 14th, 2009 at 6:56 pm

Excellent! Took about 5 mins to setup. Thanks for the Mod and the very detailed setup instructions.

links for 2009-05-20 « On New Media says:
May 20th, 2009 at 1:06 am

[…] Prosper202 Mod: LP Clickthrough for Text Ads/Keywords | Nerdy Affiliate (tags: keywords landingpage textads ads PPC) […]

Shock Marketer says:
May 27th, 2009 at 9:20 am

Thanks a ton! Early metrics like this help save cash.

Shock Marketer’s last blog post..Google’s New Search Referral String

matt says:
May 28th, 2009 at 3:48 pm

thanks for posting this, was surprised when google actually gave a useful link :)

Referer Click-through MOD for Prosper202 | B-Scan.net says:
June 10th, 2009 at 7:42 am

[…] was part that was absolutely missing in that open source affiliate tracking software. Since Jasper made such a great contribution to open source community I wanted to also help because Prosper202 is […]

B-Scan says:
June 10th, 2009 at 7:49 am

Hi Jasper.
Thank you very much for such a great contribution!

In order to help you I made Referrers CTR MOD for Prosper202.
Feel free to use it: http://www.b-scan.net/blog/95-referer-click-through-mod-for-prosper202/

B-Scan’s last blog post..Referer Click-through MOD for Prosper202

Chris says:
June 11th, 2009 at 4:49 pm

Saw this post recommended by Andrew over at CashTactics. Awesome mod and something that I was really wishing Prosper202 could do before. I’m installing it right now.

Chris’s last blog post..PPC Bully 2 Review – Does It Live Up To The Hype?

Apply this Free PPC Tip Immediately says:
June 12th, 2009 at 7:54 am

[…] I had put on my to do list, but he beat me to it. He has pushed it on his blog at this post: 202 KW CTR Script , you can go there and read more details about it and I highly suggest implementing it immediately […]

Angila Assembly says:
June 25th, 2009 at 10:01 am

This script looks awesome. I’m sure it help to minimize CPC and also helps to track down the profitable keywords and save $$$’s. I’m downloading right now to test.

Angila

Chris says:
June 29th, 2009 at 7:12 am

Nice hack. Thank you. Had no problems getting it installed at all. Now just waiting for more data to come in. :)
.-= Chris´s last blog ..SpeedPPC – It does not end after your Purchase =-.

Maverick money makers says:
July 4th, 2009 at 10:46 am

[…] Sneak Inside Maverick Money Makers Club  Prosper202 Mod […]

DIGITAL INFRARED PHOTOGRAPHY says:
September 17th, 2009 at 11:01 am

DIGITAL INFRARED PHOTOGRAPHY…

According to Google Analytics, here are the top ten keywords that brought people to this site in August 2009….

Ilya Ripa says:
September 23rd, 2009 at 9:31 am

Awesome mod. One thing though, Can’t export the data to excel when in Keywords LP CTR mode. Can someone look into that? :)

Buy Text Link Ads says:
October 16th, 2009 at 6:49 am

Does the ‘leads’ indicate conversion ratio? Does this software track conversion ratio?

Thanks.
.-= Buy Text Link Ads´s last blog ..Angela’s Links Don’t Work (and neither do link wheels) =-.

Jon says:
April 22nd, 2010 at 11:19 pm

Hey, would this work for version 1.5?

Thanks! Awesome addition. Loved using it in 1.32

JasperP says:
April 23rd, 2010 at 1:04 am

I haven’t tested it yet, but I heard from someone that it works.

Davis says:
April 27th, 2010 at 9:52 pm

Thanks, this is exactly what I needed. I got stuck at the last part though, I went to PHPMYADMIN and under my prosper202 database, clicked on the SQL tab. I copy and pasted into the window titled Run SQL query/queries on database and hit GO.

I get an error, right at the top it says:

Error

There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem

ERROR: Unknown Punctuation String @ 1
STR: //
SQL: // Create Table: 202_sort_keywords_lpctr

Not sure, did I miss something, maybe this isn’t where I was suppose to add it? Sorry, I’m pretty new to this.

Davis says:
April 27th, 2010 at 10:09 pm

Ah nevermind, just saw the updated version, works like a charm!

The Top 5 Prosper202 Hacks & Mods | Inside Affiliate says:
May 10th, 2010 at 4:13 pm

[…] 3. Prosper202 LP Click-Through for Text Ads & Keywords Mod […]

G says:
July 29th, 2010 at 2:18 pm

Not working with version 1.5.1 waiting for a working version eagerly!

G

JasperP says:
July 29th, 2010 at 2:20 pm

Its in the works

Stan Smith says:
September 30th, 2010 at 1:22 pm

Now there is something useful, thank you!

zaafealotep says:
December 18th, 2010 at 8:11 pm

zaafealotep hello site!!!
[url=http://ly1.linkpc.net]index [/url]
trayzerothe past. He took an almost boyish pleasure in these one-sidedVisit my FarmVille Strategy page for more FarmVille strategies, tips, news, and updates!odium: a reaction that soul or something does not rate whatever deferenceeruption of molten lava.”

Patagonia sale says:
May 23rd, 2011 at 2:09 pm

Thanks for the MOD

Now only if we could pay you to get the cost via Google and Bing APIs :-)

affaholic says:
October 1st, 2011 at 11:07 am

wow, worked like a charm and i’m known for breaking 202 when I play around with phpMyAdmin…

Note:
Remove the commented MYSQL code // blah blah blah

justin says:
November 19th, 2011 at 6:10 pm

I uploaded all of the files and SQL tables but now I am seeing the error… “The requested URL /tracking202/analyze/sort_keywords_lpctr.php was not found on this server.” when I try to access the tab in T202.

These files did not come with the package… what am I missing?

Leave a Reply

CommentLuv badge