プロジェクトアクティビティクエリ

Last update: Fri Apr 25 2025 00:00:00 GMT+0000 (Coordinated Universal Time)

トピック：

作成対象：

ユーザー

この記事のクエリを使用して、Enhanced Analytics と同様のデータビジュアライゼーションを作成できます。

IMPORTANT

クエリは、Enhanced Analytics で表示される結果と同様の結果を生成しますが、完全には一致しない場合があります。

前提条件

開始する前に、

Business Intelligence（BI）ツールとの接続を確立します。
1. Snowflakeのリーダーアカウントまたは接続を作成する
2. Workfront Data Connect への接続の確立

接続を確立したら、この記事のクエリを使用してデータを抽出および視覚化できます。

プロジェクトユーザーのログインイベント

特定の日にログインした、プロジェクトに割り当てられた担当者の数を表示します。

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

プロジェクトユーザーのタスクステータス変更イベント

特定の日にプロジェクトのタスクのステータスを変更したユーザーの数を表示します。

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

プロジェクト・ユーザーのタスク・ステータス変更イベント：ドリルダウン

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

プロジェクトユーザーのタスク完了イベント

特定の日にプロジェクトのタスクを完了したユーザーの数を表示します。

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

プロジェクト・ユーザーのタスク完了イベント：ドリルダウン

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