User Activity and Performance Report
Generates a user activity and performance report by integrating with ClickUp. It retrieves information such as the total number of tasks, tasks completed, tasks overdue, total time spent, task priority, task status, and task count by priority and status for each user. The results are ordered by user ID.
- Grain
- See the notes
- Built from
- 4Raw tables
- 1Core
- 1Analytics
- Reads from ClickUp
- tasktask_assigneestimeuser
- Ships as
- Weld and dbt SQL, open source on GitHub
1-- core.clickup.user_activity_and_performance_report
2-- User Activity and Performance Report. Generates a user activity and performance
3-- report by integrating with ClickUp. It retrieves information such as the total
4-- number of tasks, tasks completed, tasks overdue, total time spent, task priority,
5-- task status, and task count by priority and status for each user. The results are
6-- ordered by user ID.
7--
8-- From the weld.app template library. Not reconciled against ClickUp;
9-- check the output against the source before relying on it.
10
11with
12 UserTasks as (
13 select
14 ta.id as user_id
15 , ta.username
16 , count(distinct t.id) as total_tasks
17 , count(
18 distinct case
19 when t.date_done is not null then t.id
20 else null
21 end
22 ) as tasks_completed
23 , count(
24 distinct case
25 when t.date_done is null
26 and cast(timestamp_millis(cast(t.due_date as int)) as date) < current_date then t.id
27 else null
28 end
29 ) as tasks_overdue
30 from
31 {{raw.clickup.task_assignees}}ta
32 join {{raw.clickup.task}} t on ta.task_id = t.id
33 group by
34 ta.id
35 , ta.username
36 )
37 , UserTime as (
38 select
39 user_id
40 , sum(duration) as total_time_spentThis model and the 1 it depends on. Deploy them together; refs like {{staging.…}} resolve to the matching folders in Weld, and the dbt view uses source() and ref().
How it counts. And where it stops.
The SQL template "User Activity and Performance Report" is designed to gather data from the ClickUp integration. It provides insights into user activity and performance based on various metrics. The SQL code consists of several common table expressions (CTEs) that extract relevant information from the ClickUp database. The "UserTasks" CTE calculates the total number of tasks, tasks completed, and tasks overdue for each user. The "UserTime" CTE calculates the total time spent by each user. The "UserTaskDetails" CTE provides additional details such as task priority, status, and task count for each user. The final SELECT statement combines the data from the ClickUp "user" table with the CTEs to generate a comprehensive report. It includes user details such as user ID, username, email, color, initials, and last active date. Additionally, it includes metrics like total tasks, tasks completed, tasks overdue, total time spent, task priority, status, and task count by priority and status. This SQL template can be useful for monitoring user activity and performance in ClickUp. It allows you to identify the number of tasks assigned to each user, their completion rate, and any overdue tasks. Furthermore, it provides insights into the total time spent by each user and the distribution of tasks based on priority and status. This information can help track user productivity, identify bottlenecks, and optimize task management in ClickUp.
Raw tables it reads
- raw.clickup.task
- raw.clickup.task_assignees
- raw.clickup.time
- raw.clickup.user
Put ClickUp in front
of your agents.
Connect ClickUp, deploy the models, and ask questions in Claude, Cursor or ChatGPT. Free for 14 days, no credit card.