[Token distribution] Pulling user stats into a Google sheet

Aim: To pull specific stats and user fields from the platform into a Google Sheet in our Drive Folder

Using the Discourse Data Explorer Plugin we can specify exactly what stats we want to pull from the platform and import them into a google sheet.

I’ve not yet been able to work out how to write the code we want. I’ve collected a few examples we can use to begin pulling lines of user data out.

All users have a custom field where they can add their wallet, this column will determine where our token distributes newly minted tokens to each month, multiplied by their user score for the month.

Using a second google sheet to pull the data from the 1st allows us to apply functions on it to give more granular control, for example:

  • Multiply their stats by their current user level
  • Multiply stats which are more critical to growing the community
  • Make a final column which works out their user score this month as a percentage of the total user points accumulated this month. This is important as there is a set amount of tokens released monthly and divided equally among users based on their contributions.

We can pull code from these and combine them into our own:

User stats: discourse-data-explorer/user-stats.sql at queries · SidVal/discourse-data-explorer · GitHub
Custom user fields (their wallet): discourse-data-explorer/displaying-user-custom-fields.sql at queries · SidVal/discourse-data-explorer · GitHub
I can handle the google sheets side.

It would be great to automate this to pull data every month, but other users on the plugin don’t think that is possible. and it is possible by setting up API keys.

You’d need to give the Google Script an API Key. You can restrict that API Key to only be allowed to run data explorer queries, and you can further restrict it to only be able to run specific queries. You are correct, Google Script (or anything external) cannot simply run a data explorer query.

Check out /admin/api/keys/new and the “Granular” scope level. Scroll down to see the data explorer scope. See docs.discourse.org for details on using the API.

@pablodclavijo Its basically combining the two examples, but because they’ve been written in a different structure i found it a bit confusing having never written SQL before.

We want to pull:

  • all users stats which one example already does so no modification should be needed
  • a custom user field, which the second example already does
  • users trust level, which may or may not already be included in the 1st set of stats. If not we’ll need to work that out

A second element has just been added with creating api keys to automate the process which i probably can handle myself, but it’d be great if you have any experience with api’s so i can share any issues i run into.

Let me know what time you’re planning to work on it and i’ll try to be online as well so we can support each other.

I’m sorry @pablodclavijo, i can’t seem to find the links to the two templates we were going to use. Did i send them to you before?

If not i’ll check through my browser history for them.

I’m pretty sure you did. Let me check

1 Like

You can also begin by reading through here and getting an idea of how the plugin works, and whether it needs to be SQL or supports other code.

Cool, I’ll give it a quick read. And about the templates, I can´t find them either

Ok, here’s the basic plugin skeleton they recommend building into (although we can also use any of the examples and code into it)

We need to pull:

  1. User Trust Level
  2. User Stats - Most importantly how many hearts they’ve received, but the more data the better
  3. Custom User Field - to pull their wallet id

All of these should be linked together as one user if possible so we’re just working with one row of the google sheet. That’ll make the latter part of the task easier.

Cool. Do you happen to have the database schema?

1 Like

No, i imagine it’ll be in the Meta.discourse.org thread

I’ll make you an admin of the platform so you can access the entry field where we’re coding. The schema might also be there.

One… quirk is that you can only type one letter at a time into the admin section due to a Docuss bug that i’ve never been troubled about enough to focus on fixing. Its best to type anything you want to type in admin somewhere else and then copy/paste it in.

You should now be an admin. The address is here: Public Happiness

I am.

Ok so to reiterate, if that’s how it’s spelled, the task consists in creating a discourse plugin that:

  • Queries the database and selects a certain set of data from every user
  • Returns said set as a single row
  • Copies it onto a google spreadsheet

Am I missing something else?

1 Like

So the basic idea is that if someone does something on this platform which advances a task, supports the community, or advances the mission, users are instructed to throw them a heart. We’re going to collect those up to show who is contributing most here each month.

So hearts are most important, but grabbing more data means we can get more granular as we need, or change what tokens are rewarded for if hearts are being used incorrectly by most users.

Yes, that’s it. Although the plugin already exists, we just need to put the right commands into it. The three linked examples are pulling the exact data we want so they should contain the… um, calling string for everything we want to put into our commands.

My issue with the task is that the format of the examples is different and it got all messy when i tried working out how to combine it myself, as i’m not a dev.

Once you’re running on that i’ll start looking at our token and begin working out how to mint it to addresses collected into our google sheet. I may need technical support when working out how to add a value to the amount minted, or i may not. I’ll find out when i get there.

After that the final task is playing around with a second google sheet to pull the data we want and manipulate it into the format needed for the token distribution.

Then we’ll have achieved having a live token which is earned by contributing to this platform. A very cool 1st task :sunglasses:

Oh, and you mentioned you had an interest in security so as we build have an eye open to how you’d suggest building the more secure system which replaces this, and you can jot down thoughts while the ideas are still fresh even though we won’t need to build it for a while yet.

Okay, great. I think I have everything I need to start, so I’ll get on it. I know is like 11 pm in Portugal so don’t feel like you have to stick around.

1 Like