From Individual Productivity to Organizational Insight: Building AI Coding Metrics with LoongSuite‑Pilot and SLS

J‑Curve and ROI of AI‑Assisted Development

The Google Cloud DORA report (May 2026) introduces a J‑Curve model: early AI‑coding tool adoption often causes a temporary productivity dip before delivering ROI. A 500‑person engineering team example shows an annual AI tool investment of $8.4 M with an expected return of $11.6 M (≈39 % ROI). The report notes that coding‑speed gains do not automatically translate to business outcomes, especially on legacy code where efficiency gains are <10 % versus 35‑40 % on greenfield projects.

Measurement Stack: LoongSuite‑Pilot × Alibaba Cloud Log Service (SLS)

To locate an organization’s position on the J‑Curve, the stack collects AI‑coding agent events using LoongSuite‑Pilot that implements the LoongSuite GenAI semantic conventions (an extension of OpenTelemetry GenAI conventions). Events are written to SLS as a unified fact table; a separate personnel dimension table provides organizational context.

Data Ingestion Layer

The fact table records one row per agent invocation with fields such as:

user.id

gen_ai.session.id

gen_ai.agent.type

gen_ai.provider.name

gen_ai.request.model

gen_ai.usage.input_tokens

gen_ai.usage.output_tokens

gen_ai.usage.total_tokens

gen_ai.tool.name

gen_ai.tool.call.arguments.file_path

event.name

git.repo

git.domain

The personnel dimension table stores work_no (employee ID), show_name (name), and dept_name (full department path, e.g., "技术研发部-工程平台部-数据服务组"). The fact and dimension tables are joined on user.id = work_no.

Why a Separate Organization Dimension?

Decoupling: Organizational changes update the dimension table without rewriting historical events.

LEFT JOIN visibility: A WHERE user_id IS NULL clause reveals registered employees who have not reported any AI usage.

Hierarchical splitting: The department path is split once into three levels (dept 1, dept 2, team) and reused by all downstream charts.

Analysis Layer in SLS

All dashboards are defined directly in SLS using SQL. This provides:

Query‑as‑definition: Changing a metric is a matter of editing a single SQL statement.

Full control of definitions: Teams can customize active‑user definitions, department breakdowns, and denominator choices.

Immediate feedback: New agents appear in the dashboard within minutes, eliminating ETL latency.

Common Table Expressions (CTEs) are used as a shared engineering foundation for the 30+ charts.

WITH dept_user AS (
  SELECT work_no,
         show_name,
         COALESCE(SPLIT_PART(dept_name, '-', 1), '') AS dept_name_1,
         COALESCE(SPLIT_PART(dept_name, '-', 2), '') AS dept_name_2,
         COALESCE(SPLIT_PART(dept_name, '-', 3), '') AS dept_name_3
  FROM <dept>-logstore
  GROUP BY work_no, show_name, dept_name
),
active_user AS (
  SELECT date_trunc('day', __time__) AS t,
         "user.id" AS user_id,
         COALESCE(NULLIF("gen_ai.agent.type", 'null'), 'unknown') AS agent_type,
         COALESCE(NULLIF("gen_ai.provider.name", 'null'), 'unknown') AS provider,
         COALESCE(NULLIF("gen_ai.request.model", 'null'),
                  NULLIF("gen_ai.response.model", 'null'), 'unknown') AS model,
         SUM(COALESCE("gen_ai.usage.input_tokens", 0)) AS input_tokens,
         SUM(COALESCE("gen_ai.usage.output_tokens", 0)) AS output_tokens,
         SUM(COALESCE("gen_ai.usage.total_tokens", 0)) AS total_tokens,
         COUNT(1) AS events
  FROM <events>-logstore
  GROUP BY t, user_id, agent_type, provider, model
)
/* Additional CTEs (hourly_user, user_token, etc.) follow the same pattern */

Dashboard Sections

Section 1 – Core Overview: Cards show active users, total tokens, session count, per‑user token average, and employees without reports, each with week‑over‑week comparisons.

Section 2 – Structural Distribution: Pie charts break down token share by agent_type, model, and provider, answering resource‑allocation questions.

Section 3 – Trends: Line charts display user growth, event volume, token consumption (input/output/total), and per‑agent token trajectories over time.

Section 4 – Department Statistics: Tables and bar charts rank departments by total tokens, coverage rate, and per‑capita token usage; a LEFT JOIN ensures departments with zero usage remain visible for promotion targeting.

Section 5 – Organization & Personnel: Per‑person tables list token totals, event counts, used agents, and models, supporting one‑on‑one coaching.

Section 6 – Skill & Tool: Top‑10 skill usage and tool call counts are extracted from gen_ai.tool.call.arguments.file_path using a robust path‑parsing SQL that normalizes various directory structures.

Section 7 – Code Repositories: Token consumption is aggregated by git.repo and git.domain to assess whether AI effort aligns with core business code or experimental projects.

Section 8 – Token Concentration: A window function ranks users by total tokens and computes the top‑10 % token share, with daily trends to monitor whether productivity is concentrated in a few power users.

Key Engineering Trade‑offs

Including git.repo in the main active_user CTE would explode row counts (user × day × repo); instead, repository aggregation queries the raw event table directly.

Hourly token distribution also falls back to the raw event table because the pre‑aggregated CTEs operate at day granularity.

Skill Extraction SQL Example

The skill name is derived from diverse file‑path patterns (e.g., skills/<name>/SKILL.md, .qoderwork/<name>.skill.md) using conditional regex logic:

SELECT skill_name AS "Skill", COUNT(1) AS "调用次数"
FROM (
  SELECT CASE
    WHEN regexp_like(regexp_extract(path, '/([^/]+)/[^/]+$', 1), '^v?[0-9]+\.[0-9]+')
      THEN regexp_extract(path, '/([^/]+)/v?[0-9][^/]*/[^/]+$', 1)
    WHEN lower(regexp_extract(path, '/([^/]+)/[^/]+$', 1)) IN ('skills','skill','.claude','agents','resources','.qoderwork','docs')
      THEN regexp_replace(regexp_extract(path, '/([^/]+)$', 1), '(?i)(\.skill)?\.md$', '')
    ELSE regexp_extract(path, '/([^/]+)/[^/]+$', 1)
  END AS skill_name
  FROM <events> t
  JOIN dept_user d ON t."user.id" = d.work_no
  WHERE regexp_like(t."gen_ai.tool.call.arguments.file_path", '(?i)(/SKILL\.md|/[^/]+\.skill\.md)$')
) 
WHERE skill_name IS NOT NULL AND skill_name <> '' AND upper(skill_name) <> 'SKILL'
GROUP BY skill_name
ORDER BY "调用次数" DESC
LIMIT 10;

Token Concentration Calculation

A window function ranks users by total tokens and computes the top‑10 % token share:

user_token AS (
  SELECT a.user_id, SUM(a.total_tokens) AS total_tokens
  FROM dept_user d
  JOIN active_user a ON d.work_no = a.user_id
  GROUP BY a.user_id
  HAVING total_tokens > 0
),
ranked AS (
  SELECT user_id,
         total_tokens,
         ROW_NUMBER() OVER (ORDER BY total_tokens DESC) AS rn,
         COUNT(*) OVER () AS user_count,
         SUM(total_tokens) OVER () AS all_tokens
  FROM user_token
)
SELECT ROUND(100.0 * SUM(CASE WHEN rn <= CAST(CEIL(user_count * 0.1) AS BIGINT) THEN total_tokens ELSE 0 END) / MAX(all_tokens), 2) AS "Top10% Token占比"
FROM ranked;

Summary of the Three‑Layer Framework

Unified Semantic Collection: LoongSuite‑Pilot captures heterogeneous AI‑coding events with a consistent schema, enabling cross‑tool comparability.

Flexible SQL Analysis: Public CTEs provide a stable, reusable data foundation; dashboards are defined by SQL, allowing instant metric updates.

Actionable Organizational Insights: The personnel dimension exposes “registered but not reporting” users; token concentration charts reveal whether productivity is widespread or head‑heavy, turning raw numbers into concrete actions.

Relevant links:

https://cloud.google.com/resources/content/dora-roi-of-ai-assisted-software-development

https://dora.dev/ai/roi/report/

https://github.com/alibaba/loongsuite-semantic-conventions-genai/

https://opentelemetry.io/docs/specs/semconv/gen-ai/