In our weekly search meeting we talked about calculating and graphing an approximate paul score of the actual user behaviour, as a metric for quality of search results. Came up with the following sql which calculates it for fulltext and autocomplete for the specified number of days. Note that this isn't exactly the same paul score we use in rel forge, as we don't have all the data necessary, but the results seem to be a reasonably similar to what we get out of relforge (0.30 +- .02).
We've been using the 0.7^x value in relforge recently, which results in the 13th position having a weight of 0.01. 0.5^x also calculated here puts a little more preference on results being higher in the result set, with the 7th result having a weight of 0.008. We don't actually know the best values to use for these factors, these are guesses mostly.
Query
```
SELECT date, event_source,
ROUND(SUM(pow_7)/COUNT(1), 2) as pow_7,
ROUND(SUM(pow_5)/COUNT(1), 2) as pow_5
FROM ( SELECT event_searchSessionId,
event_source,
LEFT(MIN(timestamp), 8) as date,
SUM(IF(event_action = 'click',
POW(0.7, event_position),
0)) / SUM(IF(event_action = 'searchResultPage', 1, 0)) as pow_7,
SUM(IF(event_action = 'click',
POW(0.5, event_position),
0)) / SUM(IF(event_action = 'searchResultPage', 1, 0))as pow_5
FROM TestSearchSatisfaction2_15700292
WHERE timestamp BETWEEN '20160820000000' AND '20160831000000'
AND event_action IN ('searchResultPage', 'click')
GROUP BY event_searchSessionId, event_source
) x
GROUP BY date, event_source
```
Example results for a few days:
```
+----------+--------------+-------+-------+
| date | event_source | pow_7 | pow_5 |
+----------+--------------+-------+-------+
| 20160820 | autocomplete | 0.43 | 0.53 |
| 20160820 | fulltext | 0.30 | 0.28 |
| 20160821 | autocomplete | 0.42 | 0.52 |
| 20160821 | fulltext | 0.30 | 0.27 |
| 20160822 | autocomplete | 0.44 | 0.55 |
| 20160822 | fulltext | 0.32 | 0.29 |
| 20160823 | autocomplete | 0.45 | 0.56 |
| 20160823 | fulltext | 0.32 | 0.29 |
| 20160824 | autocomplete | 0.45 | 0.57 |
| 20160824 | fulltext | 0.31 | 0.29 |
| 20160825 | autocomplete | 0.44 | 0.56 |
| 20160825 | fulltext | 0.31 | 0.29 |
| 20160826 | autocomplete | 0.46 | 0.57 |
| 20160826 | fulltext | 0.31 | 0.28 |
| 20160827 | autocomplete | 0.42 | 0.52 |
| 20160827 | fulltext | 0.31 | 0.28 |
| 20160828 | autocomplete | 0.42 | 0.53 |
| 20160828 | fulltext | 0.30 | 0.27 |
| 20160829 | autocomplete | 0.44 | 0.56 |
| 20160829 | fulltext | 0.32 | 0.30 |
| 20160830 | autocomplete | 0.44 | 0.55 |
| 20160830 | fulltext | 0.31 | 0.28 |
+----------+--------------+-------+-------+
```