[Token distribution] Pulling user stats into a Google sheet

Its ok. I’m very happy to be working on this so i’ve set aside the evening. I won’t be online much past midnight or 1am probably, but i’m excited to get into this with you :slight_smile:

1 Like

Great. I’ll let you know if I need anything

1 Like

I forgot to mention that our homelessness and community climate action platforms are both clones of this platform and will use the exact same code to pull user stats from their platform into their own google sheet.

I can’t think of any reason that would make your task any different, but i’m mentioning it in case you come across a situation which is url/site specific (like the google form access key i’m working on will be)

Okay, thanks for the heads up. I normally try not to include any URL’s on my code but to pass them as arguments so it shouldn’t be a problem

1 Like

Perfect :slight_smile:

How’s it going, anything i can help with before clocking off for the night?

Going well so far, reading documentation. I think I’m good

1 Like

Awesome. I’ve been doing a lot of mostly reading over here too. The token distribution can be fairly simple, but it seems avoiding gas costs is a core element of the design that needs to be planned for from the beginning.

Yeah, and I don’t know how POS will affect it but as the blockchain grows transactions get more expensive too

1 Like

Not at all I don’t think. Sharding is supposed to.

Hey Andy. I’m not sure if I’ll be able to do this, or at least as a discourse plugin because there’s a lot of ruby code involved and I don’t know the first thing about it. Maybe we can explore other options? considering the simplicity of the task I think we could use pure Javascript.

1 Like

Discourse plugins can be coded in any language. The existing plugin we’re working with may require ruby code though, i’m not sure.

considering the simplicity of the task I think we could use pure Javascript.

Do you mean coding a new Discourse plugin to pull that data out and into a google sheet. If you feel that’s achievable you’d be very welcome to do so. Here’s the notes in our documentation: Creating a new Discourse plugin

I’m most interested in the solution, so any way to solve it is ok. The plugin would need to pull those three datasets into a drive spreadsheet ideally autonomously once a month.

Oh, that’s good. Looking at the examples and documentation I really thought it had to be done with ruby. In that case I’ll give it another try and let you know when there’s any update

1 Like

@Marvelxy is working on a Discourse plugin for our navigation. Marvelous do you have any tips for writing JavaScript into a plug-in?

2 Likes

Sorry @pablodclavijo, for most part, it’s always JavaScript because it is mainly frontend stuff. I had the learn like you are currently learning lol :laughing:.

I will share some resources on working with JS in Discourse.

1 Like

@pablodclavijo Let me know when you intend to work on this and i’ll try to make sure i’m online to support. I’ll edit the 1st post in this thread to include all the relevant data.

Update:

Step 1:

This is the main code and is working great at pulling the last 30 days of each active users stats out

I notice a limit in there, if that is limiting the amount of users it returns data on we need to remove it as we want to pull everyone who was active in the past month. Actually set it to 9000, just for safety.


-- [params]
-- int :from_days_ago = 0
-- int :duration_days = 30
WITH t AS (
    SELECT CURRENT_TIMESTAMP - ((:from_days_ago + :duration_days) * (INTERVAL '1 days')) AS START,
        CURRENT_TIMESTAMP - (:from_days_ago * (INTERVAL '1 days')) AS END
),
pr AS (
    SELECT user_id, COUNT(1) AS visits,
        SUM(posts_read) AS posts_read
    FROM user_visits, t
    WHERE posts_read > 0
        AND visited_at > t.START
        AND visited_at < t.
        END
    GROUP BY
        user_id
),
pc AS (
    SELECT user_id, COUNT(1) AS posts_created
    FROM posts, t
    WHERE
        created_at > t.START
        AND created_at < t.
        END
    GROUP BY
        user_id
),
ttopics AS (
    SELECT user_id, posts_count
    FROM topics, t
    WHERE created_at > t.START
        AND created_at < t.
        END
),
tc AS (
    SELECT user_id, COUNT(1) AS topics_created
    FROM ttopics
    GROUP BY user_id
),
twr AS (
    SELECT user_id, COUNT(1) AS topics_with_replies
    FROM ttopics
    WHERE posts_count > 1
    GROUP BY user_id
),
tv AS (
    SELECT user_id,
        COUNT(DISTINCT(topic_id)) AS topics_viewed
    FROM topic_views, t
    WHERE viewed_at > t.START
        AND viewed_at < t.
        END
    GROUP BY user_id
),
likes AS (
    SELECT post_actions.user_id AS given_by_user_id,
        posts.user_id AS received_by_user_id
    FROM t,
        post_actions
        LEFT JOIN
        posts
        ON post_actions.post_id = posts.id
    WHERE
        post_actions.created_at > t.START
        AND post_actions.created_at < t.
        END
        AND post_action_type_id = 2
),
lg AS (
    SELECT given_by_user_id AS user_id,
        COUNT(1) AS likes_given
    FROM likes
    GROUP BY user_id
),
lr AS (
    SELECT received_by_user_id AS user_id,
        COUNT(1) AS likes_received
    FROM likes
    GROUP BY user_id
),
e AS (
    SELECT email, user_id
    FROM user_emails u
    WHERE u.PRIMARY = TRUE
)
SELECT
    pr.user_id,
    username,
    name,
    email,
    visits,
    COALESCE(topics_viewed, 0) AS topics_viewed,
    COALESCE(posts_read, 0) AS posts_read,
    COALESCE(posts_created, 0) AS posts_created,
    COALESCE(topics_created, 0) AS topics_created,
    COALESCE(topics_with_replies, 0) AS topics_with_replies,
    COALESCE(likes_given, 0) AS likes_given,
    COALESCE(likes_received, 0) AS likes_received
FROM pr
LEFT JOIN tv USING (user_id)
LEFT JOIN pc USING (user_id)
LEFT JOIN tc USING (user_id)
LEFT JOIN twr USING (user_id)
LEFT JOIN lg USING (user_id)
LEFT JOIN lr USING (user_id)
LEFT JOIN e USING (user_id)
LEFT JOIN users ON pr.user_id = users.id
ORDER BY
    visits DESC,
    posts_read DESC,
    posts_created DESC

Step 2

Custom User Fields

We want to add to the code in step one so it also pulls custom user fields and adds them as extra columns for each users stats. Running the code in the the first step I see this data output in the columns:

  • user id, user name, name, the user stats.

I want to see:

  • user id, user name, name, user custom fields, the user stats.

Here’s some code which pulls the custom user field in a different script:

  SELECT
    u.id AS user_id,
    MAX(CASE ucf.name WHEN 'user_field_1' THEN value END) AS first_field,
    MAX(CASE ucf.name WHEN 'user_field_2' THEN value END) AS second_field,
    MAX(CASE ucf.name WHEN 'user_field_3' THEN value END) AS third_field,
    MAX(CASE ucf.name WHEN 'user_field_4' THEN value END) AS fourth_field,
    MAX(CASE ucf.name WHEN 'user_field_5' THEN value END) AS fifth_field
  FROM user_custom_fields AS ucf
  RIGHT JOIN users AS U on ucf.user_id = u.id
  GROUP BY u.id

If you need more context for this code I’ve posted the full script below, marked ‘appendix 2’

Step 3

User Trust Level

Our code should also output each users trust level, from 0 - 4. Below is some code which i’ve modified to pull out all users trust levels who have engaged a few times with our platform. Users trust level needs to added to the code in step 1 so its also pulling users trust level and adding that as a column.

The final output columns should look like this (or a similar config if its simpler to set up):

  • user id, user name, name, user custom fields, user trust level, user stats.

This means the trust level code should also be pulling only the last 30 days.


-- Established users with trust-level 0, 1, 2, 3 or 4
-- Add limit

-- [params]
-- int :min_visited_days = 5
-- int :min_posts = 2
-- int :limit = 1000
SELECT 
    id AS user_id,
    trust_level,
    days_visited,
    post_count
FROM 
    users u JOIN
        user_stats us ON u.id = us.user_id
WHERE 
    trust_level IN (0,1,2,3,4) AND
    days_visited >= :min_visited_days AND
    post_count >= :min_posts AND
    (silenced_till IS NULL OR silenced_till < NOW()) AND
    (suspended_till IS NULL OR suspended_till < NOW())
ORDER BY 
    days_visited DESC
LIMIT :limit

If it helps to see the current output of any, or all, of the above commands let me know and i’ll remove sensitive data and DM you with them.

Appendix 2


-- [params]
-- date :date_from = 1970-01-01
-- date :date_to = 2038-01-19
-- boolean :guess_domain = true
-- string :domain = https://example.com

WITH ss AS (
  SELECT CASE
    WHEN :guess_domain = true THEN concat('https://', split_part(value, '@', 2))
    ELSE :domain
  END AS domain
  FROM site_settings
  WHERE name = 'notification_email'
), cfs AS (
  SELECT
    u.id AS user_id,
    MAX(CASE ucf.name WHEN 'user_field_1' THEN value END) AS first_field,
    MAX(CASE ucf.name WHEN 'user_field_2' THEN value END) AS second_field,
    MAX(CASE ucf.name WHEN 'user_field_3' THEN value END) AS third_field,
    MAX(CASE ucf.name WHEN 'user_field_4' THEN value END) AS fourth_field,
    MAX(CASE ucf.name WHEN 'user_field_5' THEN value END) AS fifth_field
  FROM user_custom_fields AS ucf
  RIGHT JOIN users AS U on ucf.user_id = u.id
  GROUP BY u.id
)
SELECT
  ua.created_at,
  CASE
    WHEN ua.action_type = 1 THEN 'Like'
    WHEN ua.action_type = 4 THEN 'New Topic'
    WHEN ua.action_type = 5 THEN 'Topic Reply'
    WHEN ua.action_type = 12 THEN 'Message'
  END AS action,
  u.username,
  ucf.*,
  t.title AS topic_title,
  c.name AS category,
  pc.name AS parent_category,
  concat((SELECT domain from ss), '/t/', t.id, '/', (CASE WHEN p.post_number IS NOT NULL then p.post_number ELSE 1 END)) AS url
FROM user_actions AS ua
JOIN users AS u ON ua.user_id = u.id
LEFT JOIN cfs AS ucf on ucf.user_id = u.id
JOIN topics AS t ON ua.target_topic_id = t.id
LEFT JOIN posts AS p ON ua.target_post_id = p.id
LEFT JOIN categories AS c ON t.category_id = c.id
LEFT JOIN categories AS pc ON c.parent_category_id = pc.id
WHERE
  ua.user_id != -1
  AND
  ua.action_type IN (1, 4, 5, 12)
  AND
  ua.created_at BETWEEN :date_from AND :date_to
ORDER BY created_at DESC

Ok, so i’ ve got this:

SELECT
    pr.user_id,
    username,
    name,
    email,
    uf.custom_field1,
    uf.custom_field2,
    tl.trust_level,
    visits,
    COALESCE(topics_viewed, 0) AS topics_viewed,
    COALESCE(posts_read, 0) AS posts_read,
    COALESCE(posts_created, 0) AS posts_created,
    COALESCE(topics_created, 0) AS topics_created,
    COALESCE(topics_with_replies, 0) AS topics_with_replies,
    COALESCE(likes_given, 0) AS likes_given,
    COALESCE(likes_received, 0) AS likes_received
FROM pr
LEFT JOIN tv USING (user_id)
LEFT JOIN pc USING (user_id)
LEFT JOIN tc USING (user_id)
LEFT JOIN twr USING (user_id)
LEFT JOIN lg USING (user_id)
LEFT JOIN lr USING (user_id)
LEFT JOIN e USING (user_id)
LEFT JOIN uf USING (user_id)
LEFT JOIN tl USING (user_id)

but its returning this error message.

PG::UndefinedTable: ERROR:  relation "pr" does not exist
LINE 23: FROM pr
              ^

I’m not sure what the issue is. @Marvelxy @WebDev any ideas?

update: here’s what they think it the issue over on Meta-Discourse