Automatically compute detailed active installations for your WordPress Plugin

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.