In this series of articles, you'll learn about how to analyze the Content Interaction Report that is available for download for administrators of SmartUp communities. More specifically, you will learn about:
- Calculating user adoption and finding out which users have not logged in
- Calculating the total number of content read and the content completion rate
- Analyzing content interaction by Hour of Day and Day of Week
In this first article, you'll learn how to calculate user adoption and also how to determine which of your users have not consumed any content. But first, learn about our report types and how to download reports here and how to import and format data here.
To begin the content interaction analysis, you will need to download and import the Content Interaction Report and the User Details Report into a Microsoft Excel file.
Let's start by creating a column called Hour of Day. Then, enter the formula =HOUR(M2), which gives you the hour shown in the cell you reference. In this case, we are trying to find the hour in the converted timestamp. Repeat the calculation for the rest of the column.
The last column to add before beginning your analysis is the Day of Week column, this gives you the day of the week for the cell you reference. The formula to use for the calculation is =WEEKDAY(M2,2).
Once you have your data set up, you are ready to begin calculating user adoption and finding out who has not logged in.
1. Calculating User Adoption
First, copy the content interaction data you have into a new sheet. Next, you will need to find out how many unique users have interacted with content in your community by removing duplicates of users. To remove duplicates, go to the Data tab and sub-section in Data Tools in Excel and click on Remove Duplicates.
Since User ID is unique for each user on the SmartUp platform, select this field when removing duplicates. The remaining data indicates which users have logged in and consumed content. Take note of the number of unique users, as this will be used to calculate user adoption.
Click on the user details report sheet, which shows you the list of users in your community. For the purpose of this article, you will only need the first 5 columns of this sheet. Feel free to hide the other columns.
To calculate the user adoption rate, take the number of unique users from the content interaction data copied earlier and divide it by the total number of users listed in the user details report sheet.
In our example data, we have 10 unique users who consumed data and a total of 71 users in the community, giving us a user adoption rate of 14.08%
2. Finding Out Which Users Have Not Logged In
To find out which users have not logged in, begin by copying over the unique user data (after removing User ID duplicates from content interaction data) in Step 1 above. Only the first 5 columns need to be copied over. To differentiate the pasted data from the original sheet data, you may change the text colour.
Next, use Conditional Formatting to highlight duplicate User IDs in the data. The function can be found in the Home tab.
Select column A (User ID), click on Conditional Formatting, Highlight Cells Rules, Duplicate Values, then OK. Next, sort the data by Cell Colour, with No Cell Colour On Bottom.
You will see that duplicated User IDs will lie on top of your data. Any user with the User ID field not highlighted has not logged in to consume content for the chosen period.
For additional analysis, you can filter data out by whether user accounts are currently active or deactivated. Active accounts are marked with a t in the Enabled column while deactivated accounts will be marked with an f.
To find out out how to calculate total content read and content completion rate, check out Part 2 here.
To find out how to analyze content interaction by the hour of day and day of the week, check out Part 3 here.