BI, or boredness-index is the new buzzword. So, how can we measure this very interesting concept? One scientifically sound way is by the number of Facebook posts made a week. Normally, this value is hard to measure, but as I started copying all my Facebook status messages to my own blog last year, I have easy access to tell my database to pick out these values.
All my statuc updates are posted in the category Status Updates. Using a simple SQL query on my WordPress database (see the layout here), I can find the identifier of that category:
[sql]
SELECT term_taxonomy_id, name
FROM wp_term_taxonomy, wp_terms
WHERE wp_terms.term_id = wp_term_taxonomy.term_id
AND taxonomy = ‘category’;
[/sql]
We get, among others:
[raw]
+——————+———————————-+
| term_taxonomy_id | name |
+——————+———————————-+
| 1 | Uncategorized |
| 47 | Writings |
| 95 | Political Ramblings |
| 102 | Lectures |
| 449 | Pictures |
| 173 | Presentations |
| 253 | ProM Plug-in |
| 361 | Status Updates |
| 427 | Diary |
…
| 579 | CPN Tools Tutorial |
| 580 | Tutorials |
+——————+———————————-+
[/raw]
Ok, so we’re interested in taxonomy number 361. Now, we just need to extract the post counts per week with this taxonomy. This is done using
[sql]
SELECT year(post_date), week(post_date), count(*)
FROM wp_posts, wp_term_relationships
WHERE wp_posts.ID = wp_term_relationships.object_id
AND wp_term_relationships.term_taxonomy_id = 361
GROUP BY year(post_date), week(post_date);
[/sql]
We get something like this:
[raw]
+—————–+—————–+———-+
| year(post_date) | week(post_date) | count(*) |
+—————–+—————–+———-+
| 2012 | 41 | 21 |
| 2012 | 42 | 31 |
| 2012 | 43 | 28 |
| 2012 | 44 | 32 |
| 2012 | 45 | 55 |
| 2012 | 46 | 55 |
| 2012 | 47 | 43 |
…
[/raw]
Copy and paste to a spreadsheet and make a nice graph. I color the weeks I’ve been in Russia green, and let my readers draw their own conclusions:
