Project activity queries

Last update: Wed Apr 02 2025 00:00:00 GMT+0000 (Coordinated Universal Time)

Topics:
Reports and Dashboards

CREATED FOR:

User

You can use the queries in this article to create data visualizations similar to those in Enhanced Analytics.

IMPORTANT

Queries will produce similar results to those shown in Enhanced Analytics, but they may not match exactly.

Prerequisites

Before you begin, you must

Establish a connection with your Business Intelligence (BI) tool:
1. Create a reader account or connection for Snowflake
2. Establish a connection to Workfront Data Connect

Once you establish a connection, you can use the queries in this aticle to extract and visualize data.

Shows the number people assigned to the project who logged in on a given day.

WITH userlogins as (
    SELECT
        userid,
        lastlogindate
    FROM (
        SELECT
            userid,
            lastlogindate,
            lag(lastlogindate, 1, '1990-01-01') OVER (PARTITION BY userid ORDER BY begin_effective_timestamp) as previous_login
        FROM users_event
    )
    WHERE lastlogindate != previous_login
)

SELECT
    tds.projectid,
    ads.calendardate,
    count(1)
FROM assignments_daily_history ads
    INNER JOIN tasks_daily_history tds ON ads.taskid = tds.taskid AND tds.calendardate = ads.calendardate
    INNER JOIN userlogins ul ON ads.assignedtoid = ul.userid and TO_DATE(ul.lastlogindate) = ads.calendardate
GROUP BY tds.projectid, ads.calendardate

WITH userlogins as (
    SELECT
        userid,
        lastlogindate
    FROM (
        SELECT
            userid,
            lastlogindate,
            lag(lastlogindate, 1, '1990-01-01') OVER (PARTITION BY userid ORDER BY begin_effective_timestamp) as previous_login
        FROM users_event
    )
    WHERE lastlogindate != previous_login
)

SELECT
    tds.projectid,
    ul.userid,
    ads.calendardate,
    count(1)
FROM assignments_daily_history ads
INNER JOIN tasks_daily_history tds ON ads.taskid = tds.taskid AND tds.calendardate = ads.calendardate
INNER JOIN userlogins ul ON ads.assignedtoid = ul.userid AND TO_DATE(ul.lastlogindate) = ads.calendardate
group by tds.projectid, ul.userid, ads.calendardate

Projects users’ task status change events

Shows the number of people who changed the status of a task for the project on a given day.

WITH task_status_changes as (
    SELECT
        taskid,
        status,
        begin_effective_timestamp
    FROM (
        SELECT
            taskid,
            status,
            begin_effective_timestamp,
            lag(status, 1, 'NOSTATUS') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_status
        FROM tasks_event
        WHERE status != 'CPL'
    )
    WHERE status != previous_status
)

SELECT
    tds.projectid,
    count(tds.status),
    ads.calendardate
FROM assignments_daily_history ads
    INNER JOIN tasks_daily_history tds ON ads.taskid = tds.taskid AND tds.calendardate = ads.calendardate
    INNER JOIN task_status_changes tsc ON tsc.taskid = ads.taskid AND tsc.taskid = tds.taskid and TO_DATE(tsc.begin_effective_timestamp) = tds.calendardate
GROUP BY tds.projectid, ads.calendardate

Projects users’ task status change events: drill down

WITH task_status_changes as (
    SELECT
        taskid,
        status,
        lastupdatedbyid,
        begin_effective_timestamp
    FROM (
        SELECT
            taskid,
            status,
            begin_effective_timestamp,
            lastupdatedbyid,
            lag(status, 1, 'NOSTATUS') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_status
        FROM tasks_event
        WHERE status != 'CPL'
    )
    WHERE status != previous_status
)

SELECT
    tds.projectid,
    tsc.lastupdatedbyid,
    count(tsc.status),
    ads.calendardate
FROM assignments_daily_history ads
    INNER JOIN tasks_daily_history tds ON ads.taskid = tds.taskid AND tds.calendardate = ads.calendardate
    INNER JOIN task_status_changes tsc ON tsc.taskid = ads.taskid AND tsc.taskid = tds.taskid AND TO_DATE(tsc.begin_effective_timestamp) = tds.calendardate
GROUP BY tds.projectid, tsc.lastupdatedbyid, ads.calendardate

Projects users’ task completion events

Show the number of people who completed a task for the project on a given day.

WITH task_status_changes as (
    SELECT
        taskid,
        status,
        begin_effective_timestamp
    FROM (
        SELECT
            taskid,
            status,
            begin_effective_timestamp,
            lag(status, 1, 'NOSTATUS') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_status
        FROM tasks_event
        WHERE status = 'CPL'
    )
    WHERE status != previous_status
)

SELECT
    tds.projectid,
    count(tds.status),
    ads.calendardate
FROM assignments_daily_history ads
    INNER JOIN tasks_daily_history tds ON ads.taskid = tds.taskid and tds.calendardate = ads.calendardate
    INNER JOIN task_status_changes tsc ON tsc.taskid = ads.taskid and tsc.taskid = tds.taskid and TO_DATE(tsc.begin_effective_timestamp) = tds.calendardate
GROUP BY tds.projectid, ads.calendardate

Projects users’ task completion events: drill down

WITH task_status_changes as (
    SELECT
        taskid,
        status,
        lastupdatedbyid,
        begin_effective_timestamp
    FROM (
        SELECT
            taskid,
            status,
            begin_effective_timestamp,
            lastupdatedbyid,
            lag(status, 1, 'NOSTATUS') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_status
        FROM tasks_event
        WHERE status = 'CPL'
    )
    WHERE status != previous_status
)

SELECT
    tds.projectid,
    tsc.lastupdatedbyid,
    count(tsc.status),
    ads.calendardate
FROM assignments_daily_history ads
    INNER JOIN tasks_daily_history tds ON ads.taskid = tds.taskid AND tds.calendardate = ads.calendardate
    INNER JOIN task_status_changes tsc ON tsc.taskid = ads.taskid AND tsc.taskid = tds.taskid AND TO_DATE(tsc.begin_effective_timestamp) = tds.calendardate
GROUP BY tds.projectid, tsc.lastupdatedbyid, ads.calendardate

recommendation-more-help

5f00cc6b-2202-40d6-bcd0-3ee0c2316b43