システム開発における業務分析

前提

本記事では弊社で業務分析の内容を記載します。
Redmineをお使いの方は、redashを準備頂ければSQLを記載しますので、そのまま同じ分析を行う事が出来るかと思います。
但し、マスターの値をそのまま記載しているので、そちらは変更頂く必要があります。

• 全てのタスクがRedmineで管理されている
  • 予定工数と実績工数が正しく登録されている
• redashがインストールされている
  • Dockerでインストール出来る為、VPSのレンタルサーバーに数時間でインストールできます
• 上記の前提で業務に滞りがないか、改善点がないかを分析しています

分析内容

作業実績の記録が正しいか

gitのコミットメッセージからRedmineに自動的に実績時間が登録されるようになっています。
その情報から人・日のマトリックス表を作っています。
こちらの表からスタッフの作業状況を判断しています。
また、給料の査定基準に後述する予定工数と実績工数の対比を用いているため、実績工数が異常に少なく報告していないかもチェックしています。

以下、SQLです。

select hours, to_char(spent_on,'MM/DD') as "spent_on", users.firstname
from time_entries
         inner join users on time_entries.user_id = users.id
         inner join groups_users on users.id = groups_users.user_id
where spent_on > current_timestamp + '-30 day'
order by spent_on desc
;

バグの発見状況

テスターの能力査定、全体的なバグの発生件数の確認に使用しています。

以下、SQLです。

select users.firstname, to_char(issues.created_on, 'MM/DD') as "作成日"
from issues
         inner join users on users.id = issues.author_id
inner join groups_users on groups_users.user_id = issues.author_id

where issues.tracker_id = 1
  and issues.created_on > current_timestamp + '-30 day'
and groups_users.group_id in(36,215)
order by issues.created_on desc

• tracker_id=1はバグというトラッカー
• groups_users.group_id=36,215は分析したいユーザーグループ

チケットの消化件数

人とその日に消化した件数のマトリックス表です。
スタッフの状況を見ています。(昨日は無双状態だったね〜とか、最近消化してないけど、トラブってる？とかのネタにしています)

select users.firstname, to_char(issues.updated_on, 'MM/DD') as "更新日"
from issues
         inner join users on users.id = issues.assigned_to_id
inner join groups_users on groups_users.user_id = issues.assigned_to_id

where issues.status_id in (3,5)
  and issues.updated_on > current_timestamp + '-30 day'
 and groups_users.group_id in(36,215)
order by issues.updated_on desc

• status_id=3,5は消化と判断出来るステータス
• groups_users.group_id=36,215は分析したいユーザーグループ

月次版

上記の月次版です。

select users.firstname, to_char(issues.updated_on, 'YY/MM') as "更新日", count(issues.id)
from issues
         inner join users on users.id = issues.assigned_to_id
         inner join groups_users on groups_users.user_id = issues.assigned_to_id

where issues.status_id in (3, 5)
  and issues.updated_on > current_timestamp + '-365 day'
  and groups_users.group_id in (36, 215)
group by users.firstname, to_char(issues.updated_on, 'YY/MM')
order by users.firstname, to_char(issues.updated_on, 'YY/MM')

ざっくりとした予実対比

予定工数と実績工数の対比になります。
以下のデータはかなりざっくりとしています。
理由はRedmineではステータスの変更ログがないため、消化状態→バグ発生で元のステータスに変わると、この結果が変わってしまいます。
ただ、正確ではないにしろ、ある程度参考になるかと思います。
あと、月次で見ているので、固定値は毎月一回変えてます(^_^;
気が向いたら動的に求める予定です。

SELECT main.firstname,
       trunc(cast(sum(real_time) AS NUMERIC), 2)                                                  AS real_time,
       trunc(cast(sum(real_time) AS NUMERIC), 2) / 20                                             AS per_day,
       sum(estimated_hours)                                                                       AS estimated_hours,
       trunc(cast(sum(estimated_hours) / sum(real_time) AS NUMERIC), 3)                           AS percent,
       trunc(cast(sum(estimated_hours) / sum(real_time) * (sum(real_time) / 84.8) AS NUMERIC), 3) AS result
FROM (SELECT U.id, U.firstname AS firstname, (SELECT sum(T.hours)
                                              FROM time_entries T
                                              WHERE I.id = T.issue_id
                                                AND date_part('year', T.created_on) = 2019
                                                AND date_part('month', T.created_on) =
                                                    8) AS real_time, I.estimated_hours AS estimated_hours
      FROM issues I
             INNER JOIN users U ON I.assigned_to_id = U.id
      WHERE exists(SELECT *
                   FROM time_entries
                   WHERE time_entries.issue_id = I.id
                     AND date_part('year', time_entries.created_on) = 2019
                     AND date_part('month', time_entries.created_on) = 8
                     AND time_entries.hours !=0 
                )
        AND I.estimated_hours IS NOT NULL
        AND I.status_id IN (3, 5, 6)) AS main
GROUP BY main.id, main.firstname
ORDER BY result DESC;

• real_timeが実績工数です
• per_dayは一ヶ月20日労働とし、日での平均実績です(8時間勤務なのに4時間だと実績登録が正確ではないという事になります)
• estimated_hoursは予定工数です
• percentは予定工数と実績工数のパーセンテージです
• resultは基準となるスタッフを決め、そのスタッフのパーセンテージとの割合を求めています
  • 84.8は基準となるスタッフのパーセンテージで手で修正してます
• 2019=年 8=月です
  • 毎月一回の作業なので手で修正してます
• status_id in (3,5,6)は終了と見なすステータスIDです

予定工数未登録一覧

弊社の分析は予定工数と実績工数が肝なので、チケットを発行した際に予定工数を登録し忘れた一覧を表示しています。

select users.firstname,
       projects.name as "プロジェクト名",
       '<a target="_blank" href="https://ドメイン/issues/' || issues.id || '">' || issues.id || '</a>' AS "ID",
       issues.subject,
       issue_statuses.name as "ステータス"

from users
         inner join groups_users on users.id = groups_users.user_id
         inner join issues on issues.assigned_to_id = users.id
         inner join issue_statuses on issues.status_id = issue_statuses.id
         inner join  projects on issues.project_id= projects.id
where groups_users.group_id in (36, 215)
  and issues.estimated_hours is null
  and issues.status_id not in (3, 5, 6)
  and projects.status = 1
and projects.id not in (253,53)
order by issues.updated_on desc
;

• ドメイン部分はお使いのredmineのドメインに置き換えてください
  • チケットにリンクし、設定漏れチケットにすぐに飛べます
• group_idは分析したいグループを設定
• status_idは終了ではないチケット
• projects.status=1は現在アクティブなプロジェクト
• projects.id not in (253,53)は対象外としたいプロジェクト

バグの件数分析

バグの合計件数とカスタムフィールドで定義した不具合原因の月次推移です。

select count(issues.id),to_char(created_on,'YYYY/MM') as "発生年月",custom_values.value from issues
left join custom_values on issues.id = customized_id and custom_field_id = 8
where tracker_id = 1 and created_on > current_timestamp + '-2 year'
group by to_char(created_on,'YYYY/MM'),custom_values.value
order by to_char(created_on,'YYYY/MM')

• custom_field_id = 8は不具合原因のカスタムフィールドです

利益計算

請負開発

プロジェクトに受注金額用のカスタムフィールドを設定し、そちらと実績工数から1時間の利益を求めています。
こちらは結果の公開は控えさせて頂きます。

select projects.name,
       cast((custom_values.value) as double precision) as "見積金額",
       sum(time_entries.hours) as "合計時間",
       cast((custom_values.value) as double precision) /
       sum(time_entries.hours) as "時給"
from time_entries
         inner join projects on time_entries.project_id = projects.id
         inner join custom_values on customized_id = time_entries.project_id and custom_field_id = 23
where
not exists(select * from custom_values where customized_id = time_entries.project_id and custom_field_id = 24 and value = '1')
group by projects.name,custom_values.value
;

• custom_field_id = 23は見積金額が入ったカスタムフィールドです
• custom_field_id = 24は分析対象とするかどうかのフラグです

定額開発

毎月定額でシステム開発を請け負っており、そちらの月次時間単位の利益集計です。
こちらの結果も公開は控えさせて頂きます。

select projects.name,
       to_char(time_entries.updated_on, 'YY/MM'),
       cast((custom_values.value) as double precision) as "見積金額",
       sum(time_entries.hours)                         as "合計時間",
       cast((custom_values.value) as double precision) /
       sum(time_entries.hours)                         as "時給"
from time_entries
         inner join projects on time_entries.project_id = projects.id
         inner join custom_values on customized_id = time_entries.project_id and custom_field_id = 23
where exists(select *
             from custom_values
             where customized_id = time_entries.project_id
               and custom_field_id = 24
               and value = '1')
  and time_entries.updated_on > current_timestamp + '-365 day'
group by projects.name, custom_values.value, to_char(time_entries.updated_on, 'YY/MM')
order by projects.name, to_char(time_entries.updated_on, 'YY/MM')
;

納期

一覧

担当者毎の納期状況を見ています。期日未設定・遅れ・期日が7日以内・期日が30日以内で予定工数の合計で見ています。
また、リンククリックで該当のチケット一覧にリンクしています。

select users.firstname,
       '<a target="_blank" href="https://ドメイン/issues?utf8=%E2%9C%93&set_filter=1&f%5B%5D=status_id&op%5Bstatus_id%5D=o&f%5B%5D=due_date&op%5Bdue_date%5D=%21*&f%5B%5D=assigned_to_id&op%5Bassigned_to_id%5D=%3D&f%5B%5D=&c%5B%5D=project&c%5B%5D=tracker&c%5B%5D=status&c%5B%5D=priority&c%5B%5D=subject&c%5B%5D=assigned_to&c%5B%5D=updated_on&c%5B%5D=category&c%5B%5D=fixed_version&c%5B%5D=start_date&c%5B%5D=due_date&c%5B%5D=estimated_hours&c%5B%5D=done_ratio&group_by=&t%5B%5D=estimated_hours&t%5B%5D=spent_hours&t%5B%5D=&v%5Bassigned_to_id%5D%5B%5D=' ||
       users.id || '">' ||
       (select sum(estimated_hours)
        from issues
                 inner join projects on projects.id = issues.project_id
        where issues.assigned_to_id = users.id
          and due_date is null
          and issues.status_id not in (3, 5, 6)
          and projects.status = 1
       )
           || '</a>'
           as "納期未設定"
        ,
       (select sum(estimated_hours)
        from issues
                 inner join projects on projects.id = issues.project_id
        where issues.assigned_to_id = users.id
          and due_date is null
          and issues.status_id not in (3, 5, 6)
          and projects.status = 1
       )
           as "納期未設定(グラフ用)"
        ,
       '<a target="_blank" href="https://ドメイン/issues?utf8=%E2%9C%93&set_filter=1&f%5B%5D=status_id&op%5Bstatus_id%5D=o&f%5B%5D=due_date&op%5Bdue_date%5D=%3Ct-&v%5Bdue_date%5D%5B%5D=1&f%5B%5D=assigned_to_id&op%5Bassigned_to_id%5D=%3D&f%5B%5D=&c%5B%5D=project&c%5B%5D=tracker&c%5B%5D=status&c%5B%5D=priority&c%5B%5D=subject&c%5B%5D=assigned_to&c%5B%5D=updated_on&c%5B%5D=category&c%5B%5D=fixed_version&c%5B%5D=start_date&c%5B%5D=due_date&c%5B%5D=estimated_hours&c%5B%5D=done_ratio&group_by=&t%5B%5D=estimated_hours&t%5B%5D=spent_hours&t%5B%5D=&v%5Bassigned_to_id%5D%5B%5D=' ||
       users.id || '">' ||
       (select sum(estimated_hours)
        from issues
                 inner join projects on projects.id = issues.project_id
        where issues.assigned_to_id = users.id
          and due_date < now()
          and issues.status_id not in (3, 5, 6)
          and projects.status = 1
       )
           || '</a>'
           as "納期遅れ",
       (select sum(estimated_hours)
        from issues
                 inner join projects on projects.id = issues.project_id
        where issues.assigned_to_id = users.id
          and due_date < now()
          and issues.status_id not in (3, 5, 6)
          and projects.status = 1
       )
           as "納期遅れ(グラフ用)",
       (select sum(estimated_hours)
        from issues
                 inner join projects on projects.id = issues.project_id
        where issues.assigned_to_id = users.id
          and due_date < now() + '7 day'
          and due_date > now()
          and issues.status_id not in (3, 5, 6)
          and projects.status = 1
       )   as "7日以内",
       (select sum(estimated_hours)
        from issues
                 inner join projects on projects.id = issues.project_id
        where issues.assigned_to_id = users.id
          and due_date < now() + '30 day'
          and due_date > now()
          and issues.status_id not in (3, 5, 6)
          and projects.status = 1
       )   as "30日以内"
from users
         inner join groups_users on users.id = groups_users.user_id
where groups_users.group_id in (36, 215)
;

円グラフ

同一SQLで担当者の負荷割合を円グラフで見ています。

番外編

期日変更

こちらは自作のプラグインがなければ動作しません。(チケットIDと変更する日数をパラメータにして、チケットのdue_dateを更新しているだけです)
期日昇順で表示し、当日、明日、3日後、7日後、14日後などとワンクリックで期日を変更出来る一覧になります。

SELECT projects.name,
       '<a target="_blank" href="https://ドメイン/issues/' || issues.id || '">' || issues.id || '</a>' AS "ID",
       issues.subject,
       issues.due_date,
        '<a target="_blank" href="https://ドメイン/check_due_date/update?issue%5Bid%5D=' || issues.id || '&commit=0">0日</a>' AS "0日",
        '<a target="_blank" href="https://ドメイン/check_due_date/update?issue%5Bid%5D=' || issues.id || '&commit=1">1日</a>' AS "1日",
        '<a target="_blank" href="https://ドメイン/check_due_date/update?issue%5Bid%5D=' || issues.id || '&commit=3">3日</a>' AS "3日",
        '<a target="_blank" href="https://ドメイン/check_due_date/update?issue%5Bid%5D=' || issues.id || '&commit=7">7日</a>' AS "7日",
        '<a target="_blank" href="https://ドメイン/check_due_date/update?issue%5Bid%5D=' || issues.id || '&commit=14">14日</a>' AS "14日",
        '<a target="_blank" href="https://ドメイン/check_due_date/update?issue%5Bid%5D=' || issues.id || '&commit=30">30日</a>' AS "30日",
        '<a target="_blank" href="https://ドメイン/check_due_date/update?issue%5Bid%5D=' || issues.id || '&commit=90">90日</a>' AS "90日",
        '<a target="_blank" href="https://ドメイン/check_due_date/update?issue%5Bid%5D=' || issues.id || '&commit=180">180日</a>' AS "180日"
FROM issues
INNER JOIN projects ON issues.project_id = projects.id
WHERE issues.assigned_to_id = 3
  AND status_id NOT IN (5,
                        6)
  AND projects.status = 1 
ORDER BY issues.due_date