SFDC Community Cloud Reporting - How to measure new member contributions


(Laurenfaye512) #1

Hey guys! Looking for reporting advice. :slight_smile:

I’d love to be able to measure Newly registered members who make a contribution. This would measure the percentage of new members that make a contribution to the community (like, comment, ask a question).

I’m not finding an out of the box report for this. Does anyone else use a report like this or have something custom set up? I’d love to pick your brain on how you did it. Because as described in this thread (SFDC Community Cloud (Reporting & Dashboards)) Salesforce Reporting is the bane of my existence.

(Richard Millington) #2

Maybe @cocastro can point you in the right direction when he’s back from SXSW?

I need to check out the dashboard metrics of Salesforce. It’s been a while since I looked under the hood of SFDC.

There are a few non-tech ways of doing this. The easiest is to use a sample. List 100 members who joined in the past month and individually check how many made a contribution. If you can run a SQL query, then we’ve used this for discourse:

SELECT (SELECT user_id FROM user_stats WHERE user_id = u.id LIMIT 1) user_id, u.name, e.email, to_char(u.created_at, ‘YYYY-MM-DD HH24MIDD’) joined,
to_char(p.created_at, ‘YYYY-MM-DD HH24MIDD’) post_created, t.title topic_title,
CASE p.post_number
WHEN 1 THEN ‘Topic’
ELSE ‘Reply’
END post_type,
FROM posts p
LEFT JOIN topics t ON p.topic_id = t.id
LEFT JOIN users u ON p.user_id = u.id
LEFT JOIN user_emails e ON u.id = e.user_id
WHERE p.created_at = (
SELECT min(pp.created_at)
FROM posts pp
LEFT JOIN topics tt
ON pp.topic_id = tt.id
WHERE pp.user_id = p.user_id
AND tt.archetype IN (‘regular’, ‘banner’)
AND p.created_at > CURRENT_TIMESTAMP - INTERVAL ‘30 day’
ORDER BY u.name

^ that tends to be the most effective ways of getting the results you want.

Otherwise, I’d ping Salesforce and ask.

(Laurenfaye512) #3

This is super helpful. Thanks for throwing me a life line here, @richard_millington. I really appreciate it!

(Sarah Hawk) #4

It’s important to note that while you could possibly do something similar, that query above will not work for Salesforce.

The table names and the data structure are Discourse specific.

(Richard Millington) #5

yeah, don’t try to use that directly!

I don’t know if salesforce lets you run queries, I think it does. Maybe someone can jump in and ask.