In part 2 and part 3 of our plugin developer dashboard we fetched all available statistics from WordPress.org about our plugins, saved these values to SQLite database and visualized them on our Metabase dashboard.
One of these values was the active installation growth. Since we reached 10.000 installations more than a year ago we don’t see any numbers about the current installations. We just see a weekly growth in percent until we finally reach 20.000 users.
Inspired by the article “How to Get the Actual Number of Active Installs of Your WordPress Plugin” from Benjamin Intal we wanted to extend our dashboard to show the actual number of active installations.
All data is already available, we just need a little bit of math and SQL.
How to calculate active WordPress plugin installations
At first we need to remember the date when we reached the current number shown on wordpress.org. As far as I can remember we reached 10.000 installation around February 2019, maybe earlier but I’m not sure.
The next entry in our activeinstallationgrowth SQLite table is February 3 with a growth of 1.3 percent so we calculate 10000*(100+growth)/100
To calculate the value for the next week we have to use the previous calculated value which is a little bit tricky in SQL. It took some time but now I have a working recursive SELECT statement.
Add the query to Metabase
Click the button “ask a question” on the top right of your dashboard and select “native query”.
Now add the following SQL statement and replace the values for date, slug and the start number of 10000:
WITH RECURSIVE growth_step AS ( SELECT week, growth, grown_value, slug FROM ( SELECT week, growth, 10000*(100+growth)/100 as grown_value, slug FROM activeinstallationgrowth WHERE week > "2019-02-01" -- this is the date I can remember to have reached 10.000 installations AND slug = "wp-html-mail" LIMIT 1 ) UNION ALL SELECT activeinstallationgrowth.week, activeinstallationgrowth.growth, growth_step.grown_value + growth_step.grown_value * activeinstallationgrowth.growth/100 as grown_value, activeinstallationgrowth.slug FROM growth_step INNER JOIN activeinstallationgrowth ON growth_step.week < activeinstallationgrowth.week AND date(growth_step.week,'+10 days') >= activeinstallationgrowth.week AND activeinstallationgrowth.slug=growth_step.slug -- we use a time range here instead of exactly 7 days because we found at least one date out of the 7 days interval in our data ) SELECT grown_value FROM growth_step ORDER BY week desc LIMIT 1;
Execute the query and you should already see a number. Just adjust some of the visualization settings and save the result to your dashboard.