> ## Documentation Index
> Fetch the complete documentation index at: https://docs.wejam.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Export Training Data to Excel

> Pull your JAM training data into Excel with Power Query for custom reporting - once set up, refreshing is a single click.

JAM's Data Export API lets you pull your training data into Excel for custom reporting. Once set up, refreshing your data takes a single click.

## Before you start

You'll need:

* An **Owner** role in your JAM organization
* An API key - create one at [https://auth.wejam.ai/org/api\_keys/](https://auth.wejam.ai/org/api_keys/)

**To create your API key:**

1. Go to [https://auth.wejam.ai/org/api\_keys/](https://auth.wejam.ai/org/api_keys/) and sign in as an Owner.
2. Click **Create key**, give it a name, and copy it somewhere safe.
3. Keep it private - it grants read access to your organization's data. If it's ever exposed, delete it and create a new one from the same page.

<Tip>
  You only need to create the API key once. You'll paste it into Excel during
  setup, and it stays there for all future refreshes.
</Tip>

For more detail, see [Using the Jam API for Data Export](/external/coach/help-and-reference/data-export-api).

## What data you can pull

The API covers four main training entities. Others - teams, sprints, track assignments, and mission assignments - are also available and work the same way.

| Entity   | Endpoint    | What it contains                       |
| -------- | ----------- | -------------------------------------- |
| Users    | `/users`    | People in your org (name, email, IDs)  |
| Sessions | `/sessions` | Individual roleplay rounds with scores |
| Tracks   | `/tracks`   | Learning paths that group missions     |
| Missions | `/missions` | Individual training scenarios          |

<Tip>
  A **session** is a single roleplay round played by one learner on one mission.
  It's the core performance record - it carries an overall score (0-100) and a
  per-task scorecard breakdown.
</Tip>

## Set up Excel (one time)

Do this once. After setup, refreshing data is just **Data → Refresh All**.

1. In Excel, go to **Data → Get Data → From Other Sources → Blank Query**.
2. In the editor that opens, choose **Home → Advanced Editor**.
3. Delete whatever is in the box, paste one of the code examples below, and replace `YOUR_API_KEY` with your real key.
4. Click **Done**.
5. If Excel asks how to connect to `api.wejam.ai`, choose **Anonymous** and click **Connect**.
6. When the table preview looks right, click **Home → Close & Load**.

<Warning>
  Always choose **Anonymous** when Excel asks for credentials - not "Web API" or
  "Organizational account." Your API key is already included in the code as a
  header, so no other auth is needed.
</Warning>

## Code examples (Power Query)

Two methods below. **Method A** is quickest for a single entity. **Method B** is cleaner if you want all four - your key lives in one place and you get four tidy worksheets.

### Method A - one query per entity

Paste this into the Advanced Editor. It pulls **missions** by default. To pull a different entity, change the single word at the end of the `BaseUrl` line - `missions` to `users`, `sessions`, or `tracks`. Everything else stays the same.

```powerquery theme={null}
let
    BaseUrl = "https://api.wejam.ai/api/v1/data-exports/missions",
    ApiKey  = "YOUR_API_KEY",

    GetPage = (page as number) as record =>
        Json.Document(
            Web.Contents(
                BaseUrl,
                [
                    Query   = [ page = Text.From(page), limit = "100" ],
                    Headers = [ #"X-API-KEY" = ApiKey, #"accept" = "application/json" ]
                ]
            )
        ),

    Pages = List.Generate(
        () => [ p = 1, resp = GetPage(1) ],
        each [resp] <> null,
        each if [resp][meta][hasNext] = true
             then [ p = [p] + 1, resp = GetPage([p] + 1) ]
             else [ p = [p] + 1, resp = null ],
        each [resp][data]
    ),

    Rows     = List.Combine(Pages),
    AsTable  = Table.FromList(Rows, Splitter.SplitByNothing(), {"Record"}),
    Expanded = if List.IsEmpty(Rows)
               then AsTable
               else Table.ExpandRecordColumn(AsTable, "Record", Record.FieldNames(Rows{0}))
in
    Expanded
```

### Method B - one reusable function (recommended)

This keeps your API key in a single spot.

**Step 1 - store your key as a parameter.** In Power Query, go to **Home → Manage Parameters → New Parameter**. Name it `ApiKey`, type `Text`, and set the current value to your key.

**Step 2 - create the shared function.** New Source → Blank Query → Advanced Editor. Paste the code below and rename the query to `fnGetJamData`.

```powerquery theme={null}
(entity as text) as table =>
let
    BaseUrl = "https://api.wejam.ai/api/v1/data-exports/",

    GetPage = (page as number) as record =>
        Json.Document(
            Web.Contents(
                BaseUrl & entity,
                [
                    Query   = [ page = Text.From(page), limit = "100" ],
                    Headers = [ #"X-API-KEY" = ApiKey, #"accept" = "application/json" ]
                ]
            )
        ),

    Pages = List.Generate(
        () => [ p = 1, resp = GetPage(1) ],
        each [resp] <> null,
        each if [resp][meta][hasNext] = true
             then [ p = [p] + 1, resp = GetPage([p] + 1) ]
             else [ p = [p] + 1, resp = null ],
        each [resp][data]
    ),

    Rows     = List.Combine(Pages),
    AsTable  = Table.FromList(Rows, Splitter.SplitByNothing(), {"Record"}),
    Expanded = if List.IsEmpty(Rows)
               then AsTable
               else Table.ExpandRecordColumn(AsTable, "Record", Record.FieldNames(Rows{0}))
in
    Expanded
```

**Step 3 - create one query per entity.** For each entity, do New Source → Blank Query → Advanced Editor and enter a single line:

```powerquery theme={null}
= fnGetJamData("users")
```

```powerquery theme={null}
= fnGetJamData("sessions")
```

```powerquery theme={null}
= fnGetJamData("tracks")
```

```powerquery theme={null}
= fnGetJamData("missions")
```

Name each query (`Users`, `Sessions`, `Tracks`, `Missions`) and click **Close & Load**. You'll get four worksheets, all sharing the same key.

## Bonus: a ready-to-read sessions report

Raw sessions reference users and missions by ID. The query below joins them so each row shows the learner's name, mission title, date, score, and completion status - the kind of table you can hand straight to a manager. This uses the `fnGetJamData` function from Method B above.

```powerquery theme={null}
let
    Sessions = fnGetJamData("sessions"),
    Users    = fnGetJamData("users"),
    Missions = fnGetJamData("missions"),

    // Pull the score out of the nested analysis record
    WithScore = Table.AddColumn(
        Sessions, "Score",
        each if [analysis] <> null then Record.FieldOrDefault([analysis], "score", null) else null,
        Int64.Type
    ),

    // Add learner names
    WithUsers = Table.NestedJoin(
        WithScore, {"learnerUserId"}, Users, {"id"}, "u", JoinKind.LeftOuter
    ),
    AddName = Table.AddColumn(
        WithUsers, "UserName",
        each Text.Combine({ [u]{0}?[firstName]?, [u]{0}?[lastName]? }, " ")
    ),

    // Add mission titles
    WithMissions = Table.NestedJoin(
        AddName, {"missionId"}, Missions, {"id"}, "m", JoinKind.LeftOuter
    ),
    AddTitle = Table.AddColumn(
        WithMissions, "MissionTitle", each [m]{0}?[title]?
    ),

    // Keep just the report columns
    Final = Table.SelectColumns(
        AddTitle, {"UserName", "MissionTitle", "createdAt", "Score", "completed"}
    ),
    Sorted = Table.Sort(Final, {{"createdAt", Order.Descending}})
in
    Sorted
```

## Key fields by entity

The code auto-expands all fields, but these are the ones most reports rely on.

**Users:** `id` · `firstName` · `lastName` · `email`

**Sessions:**

* `analysis.score` - overall score 0-100 (the headline metric)
* `completed` - whether the roleplay was finished (true/false)
* `createdAt` - when the session was played
* `learnerUserId` - links to `id` in Users
* `missionId` - links to `id` in Missions
* Scorecard: each task has an `itemScore` (0-6), mapped to solved / partly solved / not yet solved

**Tracks & Missions:** `id` · `title` - see the [API reference](/api-reference/data-export/export-paginated-users-data) for the full schema

## Troubleshooting

| Symptom                            | Fix                                                                                                                                                             |
| ---------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Excel keeps asking for credentials | Choose **Anonymous**. If you picked the wrong type earlier, go to Data → Get Data → Data Source Settings, select `api.wejam.ai`, and clear/edit the permission. |
| `401 Unauthorized` error           | The key is wrong, expired, or deleted - or it wasn't created by an Owner. Recreate it at the API keys page.                                                     |
| Header not recognized              | Make sure the header is named `X-API-KEY`.                                                                                                                      |
| Table comes back empty             | That entity may have no data, or all data fits on page 1 (which is fine - no error).                                                                            |
| Need to refresh data               | Click **Data → Refresh All**. No other steps required.                                                                                                          |

## Related articles

* [Using the Jam API for Data Export](/external/coach/help-and-reference/data-export-api) - the API key, the `data-export` endpoint, and Python examples

***

*Last updated: June 2026*