SQL For Weekday Product Usage Analysis - Issue 166

How to get the frequency of user engagement in SQL: day of the week analysis

Welcome to the Data Analysis Journal, a weekly data science and analytics newsletter.

---

Today’s topic is a SQL solution for one of my favorite quick data explorations - weekday usage analysis (also known as DOW analysis - day of the week).

I often run it as the first query when working on a new dataset or during my onboarding - breaking user actions down by the days of the week. 

It’s my favorite because it’s simple to pull, literally takes only a few minutes to write the query, and the data is easy to interpret and present. At the same time, it opens a universe of insight into the business type, lifecycle, and user behavior. It helps to answer questions like: 

• Does the business/product have weekly cyclicality? 

• If yes, what does it look like? When does the largest volume of activity happen? Is it a complete pattern? 

• What impact do holidays and weekends create? 

• How does user acquisition work? What % of paid traffic drives signups? 

Weekday usage analysis provides a segue into exploring user behavior and activity.

Weekly cyclicality often speaks for the type of business or product offering - do users use the product during weekdays or weekends? Does it change with the beginning/end of the month? When you break users down into profiles or segments, you might notice that some personas use the product the most every Monday or Friday. or, for example, that users who are likely to churn tend to only use the product on weekends. These insights can help optimize product offerings or even affect the A/B test.

Below, I’ll share the SQL code and my approach to exploring the activity pattern.