Poll: Export poll results in CSV for poll creator?


#1

Hi,

Would it be possible for the poll results to be exported to a CSV file or so?

Context: Users in my forum created a pub quiz (see here if you’re interested to know more), but to tally the results one of the users is now going through the list of all answers, taking notes of who voted for which option. With 90 answers per question and 20 questions in a quiz, that’s a lot of tallying :slight_smile:

Life would be much easier if there was a way to export the results in some way.

Or, alternatively, is there an easy option for me to use voters.json in a creative way?


(Sam Saffron) #2

You can use data explorer for this, Data Explorer Plugin

Just need to author the right query and you should be good to go! I am sure someone in the community can help if we don’t already have it (search meta)

@rishabhn one thing to think about with the data explorer work is that we probably want to “ship” a bunch of useful queries longer term. Stuff like this is interesting.


#3

Hi @sam,

Thanks for the tip! I am not the most prolific Querybuilder in the world, but this at least got me started, so with some effort I managed to get the results! Not in the prettiest of ways, but hey - works for me!

Thanks again!

(For those interested: grabbed the json straight from the DB, converted that to CSV and processed that in a spreadsheet. Clumsy, I know, but I got there.)


(Alessio Fattorini) #4

Could you please share with us your query?


#5

Hi there!

The new poll implementation changed stuff a bit, so I had to figure out how to get this sorted.

Please note that we’re sort of mis-using the poll-feature to build a quiz. That also means that we’re not really using the poll to identify the correct answer - all we want is an overview of the voting results, so we can export those to CSV and do some further manual processing to see who actually won the quiz.

Some assumptions:

  • I know nothing about SQL. My querie can probably be optimised, rewritten to make eyes bleed less etc. But at least it works. For me. For now. In other words: Here be dragons.
  • A quiz, the way our users built it, is nothing more than a topic with in that topic a post. And in that post the quizmaster creates a number of polls - one poll for each question.
  • Closing a quiz means that the polls close. The quizmaster will then, after the polls close, publish the correct answers in another post. There is therefore no direct link between quiz questions and the correct answer, other than ‘read both posts’.

The workflow to run the quiz and collect the answers therefore is nothing more than:

  1. Create quiz: Create topic, create post in topic with N poll objects, (one per question). Make sure to mark the results as public, and make sure to mark the polls as auto-close on a specific time and date (‘tonight at 22:00’).
  2. Have people answer the questions by voting for the options.
  3. As administrator, run the query to collect the raw answers. Create CSV, send over to quizmaster for further processing
  4. As quizmaster: Remove all incorrect answers from spreadsheet, count the number of times each name of each contestant pops up. The name that comes up the most has the most correct answers and therefore wins the quiz.

Like I said: Fanatic mis-use of a great feature, and soooo many things in here that could make people cringe. However: I love the enthusiasm of the forum members - they came up with this all by themselves, and the quiz is very much enjoyed by all!

So, for the query: One of the asumptions is that there is one post in a topic which contains all the answers. The query requires you to get the topic id and the post number for the post containing the quiz. This can easily be found by clicking on the timestamp of the post in the upper left corner of the post (the one that says ‘Aug 9’ or ‘2d’ or ‘5m’ or so). That link will give you an url with somwhere at the end of the url the number of the topic followed by the post id.
Take your post:
https://meta.discourse.org/t/poll-export-poll-results-in-csv-for-poll-creator/94265/4?u=janjoost

See that bit at the end? The 94265/4? 94265 is the topic id, the 4 is the post number.

Then run the query below in Data Explorer, and get the results back in 4 columns:

  • Poll name
  • Poll answer option
  • User ID for the forum member that chose this answer option
  • Forum name of that member

[ninja-edit]: I forgot to mention that Discourse will translate the user id into the avatar of the user - when you download the results in CSV, you will get the actual user id and not the image.

As said: it ain’t pretty, but it works for me :slight_smile:

Good luck!

-- [params]
-- int :topic_id
-- int :post_number

SELECT polls.name AS "Poll name", poll_options.html AS "Answer", poll_votes.user_id AS "User ID", users.username AS "Username"
	FROM poll_options
	INNER JOIN poll_votes ON poll_options.id=poll_votes.poll_option_id
	INNER JOIN polls ON polls.id=poll_votes.poll_id
	INNER JOIN users ON users.id=poll_votes.user_id
	WHERE poll_options.id IN (
		SELECT id FROM poll_options WHERE poll_options.poll_id IN (
			SELECT id FROM polls WHERE post_id IN (
				SELECT id FROM posts WHERE topic_id=:topic_id AND post_number=:post_number ) 
			)
		)
	ORDER BY polls.name, html

What cool data explorer queries have you come up with?
(Per Torstensson) #6

A guy on my site just made a topic with multiple polls and with not showing who had voted, we were in quite a pickle to announce any winner. Your script worked perfectly and totally saved the day :slight_smile:)

BIG thank you for sharing this @JanJoost!