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 
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
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 
.
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