Wordpress Firestats Tips & Tricks: How Do I Find What Searches Are Made On The Post?
I have been analyzing the firestats data to extract the searches made on the internet that lead to my blog postings. It is excellent that firestats plugin records all the searches and the connected postings.
Here are my findings
Firestats Hits are stored in a table wp_firestats_hits and the urls, search phrases are stored in wp_firestats_urls. I just combined these tables along with wp_firestats_useragents to get the summary table called search_summary. Here is the simple query.
referers.search_terms,
reverse( replace(substr(REVERSE(urls.url),LOCATE(’/',REVERSE(urls.url),1),LOCATE(’/',REVERSE(urls.url),2)),’/',”) ) post_name,
urls.url as url,
urls.title as title
FROM
wp_firestats_hits AS hits,
wp_firestats_useragents AS agents,
wp_firestats_urls AS urls,
wp_firestats_urls AS referers
WHERE
hits.useragent_id = agents.id AND
hits.url_id = urls.id AND
hits.referer_id = referers.id
and referers.search_terms is not null
and referers.search_engine_id = 1
order by hits.id;
The results are captured in a table. You can either “create table search_summary as” or “insert into search_summary” before the select. Once the table is created, you can query the unique search phrases by using another sql joining the wp_posts. I just simply run this sql with post_id parameter and append to every post whichever I feel important. Here is the second sql inside the shell script I made. This shell script produces a file and I append to the post.
Complete Shell script will be included soon.
Comments
No comments yet.