Community Retention and Gamification

Jocelyn · 2025-02-12T21:39:31+00:00

There was an error rendering this rich post.

I am trying to find percentage of returning users to our community? Is there a way we can calculate that in HL Vanilla?

We would like to introduce gamification program in an effort to increase engagement and improve retention. I was wondering how can we calculate the success of the program. I would appreciate any advice from this community.

Find more posts tagged with

Advocacy

Analytics & Reporting

Ranks

Grow Engagement

Comments

Shauna

Hey @Jocelyn - I'm curious how you are thinking about 'returning' - does this mean

a user who has come back within a certain time frame since account creation
- i.e., I created an account on January 1st 2024, and then returned within a certain time interval since the account was created (i.e., within 60 days of Jan 1 2024 aka March 1 2024)
a user who has logged in at least once in a certain time frame regardless of account creation time (but it wasn't their very first login)
- i.e., I created an account on January 1st 2024, and have logged into the community at least once in a certain time interval from today (i.e. within 60 days of Feb 12 2025)

Or maybe something else?

I have a feeling this will. be an API/Excel exercise but want to make sure I understand the ask before going down a rabbit hole :)

Jocelyn

@Shauna I would define returning users as someone who gains some value from our community and comes back again within a certain time frame. Typically we observe that our users ask a question and never come back. I would like to encourage a behavior where our users come back to either help others, participate in discussions or even come again to ask more questions.

Jocelyn

@Shauna Any insights on this?

stefanieb

Hey @Jocelyn

This is not exactly what you're after but- in our Community we track which members are in our top 5 (non-staff) posters each month. At the end of the year we have awards for this and other behaviours like group posts, learning center contributions etc. that we recognize with in-platform awards and also real world rewards through our Advocacy Platform and Team.

I shared a little about our approach here, so I thought it might be handy to take a look.
Happy to connect and chat about which APIs/analytics we use if it helps

https://success.vanillaforums.com/discussion/comment/9999#Comment_9999

Shauna

Ok so I'm going to look to answer these questions:

How many users log in once and never come back
How many users come back within x period

To answer these, we're going to need to play in excel.

Get the User LIst

The first thing we need is an export of users as well as their first and last visit (you could also pull additional info like role, rank, etc if you wanted to glean additional like if a user is a super user is more likely to come back more frequently etc, but I'm going to stick to the basic questions above for now.

Of note, this example uses fake users and test data from my demo site.

Head on over to your dashboard then moderation then manage users .

Make sure that you have first visit and last visit displayed in the column display settings:

You can display other columns and get them in the export if you want, but these are the essentials.

Next, click the three dot menu at the top right and hit export user data

This will generate a .csv that you can open in excel or google sheets that will look something like this

Working with exported user data in Excel

Note: I am using this for illustrative purposes to help find an answer here - if there are excel ninjas in the house that can simplify any of these steps we want to hear your protips in the comments! I am also using excel for mac so this might be different in different versions

I like to delete any user data that's not essential for security reasons (and just to clean the spreadsheet up!) and size the columns:

Dates here use an ISO 8601 timestamp which is great for preciseness, but harder for excel to understand (at least in my experience)

To convert these to basic dates, create a column next to each date column, then highlight the first date column and click 'text to column' under the data option:

Choose delimited

Choose other and T

choose date (what format you prefer) and then finish

now you will have the dates alone in the first column and the times in the second column which we dont care about and can delete

Repeat this process with the last visit column

Make sure both are set to date format:

Now we just need to calculate the difference in days between the two using a simple subtract function (if you get a funky date you may have set this to date as well accidentally, make sure this is set to general)

apply this formula to this column all the way down by highlighting the column all the way down and doing copy paste

you should end up with something like

So now we've got the data how can we answer our questions

How many users log in once and never come back
How many users come back within x period

Ok, so now we need a formula to figure out the differences between the two columns - I am not an excel expert by any means, and used to have to use google searches and blunt force to figure out formulas like this, but use the ai shortcut these days.

Same day (i.e. didnt come back) =COUNTIF(C:C, 0)

1-30 days =COUNTIFS(C:C, ">=1", C:C, "<=30")

31-180 days =COUNTIFS(C:C, ">31", C:C, "<=180")

180 days plus =COUNTIFS(C:C, ">181")

You could chose any parameters that you want however and even compare with different roles etc if you feel like doing an excel rabbit hole deep dive

Finally, I just calculated the percents and voila:

Well that was a bit of a novel, but it shows the power of exporting data and playing with it in tools like excel with a little help from our friends at chatgpt!

Hope this helps!

Component react.asset.createComment had an error.

Permission Problem You need the session.valid permission to do that.

Quick Links

Upcoming Events

HLV Connect: Open Session
⭐️ Got something cool to share? Have a victory in your community and want to show off your accomplishments with fellow community builders? 🙋 Got questions? Bring your community, tactical and random questions and get answers from peers and Vanilla experts! 🗓 Join Vanilla's @AveryD and @AndrewD and a group of your peers for…
Spring Product Roadmap Webinar 2025 🌱 🌷 🐣
Join us for an exclusive webinar where we’ll showcase our recent feature updates, as well as unveil some of the exciting items coming soon on our product roadmap! Discover how our upcoming features and enhancements will empower your community, improve engagement, and streamline your workflows. This is your chance to gain…
HLV Connect: Open Session
⭐️ Got something cool to share? Have a victory in your community and want to show off your accomplishments with fellow community builders? 🙋 Got questions? Bring your community, tactical and random questions and get answers from peers and Vanilla experts! 🗓 Join me and @paddykelly, and a group of your peers for our next…