BE: Duo feature retention chart

Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.

• Close this issue

Add GraphQL endpoint to support duo feature retention chart to aiMetrics. It needs to support both weekly and monthly aggregation.

Query sample

  group(fullPath:"gitlab-org") {
    aiMetrics(startDate: "x", endDate: "y") {
      featureRetention(feature: "A", period: WEEK\MONTH) {
        nodes {
         periodStartDate
         returningUsersCount
         totalUsersCount
        }
      }
    }

We can use ai_usage_events_daily to query returning users weekly or monthly, for example:

WITH ai_usage_retention_agg AS (
    SELECT 
        date,
        namespace_path,
        event,
        groupUniqArray(user_id) AS uniq_users
    FROM ai_usage_events_daily
    WHERE event IN (1, 2, 3, 4, 12, 13, 14, 15, 16, 17)
        AND startsWith(namespace_path, '9970/')
        AND date >= '2024-11-10'
        AND date < '2025-11-11'
    GROUP BY namespace_path, event, date
),
weekly_aggregates AS (
    SELECT 
        toStartOfWeek(date) AS week,
        event,
        arrayDistinct(arrayFlatten(groupArray(uniq_users))) AS user_ids
    FROM ai_usage_retention_agg
    GROUP BY week, event
)
SELECT
    this_week.week,
    this_week.event,
    length(this_week.user_ids) AS this_week_users,
    length(last_week.user_ids) AS last_week_users,
    length(arrayIntersect(this_week.user_ids, last_week.user_ids)) AS users_who_returned,
    round(users_who_returned * 100.0 / last_week_users, 2) AS retention_percentage
FROM weekly_aggregates this_week
INNER JOIN weekly_aggregates last_week
    ON this_week.event = last_week.event
    AND last_week.week = this_week.week - INTERVAL 1 WEEK
ORDER BY this_week.week, this_week.event

We also need to support monthly retention following the same principle from the query above.