Referrer SQL Tracking for Prosper202 / Tracking202

Prosper202 is a great tool for ppc tracking.  While it is great for tracking and analyzing data, sometimes you may want a little different data.  In this post, I am going to explain a little about the SQL breakdown of Tracking202 so you can use your data for whatever you please.  I am going to explain the tables necessary to include in order to do some analysis of referrers broken down on a per campaign basis.

202_site_urls - This contains the data about any urls that are used for clicks, including landing pages, outbound sites, cloaked urls, redirects, and referrers (referer).  

202_clicks_site - This table is used to join all those site_urls to different characteristics of the click.  

202_clicks - Contains a bunch of data about the various click types.  cpc, payout, datestamp, lp, affiliate campaigns, ppc account are all in this table.

202_aff_campaigns - Obviously this is the root of our query.  We are going to start with the specific campaign and join in the rest of the tables.  

 

Heres the SQL

 

 

SELECT aff_campaign_url, count(click.click_id) 

FROM  `202_aff_campaigns` as aff 

join 202_clicks as click on (aff.aff_campaign_id = click.aff_campaign_id)   

join 202_clicks_site as cs on (cs.click_id = click.click_id)

join 202_site_urls as su on (cs.click_referer_site_url_id = su.site_url_id)

where 

click.click_filtered=0 and 

su.site_url_address like ‘%free%’

group by aff.aff_campaign_url;

 

You can execute that sql from within phpMyAdmin, or your own app, or wherever really.  It outputs a count of active clicks that have the word “free” included in the referral url.  This would be very useful for content network ads and things of that sort. Just change the “free” in the query to whatever you want to investigate. 

Filed under sql, tracking |