SELECT event_editCountBucket AS editCountBucket, COUNT(*)
FROM log.Popups_16364296
WHERE LEFT(timestamp,6) = '201705'
AND event_isAnon = 0
GROUP BY editCountBucket
ORDER BY editCountBucket;

+-----------------+----------+
| editCountBucket | COUNT(*) |
+-----------------+----------+
| 0 edits         |      758 |
| 1-4 edits       |      604 |
| 100-999 edits   |     1861 |
| 1000+ edits     |     6793 |
| 5-99 edits      |     2022 |
+-----------------+----------+
5 rows in set (7.92 sec)

SELECT 10*FLOOR(event_perceivedWait/10) AS bucket, COUNT(*) AS frequency
FROM log.Popups_16364296
WHERE wiki ='ruwiki' # the wiki with the most events. TODO: extend this to all WPs
AND event_isAnon 
AND DATE(timestamp) >= '2017-05-20'
AND DATE(timestamp) <= '2017-05-24'
AND event_popupEnabled
AND event_linkInteractionToken IS NOT NULL
AND event_action = 'dismissed'
GROUP BY bucket
ORDER BY bucket;

SELECT event_popupEnabled, 
count(DISTINCT event_linkInteractionToken)/count(DISTINCT event_pageToken) AS link_interactions_per_page
FROM log.Popups_16364296
WHERE wiki ='ruwiki'
AND event_isAnon = 1
AND LEFT(timestamp, 8) >= '20170522'
AND LEFT(timestamp, 8) < '20170530'
AND (event_action = 'pageLoaded' OR event_totalInteractionTime > 300)
GROUP BY event_popupEnabled;

+--------------------+----------------------------+
| event_popupEnabled | link_interactions_per_page |
+--------------------+----------------------------+
|                  0 |                     3.2286 |
|                  1 |                     3.5196 |
+--------------------+----------------------------+
2 rows in set (3.36 sec)

SELECT event_popupEnabled, 
COUNT(DISTINCT event_linkInteractionToken)/count(DISTINCT event_pageToken) AS link_interactions_per_page
FROM log.Popups_16364296
WHERE wiki ='ruwiki'
AND event_isAnon = 1
AND LEFT(timestamp, 8) >= '20170701'
AND LEFT(timestamp, 8) < '20170715'
AND SUBSTRING( userAgent, INSTR(userAgent,'"browser_family": "')+19, INSTR(SUBSTRING(userAgent, INSTR(userAgent,'"browser_family": "')+19), '"' )-1 ) = 'Chrome'
AND (event_action = 'pageLoaded' OR event_totalInteractionTime > 300)
GROUP BY event_popupEnabled;

+--------------------+----------------------------+
| event_popupEnabled | link_interactions_per_page |
+--------------------+----------------------------+
|                  0 |                     1.3412 |
|                  1 |                     1.5315 |
+--------------------+----------------------------+
2 rows in set (3.90 sec)

SELECT event_popupEnabled, 
count(DISTINCT event_linkInteractionToken)/count(DISTINCT event_pageToken) AS link_opens_per_page
FROM log.Popups_16364296
WHERE wiki ='ruwiki'
AND event_isAnon = 1
AND LEFT(timestamp, 8) >= '20170701'
AND LEFT(timestamp, 8) < '20170715'
AND SUBSTRING( userAgent, INSTR(userAgent,'"browser_family": "')+19, INSTR(SUBSTRING(userAgent, INSTR(userAgent,'"browser_family": "')+19), '"' )-1 ) = 'Chrome'
AND (event_action = 'pageLoaded' OR 
  event_action = 'opened')
GROUP BY event_popupEnabled;

+--------------------+---------------------+
| event_popupEnabled | link_opens_per_page |
+--------------------+---------------------+
|                  0 |              0.1775 |
|                  1 |              0.1555 |
+--------------------+---------------------+
2 rows in set (4.81 sec)

SELECT SUM(IF(event_action = 'opened',1,0))/SUM(1)
AS clickthrough_ratio
FROM log.Popups_16364296
WHERE wiki ='ruwiki'
AND event_isAnon = 1
AND LEFT(timestamp, 8) >= '20170701'
AND LEFT(timestamp, 8) < '20170715'
AND SUBSTRING( userAgent, INSTR(userAgent,'"browser_family": "')+19, INSTR(SUBSTRING(userAgent, INSTR(userAgent,'"browser_family": "')+19), '"' )-1 ) = 'Chrome'
AND event_totalInteractionTime > event_perceivedWait + 1000 #i.e. card was shown for at least one second
AND event_linkInteractionToken IS NOT NULL;
+--------------------+
| clickthrough_ratio |
+--------------------+
|             0.0647 |
+--------------------+
1 row in set (3.68 sec)