Here’s the beginning of a data-explorer query. My intent is to do something like percent posts via email per user for each of the last 4 weeks, but my SQL-fu is a bit short of that query just rolling off my fingers.
SELECT user_id,
count(1) as post_count
FROM posts
WHERE via_email=true
GROUP BY user_id
ORDER by post_count desc
select username, sum(case when (posts.via_email='t') then 100 else 0 end)/count(posts.id) as perc_by_mail
from posts
left join users on users.id = posts.user_id
where posts.created_at >= now() - interval '4 weeks'
group by username
That sounds very short sighted. Having more content will bring you a multitude of visitors.
I guess that’s why I’m asking! With this particular community, it seems that people who reply via email (as opposed to on the site) are those most disenchanted from the move from Ning.
I have never made a post via an email, so I’m admittedly clueless. But are there any situations where someone might be able to email but not have a browser? eg. at home I have my desktop browser, but on a long commute I can only send emails. Not a matter of preference, but a matter of ability.
In other words, not that I am participating only with email, but that I participate both ways.
It’s quite rare for any site to have a ton of email-only participation. 25 years later, people are pretty darn comfortable with the concept of a web browser
The reality is that email replies capture a small percent of the time people are “on the go” and want to reply but all they have access to is email. It is a convenience feature.
If you turn it off, you turn off convenience – but it’s not essential to survival in my experience. I’d still recommend it though.
In my community - yes. Our topics revolve around aircraft construction and flying, so a post will often be seen by a member while they’re at the hangar, away from the desktop browser.
Email is also pushed, so they are engaged on a daily basis. There are those who prefer email to stay engaged even though most have a smart phone and the Discourse mobile interface is good.
select
username,
sum(case when (posts.via_email='t') then 100 else 0 end)/count(posts.id) as perc_by_mail,
sum(case when (posts.via_email='t') then 1 else 0 end) cnt_by_mail,
sum(case when (posts.via_email<> 't') then 100 else 0 end)/count(posts.id) as perc_not_by_mail,
sum(case when (posts.via_email<> 't') then 1 else 0 end) cnt_not_by_mail
from posts
left join users on users.id = posts.user_id
where posts.created_at >= now() - interval '4 weeks'
group by username
All:
select
sum(case when (posts.via_email='t') then 100 else 0 end)/count(posts.id) as perc_by_mail,
sum(case when (posts.via_email='t') then 1 else 0 end) cnt_by_mail,
sum(case when (posts.via_email<> 't') then 100 else 0 end)/count(posts.id) as perc_not_by_mail,
sum(case when (posts.via_email<> 't') then 1 else 0 end) cnt_not_by_mail
from posts
left join users on users.id = posts.user_id
where posts.created_at >= now() - interval '4 weeks'
We have some long standing (and long suffering!) users who swear by reply-by-email which is why I would never turn it off. I doubt it makes up a large percentage though.
I’d be curious to know what the stats are, how does one run the SQL query on the Discourse database? Thanks!
