Views

Metrics/Mailing list queries

From MeeGo wiki

< Metrics(Difference between revisions)

Latest revision as of 16:22, 12 October 2011

These are all of the queries which we have used for mailing list statistics in the monthly report:

Note: last_month is a parameter set once in the master report representing the month and year of the previous month, in the format 'YYYYMM'. For example, in September 2011, lastmonth is '201108'.

1 ML posts per list last 4 months
2 ML message count per list name first
3 ML repeat posters last month
4 ML total mails last month
5 ML popular threads last month
6 ML top 10 posters last month
7 ML message count top 5 lists time first
8 ML top 20 posters last month
9 ML posters count last month
10 ML message count per list time first
11 MLStats database schema

ML posts per list last 4 months

SELECT
     if(messages.mailing_list_url like '%-community%' or 
        messages.mailing_list_url like '%meego-dev%' or
        messages.mailing_list_url like '%-sdk%' or
        messages.mailing_list_url like '%-packaging%' or
        messages.mailing_list_url like '%-qa%',
     substring_index(TRIM(TRAILING '/' FROM `messages`.`mailing_list_url`), '/', -1),
     'Other') as list,
     year(first_date) AS y,
     monthname(first_date) AS mon,
     month(first_date) AS m,
     date_format(first_date, '%b %Y') as monthstr,
     date_format(first_date,'%Y%m') as monthnum, 
     count(*) AS posts
FROM
     `messages`
WHERE
	month(first_date)>=MONTH(DATE_SUB(NOW(), INTERVAL 4 MONTH)) and
	year(first_date)>=YEAR(DATE_SUB(NOW(), INTERVAL 4 MONTH)) and
     mailing_list_url not like '%meego-commits%' and
     date_format(first_date,'%Y%m') <= date_format(${last_month},'%Y%m')
GROUP BY
     list,
     y,m
ORDER BY
     list ASC,
     monthnum ASC

ML message count per list name first

This query is unused in the report, I think. Left in for posterity.


SELECT
     substring_index(TRIM(TRAILING '/' FROM `messages`.`mailing_list_url`), '/', -1) AS list,
     year(first_date) AS y,
     monthname(first_date) AS mon,
     month(first_date) AS m,
     date_format(first_date, '%M %Y') as monthstr,
     date_format(first_date,'%Y%m') as monthnum, 
     count(*) AS c
FROM
     `messages`
WHERE
     year(first_date)		 > 1979 and 
     mailing_list_url not like '%meego-commits%'
GROUP BY
     `messages`.`mailing_list_url`,
     y,m
ORDER BY
     list ASC,
     monthnum ASC,
     c ASC

ML repeat posters last month

Provides the number of active mailing list participants - sending more than one email per month.


select
    monthname(${last_month}) as monthname,
    year(${last_month}) as y,
    count(*) as c
from
    ( select 
          p.email_address as member,
          count(*) AS c
      from messages as m,
           messages_people as p
      where
          m.message_id=p.message_ID and
          month(m.first_date)=month(${last_month}) and
          year(m.first_date)=year(${last_month}) and
          p.email_address NOT LIKE '%no_reply@%'
      group by p.email_address
    )
as repeat_posters
where c>=2;

ML total mails last month


SELECT
     monthname(${last_month}) as monthname,
     count(*) AS message_count
FROM
     `messages`
WHERE
     month(first_date) = month(${last_month}) and 
     year(first_date) = year(${last_month}) and 
     mailing_list_url not like '%meego-commits%'

ML popular threads last month


select 
	subject,
	count(*) as c
from messages
where year(first_date)=year(${last_month})
  and month(first_date)=month(${last_month})
group by subject
order by c desc
limit 10;

ML top 10 posters last month


select 
	p.email_address,
	count(*) as c
from
	messages as m,
	messages_people as p 
where 
	m.message_id=p.message_ID
 and month(m.first_date)=month(${last_month})
 and year(m.first_date)=year(${last_month})
 and p.email_address NOT LIKE '%no_reply@%'
group by p.email_address
order by c desc
limit 10;

ML message count top 5 lists time first

This query is used to graph the evolution of each mailing list in a graph. The top 5 lists are taken out, and the others are grouped together.


select 
    if(messages.mailing_list_url like '%-community%' or 
       messages.mailing_list_url like '%meego-dev%' or
       messages.mailing_list_url like '%-sdk%' or
       messages.mailing_list_url like '%-packaging%' or
       messages.mailing_list_url like '%-qa%',
    substring_index(TRIM(TRAILING '/' FROM `messages`.`mailing_list_url`), '/', -1),
    'Other') as list,
    year(first_date) AS y,
    monthname(first_date) AS mon,
    month(first_date) AS m,
    date_format(first_date, '%b %Y') as monthstr,
    date_format(first_date,'%Y%m') as monthnum,
    count(*) AS c
FROM      
    `messages` 
WHERE      
    year(first_date) > 1979 and       
    date_format(first_date,'%Y%m') <= date_format(${last_month},'%Y%m') and 
    messages.mailing_list_url not like '%meego-commit%'
GROUP BY
    list,y,m
ORDER BY
    monthnum ASC,
    list asc,
    c asc;

ML top 20 posters last month

I should really have done a "top N posters last month" and made N a parameter. This was the quick copy & paste way to go.


select 
	p.email_address,
	count(*) as c
from
	messages as m,
	messages_people as p 
where 
	m.message_id=p.message_ID
 and month(m.first_date)=month(${last_month})
 and year(m.first_date)=year(${last_month})
 and p.email_address NOT LIKE '%no_reply@%'
group by p.email_address
order by c desc
limit 20;

ML posters count last month


select 
	count(distinct p.email_address) as member_count,
	monthname(${last_month}) AS monthname
from
	messages as m,
	messages_people as p 
where
	m.message_id=p.message_ID
 and month(m.first_date)=month(${last_month})
 and year(m.first_date)=year(${last_month})
 and p.email_address NOT LIKE '%no_reply@%';

ML message count per list time first

All mailing lists. The graph was far too cluttered.


SELECT
     substring_index(TRIM(TRAILING '/' FROM `messages`.`mailing_list_url`), '/', -1) AS list,
     year(first_date) AS y,
     monthname(first_date) AS mon,
     month(first_date) AS m,
     date_format(first_date, '%b %Y') as monthstr,
     date_format(first_date,'%Y%m') as monthnum, 
     count(*) AS c
FROM
     `messages`
WHERE
     year(first_date)		 > 1979 and 
     mailing_list_url not like '%meego-commits%' and
     date_format(first_date,'%Y%m') <= date_format(${last_month},'%Y%m')
GROUP BY
     list,
     y,m
ORDER BY
     monthnum ASC,
     list ASC,
     c ASC;

MLStats database schema

Table compressed_files


CREATE TABLE `compressed_files` (
  `url` varchar(255) CHARACTER SET utf8 NOT NULL,
  `mailing_list_url` varchar(255) CHARACTER SET utf8 NOT NULL,
  `status` enum('new','visited','failed') DEFAULT NULL,
  `last_analysis` datetime DEFAULT NULL,
  PRIMARY KEY (`url`),
  KEY `mailing_list_url` (`mailing_list_url`),
  CONSTRAINT `compressed_files_ibfk_1` FOREIGN KEY (`mailing_list_url`) REFERENCES `mailing_lists` (`mailing_list_url`)
);

Table mailing_lists


CREATE TABLE `mailing_lists` (
  `mailing_list_url` varchar(255) CHARACTER SET utf8 NOT NULL,
  `mailing_list_name` varchar(255) CHARACTER SET utf8 DEFAULT 'NULL',
  `project_name` varchar(255) CHARACTER SET utf8 DEFAULT 'NULL',
  `last_analysis` datetime DEFAULT NULL,
  PRIMARY KEY (`mailing_list_url`)
);

Table mailing_lists_people


CREATE TABLE `mailing_lists_people` (
  `email_address` varchar(255) CHARACTER SET utf8 NOT NULL,
  `mailing_list_url` varchar(255) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`email_address`,`mailing_list_url`),
  KEY `mailing_list_url` (`mailing_list_url`),
  CONSTRAINT `mailing_lists_people_ibfk_1` FOREIGN KEY (`mailing_list_url`) REFERENCES `mailing_lists` (`mailing_list_url`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `mailing_lists_people_ibfk_2` FOREIGN KEY (`email_address`) REFERENCES `people` (`email_address`) ON DELETE CASCADE ON UPDATE CASCADE
);

Table messages


CREATE TABLE `messages` (
  `message_ID` varchar(255) CHARACTER SET utf8 NOT NULL,
  `mailing_list_url` varchar(255) CHARACTER SET utf8 NOT NULL,
  `mailing_list` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `first_date` datetime DEFAULT NULL,
  `first_date_tz` int(11) DEFAULT NULL,
  `arrival_date` datetime DEFAULT NULL,
  `arrival_date_tz` int(11) DEFAULT NULL,
  `subject` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `message_body` text CHARACTER SET utf8,
  `is_response_of` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `mail_path` text CHARACTER SET utf8,
  PRIMARY KEY (`message_ID`),
  KEY `response` (`is_response_of`),
  KEY `mailing_list_url` (`mailing_list_url`),
  CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`mailing_list_url`) REFERENCES `mailing_lists` (`mailing_list_url`) ON DELETE CASCADE ON UPDATE CASCADE
);

Table messages_people


CREATE TABLE `messages_people` (
  `type_of_recipient` enum('From','To','Cc') NOT NULL DEFAULT 'From',
  `message_id` varchar(255) CHARACTER SET utf8 NOT NULL,
  `email_address` varchar(255) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`type_of_recipient`,`message_id`,`email_address`),
  KEY `m_id` (`message_id`),
  KEY `message_id` (`message_id`),
  KEY `email_address` (`email_address`),
  CONSTRAINT `messages_people_ibfk_1` FOREIGN KEY (`message_id`) REFERENCES `messages` (`message_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `messages_people_ibfk_2` FOREIGN KEY (`email_address`) REFERENCES `people` (`email_address`) ON DELETE CASCADE ON UPDATE CASCADE
);

Table people


CREATE TABLE `people` (
  `email_address` varchar(255) CHARACTER SET utf8 NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `username` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `domain_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `top_level_domain` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`email_address`)
);

Retrieved from "http://wiki.meego.com/Metrics/Mailing_list_queries"