I need a custom wordpress plugin that will fill in values inside a custom column in the posts table in the wp database, based on referral traffic CTR (click-through) ratio.
Basically I need the plugin to detect referring websites
(only top-level domains, no pages, etc...), and to track CTR percentage on any link on my pages. I mean that if a visitor comes from, for example, Facebook to a page on my site, and clicks 5 links (either thumbnails, or text links, anything...) on that page, that will count as 500% CTR, and then another 10 visitors come from Facebook and only one of them clicks only one link, then it's 10% CTR and so on...
Whenever a CTR for any referrer reaches a certain value (needs to based on at least 100 unique visitors), let's say 300%, I need the plugin to fill in a value in the posts table of the wp database.
Here is how:
As you know a registered wordpress user can fill-in his/her website in the profile page in his account, which is then stored in the users table under the user_url column.
Whenever one of the referrers meets the 300% mark, I need the plugin to check if that referrer site's domain is found within the user_url column, and if it is, then the plugin needs to check for the most recent wordpress post made by the user that is associated with that domain, and let's say that a post with ID 7 is the most recent post of a certain user, so the plugin needs then to fill a consecutive number (value) in a custom column (will be called ID2 and will be created empty and only once, upon plugin activation) also in the posts table, in the row of post with ID 7.
By "consecutive number" I mean the next number after the highest number present in the ID2 column at that time.
After the plugin fills in the number, the CTR for that referrer site is reset to 0 and starts counting again until it reaches 300%.
Will gladly answer any questions.
Thank you very much in advance!