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

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
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
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
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
Last updated