# 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](#xp-earned-by-technology)
* [Courses activity](#courses-activity)
* [Projects activity](#projects-activity)
* [Tracks activity](#tracks-activity)

### XP earned by technology

<figure><img src="https://562960931-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FkJKV1xpCBb6myYGAGdfV%2Fuploads%2FCKyBDZepConawCwUGzjD%2Fxp_by_technology.png?alt=media&#x26;token=df1c0862-2b66-4a7a-ae41-fa9991264815" alt=""><figcaption></figcaption></figure>

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

```sql
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
```

{% hint style="info" %}
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.&#x20;

You can modify the WHERE clause to get XP across all content types. Please check the [Domain Gotchas](https://enterprise-docs.datacamp.com/integrating-our-data-into-your-tools-via-data-connector-2.0/domain-gotchas) section for potential pitfalls when calculating XP.&#x20;
{% endhint %}

### Courses Activity

<figure><img src="https://562960931-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FkJKV1xpCBb6myYGAGdfV%2Fuploads%2FGEZ4MrXRpvU2iK0JOEGv%2Fcourses_activity.png?alt=media&#x26;token=e7c19669-d4af-4f70-9204-7f538f687aa7" alt=""><figcaption></figcaption></figure>

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

```sql
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
```

{% hint style="info" %}
When counting content completions over a given period, regardless of when the content start took place, the metric can exceed 100%.&#x20;

In technical terms, it is a velocity metric, not a cohort metric.
{% endhint %}

### Projects Activity

<figure><img src="https://562960931-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FkJKV1xpCBb6myYGAGdfV%2Fuploads%2FJIcHGtuVvh4haMeI3c1n%2Fprojects_activity.png?alt=media&#x26;token=fae1f16f-ccac-4c48-807e-f9d96a096d26" alt=""><figcaption></figcaption></figure>

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

```sql
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
```

{% hint style="info" %}
When counting content completions over a given period, regardless of when the content start took place, the metric can exceed 100%.&#x20;

In technical terms, it is a velocity metric, not a cohort metric.
{% endhint %}

### Tracks Activity

<figure><img src="https://562960931-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FkJKV1xpCBb6myYGAGdfV%2Fuploads%2FFovcrcs8IcYPZNsAJ0S1%2Ftracks_activity.png?alt=media&#x26;token=1f17731e-9de4-4d43-9540-0740decf0f1f" alt=""><figcaption></figcaption></figure>

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

```sql
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
```

{% hint style="info" %}
When counting content completions over a given period, regardless of when the content start took place, the metric can exceed 100%.&#x20;

In technical terms, it is a velocity metric, not a cohort metric.
{% endhint %}