Inspired by Iain Poulson’s service Plugin Rank I wanted to see this metrics in my personal dashboard. He offers a great tool but I didn’t want to check another service, so I’m going to add this feature to my own metabase dashboard right now.
If you haven’t read part 2 and part 3 of this post series please go back to these articles because in part 2 we created ans populated a SQLite database which is necessary for this chapter and in part 3 we used this database as a data source in metabase. This part extends both of them.
Search WordPress plugins via API
Using the same API we already used in part 2 we can’t just get plugins by author but also find them by a search term. This is the way the plugin search in WordPress backend works. In my opinion this search is at least as important for WordPress plugins as Google is.
http://api.wordpress.org/plugins/info/1.2/Instead of the author parameter we used in part 2 we use the parameter search now.
In our use case we search for “email template”.
Get Plugin Search Ranking via PHP script
Our API call looks like this:
$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
'search' => $searchterm,
'fields' => [
'sections' => false,
'description' => false,
'homepage' => false,
'ratings' => false,
'requires' => false,
'downloaded' => false,
'tags' => false,
'last_updated' => false,
'active_installs'=> false
]
]
]
);We can pass a variable $searchterm and filter the results afterwards to get the ranking of our plugin. The API allows us to get up to 250 search results per page which is enough for me. If my plugin is not within these 250 plugins I just return 250 as result instead of going through the following pages. But you should know this limitation because it could impact your statistics.
Here’s the full script:
<?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 getRanking($searchterm,$slug){
$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
'search' => $searchterm,
'fields' => [
'sections' => false,
'description' => false,
'homepage' => false,
'ratings' => false,
'requires' => false,
'downloaded' => false,
'tags' => false,
'last_updated' => false,
'active_installs'=> false
]
]
]
);
$result = json_decode($result);
$plugins = $result->plugins;
foreach( $plugins as $index => $plugin ){
if( $plugin->slug == $slug )
return $index+1;
}
return 250;
}
function maybe_create_database_table( $db ){
$sql = "
CREATE TABLE IF NOT EXISTS searchranking
(
ID INTEGER PRIMARY KEY AUTOINCREMENT,
slug CHAR(100) NOT NULL,
searchterm CHAR(255) NOT NULL,
ranking INTEGER,
TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP
);
";
$db->exec($sql);
}
$plugins = [
'wp-html-mail' => [
'email template',
'email design',
'mail designer',
'email',
'woocommerce email',
'woocommerce mail'
],
'cleverreach' => [
'cleverreach',
'newsletter'
]
];
require( 'db.php' );
$db = new CQDashboardDB();
if($db) {
maybe_create_database_table( $db );
foreach( $plugins as $slug => $searchterms ){
foreach( $searchterms as $searchterm ){
$ranking = getRanking($searchterm, $slug);
echo "$slug, $searchterm: " . $ranking ."\n";
$sql = "
INSERT INTO searchranking( slug, searchterm, ranking )
VALUES( '$slug', '$searchterm', $ranking );
";
$db->exec($sql);
}
}
}
It uses the SQLite database connection we created in part 2 and an array of plugin slugs and search terms. Copy the code above and paste it to a new file wordpressorg-search-ranking.php next to the files db.php and wordpressorg.php created in part 2.
Afterwards replace the $plugins variable with your plugins slug and search terms.
Open a terminal, navigate to the directory where you saved this script and run
php wordpressorg-search-ranking.phpIf everything works fine you just have to schedule future executions of your script using cron. In part 2 we created a bash script run-hourly.sh. For the search ranking it should be enough to run it once a day/night so I’m using a script run-nightly.sh to execute this php script:
#!/bin/sh
current_path="${0%/*}"
cd "$current_path"
php wordpressorg-search-ranking.phpTo trigger this script type crontab -e and add this line:
0 1 * * * /home/YOUR-PATH/run-nightly.shRefresh your data source in Metabase
We have modified the structure of our data source by adding an additional database table. In Metabase it could take some time until it refreshes the table structure. If you want to speed up this process navigate to
Admin -> Databases -> WordPress.org Plugin Data
There’s a button to sync the database schema.
Show wordpress.org plugin search ranking in your Metabase dashboard
As we already did in Part 3 we click the button “Ask a question“.
Then we choose Simple Question and select our new database table.
We use a line chart and filter by one single plugin and add a graph per search term. (see screenshot below for settings)
The chart looks a little bit boring at the moment but I promise, it’s getting more interesting over time when our cron job fetches new data every day.
You can create another chart like this for each of you plugins.
In part 5 we are going to add our PayPal balance to the dashboard.