How to calculate an Activation Funnel

  • 6 September 2021
  • 3 replies

Userlevel 7
Badge +1

We often speak about activating your community user base. There are many different things that Community Managers focus on to achieve that, e.g. adjusting the gamification strategy or improving the user communication. To get a good overview of the performance of the activation of your community, we usually present an activation funnel during our review meetings.

Often, we then receive the question how such an Activation Funnel can be calculated. It is actually quite easy to achieve, this guide will show you the process of creating an overview step-by-step. Attached to this guide, you can also find an example calcuation.


What you will need

  • Access to the Analytics of your community
  • Goole Analytics access (optional)


What period should be analyzed

We usually analyze the last 6 months of activty. We would not advise to look at a shorter period, simply because there will not be many users that registered in this period that had the chance to reach those higher activity levels. If you want to look at a longer period, e.g. 12 months, then this is not a problem at all.

Whatever you will choose, ideally you create a “running” report out of this, so you can compare Q2 & Q3 against Q1 & Q2 performance and so forth...


Step 1: Generating the data

First, you want to know how many people have visited your community in the period which you want to analyse. Within Google Analytics, you can actually calculate the total amount of new visitors. This is the first metric which we want to collect, as this comes the closest to the amount of users that you possibly can activate.

No Google Analytics? No problem!
Should you not be using Google Analytics, you should be able to get this number from different tools that you use (e.g. Adobe Analytics), or get the number of unique visitors from our Audience Dashboard. Our Dashboard does not specify how many new users have visited, but it will still be a good basis for our Funnel.

After you have captured this number in an Excel Sheet, you want to do an export of the users on your community. Go to [Analytics] - [Export], select [User] and the period you want to analyze. Download the export.


Step 2: Prepare your data

Once you open the export, you will see a lot of columns showing activity data of registered users. When they registered, how much activity a user had & all the profile fields of a user. Of course we are only interested in the activity information. So feel free to delete everything besides the username, the user role (to filter out colleagues), total topics and total comments. We do not track how many answers a user provided or how many likes a user has given / received, as we mainly focus on the content creation in our version of the Funnel.

Now you want to combine the amount of topics & comments that a user has created to a new column. We usually simply add both column values to a new column value, called total_activity. The formula for this is super simple, but in case you don't know how to do that, here is how it should look:

Expand this formula to all rows, and you will get the amount of total activity for each user.


Step 3: Calculate your users activity levels


Generate a pivot table in a new sheet for the user activity tables you have just prepared.

Don't know what a pivot table is?

You can find a guide for MS Excel here, in Google Sheets you simply need to select all data and then click on [Data] - [Pivot table]

In this pivot table, you will need to define what should be displayed in the rows and in the columns. As we are interested in how many users have had which activity level, you should select the total_activity column for rows and the count of usernames for the values.

In case you want to filter out any team members / colleagues, you can also filter out the respective user roles in the [Filters] section.

You will then see the distribution of users amongst different activity levels.


Step 4: Combine everything in a table

Now you just need to sum up all the users that had specific activity levels (we simply copy the list and then auto-sum all the numbers, see attached document for an example). Put these results in a table that looks a little bit like the one below, it will enable you to determine the % of visitors that registered, the share of users that had at least 1 activity etc.

Example of a mature B2C community Activation Funnel


Evaluating the activation performance

Now of course you want to know how your activation funnel can be interpreted, and maybe even more how you are comparing to other communities out there. In the next few days, we will present our new benchmark of 2021 and show you multiple dimensions for benchmarking this data. Until then, the table above should give you a good, general indication of a healthy activation funnel for a B2C community. Please note that B2B funnels look differently, especially in the conversion of registered users.

Here some tips how to improve the conversion for each level, should you have identified areas where you would like to improve:

Funnel level Suggested actions
Registration Add call-to-actions & engaging & stimulating content
1+ activities Improved welcome notification & onboarding content
2+ activities Gamification triggers, Improved notifications, engagement
3+ activities Gamification triggers, Improved notifications, engagement
10+ activities Gamification triggers, Superuser / advocacy program
100+ activities Gamification triggers, Superuser / advocacy program


In case you have questions, challenges or feedback, let us know in the comments. :wink:

3 replies

Userlevel 1

Hello @Julian 

Is the attach still available? I can’t open it.

Userlevel 7
Badge +1

Hmm, that is strange, because I can download and open it without problems. Do you get an error message?

I can also simply download it and email it to you, but it would be good for me to see if there is a problem.

Userlevel 1

Hi @Julian  I am also not able to open the PDF - can you send it?