Analyze LiveSchool Behavior Data with Illuminate

Learn how to upload behavior data to Illuminate to access advanced reporting features.
In this guest post by Peter Setter, learn to combine your academic data with LiveSchool to create enhanced "360 degree" student reports.
Featuring 
Liveschool Team

Setting up Illuminate to Store LiveShool Data

One of Illuminate’s best features is access to their well-documented SQL back-end. Summary assessments allow you to create your own tables with your own schema. It’s this feature that I use to upload and store LiveSchool data in Illuminate. The steps below outline how to get started.

  1. After you log into Illuminate, click on Assessments then Create a New Summary Assessment
  1. Give the summary assessment a specific name. Since we’re a multi-site network, I use the name of the school and specify the month. Since the points log is exportable by month, I created a separate summary assessment for each month. Having a separate summary assessment for each month makes the SQL more cumbersome; however, it is easier to upload data on a regular basis.
  2. Click on Columns to set-up the fields you want to upload.
  • Name your columns the same as the column names in the points log. When you upload the points log, Illuminate will automatically match the columns, so you don’t need to by hand.
  • You can choose to add all the columns or a subset. When in doubt, add all of them, because you may have questions you want to answer later.
  • Remember to specify the correct column type. This makes your data easier to work with when you pull it later.

Uploading Points Logs

  1. After you download the points log, go to the summary assessment.
  2. If you are uploading a points log in a summary assessment where you have data, remember to remove all student responses. The alternative to removing all student responses is selecting DISTINCT record IDs in your query.
  3. Click Save and you’re ready to upload your first file.
  4. Use Duplicate Repository under Advanced to quickly make all the summary asseesments you’ll need for the year.
  1. Click on Responses and Import Student Responses from a File.
  1. The points log is a comma separated values file. Click on Add Student Data. If you choose Update Student Data you can only upload one record per student. Using Add allows unlimited records. Considering footnote 2, you could upload data without clearing it out first, but that will result in duplicating records each time you upload. Choose your file then click Upload File.
  1. The last step is to specify the Student ID column; you always need to do this manually. If the names of your summary assessment match that of the points log, they will be matched for you. Click Import and you’re done.

Getting Data Out of Illuminate

  1. If you don’t currently have Illuminate database access, you’ll need to contact Illuminate support to set this up. This is beyond the scope of this document, but the steps are:
  • Provide Illuminate with your IP address. Database access is location specific. If you want to pull data off-site, you’ll need to either provide this additional IP address or set-up a VPN.
  • Illuminate will send you your login information.
  • Download software to build and run your queries. Don't forget the drivers. I use the open-source pgAdminIII and R through the RODBC package.
  1. Ask Illuminate to update your repositories. Summary assessments have table names in the form of dna_repositories.respository_#. You can find the repository ID in the URL of the summary assessment. For example:dna/?action=index&repository_id=91&page=Repository_IndexController
  2. Now you’re ready to start pulling your data for analysis or reporting, such as Tableau, progress reports, or report cards.

Example Query

In the query below, I roll-up LiveSchool data which will be used in an elementary school report card.

WITH ls AS (    SELECT *    FROM (    SELECT *    FROM dna_repositories.repository_97    UNION    SELECT *    FROM dna_repositories.repository_99    UNION        SELECT *    FROM dna_repositories.repository_123    UNION        SELECT *    FROM dna_repositories.repository_124    UNION        SELECT *    FROM dna_repositories.repository_125    UNION        SELECT *    FROM dna_repositories.repository_126    UNION        SELECT *    FROM dna_repositories.repository_127    UNION        SELECT *    FROM dna_repositories.repository_128    UNION        SELECT *    FROM dna_repositories.repository_129        UNION        SELECT *    FROM dna_repositories.repository_130    UNION        SELECT *    FROM dna_repositories.repository_131    ) AS repo_all    )

I combine all the LiveSchool data in a WITH clause. Since I’m including all the repositories, I can use this code in all of my queries for this school. (As opposed to only using the queries for a particular month.)

SELECT local_student_id, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Homework' AND field_points > 0 THEN field_points ELSE 0 END) AS complete_homework, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Homework' AND field_points < 0 THEN field_points ELSE 0 END) AS incomplete_homework, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Be a Leader' AND field_points > 0 THEN field_points ELSE 0 END) AS glow_leader, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Be a Leader' AND field_points < 0 THEN field_points ELSE 0 END) AS grow_leader, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Work Hard' AND field_points > 0 THEN field_points ELSE 0 END) AS glow_workhard, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Work Hard' AND field_points < 0 THEN field_points ELSE 0 END) AS grow_workhard, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Be Kind' AND field_points > 0 THEN field_points ELSE 0 END) AS glow_bekind, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Be Kind' AND field_points < 0 THEN field_points ELSE 0 END) AS grow_bekind, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Community' THEN field_points ELSE 0 END) AS community, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Justice' THEN field_points ELSE 0 END) AS justice, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Love' THEN field_points ELSE 0 END) AS love, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Grow' THEN field_points ELSE 0 END) AS grow 

FROM ls INNER JOIN public.students s ON s.student_id = ls.student_id 

WHERE field_official_date BETWEEN '8/17/2015' AND '11/13/2015' 

GROUP BY local_student_id

Note that Illuminate affixes field_ to all your column names.

At our elementary school, each of the behaviors have a point value in its name, like Homework (5). This set-up allows teachers to enter values for the entire week rather than day-by-day. split_part divides the string. I use the first part to match the behavior name. If you’re data isn’t set-up this way, you can simply use: field_behavior == my_behavior.

local_student_id is the stuD from your district or state. student_id is the internal Illuminate ID.

Example Reports

Using this approach, we are able to create "360 degree" reports for our students and families. Below are the front and back of a sample report. You can see that we combine a variety of data sources to provide the fullest picture possible about a student's progress:

The front of our "360 degree" report card
The back of the report lists specific behavior recorded by teachers

Happy reporting!

Analyze LiveSchool Behavior Data with Illuminate

Learn how to upload behavior data to Illuminate to access advanced reporting features.
Chapter 
 | 
 🚀
 🥤

Setting up Illuminate to Store LiveShool Data

One of Illuminate’s best features is access to their well-documented SQL back-end. Summary assessments allow you to create your own tables with your own schema. It’s this feature that I use to upload and store LiveSchool data in Illuminate. The steps below outline how to get started.

  1. After you log into Illuminate, click on Assessments then Create a New Summary Assessment
  1. Give the summary assessment a specific name. Since we’re a multi-site network, I use the name of the school and specify the month. Since the points log is exportable by month, I created a separate summary assessment for each month. Having a separate summary assessment for each month makes the SQL more cumbersome; however, it is easier to upload data on a regular basis.
  2. Click on Columns to set-up the fields you want to upload.
  • Name your columns the same as the column names in the points log. When you upload the points log, Illuminate will automatically match the columns, so you don’t need to by hand.
  • You can choose to add all the columns or a subset. When in doubt, add all of them, because you may have questions you want to answer later.
  • Remember to specify the correct column type. This makes your data easier to work with when you pull it later.

Uploading Points Logs

  1. After you download the points log, go to the summary assessment.
  2. If you are uploading a points log in a summary assessment where you have data, remember to remove all student responses. The alternative to removing all student responses is selecting DISTINCT record IDs in your query.
  3. Click Save and you’re ready to upload your first file.
  4. Use Duplicate Repository under Advanced to quickly make all the summary asseesments you’ll need for the year.
  1. Click on Responses and Import Student Responses from a File.
  1. The points log is a comma separated values file. Click on Add Student Data. If you choose Update Student Data you can only upload one record per student. Using Add allows unlimited records. Considering footnote 2, you could upload data without clearing it out first, but that will result in duplicating records each time you upload. Choose your file then click Upload File.
  1. The last step is to specify the Student ID column; you always need to do this manually. If the names of your summary assessment match that of the points log, they will be matched for you. Click Import and you’re done.

Getting Data Out of Illuminate

  1. If you don’t currently have Illuminate database access, you’ll need to contact Illuminate support to set this up. This is beyond the scope of this document, but the steps are:
  • Provide Illuminate with your IP address. Database access is location specific. If you want to pull data off-site, you’ll need to either provide this additional IP address or set-up a VPN.
  • Illuminate will send you your login information.
  • Download software to build and run your queries. Don't forget the drivers. I use the open-source pgAdminIII and R through the RODBC package.
  1. Ask Illuminate to update your repositories. Summary assessments have table names in the form of dna_repositories.respository_#. You can find the repository ID in the URL of the summary assessment. For example:dna/?action=index&repository_id=91&page=Repository_IndexController
  2. Now you’re ready to start pulling your data for analysis or reporting, such as Tableau, progress reports, or report cards.

Example Query

In the query below, I roll-up LiveSchool data which will be used in an elementary school report card.

WITH ls AS (    SELECT *    FROM (    SELECT *    FROM dna_repositories.repository_97    UNION    SELECT *    FROM dna_repositories.repository_99    UNION        SELECT *    FROM dna_repositories.repository_123    UNION        SELECT *    FROM dna_repositories.repository_124    UNION        SELECT *    FROM dna_repositories.repository_125    UNION        SELECT *    FROM dna_repositories.repository_126    UNION        SELECT *    FROM dna_repositories.repository_127    UNION        SELECT *    FROM dna_repositories.repository_128    UNION        SELECT *    FROM dna_repositories.repository_129        UNION        SELECT *    FROM dna_repositories.repository_130    UNION        SELECT *    FROM dna_repositories.repository_131    ) AS repo_all    )

I combine all the LiveSchool data in a WITH clause. Since I’m including all the repositories, I can use this code in all of my queries for this school. (As opposed to only using the queries for a particular month.)

SELECT local_student_id, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Homework' AND field_points > 0 THEN field_points ELSE 0 END) AS complete_homework, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Homework' AND field_points < 0 THEN field_points ELSE 0 END) AS incomplete_homework, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Be a Leader' AND field_points > 0 THEN field_points ELSE 0 END) AS glow_leader, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Be a Leader' AND field_points < 0 THEN field_points ELSE 0 END) AS grow_leader, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Work Hard' AND field_points > 0 THEN field_points ELSE 0 END) AS glow_workhard, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Work Hard' AND field_points < 0 THEN field_points ELSE 0 END) AS grow_workhard, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Be Kind' AND field_points > 0 THEN field_points ELSE 0 END) AS glow_bekind, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Be Kind' AND field_points < 0 THEN field_points ELSE 0 END) AS grow_bekind, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Community' THEN field_points ELSE 0 END) AS community, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Justice' THEN field_points ELSE 0 END) AS justice, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Love' THEN field_points ELSE 0 END) AS love, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Grow' THEN field_points ELSE 0 END) AS grow 

FROM ls INNER JOIN public.students s ON s.student_id = ls.student_id 

WHERE field_official_date BETWEEN '8/17/2015' AND '11/13/2015' 

GROUP BY local_student_id

Note that Illuminate affixes field_ to all your column names.

At our elementary school, each of the behaviors have a point value in its name, like Homework (5). This set-up allows teachers to enter values for the entire week rather than day-by-day. split_part divides the string. I use the first part to match the behavior name. If you’re data isn’t set-up this way, you can simply use: field_behavior == my_behavior.

local_student_id is the stuD from your district or state. student_id is the internal Illuminate ID.

Example Reports

Using this approach, we are able to create "360 degree" reports for our students and families. Below are the front and back of a sample report. You can see that we combine a variety of data sources to provide the fullest picture possible about a student's progress:

The front of our "360 degree" report card
The back of the report lists specific behavior recorded by teachers

Happy reporting!

No items found.

Analyze LiveSchool Behavior Data with Illuminate

Learn how to upload behavior data to Illuminate to access advanced reporting features.
By 
Liveschool Team
 | 
April 11, 2016
Register Now

About the Event

Setting up Illuminate to Store LiveShool Data

One of Illuminate’s best features is access to their well-documented SQL back-end. Summary assessments allow you to create your own tables with your own schema. It’s this feature that I use to upload and store LiveSchool data in Illuminate. The steps below outline how to get started.

  1. After you log into Illuminate, click on Assessments then Create a New Summary Assessment
  1. Give the summary assessment a specific name. Since we’re a multi-site network, I use the name of the school and specify the month. Since the points log is exportable by month, I created a separate summary assessment for each month. Having a separate summary assessment for each month makes the SQL more cumbersome; however, it is easier to upload data on a regular basis.
  2. Click on Columns to set-up the fields you want to upload.
  • Name your columns the same as the column names in the points log. When you upload the points log, Illuminate will automatically match the columns, so you don’t need to by hand.
  • You can choose to add all the columns or a subset. When in doubt, add all of them, because you may have questions you want to answer later.
  • Remember to specify the correct column type. This makes your data easier to work with when you pull it later.

Uploading Points Logs

  1. After you download the points log, go to the summary assessment.
  2. If you are uploading a points log in a summary assessment where you have data, remember to remove all student responses. The alternative to removing all student responses is selecting DISTINCT record IDs in your query.
  3. Click Save and you’re ready to upload your first file.
  4. Use Duplicate Repository under Advanced to quickly make all the summary asseesments you’ll need for the year.
  1. Click on Responses and Import Student Responses from a File.
  1. The points log is a comma separated values file. Click on Add Student Data. If you choose Update Student Data you can only upload one record per student. Using Add allows unlimited records. Considering footnote 2, you could upload data without clearing it out first, but that will result in duplicating records each time you upload. Choose your file then click Upload File.
  1. The last step is to specify the Student ID column; you always need to do this manually. If the names of your summary assessment match that of the points log, they will be matched for you. Click Import and you’re done.

Getting Data Out of Illuminate

  1. If you don’t currently have Illuminate database access, you’ll need to contact Illuminate support to set this up. This is beyond the scope of this document, but the steps are:
  • Provide Illuminate with your IP address. Database access is location specific. If you want to pull data off-site, you’ll need to either provide this additional IP address or set-up a VPN.
  • Illuminate will send you your login information.
  • Download software to build and run your queries. Don't forget the drivers. I use the open-source pgAdminIII and R through the RODBC package.
  1. Ask Illuminate to update your repositories. Summary assessments have table names in the form of dna_repositories.respository_#. You can find the repository ID in the URL of the summary assessment. For example:dna/?action=index&repository_id=91&page=Repository_IndexController
  2. Now you’re ready to start pulling your data for analysis or reporting, such as Tableau, progress reports, or report cards.

Example Query

In the query below, I roll-up LiveSchool data which will be used in an elementary school report card.

WITH ls AS (    SELECT *    FROM (    SELECT *    FROM dna_repositories.repository_97    UNION    SELECT *    FROM dna_repositories.repository_99    UNION        SELECT *    FROM dna_repositories.repository_123    UNION        SELECT *    FROM dna_repositories.repository_124    UNION        SELECT *    FROM dna_repositories.repository_125    UNION        SELECT *    FROM dna_repositories.repository_126    UNION        SELECT *    FROM dna_repositories.repository_127    UNION        SELECT *    FROM dna_repositories.repository_128    UNION        SELECT *    FROM dna_repositories.repository_129        UNION        SELECT *    FROM dna_repositories.repository_130    UNION        SELECT *    FROM dna_repositories.repository_131    ) AS repo_all    )

I combine all the LiveSchool data in a WITH clause. Since I’m including all the repositories, I can use this code in all of my queries for this school. (As opposed to only using the queries for a particular month.)

SELECT local_student_id, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Homework' AND field_points > 0 THEN field_points ELSE 0 END) AS complete_homework, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Homework' AND field_points < 0 THEN field_points ELSE 0 END) AS incomplete_homework, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Be a Leader' AND field_points > 0 THEN field_points ELSE 0 END) AS glow_leader, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Be a Leader' AND field_points < 0 THEN field_points ELSE 0 END) AS grow_leader, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Work Hard' AND field_points > 0 THEN field_points ELSE 0 END) AS glow_workhard, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Work Hard' AND field_points < 0 THEN field_points ELSE 0 END) AS grow_workhard, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Be Kind' AND field_points > 0 THEN field_points ELSE 0 END) AS glow_bekind, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Be Kind' AND field_points < 0 THEN field_points ELSE 0 END) AS grow_bekind, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Community' THEN field_points ELSE 0 END) AS community, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Justice' THEN field_points ELSE 0 END) AS justice, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Love' THEN field_points ELSE 0 END) AS love, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Grow' THEN field_points ELSE 0 END) AS grow 

FROM ls INNER JOIN public.students s ON s.student_id = ls.student_id 

WHERE field_official_date BETWEEN '8/17/2015' AND '11/13/2015' 

GROUP BY local_student_id

Note that Illuminate affixes field_ to all your column names.

At our elementary school, each of the behaviors have a point value in its name, like Homework (5). This set-up allows teachers to enter values for the entire week rather than day-by-day. split_part divides the string. I use the first part to match the behavior name. If you’re data isn’t set-up this way, you can simply use: field_behavior == my_behavior.

local_student_id is the stuD from your district or state. student_id is the internal Illuminate ID.

Example Reports

Using this approach, we are able to create "360 degree" reports for our students and families. Below are the front and back of a sample report. You can see that we combine a variety of data sources to provide the fullest picture possible about a student's progress:

The front of our "360 degree" report card
The back of the report lists specific behavior recorded by teachers

Happy reporting!

Register Now

About the Event

Setting up Illuminate to Store LiveShool Data

One of Illuminate’s best features is access to their well-documented SQL back-end. Summary assessments allow you to create your own tables with your own schema. It’s this feature that I use to upload and store LiveSchool data in Illuminate. The steps below outline how to get started.

  1. After you log into Illuminate, click on Assessments then Create a New Summary Assessment
  1. Give the summary assessment a specific name. Since we’re a multi-site network, I use the name of the school and specify the month. Since the points log is exportable by month, I created a separate summary assessment for each month. Having a separate summary assessment for each month makes the SQL more cumbersome; however, it is easier to upload data on a regular basis.
  2. Click on Columns to set-up the fields you want to upload.
  • Name your columns the same as the column names in the points log. When you upload the points log, Illuminate will automatically match the columns, so you don’t need to by hand.
  • You can choose to add all the columns or a subset. When in doubt, add all of them, because you may have questions you want to answer later.
  • Remember to specify the correct column type. This makes your data easier to work with when you pull it later.

Uploading Points Logs

  1. After you download the points log, go to the summary assessment.
  2. If you are uploading a points log in a summary assessment where you have data, remember to remove all student responses. The alternative to removing all student responses is selecting DISTINCT record IDs in your query.
  3. Click Save and you’re ready to upload your first file.
  4. Use Duplicate Repository under Advanced to quickly make all the summary asseesments you’ll need for the year.
  1. Click on Responses and Import Student Responses from a File.
  1. The points log is a comma separated values file. Click on Add Student Data. If you choose Update Student Data you can only upload one record per student. Using Add allows unlimited records. Considering footnote 2, you could upload data without clearing it out first, but that will result in duplicating records each time you upload. Choose your file then click Upload File.
  1. The last step is to specify the Student ID column; you always need to do this manually. If the names of your summary assessment match that of the points log, they will be matched for you. Click Import and you’re done.

Getting Data Out of Illuminate

  1. If you don’t currently have Illuminate database access, you’ll need to contact Illuminate support to set this up. This is beyond the scope of this document, but the steps are:
  • Provide Illuminate with your IP address. Database access is location specific. If you want to pull data off-site, you’ll need to either provide this additional IP address or set-up a VPN.
  • Illuminate will send you your login information.
  • Download software to build and run your queries. Don't forget the drivers. I use the open-source pgAdminIII and R through the RODBC package.
  1. Ask Illuminate to update your repositories. Summary assessments have table names in the form of dna_repositories.respository_#. You can find the repository ID in the URL of the summary assessment. For example:dna/?action=index&repository_id=91&page=Repository_IndexController
  2. Now you’re ready to start pulling your data for analysis or reporting, such as Tableau, progress reports, or report cards.

Example Query

In the query below, I roll-up LiveSchool data which will be used in an elementary school report card.

WITH ls AS (    SELECT *    FROM (    SELECT *    FROM dna_repositories.repository_97    UNION    SELECT *    FROM dna_repositories.repository_99    UNION        SELECT *    FROM dna_repositories.repository_123    UNION        SELECT *    FROM dna_repositories.repository_124    UNION        SELECT *    FROM dna_repositories.repository_125    UNION        SELECT *    FROM dna_repositories.repository_126    UNION        SELECT *    FROM dna_repositories.repository_127    UNION        SELECT *    FROM dna_repositories.repository_128    UNION        SELECT *    FROM dna_repositories.repository_129        UNION        SELECT *    FROM dna_repositories.repository_130    UNION        SELECT *    FROM dna_repositories.repository_131    ) AS repo_all    )

I combine all the LiveSchool data in a WITH clause. Since I’m including all the repositories, I can use this code in all of my queries for this school. (As opposed to only using the queries for a particular month.)

SELECT local_student_id, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Homework' AND field_points > 0 THEN field_points ELSE 0 END) AS complete_homework, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Homework' AND field_points < 0 THEN field_points ELSE 0 END) AS incomplete_homework, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Be a Leader' AND field_points > 0 THEN field_points ELSE 0 END) AS glow_leader, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Be a Leader' AND field_points < 0 THEN field_points ELSE 0 END) AS grow_leader, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Work Hard' AND field_points > 0 THEN field_points ELSE 0 END) AS glow_workhard, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Work Hard' AND field_points < 0 THEN field_points ELSE 0 END) AS grow_workhard, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Be Kind' AND field_points > 0 THEN field_points ELSE 0 END) AS glow_bekind, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Be Kind' AND field_points < 0 THEN field_points ELSE 0 END) AS grow_bekind, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Community' THEN field_points ELSE 0 END) AS community, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Justice' THEN field_points ELSE 0 END) AS justice, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Love' THEN field_points ELSE 0 END) AS love, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Grow' THEN field_points ELSE 0 END) AS grow 

FROM ls INNER JOIN public.students s ON s.student_id = ls.student_id 

WHERE field_official_date BETWEEN '8/17/2015' AND '11/13/2015' 

GROUP BY local_student_id

Note that Illuminate affixes field_ to all your column names.

At our elementary school, each of the behaviors have a point value in its name, like Homework (5). This set-up allows teachers to enter values for the entire week rather than day-by-day. split_part divides the string. I use the first part to match the behavior name. If you’re data isn’t set-up this way, you can simply use: field_behavior == my_behavior.

local_student_id is the stuD from your district or state. student_id is the internal Illuminate ID.

Example Reports

Using this approach, we are able to create "360 degree" reports for our students and families. Below are the front and back of a sample report. You can see that we combine a variety of data sources to provide the fullest picture possible about a student's progress:

The front of our "360 degree" report card
The back of the report lists specific behavior recorded by teachers

Happy reporting!

About the Presenter

You know what they teamwork makes the dream work. These articles have been written by the wonderful members of our team.

Analyze LiveSchool Behavior Data with Illuminate

Learn how to upload behavior data to Illuminate to access advanced reporting features.
By 
Liveschool Team
 | 
April 11, 2016

In this guest post by Peter Setter, learn to combine your academic data with LiveSchool to create enhanced "360 degree" student reports.

Setting up Illuminate to Store LiveShool Data

One of Illuminate’s best features is access to their well-documented SQL back-end. Summary assessments allow you to create your own tables with your own schema. It’s this feature that I use to upload and store LiveSchool data in Illuminate. The steps below outline how to get started.

  1. After you log into Illuminate, click on Assessments then Create a New Summary Assessment
  1. Give the summary assessment a specific name. Since we’re a multi-site network, I use the name of the school and specify the month. Since the points log is exportable by month, I created a separate summary assessment for each month. Having a separate summary assessment for each month makes the SQL more cumbersome; however, it is easier to upload data on a regular basis.
  2. Click on Columns to set-up the fields you want to upload.
  • Name your columns the same as the column names in the points log. When you upload the points log, Illuminate will automatically match the columns, so you don’t need to by hand.
  • You can choose to add all the columns or a subset. When in doubt, add all of them, because you may have questions you want to answer later.
  • Remember to specify the correct column type. This makes your data easier to work with when you pull it later.

Uploading Points Logs

  1. After you download the points log, go to the summary assessment.
  2. If you are uploading a points log in a summary assessment where you have data, remember to remove all student responses. The alternative to removing all student responses is selecting DISTINCT record IDs in your query.
  3. Click Save and you’re ready to upload your first file.
  4. Use Duplicate Repository under Advanced to quickly make all the summary asseesments you’ll need for the year.
  1. Click on Responses and Import Student Responses from a File.
  1. The points log is a comma separated values file. Click on Add Student Data. If you choose Update Student Data you can only upload one record per student. Using Add allows unlimited records. Considering footnote 2, you could upload data without clearing it out first, but that will result in duplicating records each time you upload. Choose your file then click Upload File.
  1. The last step is to specify the Student ID column; you always need to do this manually. If the names of your summary assessment match that of the points log, they will be matched for you. Click Import and you’re done.

Getting Data Out of Illuminate

  1. If you don’t currently have Illuminate database access, you’ll need to contact Illuminate support to set this up. This is beyond the scope of this document, but the steps are:
  • Provide Illuminate with your IP address. Database access is location specific. If you want to pull data off-site, you’ll need to either provide this additional IP address or set-up a VPN.
  • Illuminate will send you your login information.
  • Download software to build and run your queries. Don't forget the drivers. I use the open-source pgAdminIII and R through the RODBC package.
  1. Ask Illuminate to update your repositories. Summary assessments have table names in the form of dna_repositories.respository_#. You can find the repository ID in the URL of the summary assessment. For example:dna/?action=index&repository_id=91&page=Repository_IndexController
  2. Now you’re ready to start pulling your data for analysis or reporting, such as Tableau, progress reports, or report cards.

Example Query

In the query below, I roll-up LiveSchool data which will be used in an elementary school report card.

WITH ls AS (    SELECT *    FROM (    SELECT *    FROM dna_repositories.repository_97    UNION    SELECT *    FROM dna_repositories.repository_99    UNION        SELECT *    FROM dna_repositories.repository_123    UNION        SELECT *    FROM dna_repositories.repository_124    UNION        SELECT *    FROM dna_repositories.repository_125    UNION        SELECT *    FROM dna_repositories.repository_126    UNION        SELECT *    FROM dna_repositories.repository_127    UNION        SELECT *    FROM dna_repositories.repository_128    UNION        SELECT *    FROM dna_repositories.repository_129        UNION        SELECT *    FROM dna_repositories.repository_130    UNION        SELECT *    FROM dna_repositories.repository_131    ) AS repo_all    )

I combine all the LiveSchool data in a WITH clause. Since I’m including all the repositories, I can use this code in all of my queries for this school. (As opposed to only using the queries for a particular month.)

SELECT local_student_id, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Homework' AND field_points > 0 THEN field_points ELSE 0 END) AS complete_homework, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Homework' AND field_points < 0 THEN field_points ELSE 0 END) AS incomplete_homework, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Be a Leader' AND field_points > 0 THEN field_points ELSE 0 END) AS glow_leader, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Be a Leader' AND field_points < 0 THEN field_points ELSE 0 END) AS grow_leader, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Work Hard' AND field_points > 0 THEN field_points ELSE 0 END) AS glow_workhard, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Work Hard' AND field_points < 0 THEN field_points ELSE 0 END) AS grow_workhard, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Be Kind' AND field_points > 0 THEN field_points ELSE 0 END) AS glow_bekind, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Be Kind' AND field_points < 0 THEN field_points ELSE 0 END) AS grow_bekind, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Community' THEN field_points ELSE 0 END) AS community, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Justice' THEN field_points ELSE 0 END) AS justice, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Love' THEN field_points ELSE 0 END) AS love, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Grow' THEN field_points ELSE 0 END) AS grow 

FROM ls INNER JOIN public.students s ON s.student_id = ls.student_id 

WHERE field_official_date BETWEEN '8/17/2015' AND '11/13/2015' 

GROUP BY local_student_id

Note that Illuminate affixes field_ to all your column names.

At our elementary school, each of the behaviors have a point value in its name, like Homework (5). This set-up allows teachers to enter values for the entire week rather than day-by-day. split_part divides the string. I use the first part to match the behavior name. If you’re data isn’t set-up this way, you can simply use: field_behavior == my_behavior.

local_student_id is the stuD from your district or state. student_id is the internal Illuminate ID.

Example Reports

Using this approach, we are able to create "360 degree" reports for our students and families. Below are the front and back of a sample report. You can see that we combine a variety of data sources to provide the fullest picture possible about a student's progress:

The front of our "360 degree" report card
The back of the report lists specific behavior recorded by teachers

Happy reporting!

All Reward Ideas for Students

🎉
👑
🎁
The A-List
Grades 6-12
School
Event
Low Cost/DIY
🎉
👑
🎁
Class Pet
Grades K-12
Student
Privilege
Deluxe
🎉
👑
🎁
Hat Pass
Grades 6-12
Student
Privilege
Free
🎉
👑
🎁
Bonfire
Grades 9-12
Class/House
Event
Deluxe
🎉
👑
🎁
Student Messenger
Grades 3-8
Student
Privilege
Free
🎉
👑
🎁
Silent Disco
Grades 6-12
Class/House
Event
Low Cost/DIY
🎉
👑
🎁
Line Leader
Grades K-5
Student
Privilege
Free
🎉
👑
🎁
Class Book
Grades K-5
Student
Privilege
Free
🎉
👑
🎁
Stickers
Grades K-5
Student
Tangible
Low Cost/DIY
🎉
👑
🎁
Silly Science Experiments
Grades K-5
Class/House
Privilege
Low Cost/DIY
🎉
👑
🎁
Talk Time
Grades 6-8
Student
Privilege
Free
🎉
👑
🎁
Stairway Messages
Grades 9-12
Class/House
Event
Free
🎉
👑
🎁
Blood Drive
Grades 9-12
School
Event
Free
🎉
👑
🎁
Create the Seating Chart
Grades 6-12
Student
Privilege
Free
🎉
👑
🎁
Virtual Field Trip
Grades K-12
Class/House
Privilege
Free
🎉
👑
🎁
Trunk or Treat
Grades K-8
School
Event
Low Cost/DIY

All Reward Ideas for Elementary School Students

🎉
👑
🎁
Show & Tell
Grades K-8
Student
Privilege
Free
🎉
👑
🎁
Tech Time
Grades 3-12
Student
Privilege
Free
🎉
👑
🎁
Picnic Lunch
Grades K-12
Class/House
Privilege
Low Cost/DIY
🎉
👑
🎁
Seating Choice
Grades K-12
Student
Privilege
Free
🎉
👑
🎁
Podcast
Grades 3-12
Student
Privilege
Free
🎉
👑
🎁
Theme Party
Grades K-8
Class/House
Event
Low Cost/DIY
🎉
👑
🎁
Movie Posters
Grades 3-12
Student
Tangible
Low Cost/DIY
🎉
👑
🎁
Board Game Party
Grades 3-12
Class/House
Privilege
Low Cost/DIY
🎉
👑
🎁
Snack Party
Grades 3-12
Class/House
Privilege
Low Cost/DIY
🎉
👑
🎁
Special Pen
Grades K-5
Student
Privilege
Low Cost/DIY
🎉
👑
🎁
Teacher Q&A
Grades K-12
Class/House
Privilege
Free
🎉
👑
🎁
Stickers
Grades K-5
Student
Tangible
Low Cost/DIY
🎉
👑
🎁
Morning Meeting Leader
Grades 3-8
Student
Privilege
Free
🎉
👑
🎁
Extra Reading Time
Grades K-5
Student
Privilege
Free
🎉
👑
🎁
Drop Lowest Quiz
Grades 3-12
Student
Privilege
Free
🎉
👑
🎁
Virtual Field Trip
Grades K-12
Class/House
Privilege
Free

All Event Ideas for Schools

💰
🎨
Karaoke Night
💰
🎨
Dance Party
💰
🎨
STEM Field Day
💰
🎨
The Love Soiree
💰
🎨
The A-List
💰
🎨
Final Fridays
💰
🎨
Glow Party
💰
🎨
Theme Party
💰
🎨
Camp Read Away
💰
🎨
Blood Drive
💰
🎨
Meme Party

All Free Reward Ideas for Schools

🎉
👑
🎁
Be a Comedian.
🎉
👑
🎁
Stairway Messages
🎉
👑
🎁
Lunch Concert
🎉
👑
🎁
Class Book
🎉
👑
🎁
School Assembly
🎉
👑
🎁
Free Dress
🎉
👑
🎁
Camp Read Away
🎉
👑
🎁
Locker Choice
🎉
👑
🎁
Podcast
🎉
👑
🎁
Extra Reading Time

All Reward Ideas for High School Students

🎉
👑
🎁
Graduation Celebration
Grades 6-12
School
Event
Deluxe
🎉
👑
🎁
Extra Computer Games
Grades 3-12
Student
Privilege
Free
🎉
👑
🎁
Snack Party
Grades 3-12
Class/House
Privilege
Low Cost/DIY
🎉
👑
🎁
Video Game Rewards
Grades 3-12
Student
Privilege
Low Cost/DIY
🎉
👑
🎁
Movie Posters
Grades 3-12
Student
Tangible
Low Cost/DIY
🎉
👑
🎁
Free Dress
Grades K-12
Student
Privilege
Free
🎉
👑
🎁
Movie Night
Grades 9-12
Student
Event
Low Cost/DIY
🎉
👑
🎁
Game Week
Grades 9-12
School
Event
Low Cost/DIY
🎉
👑
🎁
Fake The Funk
Grades 9-12
School
Event
Low Cost/DIY
🎉
👑
🎁
Books
Grades K-12
Student
Tangible
Low Cost/DIY
🎉
👑
🎁
Art Contest
Grades 3-12
Class/House
Event
Free
🎉
👑
🎁
Digital Escape Rooms
Grades 6-12
Class/House
Privilege
Deluxe
🎉
👑
🎁
Certificate of Achievement
Grades K-12
Student
Privilege
Free
🎉
👑
🎁
Awards Show Afterparty
Grades 6-12
School
Event
Free
🎉
👑
🎁
Emcee the Announcements
Grades 6-12
Student
Privilege
Free
🎉
👑
🎁
Parking Spots
Grades 9-12
Student
Privilege
Free

All Reward Ideas for Middle School Students

🎉
👑
🎁
Board Game Party
Grades 3-12
Class/House
Privilege
Low Cost/DIY
🎉
👑
🎁
Virtual Talent Show. 🎤
Grades 3-12
Class/House
Event
Free
🎉
👑
🎁
Partner Work
Grades 3-12
Student
Privilege
Free
🎉
👑
🎁
Family Feast
Grades K-8
Class/House
Event
Deluxe
🎉
👑
🎁
Teacher v Student Competition
Grades 6-12
School
Privilege
Free
🎉
👑
🎁
Art Contest
Grades 3-12
Class/House
Event
Free
🎉
👑
🎁
Design the Bulletin Board
Grades K-12
Class/House
Privilege
Low Cost/DIY
🎉
👑
🎁
Talk Time
Grades 6-8
Student
Privilege
Free
🎉
👑
🎁
Old School Cookout
Grades 6-12
Class/House
Event
Deluxe
🎉
👑
🎁
Meet the Teacher
Grades K-8
School
Event
Free
🎉
👑
🎁
Read Across America
Grades K-8
School
Event
Free
🎉
👑
🎁
Sports Tickets
Grades 3-12
Student
Tangible
Deluxe
🎉
👑
🎁
Create the Seating Chart
Grades 6-12
Student
Privilege
Free
🎉
👑
🎁
Ice Cream Sundae Party
Grades K-12
Class/House
Event
Deluxe
🎉
👑
🎁
Theme Party
Grades K-8
Class/House
Event
Low Cost/DIY
🎉
👑
🎁
Seating Choice
Grades K-12
Student
Privilege
Free

All Student Reward & Incentive Ideas

💰
🎨
Assist the Custodian.
Grades 6-8
Student
Privilege
Free
💰
🎨
Class Jobs
Grades 3-8
Student
Privilege
Free
💰
🎨
Lunch Concert
Grades 6-8
Class/House
Privilege
Free
💰
🎨
Drop Lowest Quiz
Grades 3-12
Student
Privilege
Free
💰
🎨
Seating Choice
Grades K-12
Student
Privilege
Free
💰
🎨
Teacher for the Day
Grades 6-12
Student
Privilege
Free
💰
🎨
Homework Pass
Grades 6-12
Student
Privilege
Free
💰
🎨
Social Media Reporter
Grades 3-12
Student
Privilege
Free
💰
🎨
Donate $1
Grades 3-12
Student
Privilege
Low Cost/DIY
💰
🎨
Dress Up or Down Day
Grades 3-12
Student
Privilege
Free
💰
🎨
Brain Break
Grades K-12
Student
Privilege
Free
💰
🎨
Create the Seating Chart
Grades 6-12
Student
Privilege
Free
💰
🎨
Video Game Rewards
Grades 3-12
Student
Privilege
Low Cost/DIY
💰
🎨
Design the Bulletin Board
Grades K-12
Class/House
Privilege
Low Cost/DIY
💰
🎨
Teacher v Student Competition
Grades 6-12
School
Privilege
Free
💰
🎨
Stickers
Grades K-5
Student
Tangible
Low Cost/DIY

All Virtual Reward Ideas for Schools

🎉
👑
🎁
Positive Note or Call Home
Grades K-12
Student
Privilege
Free
🎉
👑
🎁
Teacher Q&A
Grades K-12
Class/House
Privilege
Free
🎉
👑
🎁
Certificate of Achievement
Grades K-12
Student
Privilege
Free
🎉
👑
🎁
Digital Escape Rooms
Grades 6-12
Class/House
Privilege
Deluxe
🎉
👑
🎁
Virtual Field Trip
Grades K-12
Class/House
Privilege
Free
🎉
👑
🎁
Podcast
Grades 3-12
Student
Privilege
Free
🎉
👑
🎁
Virtual Talent Show. 🎤
Grades 3-12
Class/House
Event
Free
🎉
👑
🎁
Extra Computer Games
Grades 3-12
Student
Privilege
Free
🎉
👑
🎁
Morning Meeting Leader
Grades 3-8
Student
Privilege
Free
🎉
👑
🎁
Show & Tell
Grades K-8
Student
Privilege
Free
🎉
👑
🎁
Stickers
Grades K-5
Student
Tangible
Low Cost/DIY
🎉
👑
🎁
Brain Break
Grades K-12
Student
Privilege
Free
🎉
👑
🎁
Donate $1
Grades 3-12
Student
Privilege
Low Cost/DIY
🎉
👑
🎁
Classroom DJ
Grades K-12
Student
Privilege
Free
🎉
👑
🎁
Social Media Reporter
Grades 3-12
Student
Privilege
Free
🎉
👑
🎁
Host a Virtual Party. 🎶
Grades 6-12
Class/House
Event
Free
See all Rewards

Want more ideas?

Rewards that Rock 🎸 has 100+ rewards, incentives, and event ideas to build your school culture.
Find Rewards
Learn more about the author, 
Liveschool Team
 

Analyze LiveSchool Behavior Data with Illuminate

Learn how to upload behavior data to Illuminate to access advanced reporting features.
By 
Liveschool Team
 | 
April 11, 2016

In this guest post by Peter Setter, learn to combine your academic data with LiveSchool to create enhanced "360 degree" student reports.

Setting up Illuminate to Store LiveShool Data

One of Illuminate’s best features is access to their well-documented SQL back-end. Summary assessments allow you to create your own tables with your own schema. It’s this feature that I use to upload and store LiveSchool data in Illuminate. The steps below outline how to get started.

  1. After you log into Illuminate, click on Assessments then Create a New Summary Assessment
  1. Give the summary assessment a specific name. Since we’re a multi-site network, I use the name of the school and specify the month. Since the points log is exportable by month, I created a separate summary assessment for each month. Having a separate summary assessment for each month makes the SQL more cumbersome; however, it is easier to upload data on a regular basis.
  2. Click on Columns to set-up the fields you want to upload.
  • Name your columns the same as the column names in the points log. When you upload the points log, Illuminate will automatically match the columns, so you don’t need to by hand.
  • You can choose to add all the columns or a subset. When in doubt, add all of them, because you may have questions you want to answer later.
  • Remember to specify the correct column type. This makes your data easier to work with when you pull it later.

Uploading Points Logs

  1. After you download the points log, go to the summary assessment.
  2. If you are uploading a points log in a summary assessment where you have data, remember to remove all student responses. The alternative to removing all student responses is selecting DISTINCT record IDs in your query.
  3. Click Save and you’re ready to upload your first file.
  4. Use Duplicate Repository under Advanced to quickly make all the summary asseesments you’ll need for the year.
  1. Click on Responses and Import Student Responses from a File.
  1. The points log is a comma separated values file. Click on Add Student Data. If you choose Update Student Data you can only upload one record per student. Using Add allows unlimited records. Considering footnote 2, you could upload data without clearing it out first, but that will result in duplicating records each time you upload. Choose your file then click Upload File.
  1. The last step is to specify the Student ID column; you always need to do this manually. If the names of your summary assessment match that of the points log, they will be matched for you. Click Import and you’re done.

Getting Data Out of Illuminate

  1. If you don’t currently have Illuminate database access, you’ll need to contact Illuminate support to set this up. This is beyond the scope of this document, but the steps are:
  • Provide Illuminate with your IP address. Database access is location specific. If you want to pull data off-site, you’ll need to either provide this additional IP address or set-up a VPN.
  • Illuminate will send you your login information.
  • Download software to build and run your queries. Don't forget the drivers. I use the open-source pgAdminIII and R through the RODBC package.
  1. Ask Illuminate to update your repositories. Summary assessments have table names in the form of dna_repositories.respository_#. You can find the repository ID in the URL of the summary assessment. For example:dna/?action=index&repository_id=91&page=Repository_IndexController
  2. Now you’re ready to start pulling your data for analysis or reporting, such as Tableau, progress reports, or report cards.

Example Query

In the query below, I roll-up LiveSchool data which will be used in an elementary school report card.

WITH ls AS (    SELECT *    FROM (    SELECT *    FROM dna_repositories.repository_97    UNION    SELECT *    FROM dna_repositories.repository_99    UNION        SELECT *    FROM dna_repositories.repository_123    UNION        SELECT *    FROM dna_repositories.repository_124    UNION        SELECT *    FROM dna_repositories.repository_125    UNION        SELECT *    FROM dna_repositories.repository_126    UNION        SELECT *    FROM dna_repositories.repository_127    UNION        SELECT *    FROM dna_repositories.repository_128    UNION        SELECT *    FROM dna_repositories.repository_129        UNION        SELECT *    FROM dna_repositories.repository_130    UNION        SELECT *    FROM dna_repositories.repository_131    ) AS repo_all    )

I combine all the LiveSchool data in a WITH clause. Since I’m including all the repositories, I can use this code in all of my queries for this school. (As opposed to only using the queries for a particular month.)

SELECT local_student_id, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Homework' AND field_points > 0 THEN field_points ELSE 0 END) AS complete_homework, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Homework' AND field_points < 0 THEN field_points ELSE 0 END) AS incomplete_homework, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Be a Leader' AND field_points > 0 THEN field_points ELSE 0 END) AS glow_leader, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Be a Leader' AND field_points < 0 THEN field_points ELSE 0 END) AS grow_leader, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Work Hard' AND field_points > 0 THEN field_points ELSE 0 END) AS glow_workhard, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Work Hard' AND field_points < 0 THEN field_points ELSE 0 END) AS grow_workhard, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Be Kind' AND field_points > 0 THEN field_points ELSE 0 END) AS glow_bekind, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Be Kind' AND field_points < 0 THEN field_points ELSE 0 END) AS grow_bekind, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Community' THEN field_points ELSE 0 END) AS community, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Justice' THEN field_points ELSE 0 END) AS justice, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Love' THEN field_points ELSE 0 END) AS love, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Grow' THEN field_points ELSE 0 END) AS grow 

FROM ls INNER JOIN public.students s ON s.student_id = ls.student_id 

WHERE field_official_date BETWEEN '8/17/2015' AND '11/13/2015' 

GROUP BY local_student_id

Note that Illuminate affixes field_ to all your column names.

At our elementary school, each of the behaviors have a point value in its name, like Homework (5). This set-up allows teachers to enter values for the entire week rather than day-by-day. split_part divides the string. I use the first part to match the behavior name. If you’re data isn’t set-up this way, you can simply use: field_behavior == my_behavior.

local_student_id is the stuD from your district or state. student_id is the internal Illuminate ID.

Example Reports

Using this approach, we are able to create "360 degree" reports for our students and families. Below are the front and back of a sample report. You can see that we combine a variety of data sources to provide the fullest picture possible about a student's progress:

The front of our "360 degree" report card
The back of the report lists specific behavior recorded by teachers

Happy reporting!

All Reward Ideas for Students

🎉
👑
🎁
School Spirit Day
Grades K-12
School
Privilege
Free
🎉
👑
🎁
Teacher v Student Competition
Grades 6-12
School
Privilege
Free
🎉
👑
🎁
Classroom DJ
Grades K-12
Student
Privilege
Free
🎉
👑
🎁
Anime Themed Party
Grades 6-12
School
Event
Low Cost/DIY
🎉
👑
🎁
Class Book
Grades K-5
Student
Privilege
Free
🎉
👑
🎁
The Love Soiree
Grades 6-12
School
Event
Low Cost/DIY
🎉
👑
🎁
Special Pen
Grades K-5
Student
Privilege
Low Cost/DIY
🎉
👑
🎁
STEM Field Day
Grades K-8
School
Event
Low Cost/DIY
🎉
👑
🎁
Brain Break
Grades K-12
Student
Privilege
Free
🎉
👑
🎁
Awards Show Afterparty
Grades 6-12
School
Event
Free
🎉
👑
🎁
Career Day
Grades 3-12
School
Event
Low Cost/DIY
🎉
👑
🎁
Class Pet
Grades K-12
Student
Privilege
Deluxe
🎉
👑
🎁
Kickback Vibes
Grades 6-12
Class/House
Event
Low Cost/DIY
🎉
👑
🎁
Drop Lowest Quiz
Grades 3-12
Student
Privilege
Free
🎉
👑
🎁
Snack Party
Grades 3-12
Class/House
Privilege
Low Cost/DIY
🎉
👑
🎁
School Assembly
Grades K-12
Student
Privilege
Free

All Reward Ideas for Elementary School Students

🎉
👑
🎁
Final Fridays
Grades K-8
School
Event
Low Cost/DIY
🎉
👑
🎁
Student Messenger
Grades 3-8
Student
Privilege
Free
🎉
👑
🎁
Family Feast
Grades K-8
Class/House
Event
Deluxe
🎉
👑
🎁
Theme Party
Grades K-8
Class/House
Event
Low Cost/DIY
🎉
👑
🎁
Seating Choice
Grades K-12
Student
Privilege
Free
🎉
👑
🎁
Class Pet
Grades K-12
Student
Privilege
Deluxe
🎉
👑
🎁
Dress Up or Down Day
Grades 3-12
Student
Privilege
Free
🎉
👑
🎁
Movie Posters
Grades 3-12
Student
Tangible
Low Cost/DIY
🎉
👑
🎁
Career Day
Grades 3-12
School
Event
Low Cost/DIY
🎉
👑
🎁
Picnic Lunch
Grades K-12
Class/House
Privilege
Low Cost/DIY
🎉
👑
🎁
Free Dress
Grades K-12
Student
Privilege
Free
🎉
👑
🎁
Camp Read Away
Grades K-8
Class/House
Event
Free
🎉
👑
🎁
Sports Tickets
Grades 3-12
Student
Tangible
Deluxe
🎉
👑
🎁
Silly Science Experiments
Grades K-5
Class/House
Privilege
Low Cost/DIY
🎉
👑
🎁
Trunk or Treat
Grades K-8
School
Event
Low Cost/DIY
🎉
👑
🎁
Trip to the Treasure Box
Grades K-5
Student
Privilege
Low Cost/DIY

All Event Ideas for Schools

💰
🎨
Art Contest
💰
🎨
Silent Disco
💰
🎨
STEM Field Day
💰
🎨
The Love Soiree
💰
🎨
Glow Party
💰
🎨
Trunk or Treat
💰
🎨
Dance Party
💰
🎨
Kickback Vibes
💰
🎨
Meme Party
💰
🎨
Bonfire
💰
🎨
Music Fest
💰
🎨
Game Week
💰
🎨
Final Fridays

All Free Reward Ideas for Schools

🎉
👑
🎁
Meme Party
🎉
👑
🎁
Class Jobs
🎉
👑
🎁
Talk Time
🎉
👑
🎁
Tutor
🎉
👑
🎁
Free Dress
🎉
👑
🎁
School Spirit Day
🎉
👑
🎁
Virtual Field Trip
🎉
👑
🎁
Operate Equipment.

All Reward Ideas for High School Students

🎉
👑
🎁
Class Pet
Grades K-12
Student
Privilege
Deluxe
🎉
👑
🎁
Blood Drive
Grades 9-12
School
Event
Free
🎉
👑
🎁
School Dance
Grades 9-12
School
Event
Low Cost/DIY
🎉
👑
🎁
Homework Pass
Grades 6-12
Student
Privilege
Free
🎉
👑
🎁
Silly School Leader
Grades 6-12
Student
Privilege
Free
🎉
👑
🎁
The Love Soiree
Grades 6-12
School
Event
Low Cost/DIY
🎉
👑
🎁
Digital Escape Rooms
Grades 6-12
Class/House
Privilege
Deluxe
🎉
👑
🎁
Bonfire
Grades 9-12
Class/House
Event
Deluxe
🎉
👑
🎁
Career Day
Grades 3-12
School
Event
Low Cost/DIY
🎉
👑
🎁
Movie Posters
Grades 3-12
Student
Tangible
Low Cost/DIY
🎉
👑
🎁
Classroom DJ
Grades K-12
Student
Privilege
Free
🎉
👑
🎁
Donate $1
Grades 3-12
Student
Privilege
Low Cost/DIY
🎉
👑
🎁
Karaoke Night
Grades 9-12
Class/House
Event
Deluxe
🎉
👑
🎁
Locker Choice
Grades 9-12
Student
Privilege
Free
🎉
👑
🎁
Dance Party
Grades K-12
Student
Event
Free
🎉
👑
🎁
Silent Disco
Grades 6-12
Class/House
Event
Low Cost/DIY

All Reward Ideas for Middle School Students

🎉
👑
🎁
Homework Pass
Grades 6-12
Student
Privilege
Free
🎉
👑
🎁
Technology
Grades 6-12
Student
Tangible
Deluxe
🎉
👑
🎁
Read Across America
Grades K-8
School
Event
Free
🎉
👑
🎁
TikTok with the Teacher
Grades 6-12
Student
Privilege
Free
🎉
👑
🎁
Camp Read Away
Grades K-8
Class/House
Event
Free
🎉
👑
🎁
Career Day
Grades 3-12
School
Event
Low Cost/DIY
🎉
👑
🎁
Loudspeaker Shoutout
Grades K-12
Student
Privilege
Free
🎉
👑
🎁
Gift Cards
Grades 6-12
Student
Privilege
Deluxe
🎉
👑
🎁
Virtual Talent Show. 🎤
Grades 3-12
Class/House
Event
Free
🎉
👑
🎁
Morning Meeting Leader
Grades 3-8
Student
Privilege
Free
🎉
👑
🎁
Lunch Concert
Grades 6-8
Class/House
Privilege
Free
🎉
👑
🎁
Teacher for the Day
Grades 6-12
Student
Privilege
Free
🎉
👑
🎁
Dance Party
Grades K-12
Student
Event
Free
🎉
👑
🎁
School Supplies & Merch
Grades K-12
Student
Tangible
Deluxe
🎉
👑
🎁
School Spirit Day
Grades K-12
School
Privilege
Free
🎉
👑
🎁
Silent Disco
Grades 6-12
Class/House
Event
Low Cost/DIY

All Student Reward & Incentive Ideas

💰
🎨
Silly School Leader
Grades 6-12
Student
Privilege
Free
💰
🎨
Tech Time
Grades 3-12
Student
Privilege
Free
💰
🎨
♟️Chess With the Principal
Grades 6-12
Student
Privilege
Low Cost/DIY
💰
🎨
Special Pen
Grades K-5
Student
Privilege
Low Cost/DIY
💰
🎨
Picnic Lunch
Grades K-12
Class/House
Privilege
Low Cost/DIY
💰
🎨
Lunch Concert
Grades 6-8
Class/House
Privilege
Free
💰
🎨
Hat Pass
Grades 6-12
Student
Privilege
Free
💰
🎨
Morning Meeting Leader
Grades 3-8
Student
Privilege
Free
💰
🎨
School Supplies & Merch
Grades K-12
Student
Tangible
Deluxe
💰
🎨
Teacher v Student Competition
Grades 6-12
School
Privilege
Free
💰
🎨
Talk Time
Grades 6-8
Student
Privilege
Free
💰
🎨
School Assembly
Grades K-12
Student
Privilege
Free
💰
🎨
Extra Computer Games
Grades 3-12
Student
Privilege
Free
💰
🎨
Positive Note or Call Home
Grades K-12
Student
Privilege
Free
💰
🎨
Virtual Field Trip
Grades K-12
Class/House
Privilege
Free
💰
🎨
Books
Grades K-12
Student
Tangible
Low Cost/DIY

All Virtual Reward Ideas for Schools

🎉
👑
🎁
Student Spotlight Board
Grades K-5
Student
Privilege
Low Cost/DIY
🎉
👑
🎁
Host a Virtual Party. 🎶
Grades 6-12
Class/House
Event
Free
🎉
👑
🎁
Virtual Field Trip
Grades K-12
Class/House
Privilege
Free
🎉
👑
🎁
Morning Meeting Leader
Grades 3-8
Student
Privilege
Free
🎉
👑
🎁
Classroom DJ
Grades K-12
Student
Privilege
Free
🎉
👑
🎁
Positive Note or Call Home
Grades K-12
Student
Privilege
Free
🎉
👑
🎁
Digital Escape Rooms
Grades 6-12
Class/House
Privilege
Deluxe
🎉
👑
🎁
Social Media Reporter
Grades 3-12
Student
Privilege
Free
🎉
👑
🎁
Show & Tell
Grades K-8
Student
Privilege
Free
🎉
👑
🎁
Podcast
Grades 3-12
Student
Privilege
Free
🎉
👑
🎁
Stickers
Grades K-5
Student
Tangible
Low Cost/DIY
🎉
👑
🎁
Extra Computer Games
Grades 3-12
Student
Privilege
Free
🎉
👑
🎁
Brain Break
Grades K-12
Student
Privilege
Free
🎉
👑
🎁
Virtual Talent Show. 🎤
Grades 3-12
Class/House
Event
Free
🎉
👑
🎁
Certificate of Achievement
Grades K-12
Student
Privilege
Free
🎉
👑
🎁
Teacher Q&A
Grades K-12
Class/House
Privilege
Free
See all Rewards

Want more ideas?

Rewards that Rock 🎸 has 100+ rewards, incentives, and event ideas to build your school culture.
Find Rewards
Learn more about the author, 
Liveschool Team
 

Subscribe via Email

Receive the best school culture resources monthly to inspire your planning.

Related Resources

-