Content insights

The following queries match what is shown on selected reports in the Content tab (inside the Reporting section of the Groups tab):

• XP earned by technology

• Courses activity

• Projects activity

• Tracks activity

XP earned by technology

To calculate XP earned by the technology (all time), you can use the following query:

SELECT 
    content.technology, 
    sum(events.xp_earned) AS total_xp
FROM data_connector_1234.fact_learn_events AS events 
INNER JOIN data_connector_1234.dim_content AS content 
  ON events.content_id = content.content_id
-- For legacy reasons, the report only shows course XP
WHERE events.event_name = 'course_engagement'
GROUP BY content.technology
ORDER BY total_xp DESC

The query above only includes XP earned in courses. This matches the report in the Groups tab, which is restricted to course XP for legacy reasons.

You can modify the WHERE clause to get XP across all content types. Please check the Domain Gotchas section for potential pitfalls when calculating XP.

Courses Activity

To replicate the Courses Activity table, you can use the following query:

WITH course_events AS (
        SELECT 
            content_id,
            event_name
        FROM data_connector_1234.fact_learn_events
        WHERE event_name IN ('course_started', 'course_completed')
    ),

    course_activity_counts AS (
        SELECT 
            content_id,
            count_if(event_name = 'course_started') AS starts,
            count_if(event_name = 'course_completed') AS completions
        FROM course_events
        GROUP BY content_id
    )

SELECT 
    content.content_title AS course,
    content.topic,
    content.technology,
    counts.starts,
    counts.completions,
    if(counts.starts = 0, 1, 
        round(100 * counts.completions / counts.starts, 2)) AS completion_rate
FROM course_activity_counts AS counts
INNER JOIN data_connector_1234.dim_content AS content
    ON counts.content_id = content.content_id
ORDER BY completions DESC

When counting content completions over a given period, regardless of when the content start took place, the metric can exceed 100%.

In technical terms, it is a velocity metric, not a cohort metric.

Projects Activity

To replicate the Projects Activity table, you can use the following query:

WITH project_events AS (
        SELECT 
            content_id,
            event_name
        FROM data_connector_1234.fact_learn_events
        WHERE event_name IN ('project_started', 'project_completed')
    ),

    project_activity_counts AS (
        SELECT 
            content_id,
            count_if(event_name = 'project_started') AS starts,
            count_if(event_name = 'project_completed') AS completions
        FROM project_events
        GROUP BY content_id
    )

SELECT 
    content.content_title AS project,
    content.technology,
    counts.starts,
    counts.completions,
    if(counts.starts = 0, 1, 
        round(100 * counts.completions / counts.starts, 2)) AS completion_rate
FROM project_activity_counts AS counts
INNER JOIN data_connector_1234.dim_content AS content
    ON counts.content_id = content.content_id
ORDER BY completions DESC

When counting content completions over a given period, regardless of when the content start took place, the metric can exceed 100%.

In technical terms, it is a velocity metric, not a cohort metric.

Tracks Activity

To replicate the Tracks Activity table, you can use the following query:

WITH track_names AS (
        SELECT DISTINCT
            track_id,
            /* Every time a particular track is updated, it gets a new track_version_id 
            while keeping the same track_id. Below, we keep the latest track_title
            and track_technology for each track_id */
            first_value(trim(track_title)) 
                OVER(PARTITION BY track_id 
                    ORDER BY track_version_id DESC) AS track_title,
            first_value(track_technology) 
                OVER(PARTITION BY track_id                            
                    ORDER BY track_version_id DESC) AS track_technology
        FROM data_connector_1234.dim_track
    ),
    
    track_events AS (
        SELECT 
            track_version_id,
            event_name
        FROM data_connector_1234.fact_learn_events
        WHERE event_name IN ('track_started', 'track_completed')
    ),

    track_activity_counts AS (
        SELECT 
            track_version_id,
            count_if(event_name = 'track_started') AS enrollments,
            count_if(event_name = 'track_completed') AS completions
        FROM track_events
        GROUP BY track_version_id
    ),

    track_activity_metrics AS (
        SELECT 
            track.track_id,
            track.track_category as track_type,
            sum(counts.enrollments) as enrollments,
            sum(counts.completions) as completions,
            if(sum(counts.enrollments) = 0, 1, 
                round(100* sum(counts.completions) / sum(counts.enrollments), 2)) AS completion_rate
        FROM track_activity_counts AS counts
        INNER JOIN data_connector_1234.dim_track AS track
            ON counts.track_version_id = track.track_version_id
        GROUP BY track.track_id, track.track_category
    )

SELECT
    /* The line below is to format the track names the 
    same way they appear in the Tracks Activity report */
    if(tracks.track_technology IS NULL, 
        tracks.track_title, 
        concat(tracks.track_title, ' (', tracks.track_technology, ')')
      ) AS track,
    metrics.track_type,
    metrics.enrollments,
    metrics.completions,
    metrics.completion_rate
FROM track_activity_metrics AS metrics
LEFT JOIN track_names AS tracks
    ON metrics.track_id = tracks.track_id
ORDER BY completions DESC

When counting content completions over a given period, regardless of when the content start took place, the metric can exceed 100%.

In technical terms, it is a velocity metric, not a cohort metric.