olegbc1
9월 16, 2019, 8:44오후
1
I am looking to put together a SQL query to show me top search items for any given date range along with the CTR. Does anyone know how is the Search Logs report produced (it’s in …/admin/logs/search_logs?period=weekly) and especially how is the CTR calculated?
I see that there is a search_logs table, but not sure what is being looked at for CTR.
Thanks,
loginerror
10월 23, 2019, 11:38오전
2
답변이 없는 오래된 주제네요, 한번 더 올려볼게요
특정 검색어에 대해 어떤 주제들이 클릭되었는지 확인하고 싶습니다. 이 쿼리를 찾았는데 꽤 근접한 것 같지만, 해당 테이블은 그 이후로 비추천(deprecated)된 상태인 것 같습니다:
We actually did complete the work of logging the information. You can try out:
SELECT term, count(*) searches,
sum(case when clicked_topic_id is not null then 1 else 0 end)
"continued from search"
from search_logs
where created_at > current_timestamp - interval '30' day
group by term
order by count(*) desc
limit 100
It Data Explorer , it will show you top 100 searches and even counts of who actually clicked through from search. I find the results super interesting.
We will work on exposin…
최신 버전에서 이를 구현할 수 있는 방법이 있을까요?
j.jaffeux
10월 23, 2019, 3:14오후
3
https://github.com/discourse/discourse/blob/master/app/models/reports/trending_search.rb
그리고
return 0 if crawler_agents.empty?
crawler_agents
.each_slice(BACKFILL_AGENT_BATCH_SIZE)
.sum { |batch| DB.exec(<<~SQL, agents: batch) }
UPDATE search_logs
SET crawler = TRUE
WHERE NOT crawler
AND user_agent IN (:agents)
SQL
end
def self.flag_likely_crawlers!(window_start:, window_end:)
DB.exec(<<~SQL, window_start: window_start, window_end: window_end)
UPDATE search_logs
SET likely_crawler = TRUE
WHERE NOT search_logs.likely_crawler
AND search_logs.session_id IS NOT NULL
AND search_logs.created_at >= :window_start
AND search_logs.created_at < :window_end
AND EXISTS (
이 두 파일만 보면 필요한 모든 정보를 얻을 수 있습니다.
