Skip to main content
MindTouch Success Center

Create performance leaderboards with report CSVs

This tutorial demonstrates how to create MindTouch user leaderboards through your site history report.

 

Why should I use a leaderboard?


There are many ways to visualize your MindTouch event data. A leaderboard is a quick way for you to see how your users are ranking in a group in order to increase participation or skill level for an activity.  This encourages a healthy competition among your users while at the same time letting you identify those that might need additional help.

How to create leaderboards with your site history report


In this tutorial, we will use the site history, usersgroups, and user2group reports to generate leaderboards displaying the top 10 users within a given group. For this example, we will create leaderboards tracking users within the group, Customer Success, against 3 key metrics: pages created, pages updated, and drafts published. The process is relatively similar across many standard platforms (Excel, Numbers, Google Sheets, etc.). The example below employs the following functions in Excel to modify and visualize data:

  • VLOOKUP() to merge multiple tables
  • COUNTIFS() to count and sum data respectively given specified criteria
  • IF()to perform logical tests
  • Charts to visualize your data in an easy and intuitive way

At the end of this exercise, you should have a leaderboard that looks something like the following:

Prerequisites


Before reviewing this article, make sure you have the following:

  • Administrative permissions.
  • Spreadsheet software: this tutorial employs Microsoft Excel, but other applications like Google Sheets, Numbers, or a number of data visualization tools should work as well.

Create a performance leaderboard


Take the following steps to create your first performance leaderboard.

Step 1: Download reports

If you aren't familiar with the locations of these reports, follow these links:


​​​​​If you do not see an option to download these reports, please contact MindTouch Support.

Step 2: Combine reports into a single CSV

Please review this document on how to merge multiple reports within Excel.

  1. Merge the four reports into a single Excel workbook.
  2. Rename the site history report to a more concise sitehistory.csv (concise names will be easier to manage when reviewing functions).
  3. Once you've merged your reports into a single workbook, your tab structure should look like the following screenshot.


Step 3: Label your lists and data tables (optional)

Please review this document on how to add labels to lists and tables in Excel.

This isn't a necessary step, but it will help to define more intuitive functions going forward.

Label the following lists:

  • SITEHISTORY_TYPE 
  • SITEHISTORY_USERID 
  • GROUPS_GROUPID 
  • USER2GROUP_GROUPID 
  • USER2GROUP_USERID 
  • USERS_USERID
  • USERS_USERNAME

To label data tables, highlight the entire data table and label it as you would a list. Label the following data tables:

  • GROUPS
  • USER2GROUP

Step 4: Combine all sheets into one group based sheet

Use VLOOKUP() and Data Validation to merge data from the four reports into a single sheet, titled Group Selector:

  1. Create a new sheet and title it "Group Selector".
  2. From the sitehistory.csv sheet, copy column E (USER ID) and paste into column A within the Group Selector sheet.

  1. In cell B1, enter the header IN GROUP.

Step 5: Create a group selector drop-down

To enable the easy designation of the desired group, we will create a GROUP ID drop-down list.  Follow these steps to create your drop-down.

  1. Create a new sheet and title it Leaderboard.
  2. Title cell A1 "Group =".
  3. Click on cell B1.
  4. Select Validate from the Data menu.

  1. Within the Data Validation pop up window:
  • Select List from the Allow options drop-down list.
  • Under the Source section, select the full list of GROUP IDs from the groups.csv sheet.

  1. You'll now see the GROUP ID drop-down appear in cell B1.

  1. To pull the GROUP NAME for the specified GROUP ID, click on cell C1, and add the following function:
=VLOOKUP(B1,GROUPS,2,FALSE())

Read here for more information on VLOOKUP() functions.

  1. Now, when a GROUP ID is selected, the associated GROUP NAME will also populate.

Step 6: Track users within your desired group

  1. From the groups.csv sheet, find the GROUP ID associated with your desired group. In this example, for GROUP NAME, Engineering, the associated GROUP ID is 9.

  1. From the Leaderboard sheet select GROUP ID 9 from the GROUP ID drop-down. GROUP NAME Engineeringalso populates.

  1. From the Group Selector sheet, enter the following formula into cell B.
=IF(COUNTIFS(USER2GROUP_USERID,'Group Selector'!A2,USER2GROUP_GROUPID,'Leaderboard'!$B$1),'Leaderboard'!$B$1,"-")

 For more information about these functions, see the COUNTIFS and IF function breakdowns below.


 

  1. To populate the entire IN GROUP column, highlight B2, and drag it down to the bottom of the list (or double click the square at the bottom right-hand side of the highlighted selection.

  1. Label the IN GROUP list GROUPSELECTOR_INGROUP using the process described in Step 3.

COUNTIFS function breakdown

The COUNTIFS()function allows you to count cells within an array given specific criteria:

COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2,...)

The table below explains the syntax of our COUNTIFS() function in:

=IF(COUNTIFS(USER2GROUP_USERID,'Group Selector'!A2,USER2GROUP_GROUPID,'Leaderboard'!$B$1),'Leaderboard'!$B$1,"-")

 

Reference Purpose In our example Explanation
criteria_range An array to test criteria against.

criteria_range1 is set to USER2GROUP_USERID

criteria_range2 is set to USER2GROUP_GROUPID

USER2GROUP_USERID is the full range of users, found in the user2group sheet.

USER2GROUP_GROUPID is the full range of groups, found in the user2group sheet.

criteria The criteria used to determine whether a cell should be counted within the specified array.

criteria1 is set to 'Group Selector'!B2 

criteria2 is set to 'Leaderboard'!$B$1

'Group Selector'!B2 matches the USER ID in cell B2 to each value within the array:

USER2GROUP_USERID 

'Leaderboard'!$B$1 matches the specified GROUP ID in cell D1 to each value within the array:

USER2GROUP_GROUPID

 

IF function breakdown

The function IF() performs a logical test, and return one value if the condition is true and another value if the condition is false:

IF(logical_test, [value_if_true], [value_if_false])

The table below explains the syntax of our IF() function in:

=IF(COUNTIFS(USER2GROUP_USERID,'Group Selector'!A2,USER2GROUP_GROUPID,'Leaderboard'!$B$1),'Leaderboard'!$B$1,"-")

 

Reference Purpose In our example Explanation
logical_test A logical test. logical_test is the COUNTIFS() function In this example we are testing whether the COUNTIFS()function returns a user within the specified group.
[value_if_true] The output value if the COUNTIFS() input passes the logical test defined in the IF() function. [value_if_true] is set to 'Leaderboard'!$B$1  'Leaderboard'!$B$1 represents the specified GROUP ID
[value_if_false] The output value if the COUNTIFS() input fails the logical test defined in the IF() function. [value_if_false] is set to "-"  "-"  demonstrates that the user is not in the desired group.

Step 7: Create a list of all activity types

Now that you are able to track users by group, you can take the necessary steps to track user activity by a given type.  In this step, we will create a list of all possible TYPE options.

  1. Create a new sheet and title it "Type List."
  2. From the sitehistory.csv sheet, copy column D (TYPE) and paste into column A within the Type List sheet.
  3. With the list highlighted, choose Remove Duplicates from the Data menu.

  1. The resulting list includes the full TYPE list.
  2. Label the TYPE list, TYPE_LIST, using the process described in Step 3.

Step 8: Track user activity

Now that we can access a full TYPE list, let's track user activity against a given TYPE

  1. Create a new sheet, and title it Track User Activity.
  2. Copy the entire User ID list from the users.csv sheet and past it to column B of the Track User Activity sheet.

In column C , we are going to track users within the specified group against a given activity type.  To do so:

  1. Title cell C1 "Type =".
  2. Click on cell D1.
  3. Choose Validate from the Data menu.

  1. Within the Data Validation pop up window:
  • Select List from the Allow options drop-down list.
  • Under the Source section, select the full list of types from the Type List sheet.

  1. You'll now see the Type drop-down appear in cell D1.

  1. From the Track User Activity sheet, enter the following formula into cell C2
=COUNTIFS(SITEHISTORY_USERID,'Track User Activity'!B2,GROUPSELECTOR_INGROUP,'Leaderboard'!$B$1,SITEHISTORY_TYPE,'Track User Activity'!$D$1)

  1. To populate the entire Type = column, highlight C2, and drag it down to the bottom of the list (or double click the square at the bottom right-hand side of the highlighted selection.

Step 9: Rank your top 10 users

Now that we can easily track users within a group by the desired activity type, let's start to create a leaderboard for the top 10 users. First, we'll need to rank users within a specified group by the number of times they have completed a specified activity type:

  1. Within the Track User Activity sheet, title cell A1 "RANK."
  2. Enter the following formula into cell A2:  
=IF(C2=0,0,RANK(C2,$C$2:$C$1925)+COUNTIF($C$2:C2,C2)-1)

This formula assigns a rank to all users, automatically assigning a 0 to users that haven't completed the specified activity type.

  1. To populate the entire RANK column, highlight A2, and drag it down to the bottom of the list (or double click the square at the bottom right-hand side of the highlighted selection.

Step 10: Build a table for your top 10 users

  1. Give the following titles to the following cells:
    1. G1: "RANK"
    2. H1: "USER ID"
    3. I1: "USER NAME"
  2. Enter the following formula into cell J1:
    =D1
    Column J will list the number of times the specified activity type is completed for a given user.  The title of that column will be the activity type.
     
  3. Under the RANK header, number the fields G2 through G11 1 through 10.
  4. In cell H2, under the header USER ID, add the following:
    =VLOOKUP(G2,$A$1:$C$1925,2,FALSE())
    
    This function returns the user ID associated with the referenced rank.
     
  5. In cell I2, under the header USER NAME, add the following:
    =IFERROR(VLOOKUP(H2,users.csv!$A$1:$I$1925,4,FALSE()),"-")
    
    This function returns the user name associated with the referenced user ID.
     
  6. In cell J2, under the activity type, add the following:
    =IFERROR(VLOOKUP(H2,$B$2:$C$1925,2,FALSE()),0)
    
    This function returns the total activities of the specified type associated with the referenced user ID.
     
  7. To populate the entire top 10 list, highlight H2-J2 and drag them down to the bottom of the list (or double click the square at the bottom right-hand side of the highlighted selection.

Step 11: Create a chart for your leaderboard                                       

Now that you've created your top 10 list, the final step is to add a chart to your Leaderboard sheet.

  1. Select the User Name and activity type columns within the top 10 list of your of the Track User Activity sheet.

  1. Choose a chart type from the Charts menu (in this example I've selected the Column option).

  1. A chart is now created for your leaderboard.

  1. Cut and paste this chart into your leaderboard.

Step 12: Add new charts to your leaderboard

To add additional charts to your leaderboard, follow these steps:

  1. Right-click the Track User Activity sheet tab.
  2. Select Move or Copy.
  3. Check the box labeled, Create a copy.
  4. Click OK.
  5. Click on the Type drop-down (in cell D1) to select an alternative activity type.
  6. Follow the process of creating a leaderboard in Step 11.

Congratulations, you have successfully created your first leaderboard!

  • Was this article helpful?