In the first part of our Plugin Developer Statistics blog post we described the different data sources and chose Metabase as our stats dashboard. Have a look at the article in case you’ve missed it.
In this article we are going to fetch some public metrics available on wordpress.org and save them in a SQLite database for later usage in our Metabase dashboard:
- Active installations
- Average Rating and number of ratings
- Number of support tickets and solved support tickets
- Week by week active installation growth
- Downloads per day
WordPress provides two public API endpoints to fetch this data.
wordpress.org Plugin Info API Endpoint
Using the API endpoint http://api.wordpress.org/plugins/info/1.2/ you can query plugins by different parameters.
Before using a script to fetch the values you just explore the results in Postman. If you haven’t heard about Postman yet and you’re interested in exploring some more APIs you definitely should install this tool.
Exploring wordpress.org Plugin Info API with Postman
Create a new request, enter a name and add it to a new or existing collection of API requests.
For our use case we want to get all our own plugins queried by wordpress.org user name. My username is @haet so please replace it in the example below with your own.
Click the send button and you’re gonna see all your plugins with lots of informations. You can also pass true or false for each field you want to show up in the JSON result but we are going to do this later in our script.
We’ve seen the raw data now so let’s continue to the next API endpoint:
WordPress plugin active installations API
There’s another API to get the active installations growth week by week as shown on the page “advanced” of your plugin page: https://api.wordpress.org/stats/plugin/1.0/active-installs.php
There isn’t much documentation about this API (I didn’t find anything at the time of writing in the codex page) but I had a look at the source code of the WordPress plugin repository which is open source and found an API call in a file stats.js. This file seems to generate the graphs on the page “advanced” on the plugin details on wordpress.org.
The API call uses three parameters:
- slug
- limit
- callback
similar to the downloads-endpoint documented in the codex.
This API is simple enough to directly try it in the browser without Postman. We omit the parameter “callback” and by trial and error we found a maximum value of the “limit” parameter of 730 days (two years).
The resulting query for our plugin slug wp-html-mail is https://api.wordpress.org/stats/plugin/1.0/active-installs.php?slug=wp-html-mail&limit=730
which returns the active installation growth of our plugin for the last two years week by week as JSON.
Get statistics and save to SQLite database
We decided to use store our data in a SQLite database because we can just backup and restore the database file which saves us a few minutes of writing a backup script. I personally prefer Python for automation scripts but this articles are for WordPress developers and every WordPress developer is familiar with PHP. We can also execute PHP scripts from commandline without a webserver or a browser.
When we have set up Metabase we mapped a folder “metabase-data” as a volume to the docker container, but you can use any folder reachable by Metabase for your SQLite file. For simplicity we also put our scripts to this folder so our folder structure looks like this.
Create the SQLite database connection in PHP
The first file you can already see in the screenshot above is db.php:
<?php
class CQDashboardDB extends SQLite3 {
function __construct() {
$this->open( __DIR__ . '/../plugindata.db' );
}
}
?>It just opens a database connection to a file plugindata.db one folder above. The db-file will be created automatically when you execute the script later.
Create table structure and add data from API
Let’s create the second file now and call it wordpressorg.php
<?php
function callAPI($method, $url, $data){
$curl = curl_init();
switch ($method){
case "POST":
curl_setopt($curl, CURLOPT_POST, 1);
if ($data)
curl_setopt($curl, CURLOPT_POSTFIELDS, $data);
break;
case "PUT":
curl_setopt($curl, CURLOPT_CUSTOMREQUEST, "PUT");
if ($data)
curl_setopt($curl, CURLOPT_POSTFIELDS, $data);
break;
default:
if ($data)
$url = sprintf("%s?%s", $url, http_build_query($data));
}
// OPTIONS:
curl_setopt($curl, CURLOPT_URL, $url);
curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($curl, CURLOPT_HTTPAUTH, CURLAUTH_BASIC);
// EXECUTE:
$result = curl_exec($curl);
if(!$result){die("Connection Failure");}
curl_close($curl);
return $result;
}
function maybe_create_database_table( $db ){
$sql = "
CREATE TABLE IF NOT EXISTS wordpressorg
(
ID INTEGER PRIMARY KEY AUTOINCREMENT,
slug CHAR(100) NOT NULL,
name CHAR(200),
rating REAL,
numratings INTEGER,
installs INTEGER,
support CHAR(100),
TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS activeinstallationgrowth
(
slug CHAR(100) NOT NULL,
week date,
growth REAL
)
;
";
$db->exec($sql);
}
require( 'db.php' );
$db = new CQDashboardDB();
if($db) {
maybe_create_database_table( $db );
$result = callAPI( 'GET', 'http://api.wordpress.org/plugins/info/1.1/',[
'action' => 'query_plugins',
'timeout' => 15,
'request' => [
'per_page' => 250, // 250 is the max allowed
'author' => 'haet',
'fields' => [
'sections' => false,
'description' => false,
'homepage' => false,
'ratings' => true,
'requires' => false,
'downloaded' => false,
'tags' => false,
'donate_link' => false,
'active_installs'=> true
]
]
]
);
$result = json_decode($result);
$plugins = $result->plugins;
foreach( $plugins as $index => $plugin ){
// print_r( $plugin );
echo '########## ' . $plugin->name . "\n";
$slug = $plugin->slug;
$name = html_entity_decode( $plugin->name );
$rating = round( 5 * ( $plugin->rating / 100 ), 1);
$numratings = $plugin->num_ratings;
$installs = $plugin->active_installs;
$support = $plugin->support_threads_resolved . ' / ' . $plugin->support_threads;
$sql = "
INSERT INTO wordpressorg( SLUG, NAME, RATING, NUMRATINGS, INSTALLS, SUPPORT )
VALUES( '$slug', '$name', $rating, $numratings, $installs, '$support' );
";
$db->exec($sql);
$growth_result = callAPI( 'GET', 'https://api.wordpress.org/stats/plugin/1.0/active-installs.php',[
'slug' => $slug,
'limit' => 730
]
);
$growth_result = json_decode($growth_result);
foreach( $growth_result as $week => $growth ){
$growth = floatval( str_replace( '+', '', $growth ) );
$existing_rows = $db->querySingle("SELECT COUNT(*) FROM activeinstallationgrowth WHERE WEEK = date('$week') AND SLUG='$slug'");
if( $existing_rows == 0 ){
$sql = "
INSERT INTO activeinstallationgrowth( SLUG, WEEK, GROWTH )
VALUES( '$slug', date('$week'), $growth );
";
$db->exec($sql);
}
}
}
$db->close();
}We don’t care for any class structure here because we will just call this file from command line and wont embed it in any application.
Maybe you need to install some dependencies first. We need PHP, PHP SQLite and PHP CURL. In debian based environments we use this command to install:
sudo apt-get install php7.2-sqlite php7.2-curlnow execute php wordpressorg.php
Within a few seconds the database file plugindata.db should appear and receive some data. To verify whether or not our script works we can download the database and open it in DB Browser for SQLite.
Refresh data via cron
We just have todays data now, but we don’t want to refresh this data manually so we set up a cron job to do this automatically.
Because we are going to have multiple scripts we decided to create a bash script to execute our php scripts. I’ve called it run-hourly.sh:
#!/bin/sh
current_path="${0%/*}"
cd "$current_path"
php wordpressorg.phpThis script is going to be executed by cron every hour: Type:
crontab -eand add the following line:
0 * * * * /home/YOUR-PATH/run-hourly.shNow it’s time to add this data to our dashboard in part 3 of this blog post.