An easy method to see the quantity of searches made from visits from adwords
SELECT count(*) qty, sp.phrase
FROM cscart_stat_sessions ss
JOIN cscart_stat_search_phrases sp ON ss.phrase_id=sp.phrase_id
JOIN
( SELECT sr1.sess_id
FROM (
SELECT sess_id, min(timestamp) timestamp
FROM cscart_stat_requests
WHERE timestamp > (UNIX_TIMESTAMP(now()) - (7 * 24 * 60 * 60))
GROUP BY sess_id
) sr1
JOIN cscart_stat_requests sr2 ON (sr1.sess_id=sr2.sess_id AND sr1.timestamp=sr2.timestamp)
WHERE sr2.url LIKE'%gclid=%'
) peticiones ON peticiones.sess_id=ss.sess_id
WHERE ss.timestamp > (UNIX_TIMESTAMP(now()) - (7 * 24 * 60 * 60))
GROUP BY ss.phrase_id
ORDER BY qty DESC
[quote name=‘Macius’]An easy method to see the quantity of searches made from visits from adwords
[/quote]
Thanks for this, I’m not sure what that other table is (peticiones); but the query does work properly without it, and it does reveal our sites top adwords search based clicks. Putting this into my cs-cart utils list!
[quote name=‘owenad’]Thanks for this, I’m not sure what that other table is (peticiones); but the query does work properly without it, and it does reveal our sites top adwords search based clicks. Putting this into my cs-cart utils list! :D[/QUOTE]
[quote name=‘jhagg’]Where would you enter this code?[/quote]
It’s just a raw SQL query, you can just run it via your SQL query tool; such as phpmyadmin perhaps.
It’d take more work and files to make it a viewable admin page, maybe I’ll whip this up and release it though, could be useful; however, since you clearly are using google adwords to utilize this, you can also just view this information in your adwords account too.
[quote name=‘tbirnseth’]What’s the structure of ‘peticiones’ table?
Sure you want to use JOIN’s rather than LEFT JOIN’s?[/QUOTE]
‘peticiones’ => url’s visited (I’m spanish and several times I use Spanish terms. This “table” it’s for only count the first visit of each customer.
In “cscart_stat_search_phrases” we can use “Left Join”, but in “peticiones”, I use “Join” instead “Left Join” because need that two tables have data.
[quote name=‘owenad’]It’s just a raw SQL query, you can just run it via your SQL query tool; such as phpmyadmin perhaps.
[/QUOTE]
PHPMyAdmin it’s very easy to use, but you need to handle with care, you can delete all !!!
[quote name=‘owenad’]
It’d take more work and files to make it a viewable admin page, maybe I’ll whip this up and release it though, could be useful; however, since you clearly are using google adwords to utilize this, you can also just view this information in your adwords account too.
I still think its a nifty idea though.[/QUOTE]
Thanks.
This has been deleloped for www.dormitia.com, due that these data are controlled by a third part, it’s used in another control panel and not it’s integrated in CS Cart.
