# 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 %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://enterprise-docs.datacamp.com/integrating-our-data-into-your-tools-via-data-connector-2.0/queries-to-recreate-key-reports-in-the-groups-tab/reporting-section/content-insights.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.