Students w/ grade <60...and then some

[rexj]

Hi All,

I have been running the "Students with a Current Course Score < 60" report (Admin >Admin Analytics > Students) for our student success department. The report includes the number of courses a student has with a grade <60, but not the specific courses. They asked me today if I could include the specific course information.

• I found this post about getting specific grade information per student per course. 

It looks like I could add the 'enrollment_term_id' in this call to limit results to the desired term. Is this correct?

Am I correct then, that I could filter the resulting CSV in Excel for grades <60 to produce a similar report that includes all course information for each student?
I think that rather than having the count of courses per student each instance of a <60 result would be contained in a row of the CSV. Is that correct?

Am I on he right track or missing something?

Thank you!

Jedidiah

[James]

@rexj

As the other post mentioned, you can get the current and final scores via the enrollments API. Adding the enrollment_term_id is only helpful if you're using the user version of the call, which is not what I would recommend. You would need to make one API call for each student and then you could get all the enrollments for that student. If you have 100 students taking 5 classes each, you would need 100 API calls.

On the other hand, there is a course-version of that enrollments API where you make one API call per course. If you have 100 students taking 5 classes each but there are only 10 courses to pick from, then you would only need 10 API calls. There are likely more than 10 students in a class, so you would want to add per_page=100 to the querystring. If you have more than 100 students in the class, then you would need to worry about pagination. The enrollments endpoints use bookmarks rather than page numbers, so adding page=2 will not work.

If, however, you somehow know which students are underperforming and there are fewer of those students than courses, then using the user-version of the enrollments API could be quicker. But then you'll still need to get the course information for them.

Here's the old way I would do this.

I would use the term_id to get a list of all courses for the current term. It goes under the list active courses in an account endpoint where you specify enrollment_term_id. The enrollment_term_id needs to be the Canvas enrollment term ID, not the SIS term ID (some IDs can be given as a SIS ID). This means you may need to make an extra API call to get the Canvas enrollment term ID before you can use it.

1. Get the enrollment term ID using GET /api/v1/accounts/self/terms?per_page=100. You may be able to use the term_name to search and narrow that down: GET /api/v1/accounts/self/terms?per_page=100&term_name=fall+2025 (the + represents a space and could also be encoded as %20).
   For my instance, that returns id=17941, so I know that's the Canvas enrollment term ID that I need in step 2.
2. Get a list of active courses in an account using GET /api/v1/accounts/self/courses?enrollment_type[]=student&published=true&enrollment_term_id=17941&per_page=100. If you have more than 100 courses in a term, you will need to handle pagination. In this case, it will be of the form page=2, page=3, etc., but look at the headers to make sure. Note that you may be able to avoid step 1 by finding courses that haven't ended yet. One of the parameters for listing active courses is ends_after and you might be able to get by with ends_after=(today's ISO8601 timestamp). For example, if it's 2025-10-09 and you're in Central Daylight Time (Chicago), which is 5 hours behind UTC, you could use GET /api/v1/accounts/self/courses?enrollment_type[]=student&published=true&ends_after=2025-10-10T04:59:59Z&per_page=100. 04:59:59Z is 11:59:59 pm CDT. You may be able to further filter these based on courses that have SIS IDs or match other naming schemes.
3. For each course in your list of active courses, get the enrollments for that course usingGET /api/v1/courses/:course_id/enrollments?role[]=StudentEnrollment&per_page=100​.

As you process each enrollment, you would take the information about the course and include it in the record you export to Excel. If you are listing the students having trouble in the class, then I would repeat it for each student. If you're merely identifying how many students in the course have issues, then it would just be one line per course.

Let's say that you have 100 students taking 5 courses each from 20 courses. Of those 100 students, 15 have a grade of less than 60% in at least one course.

With the approach outline before, it would take 1 call to get the enrollment ID, 1 call to get the courses, and then 20 calls to get the enrollments within the courses. That's 22 calls all together.

If you can somehow identify the 15 students (not sure how many calls it took to do that, but the Admin Analytics might give you a start on it), but let's say it's only the 15 students involved in athletics that you need to check. Then you have 15 calls to get their enrollments for all classes and then 1 extra call for each unique course where they are underperforming. If there are fewer than 7 courses, you come out ahead by making the user-version call of the enrollments. Otherwise, the course-version is faster.

Now, all of that said, that's the old way I would do things. My newer way would be using GraphQL.

You can try this out interactively before turning it into API calls by adding /graphiql to the end of your dashboard.

Paste this into the editor in the middle section.

query coursesByTerm($termId: String, $c1: String) {
  term(sisId: $termId) {
    coursesConnection(first: 100, after: $c1) {
      nodes {
        _id
        sisId
        name
        usersConnectionCount(
          filter: {excludeTestStudents: true, enrollmentTypes: StudentEnrollment}
        )
      }
      pageInfo {
        hasNextPage
        endCursor
      }
    }
  }
}

Then add this to the variable section:

{
  "termId": "2025c",
  "c1": null
}

Note that this uses the SIS enrollment term ID rather than the Canvas enrollment term ID. My Fall 2025 term is "2025c". Be sure to modify it to match your data.

If you would rather use the Canvas enrollment term ID, then you would need to modify the first two lines of the query as such

query coursesByTerm($termId:ID!, $c1: String) {
  term(id: $termId) {

and then you would put the integer ID in the variables section.

After making the customizations, now click the big Run / Play button. You'll get an object that starts off with data.term.coursesConnect.nodes. That's an array that contains the Canvas ID (_id) of the course, the SIS ID (sisId) of the course, and the number of students, excluding the test student account, in the course. The name of the course is supplied so you can give it in your report.

{
  "data": {
    "term": {
      "coursesConnection": {
        "nodes": [
          {
            "_id": "7718261",
            "sisId": "fa25-math_121-01",
            "name": "MATH 121 - Calculus 1 (FA25)",
            "usersConnectionCount": 16
          },
... more nodes ...
          {
            "_id": "7561626",
            "sisId": "fa25-biol_220-y1",
            "name": "BIOL 220 (Y1) - Microbiology (FA25)",
            "usersConnectionCount": 18
          }
        ],
        "pageInfo": {
          "hasNextPage": true,
          "endCursor": "MTAw"
        }
      }
    }
  }
}

The SIS ID may not be helpful for you, but I can use it to match against a regular expression to tell which courses are academic courses and which aren't. You can also filter on the usersConnectionCount to make sure it's positive as there's no need to get the information for a course that has no students.

You might also want to include state as it will let you know if the course is published or not.

At the bottom with be pageInfo. If hasNextPage is true, then you need to worry about pagination. The endCursor "MTAw", which is a base-64 encoding of 100, gets placed into the variables section and then the call is made again to get the next 100 courses.

{
  "termId": "2025c",
  "c1": "MTAw"
}

If you have less than 100 students in every course, then you can actually get the student grades at the same time.

query coursesWithGradesByTerm($termId: String, $c1: String) {
  term(sisId: $termId) {
    coursesConnection(first: 100, after: $c1) {
      nodes {
        _id
        sisId
        name
        enrollmentsConnection(
          filter: {types: StudentEnrollment, states: active}
          first: 100
        ) {
          nodes {
            grades {
              currentScore
            }
            lastActivityAt
            totalActivityTime
            user {
              _id
              sisId
              sortableName
            }
          }
        }
      }
      pageInfo {
        hasNextPage
        endCursor
      }
    }
  }
}

I chose currentScore, but there are other options to pick from. I also included the lastActivityAt and totalActivityTime so I could see if the student was actively engaged in the course. The lastActivityAt is pretty good when the student spends a meaningful amount of time (it used to be 2 minutes or more) in the course, but the totalActivityTime is pretty worthless unless used in comparison to something else. I used to download this data every night so that I could see how it changed overtime. Otherwise, I have no idea whether the time was spent in the first week of class or the last day. Also, it tends to overcount sometimes (I suspect when multiple tabs are open).

If you do have more than 100 student enrollments in a class, then you would need to use a second cursor (my scheme is $c1 for the 1st cursor and $c2 for the second) and page info in the enrollmentsConnection. You could probably find a GraphQL library somewhere that handles multiple levels of pagination, but I don't use any of those libraries and so I wrote my code to just handle one level. If I need two levels, I break it up into multiple queries.

Note that more than 100 student enrollments may not be the same as 100 students. If you enroll students in 2 sections each, then 50 students would make 100 enrollments. We only enroll students in one section, so 1 student = 1 enrollment.

If you do have repeats, then sort that out in your code that analyzes and prepares the data.

You're going to have to do some filtering of the data to see which students have scores less than 60%.

Also note that the combined query is time consuming. You may want to only fetch 50 or 25 courses at a time and make multiple requests. If the request takes too many resources, Canvas may cancel it and you'll get nothing.

The JSON data that you get from the interactive GraphQL interface can be hard to follow by hand. I often paste it into a JSON to CSV converter like the one at ConvertCSV.com (it keeps the information in your browser rather than sending potentially confidential information to a server).

1. Select all the output from Canvas and paste it into the box for step 1.
2. For step 2, check the box for Pivot data down instead of flattening. This will give one row per student instead of one row per course. I also check the box so that the output field separator is a tab as this allows me to just copy/paste into Excel.
3. In step 3, Convert JSON to CSV. I would not use the JSON to Excel as it makes everything a string when it creates the Excel file. The results appear in a textbox and then an HTML table below that. You can copy/paste the textbox contents somewhere else.

The column names suck. For example, you get enrollmentsConnection/nodes/0/user/sortableName instead of just sortableName. You can clean those up afterwards if necessary.

The idea is not to use the graphiql interface on a regular basis, but to plan out what you need and then automate that. You can do that with the GraphQL API interface. The documentation isn't the best if you're using JSON for your variables object.

In JavaScript, I create an object that has the query and variables, then JSON encode it and make it the body to send. Here's some code that will hopefully be helpful no matter what programming language you're using.

const graphqlUrl = `${canvasInstance}/api/graphql`;
const apiToken = `Bearer ${superSecretAPIToken}`;
const query = `query coursesWithGradesByTerm ... `;
const variables = { termId: "2025c", c1: null };
const obj = { query: query, variables: variables };
const body = JSON.stringify(obj);
const options = {
  method: 'POST',
  headers: {
    'accept': 'application/json',
    'authorization': apiToken,
    'content-type': 'application/json',
    'content-length' : body.length,
  },
  body: body,
};
return fetch(grapqlUrl, options);

Again, there are probably libraries that will do all that for you. I just tend to not use libraries as much as possible so that people who use my code aren't locked into one particular technology -- or don't have to load a second technology to run my script when they're already using something else.