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 | Comment (0)What is Douchy SEO?
This is SEO for frat boys. If you can do a keg stand, whoop ass at beer pong, or dominate at flip cup. Douchy SEO is certainly for you. Following the moral compass of Matt Cutts and trying not to rank better than you deserve for anything. After all, your a douche. Why would you deserve to rank for anything?
You hand submit to directories, because anything more would be spammy. You write content for subjects your not *really* an expert, because generating content would be spammy. You would never scrape multiple sources for data, come up with meaning and regurgitate it, unless you called it a “mash-up.” Then it would be fine.
The best part of Douchy SEO is that you get to put down anyone who is less righteous than you, report anyone who does something that works better than you, and otherwise try to build up the worth of your existence in your mind.
Filed under Uncategorized | Comment (0)