4 hacks pour extraire des statistiques de WordPress

Date: 5/01/2011 | Catégories: Blog,Open-source,Planet-libre,Systeme,Web | Tags: ,,,

C'est le début de l'année et le moment des bilans concernant les statistiques de votre blog. Voici donc un petit billet sur quelques hacks MySQL permettant d'obtenir des statistiques à diffuser vers vos lecteurs ou à garder au chaud !

Avant tout il faut disposer d'un accès à votre base de donnée MySQL (soit via une interface de type phpMySQL, soit directement via une ligne de commande mysql). Pour avoir vos paramètres de connexion à votre BD WordPress il suffit de regarder le fichier wp-config.php à la racine de votre répertoire Web.

Voici l'architecture des tables de votre BD WordPress. Je ne suis plus trop un expert en requêtes MySQL (les cours datent de plus de 10 ans maintenant ;)) alors si vous voyez des améliorations dans les requêtes ci-dessous je suis preneur de commentaires.

Nombre de billets publiés en 2010

Cette première requête donne le nombre de billets publiés (status="publish") sur l'année 2010 (c'est à dire entre le 1er janvier et le 31 décembre 2010).

mysql> SELECT COUNT(*) FROM wp_posts WHERE post_status="publish" AND post_date BETWEEN '2010-01-01' AND '2010-12-31';

+----------+

| COUNT(*) |

+----------+

| 161 |

+----------+

1 row in set (0.00 sec)

Nombre de commentaires pour les billets publiés en 2010

Une requête un peu plus complexe qui somme (SUM) le nombre de commentaires pour les billets 2010.

mysql> SELECT SUM(comment_count) FROM wp_posts WHERE post_status="publish" AND post_date BETWEEN '2010-01-01' AND '2010-12-31' GROUP BY post_status;

+--------------------+

| SUM(comment_count) |

+--------------------+

| 1314 |

+--------------------+

1 row in set (0.01 sec)

Liste des 10 billets les plus commentés publiés en 2010

On continu pas le classement (TOP 10) des nouveaux billets les plus commentés.

mysql> SELECT post_title,comment_count FROM wp_posts WHERE post_status="publish" AND post_date BETWEEN '2010-01-01' AND '2010-12-31' ORDER BY comment_count DESC LIMIT 0,10;

+-------------------------------------------------------------------+---------------+

| post_title | comment_count |

+-------------------------------------------------------------------+---------------+

| Configurer VPNTunnel sous Ubuntu | 58 |

| Installation et test de Flumotion 0.8 | 46 |

| Script d'installation automatique de Nagios | 42 |

| Spideroak, un sérieux concurrent à Dropbox | 40 |

| Utiliser Nmap pour générer vos fichiers de configuration Nagios | 38 |

| Installation d'un serveur OpenVPN sous Debian/Ubuntu | 36 |

| 12 étapes pour optimiser les performances de son blog WordPress | 35 |

| Le blog de Nicolargo a son application IOS | 35 |

| Tu fais quoi après l'installation de Firefox ? | 26 |

| MyScreenCast, comment faire du screencast avec GStreamer | 26 |

+-------------------------------------------------------------------+---------------+

10 rows in set (0.01 sec)

Top 10 des lecteurs ayant le plus posté de commentaires sur le blog en 2010

Enfin une requête un peu plus complexe qui donne le classement des plus gros "commentateurs" sur le blog.

mysql> SELECT comment_author,COUNT(comment_count) AS F01 FROM wp_comments,wp_posts WHERE comment_approved=1 AND comment_post_ID=ID AND comment_date BETWEEN '2010-01-01' AND '2010-12-31' GROUP BY comment_author ORDER BY F01 DESC LIMIT 0,10;+----------------+-----+

| comment_author | F01 |

+----------------+-----+

| NicoLargo | 315 |

| Albert | 22 |

| Pierre-Yves | 18 |

| ninja21a | 18 |

| Mr Xhark | 15 |

| Sylvain | 14 |

| Christophe | 12 |

| floppy84 | 12 |

| Nico | 11 |

| Guillaume | 10 |

+----------------+-----+

10 rows in set (0.07 sec)

Si vous avez d'autres requêtes MySQL dans votre besace et bien faites tourner !

  • Pingback: Tweets that mention 4 hacks pour extraire des statistiques de Wordpress -- Topsy.com()

  • Salut,
    Elles sont bonnes tes requêtes SQL et facilement intégrables avec des shortcode… J’ai récemment fait le test et je trouve cela génial…. 🙂

    Pour la première je préfère utiliser l’instruction LIKE pour les dates, du genre :

    mysql> SELECT COUNT(*) FROM wp_posts WHERE post_status= »publish » AND post_date LIKE ‘%2010-% ;

    Après je ne sais pas ce qui est le plus optimisé…

  • A mon avis le plus simple (et je pense plus optimisé qu’un LIKE) est de faire WHERE YEAR(post_date) = ‘2010’.

    Je n’utilise par WordPress mais ca donne quelques idées à utiliser sur mon blog, merci! 🙂

    • BETWEEN est le plus optimisé.
      Il est bien plus optimisé qu’un LIKE, comme toutes les fonctions de chaines de caractères en règle générale
      Et un YEAR va devoir extraire d’abord l’année puis la comparer aux autres, ce qui prendra légèrement plus de temps sur une petite table et plombera le SGBD sur une grosse table

      • « Et un YEAR va devoir extraire d’abord l’année puis la comparer aux autres »
        Il fallait bien entendu lire « et la comparer à la date donnée.
        Surtout que, bien souvent, les dates sont stockés sous forme de timestamp.
        Un « A BETWEEN B AND C » est équivalent à « A >= B AND A <= C". En l'occurence, il va convertir B et C en timestamp et faire une bête comparaison sur des int alors que le YEAR() devrai calculer l'année du timestamp pour chaque ligne pour finalement la comparer à 2010.

  • Salut,

    Merci pour ces requêtes ! 🙂
    Existe-t’il un plugin qui me permettrait de savoir combien de billet j’écris par mois pour une année précise ?
    Je voudrais faire une sorte de courbe de mon activité de blogueur.
    Peut-être une petite requête pourrais me faire çà ?? 🙂
    Ben sinon, il faudra que je déterre mes cours de SQL !!

  • Article intéressant une fois de plus sur WordPress ! Juste une précision qui suit bien avec les articles que vous écrivez sur la sécurité dans WordPress. Pour les requêtes le préfixe des noms de tables n’est pas forcément wp_ c’est même conseillé de le modifier. Je crois que vous allez en parler dans un prochain article ^^

    • Effectivement 🙂 Mais pour illustrer ce billet j’ai trouvé plus utile d’utiliser les préfixes standards.

  • Pingback: wp-popular.com » Blog Archive » 4 hacks pour extraire des statistiques de Wordpress | Nicolargo()

  • Je suis un hackeur, je fais des requêtes SQL !

  • Très bon, je ne savais pas que j’étais le 3ème plus gros commentateur 😉
    | Pierre-Yves | 18 |

  • Pingback: Statistiques d'utilisation de Wordpress sur une année - Tux-planet()

  • Merci pour ces requêtes, et puis je remet un comm’ pour entrer dans le top 3 🙂

  • Si jamais ça intéresse quelqu’un voici les requêtes pour Dotclear : (j’ai modifié la dernière pour m’exclure des résultats)

    SELECT COUNT(*) FROM dc_post WHERE post_status=1 AND post_dt BETWEEN ‘2010-01-01 00:00:00’ AND ‘2010-12-31 00:00:00’ AND post_status=1 AND blog_id = ‘default’;

    SELECT SUM(nb_comment) FROM dc_post WHERE post_status=1 AND blog_id = ‘default’ AND post_dt BETWEEN ‘2010-01-01’ AND ‘2010-12-31’;

    SELECT post_title, nb_comment FROM dc_post WHERE post_status=1 AND blog_id = ‘default’ AND post_dt BETWEEN ‘2010-01-01’ AND ‘2010-12-31’ ORDER BY nb_comment DESC LIMIT 0,10;

    SELECT comment_author,COUNT(nb_comment) AS F01 FROM dc_comment,dc_post WHERE dc_comment.post_id = dc_post.post_id AND comment_status=1 AND comment_author != ‘annso’ AND comment_dt BETWEEN ‘2010-01-01’ AND ‘2010-12-31’ GROUP BY comment_author ORDER BY F01 DESC LIMIT 0,10;

  • Pingback: Votre rétrospective 2010 - Philippe Scoffoni()

  • Pingback: 22 requêtes MySQL indispensables pour WordPress | WordPress Channel()

  • Pingback: En vrac #61 « wOueb by Romain DECKER / Another IT Guy Blog()

  • Pingback: Bilan du blog après six mois de mise en ligne | Journal d'un François au Japon()

  • Pingback: Planète Xoxo, le répertoire multimédia du Web !()