{"id":534,"date":"2023-01-14T15:01:30","date_gmt":"2023-01-15T00:01:30","guid":{"rendered":"https:\/\/blog.geofictician.net\/?p=534"},"modified":"2023-01-14T15:01:30","modified_gmt":"2023-01-15T00:01:30","slug":"opengeofiction-aggregate-active-user-data-since-migration","status":"publish","type":"post","link":"https:\/\/blog.geofictician.net\/index.php\/2023\/01\/14\/opengeofiction-aggregate-active-user-data-since-migration\/","title":{"rendered":"OpenGeofiction Aggregate Active User Data (Since Migration)"},"content":{"rendered":"

I decided to try to brush off some very rusty SQL skills and write a query against the OGF database to find out what our monthly active user counts looked like.<\/p>\n

This data can only be compiled accurately for dates since the “migration”, which was in August, 2021. Before that changesets are not properly dated as they were all loaded at once from the old instance (Thilo’s) to the new instance (Luciano’s).<\/p>\n

I decided to categorize unique user counts for each month by their “start year” (e.g. a user like myself would be under start year 2014). That way I could also see how old “generations” of users drop off over time.<\/p>\n

I started with a nested SQL query, which is basically what I used to do professionally about 20 years ago, but it’s been a while since I wrote raw queries against complex data like this. It comes back fairly quickly though, and this is definitely the result of a little bit of trial and error. I’m working with two tables in the openstreetmap PostgreSQL database: users and changesets. I’m counting changesets by the month they occur and by a breakdown of users by when they started using the OGF site (with a special category for users who are “new” in the month they are active).<\/p>\n

Here’s the query I made and ran at the PSQL prompt on the server (these data structures are all public knowledge, part of the OSM specification, so I don’t feel worried sharing it).<\/p>\n

SELECT \n t.changeset_month,\n t.user_first_year, \n t.new_user_flag,\n COUNT(DISTINCT t.user_id) AS count_users,\n COUNT(t.changeset_id) AS count_changesets\nFROM ( \n  SELECT\n   i.changeset_id,\n   i.changeset_month,\n   i.user_id,\n   LEFT(i.creation_month, 4) AS user_first_year,\n   CASE\n    WHEN i.creation_month = i.changeset_month\n    THEN 'new_user'\n    ELSE 'old_user'\n   END AS new_user_flag\n  FROM ( \n    SELECT \n     to_char(changesets.closed_at, 'YYYY-MM') AS changeset_month,\n     changesets.user_id AS user_id, \n     changesets.id AS changeset_id, \n     to_char(users.creation_time, 'YYYY-MM') as creation_month\n    FROM changesets INNER JOIN users \n    ON users.id = changesets.user_id \n    WHERE changesets.closed_at >= '2021-10-01'::date\n    AND changesets.closed_at <= '2022-12-31'::date\n  ) AS i\n) AS t\nGROUP BY \n t.changeset_month,\n t.user_first_year, \n t.new_user_flag\nORDER BY \n t.changeset_month,\n t.user_first_year, \n t.new_user_flag DESC;\n<\/pre>\n
Here’s the raw output I got.<\/p>\n
changeset_month | user_first_year | new_user_flag | count_users | count_changesets\n-----------------+-----------------+---------------+-------------+------------------\n2021-10         | 2013            | old_user      |          10 |              112\n2021-10         | 2014            | old_user      |          17 |             1098\n2021-10         | 2015            | old_user      |          23 |              720\n2021-10         | 2016            | old_user      |          24 |              448\n2021-10         | 2017            | old_user      |          34 |              787\n2021-10         | 2018            | old_user      |          45 |             1088\n2021-10         | 2019            | old_user      |          35 |              750\n2021-10         | 2020            | old_user      |          28 |              557\n2021-10         | 2021            | old_user      |          34 |              492\n2021-10         | 2021            | new_user      |          24 |              280\n2021-11         | 2012            | old_user      |           1 |                1\n2021-11         | 2013            | old_user      |           8 |              207\n2021-11         | 2014            | old_user      |          19 |              687\n2021-11         | 2015            | old_user      |          26 |              697\n2021-11         | 2016            | old_user      |          21 |              408\n2021-11         | 2017            | old_user      |          36 |              738\n2021-11         | 2018            | old_user      |          41 |             1271\n2021-11         | 2019            | old_user      |          29 |              663\n2021-11         | 2020            | old_user      |          26 |              749\n2021-11         | 2021            | old_user      |          28 |              439\n2021-11         | 2021            | new_user      |          23 |              498\n2021-12         | 2013            | old_user      |          10 |              335\n2021-12         | 2014            | old_user      |          17 |              773\n2021-12         | 2015            | old_user      |          23 |              892\n2021-12         | 2016            | old_user      |          25 |              657\n2021-12         | 2017            | old_user      |          31 |              703\n2021-12         | 2018            | old_user      |          43 |             1506\n2021-12         | 2019            | old_user      |          29 |              769\n2021-12         | 2020            | old_user      |          25 |              664\n2021-12         | 2021            | old_user      |          45 |              854\n2021-12         | 2021            | new_user      |          22 |              250\n2022-01         | 2012            | old_user      |           1 |                3\n2022-01         | 2013            | old_user      |           7 |              174\n2022-01         | 2014            | old_user      |          13 |             1023\n2022-01         | 2015            | old_user      |          22 |              805\n2022-01         | 2016            | old_user      |          23 |              568\n2022-01         | 2017            | old_user      |          32 |              753\n2022-01         | 2018            | old_user      |          39 |             1642\n2022-01         | 2019            | old_user      |          30 |             1062\n2022-01         | 2020            | old_user      |          30 |              878\n2022-01         | 2021            | old_user      |          42 |              703\n2022-01         | 2022            | new_user      |          24 |              192\n2022-02         | 2013            | old_user      |          10 |              397\n2022-02         | 2014            | old_user      |          19 |             1251\n2022-02         | 2015            | old_user      |          21 |              768\n2022-02         | 2016            | old_user      |          22 |              540\n2022-02         | 2017            | old_user      |          30 |             1136\n2022-02         | 2018            | old_user      |          41 |             1612\n2022-02         | 2019            | old_user      |          25 |              617\n2022-02         | 2020            | old_user      |          27 |              888\n2022-02         | 2021            | old_user      |          41 |              722\n2022-02         | 2022            | old_user      |           8 |              117\n2022-02         | 2022            | new_user      |          21 |              234\n2022-03         | 2013            | old_user      |           8 |               61\n2022-03         | 2014            | old_user      |          17 |             1384\n2022-03         | 2015            | old_user      |          25 |              927\n2022-03         | 2016            | old_user      |          22 |              705\n2022-03         | 2017            | old_user      |          35 |             1498\n2022-03         | 2018            | old_user      |          41 |             1211\n2022-03         | 2019            | old_user      |          33 |              861\n2022-03         | 2020            | old_user      |          30 |             1018\n2022-03         | 2021            | old_user      |          38 |             1458\n2022-03         | 2022            | old_user      |          13 |              491\n2022-03         | 2022            | new_user      |          27 |              758\n2022-04         | 2013            | old_user      |           7 |              186\n2022-04         | 2014            | old_user      |          17 |              871\n2022-04         | 2015            | old_user      |          24 |              500\n2022-04         | 2016            | old_user      |          19 |              683\n2022-04         | 2017            | old_user      |          30 |             1093\n2022-04         | 2018            | old_user      |          39 |             1291\n2022-04         | 2019            | old_user      |          29 |              833\n2022-04         | 2020            | old_user      |          28 |              673\n2022-04         | 2021            | old_user      |          32 |              753\n2022-04         | 2022            | old_user      |          25 |              900\n2022-04         | 2022            | new_user      |          21 |              231\n2022-05         | 2012            | old_user      |           1 |                1\n2022-05         | 2013            | old_user      |           8 |              214\n2022-05         | 2014            | old_user      |          22 |             1093\n2022-05         | 2015            | old_user      |          25 |              526\n2022-05         | 2016            | old_user      |          25 |              899\n2022-05         | 2017            | old_user      |          28 |             1100\n2022-05         | 2018            | old_user      |          40 |             1097\n2022-05         | 2019            | old_user      |          30 |              873\n2022-05         | 2020            | old_user      |          30 |              673\n2022-05         | 2021            | old_user      |          34 |             1328\n2022-05         | 2022            | old_user      |          27 |              558\n2022-05         | 2022            | new_user      |          30 |              348\n2022-06         | 2013            | old_user      |           7 |              249\n2022-06         | 2014            | old_user      |          17 |              979\n2022-06         | 2015            | old_user      |          26 |              605\n2022-06         | 2016            | old_user      |          26 |              593\n2022-06         | 2017            | old_user      |          30 |              988\n2022-06         | 2018            | old_user      |          39 |              941\n2022-06         | 2019            | old_user      |          27 |              985\n2022-06         | 2020            | old_user      |          26 |              783\n2022-06         | 2021            | old_user      |          36 |              919\n2022-06         | 2022            | old_user      |          44 |              606\n2022-06         | 2022            | new_user      |          31 |              523\n2022-07         | 2013            | old_user      |           8 |               84\n2022-07         | 2014            | old_user      |          19 |             1414\n2022-07         | 2015            | old_user      |          25 |              630\n2022-07         | 2016            | old_user      |          24 |              558\n2022-07         | 2017            | old_user      |          29 |             1209\n2022-07         | 2018            | old_user      |          39 |             1199\n2022-07         | 2019            | old_user      |          27 |              632\n2022-07         | 2020            | old_user      |          29 |              796\n2022-07         | 2021            | old_user      |          35 |             1082\n2022-07         | 2022            | old_user      |          41 |              750\n2022-07         | 2022            | new_user      |          38 |              632\n2022-08         | 2013            | old_user      |          10 |              165\n2022-08         | 2014            | old_user      |          17 |             1236\n2022-08         | 2015            | old_user      |          24 |              687\n2022-08         | 2016            | old_user      |          25 |              636\n2022-08         | 2017            | old_user      |          29 |              961\n2022-08         | 2018            | old_user      |          41 |             1145\n2022-08         | 2019            | old_user      |          35 |              808\n2022-08         | 2020            | old_user      |          25 |              585\n2022-08         | 2021            | old_user      |          38 |              847\n2022-08         | 2022            | old_user      |          50 |             1049\n2022-08         | 2022            | new_user      |          40 |              589\n2022-09         | 2013            | old_user      |          10 |              175\n2022-09         | 2014            | old_user      |          19 |             1534\n2022-09         | 2015            | old_user      |          20 |              604\n2022-09         | 2016            | old_user      |          24 |              750\n2022-09         | 2017            | old_user      |          23 |              735\n2022-09         | 2018            | old_user      |          41 |             1302\n2022-09         | 2019            | old_user      |          28 |              841\n2022-09         | 2020            | old_user      |          24 |              503\n2022-09         | 2021            | old_user      |          30 |              609\n2022-09         | 2022            | old_user      |          54 |             1035\n2022-09         | 2022            | new_user      |          25 |              261\n2022-10         | 2012            | old_user      |           1 |                3\n2022-10         | 2013            | old_user      |           8 |              231\n2022-10         | 2014            | old_user      |          18 |             1112\n2022-10         | 2015            | old_user      |          22 |              433\n2022-10         | 2016            | old_user      |          25 |              789\n2022-10         | 2017            | old_user      |          25 |              809\n2022-10         | 2018            | old_user      |          39 |             1267\n2022-10         | 2019            | old_user      |          28 |              895\n2022-10         | 2020            | old_user      |          30 |              736\n2022-10         | 2021            | old_user      |          27 |              382\n2022-10         | 2022            | old_user      |          54 |             1040\n2022-10         | 2022            | new_user      |          17 |              173\n2022-11         | 2013            | old_user      |           8 |              248\n2022-11         | 2014            | old_user      |          16 |             1164\n2022-11         | 2015            | old_user      |          22 |              357\n2022-11         | 2016            | old_user      |          23 |              675\n2022-11         | 2017            | old_user      |          25 |              927\n2022-11         | 2018            | old_user      |          37 |              897\n2022-11         | 2019            | old_user      |          25 |              702\n2022-11         | 2020            | old_user      |          25 |              679\n2022-11         | 2021            | old_user      |          25 |              577\n2022-11         | 2022            | old_user      |          49 |             1100\n2022-11         | 2022            | new_user      |          31 |             1641\n2022-12         | 2013            | old_user      |           6 |              317\n2022-12         | 2014            | old_user      |          18 |              981\n2022-12         | 2015            | old_user      |          23 |              543\n2022-12         | 2016            | old_user      |          20 |              541\n2022-12         | 2017            | old_user      |          25 |              941\n2022-12         | 2018            | old_user      |          34 |              973\n2022-12         | 2019            | old_user      |          28 |              935\n2022-12         | 2020            | old_user      |          22 |              500\n2022-12         | 2021            | old_user      |          29 |              432\n2022-12         | 2022            | old_user      |          59 |             1623\n2022-12         | 2022            | new_user      |          25 |              317\n<\/pre>\n
I plugged these data into a spreadsheet, did a few changes and a pivot table. Here’s a graph of the result.<\/p>\n
\"picture\"<\/a><\/p>\n
The top band, in light blue, is the “new users” band – these are users each month who are active in their first month of joining OGF. The lower bands represent each year back to OGF’s founding, in 2012 (there were only 2 users in the first year, Thilo and Joschi).<\/p>\n
Based on that graph, I would say really OGF is quite stable. We acquire a certain number of new users each month, we lose about an equal proportion of old users, but some subset of long-term users stick around.<\/p>\n","protected":false},"excerpt":{"rendered":"
I decided to try to brush off some very rusty SQL skills and write a query against the OGF database to find out what our monthly active user counts looked like. This data can only be compiled accurately for dates since the “migration”, which was in August, 2021. Before that changesets are not properly dated … Continue reading OpenGeofiction Aggregate Active User Data (Since Migration)<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,11],"tags":[],"class_list":["post-534","post","type-post","status-publish","format-standard","hentry","category-hrate","category-ogf-sysadmin"],"_links":{"self":[{"href":"https:\/\/blog.geofictician.net\/index.php\/wp-json\/wp\/v2\/posts\/534","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.geofictician.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.geofictician.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.geofictician.net\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.geofictician.net\/index.php\/wp-json\/wp\/v2\/comments?post=534"}],"version-history":[{"count":0,"href":"https:\/\/blog.geofictician.net\/index.php\/wp-json\/wp\/v2\/posts\/534\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.geofictician.net\/index.php\/wp-json\/wp\/v2\/media?parent=534"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.geofictician.net\/index.php\/wp-json\/wp\/v2\/categories?post=534"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.geofictician.net\/index.php\/wp-json\/wp\/v2\/tags?post=534"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}