Identifying Power Users

but gut feelings aren't good enough let's look at how to identify them systematically through data the sql queries we'll explore are intentionally generic they'll work with any product that tracks user actions just replace the table and column names with your own, and you'll have a foundation for identifying your power users the query breaks down into three main components that build upon each other let's break down each part 1\ active workspace analysis first, we identify active workspaces in the system create or replace table workspace data as ( select team id, workspace id, workspace url, created at from workspaces where url != '' and url is not null and id not like 'temp %' and status = 'active' order by created at desc ); this creates a clean dataset of active workspaces by filtering out empty or invalid urls excluding temporary workspaces only including active statuses maintaining creation date order 2\ team activity metrics next, we analyze team level engagement create or replace table team activity metrics as ( select team id, count(activity id) as total activities, countif(date(activity created at) > date add(current date(), interval 30 day)) as recent activities, from activity logs where activity type in ('key action 1', 'key action 2') group by team id ); this builds a activity profile for each team by counting total activities tracking recent (30 day) activities focusing on key actions that indicate engagement 3\ power user identification finally, we identify the most active user in each team create or replace table power users as ( with user rankings as ( select team id, user id, count(activity type) as activity count, row number() over ( partition by team id order by count(activity type) desc ) as rank from activity logs join users on users id = activity logs user id join teams on teams id = activity logs team id where users active = true and teams active = true group by team id, user id ) select team id, user id, rank, activity count from user rankings where rank = 1 ); this identifies power users by counting activities per user within each team ranking users by activity level within their team selecting the top user from each team ensuring both users and teams are active the logic behind power user identification the system defines power users based on several key principles activity level raw count of meaningful actions recent activity (last 30 days) consistency of engagement team context activities relative to team size comparison within team rather than globally role in workspace management data quality only considering active users filtering out temporary or test data focusing on meaningful actions