Monthly User Logon Analysis

Whether you're a product manager, data analyst, or system engineer, at some point, you will need to generate and analyze metrics from large data sets.

One common task is analyzing user login data to determine usage patterns.

I encountered a challenge this week generating a particular metric (monthly logins per user). In this post, I will uncover the issue and show what changes I made to work around it.

We will delve into some foundational Excel functions: UNIQUE, COUNTIFS, and MONTH. These invaluable tools can help you quickly transform raw data into meaningful metrics, enabling better decision-making.

Lastly, I will show how the metric links to fundamental product management concepts. Giving you a view on how to think about levering data analysis in a day-to-day product context.

Plus, don't miss the link to the new GetFormula site at the bottom of the email.

The Challenge

I was putting together a report and needed to quickly calculate the number of unique user logins for the year's first three months. The counts were needed to validate usage patterns and support product strategy decisions.

So to get the data, I needed to:

1. Identify all the unique users.

2. Calculate user logins per month.

As an example, I have laid out the sample dataset below.

• Column B: Contains the range of logon dates (dd-mm-yyyy)

• Column C: Contains the range of user names

1) Identifying all the unique users

Identifying the unique users is pretty straightforward using the UNIQUE function.

The formula is: =UNIQUE(array) / =UNIQUE(C2:C6)

The output generated is in collum F2 to F4.

2) Calculate user logins per month

Next, I needed to count the number of times each user logged in per month.

Using the COUNTIFS and MONTH functions, I used the following formula structure:

=COUNTIFS($B$2:$B$6, $F2, MONTH($B$2:$B$6), 1)

The formula was supposed to work as follows:

• $B$2:$B$6 is the range of all the Usernames.

• $F2 is the cell that contains the specific Username to match against.

• MONTH($B$2:$B$6) is deriving the numerical month from the array range

• # (e.g.; 1,2,3) is the numerical month to match against.

When running the formula, you get the following error which is miss leading:

It turned out that the COUNTIFS function in Excel cannot handle array operations directly.

So the array function I had entered MONTH($B$2:$B$6) was causing the error.

To work around the issue, I created a helper column to identify the month from each login date from column B.

To do this, I used the following function =MONTH(D2) to extract the month from the Login column and placed it in column D.

The formula was updated as follows:

=COUNTIFS($B$2:$B$6, $F2, $D$2:$D$6, 1)

Remember: To change the month number—2 for February, 3 for March for the month you are calculating. Depending on the size of your dataset, you could also explore creating a helper row for the month collums so you could reference the cells rather than having a fixed value.

Update (May 27): Alternatively, you could use the TEXT function to convert the numeric date value to text.

=TEXT(B2, “mmm”)

Analyzing the output

After successfully calculating the monthly login frequency, I was able to use that metric to provide valuable insights and support a wider product team discussion.

1) Understand user behavior: Login data helps identify when and how often users engage with your product, providing valuable insights into user habits, preferences, and overall usage times.

2) Measuring User Engagement and Retention: Regular logins serve as a strong indicator of user engagement and retention. A high frequency of logins suggests that users are finding value in the product, while a decrease may flag areas needing attention.

3) Informing Product Development and Lifecycle Strategies: The derived data can directly inform product lifecycle management decisions. This ranges from maintaining the status quo if the product performs well, updating or iterating on the product when liking engagement with user feedback or observed usage trends, or even sunsetting/retiring the product if consistently low engagement is observed.

Having this data available allowed the discussion within the team to be more objective, customer-focused, and data-driven, allowing the team overall to make more informed decisions to meet our user’s needs.

Final Thoughts

Excel's COUNTIFS, UNIQUE, and MONTH functions are not just tools; they're powerful allies in effectively manipulating and analyzing data quickly.

By leveraging these and other functions, you can unlock valuable insights to support your decision-making process.

It's common to encounter errors when applying Excel formulas. Don't let them deter you. Sometimes, the solution is as simple as switching from “,” to “;” depending on your localized settings. Other times, as in our example, it's about understanding and working with formula limitations. So, when you are getting an error message, take a moment, breathe, and strategize your next move. You've got this!

In the age of AI, resources for troubleshooting formulas are becoming even more sophisticated. With tools like ChatGPT, Excel Formula Bot, and the upcoming Microsoft Copilot for Excel, your Excel struggles may soon be a thing of the past. Stay tuned for future posts on these exciting developments!

So, keep exploring, learning, and enhancing your Excel skills!

 Happy spreadsheeting!📊

Thanks for reading!

-Reede

As an early ✨ subscriber ✨, I would be grateful for any feedback you have on the newsletter or the new site.

Please feel free to use the feedback form below. ⬇️