JOU-1526_ COMPUTER-ASSISTED REPORTING

Course Outline 2010
Mondays 8-10

David McKie
181 Queen Street
1-613-288-6523 (office)
1-613-290-7380 (cell)

1-613-288-6490 (fax)

Rachel Aiello
613-897-4405

Julie Kelly
cell:

COURSE DESCRIPTION

To teach students how to find information on the Internet and government hard drives using advanced search techniques and tools such as Excel, pdf-converters and data visualization software such as Tableau to make sense of the information and render it more accessible readers, viewers and listeners through multi-media platforms. We will also learn how to negotiate for data that government officials are reluctant to release. Taken together, these skills comprise”computer-assisted reporting.” Our textbook, “Computer-Assisted Reporting: A Comprehensive Primer” , will be our guide and is available at the Algonquin College bookstore.

At the end of this term, you will become adept searching affectively and with precision for information, analyzing it for story ideas, or for questions that could lead to stories. The story is the end product that we will always keep in mind when running through the exercises, though we will not be writing stories, per se. This is primarily a research methods course with a heavy emphasis on storytelling. As such, we will begin each class with a story that has appeared in the news: either as an example of how computer-assisted reporting, also known as data journalism, was used, or how it could have been used. These discussions and the exercises will help you develop new reflexes that will allow you to examine news more critically. .

This will be a hybrid couse, so described because it will take place in two venues: in class and online. The course will be divided up into modules. Each module, lasting roughly three weeks, will teach a new skill. You will be introduced to a concept and skill in class, and then with the use of our textbook, online tutorials to help learn the material. The tutorials, online discussions and troubleshooting sessions using desktop sharing software will prepare you for the in-class tests. Do the work online, and the modules will go smoothly.

Through three news quizzes, you will also be tested on your knowledge of the day’s current events. Fall is a busy time. Apart from the unforeseen events that will inevitably make news make news, the next several weeks will be filled with significant happenings. You will be expected to stay on top of these events, for they will become grist for our news quizzes. Being informed is your job as a journalist. For those who do so, the quizzes will be easy.

WHAT YOU WILL LEARN

1) How to find and analyze the credibility of information online

2) How to think offline before searching online

3) How to dig for gold in cyberspace

4) How to use a spreadsheet to analyze statistics

5) How to use technical skills such as exporting tables from pdfs into Excel

6) How to negotiate for data that can be obtained without going the formal route

7) How to create interactive pie and bar charts, using visualization tools such as Tableau

8) How to use visualization tools to help analyze data and improve storytelling

9) How to create a pivot table

10) How to use advanced functions in Excel

11) How to advance your numersey skills

12) How to become critical news consumers

13) How to use mapping software

Assignments and Deadlines	Percent
Quizzes: There will be three of them during our 15 weeks together. The quizzes, worth five marks each, will deal with stories making news, or material that we’ve covered during the course and in the textbook. Each quiz will be graded out of five marks for a combined total of 15 percent of the course’s final mark.	15%
Assignment#1: (Sept. 22) In-class Excel assignment	10%
Assignment #2: (Oct. 6) In-class pivot table test	10%
Assignment #3: (Nov. 3) (Fusion Tables)	15%
Assignment #4: (Dec. 8) (Mapping using ArcGIS online)	15%
Assignment #5: (midnight, Dec. 11)	25%
Participation/professional conduct: For informative participation, completing tutorials, and professionalism displayed in class (no spending time on Facebook while the professor is lecturing) and on-line	10%

The emphasis will be learning how to use Excel to perform basic tasks such as sorting, filtering, making pivot tables, doing simple math such as percentage changes and ratios. The way we learn Excel will follow a simple pattern: in-class lesson, practice tutorial which will be posted on this syllabus, and then an in-class test to ensure that you’ve mastered the material. It impossible to learn these skills without lots of practice, so the emphasis will be on repetition, using newsworthy data. When possible, I will attempt to link the exercises to stories in the news, thus emphasizing the practical value of these skills in helping to find and tell stories. If you attend class, pay attention, do the tutorials, and come to class prepared, you will master the material easily and have fun in the process. The course is designed for success. If you regularly miss classes or neglect to do the online tutorials, you will quickly fall behind and find it difficult to succeed. So for this reason, regular attendance is a MUST. If you must miss a class for a reason such as an illness, please give me a heads-up via email, just like you would an editor at work. The instructor also reserves the right to ask for a doctors’s note. We will run this class like a professional newsroom, which doesn’t only mean regular attendance, but being on time, paying attention in class, refraining from distractions such as Twitter, Facebook and email correspondence and chatting with your neighbor. If you miss class and neglect to provide advanced warning, you will receive a zero for the assignnment and will not be given an opportunity for a make-up test.

GRADES

We will be using a 12-point grading scale with the marks to be as follows, meaning the final mark will be out of 120 with the following breakdown:

A+ = 12

A= 11

A-= 10

B+ = 9

B= 8

B-=7

C+=6

C=5

C-=4

Values will be rounded off. For instance, values 10.5 and higher will be rounded off to 11. Conversely, 10.4 and lower will be rounded off to 10.

COMMUNICATIONSWITH STUDENTS

This will be done primarily through e-mail correspondence and phone calls, given that I do not have an office at the college. The protocol will dictate that e-mailed queries will be answered as promptly as possible. As such, it will be important for you to regularly check your e-mail account for communications such as revisions to instructions for assignments or readings. The excuse that “I forgot to check my email”
is unprofessional and will not be accepted. This is a dynamic course, which mean I frequently send e-mails alerting students to significant events.

PROFESSIONALISM

Because we’ll conduct ourselves as professional journalists, you will be expected to exhibit the appropriate behaviour. That means attending class, showing up on time; being prepared and ready to make a meaningful contribution based the preparation work you’ve been assigned; reading the assigned chapters in our textbook; paying attention to your instructor and colleagues and ignoring e-mail correspondence, Facebook and text messages; and promptly responding to e-mails from the instructor. You’ll also be expected to stay on top of current events, which is part of our obligation as journalists. If you must miss class, you will be expected to communicate with the instructor via e-mail.

If an illness forces an absence, then you may be required to provide a doctor’s note. If it’s for an internship, then you’ll have to provide details. And if it’s a family emergency, I only need a minimal amount of information. I will take attendance to track your in-class attendance. Missed classes or on-line sessions will mean missed marks, and possibly missed in-class tests, which will be assigned based on the previous week’s work. You will be allowed to use your textbook for in-class tests.

OUTLINE

Week One(TOP) – Sept. 8

Discussion of using Excel as a tool to analyze data.

Women charged after plane disruption prompts military escort
http://www.theglobeandmail.com/news/national/plane-gets-military-escort-after-passengers-disrupt-flight/article20231266/#dashboard/alerts

WestJet has ‘little tolerance’ for intoxicated flyers (The Globe and Mail)
http://www.theglobeandmail.com/news/national/westjet-has-little-tolerance-for-intoxicated-flyers/article20290565/#dashboard/alerts

To download disruptive passenger stories, please click here

Civil Aviation Daily Occurrence Reporting System (CADORS): Main Menu
http://wwwapps.tc.gc.ca/Saf-Sec-Sur/2/cadors-screaq/m.aspx

To download the CADORS disruptive passenger Excel file, please click here

‘Sunshine list’ 2014: Ontario’s top public earners list released (CBC News)
http://www.cbc.ca/news/canada/toronto/sunshine-list-2014-ontario-s-top-public-earners-list-released-1.2590152

Which public servants earned more than $100,000? Search Ontario’s ‘Sunshine List (The Globe and Mail)
http://www.theglobeandmail.com/news/national/which-public-servants-earn-more-than-100000-search-the-sunshine-list/article17699726/#dashboard/alerts

To download the sunshine list containing public sector salaries from 2013, please click here.

’Sunshine list’ includes salaries of key figures in gas plants scandal (Toronto Star)
http://www.thestar.com/news/queenspark/2014/03/28/sunshine_list_includes_salaries_of_key_figures_in_gas_plants_scandal.html

The Sunshine List
http://www.thesunshinelist.com/

Public Sector Salary Disclosure 2014 (Disclosure for 2013)
http://www.fin.gov.on.ca/en/publications/salarydisclosure/pssd/

To download the CADORS tutorial, please click here

Week Two (TOP) Sept. 15

Excel tutorial

Week Three (TOP) Sept. 22

In-class Excel test

To download the test, please click here

Via Rail paid its competitors nearly $2 million last year (Ottawa Citizen)
http://ottawacitizen.com/news/politics/via-rail-paid-its-competitors-nearly-2-million-last-year

‘Sunshine list’ 2014: Ontario’s top public earners list released (CBC News)
http://www.cbc.ca/news/canada/toronto/sunshine-list-2014-ontario-s-top-public-earners-list-released-1.2590152

To download the sunshine list containing public sector salaries from 2013, please click here.

Algonquin College
http://www.algonquincollege.com/public-relations/fast-facts/

Association of Universities and Colleges of Canada
http://www.aucc.ca/canadian-universities/facts-and-stats/enrolment-by-university/

A list of Ontario Colleges

College and University stats

Introduction to pivot tables

Week Four (TOP) Sept. 29

On-line Excel pivot table tutorial using the sunshine list. To download
the tutorial, please click here.

Week Five (TOP) Oct. 6

In-class Excel pivot table test

To download pivot table test, click here

Ottawa population numbers
http://ottawa.ca/en/city-hall/get-know-your-city/statistics/population-and-households-occupied-dwellings-estimates-ward

To download Ottawa population numbers, click here

Crimes and offences (Statcan summary tables that break crimes down by province and territories)
http://cansim2.statcan.gc.ca/cgi-win/cnsmcgi.pgm?Lang=E&SP_Action=Result&SP_ID=2102&SP_TYP=5&SP_Sort=1&SP_Portal=2

Juristat (Statistics Canada)
http://www.statcan.gc.ca/pub/85-002-x/85-002-x2014001-eng.htm

Crime Reports (crime mapping company for police forces in North America)
http://www.crimereports.com/map?search=Ottawa+Ontario

City Council & Committee Agendas & Minutes
http://ottawa.ca/cgi-bin/docs.pl?t=1&lang=en

Payment plea for Tamil protest costs ignored (CBC News)
http://www.cbc.ca/news/politics/story/2010/07/29/ottawa-tamil-protest-costs-public-safety.html

To download the primer on using numbers, please click here.

Week Six (TOP) (Thanksgiving)

Week Seven (TOP) Oct 20

News quiz

The art of pitching

To download the crime rate tutorial, please click here.

To download the Mapping Crime Rate Tutorial, please click here.

To download the KML file for step 30 in the Mapping Crime Rate Tutorial, right click here and use the “save as” function.

Introduction to Fusion Tables

Week Eight (TOP) Oct 27

To download the “Mapping Discarded Needles in Fusion Tables”, please click here

To download the Tableau tutorial, please click here.

Tableau tutorial using Industry Canada trade data

Week Nine (TOP) Nov. 3

In-class Fusion Table test

You can have one-on-one time to discuss your final assignment. So be sure to bring your data sets.

Week Ten (TOP) Nov. 10

Reading the mapping chapter in preparation for next week’s class.

Week Eleven (TOP) Nov 17

Mapping quiz based on the material in chapter seven of our textbook.

To download your mapping quiz, please click here.

Introduction to mapping, and tutorial one

To download the first mapping tutorial, please click here.

To download the second mapping tutorial, please click here.

To download the contaminated sites csv file that goes with the second mapping tutorial, please click here.

Assignment for next week: complete tutorial two.

Week Twelve (TOP) Nov. 24

In-class news quiz

Hands-on ArcGIS in-class mapping session and introduction to tutorial three

Week Thirteen (TOP) Dec. 1

Hands-on ArcGIS in-class mapping session and introduction to tutorial four

To download ArcGIS tutorial four, please click here.

Your Right To Know: How to Use the Law to Get Government Secrets
http://www.amazon.ca/Your-Right-Know-Government-Secrets/dp/177040211X

Week Fourteen (TOP) Dec. 8

To download the ArcGIS online mapping test, please click here.

Tutorials(TOP)

From week one: To download the CADORS tutorial, please click here

From week four: On-line Excel pivot table tutorial using the sunshine list. To download
the tutorial, please click here.

From week five: To download the primer on using numbers, please click here.

From week seven: To download the crime rate tutorial, please click here.

From week seven: To download the Mapping Crime Rate Tutorial, please click here.

From week eleven: To download the first mapping tutorial, please click here.

From week eleven: To download the second mapping tutorial, please click here.

From week twelve: Hands-on ArcGIS in-class mapping session and introduction to tutorial three

Final Assignment(TOP)

There are two categories: Data as a part of a story; and stand-alone data for those who are not required to write a story.

Requirements for those in category one:

– The data set should be up-to-date, the more recent, the better.
– The data set should be large enough to reflect a trend over time, not a snap shot of one month, or one year.
– The data should either drive your story, that is, the trend identified in the data set leads to the story, or the data can be an element of your story that adds valuable context.
– You will be required to use at least one data set, and will be given bonus marks for using more than one ( For instance, if you’re doing a story about local marijuana convictions, it would be interesting to see how those convictions compared to the provincial and national rates based on Statistics Canada data. I’m looking for evidence of the kind of legwork that I’ve indicated in my feedback.)
– The more research the better. The trend in your data or data sets MUST BE verified through research that helps to explain WHY the trend is newsworthy. A trend that is not researched is an idea, not a story. A trend that has already made news is not a story. In other words, your research determines the story’s newsworthiness. A trend that advances what we already know or tells us something new is a story.
– You MUST visualize the data using Fusion Tables, Tableau ( for those comfortable enough to use it after completing the tutorial ) or ArcGIS Online.
– The visualizations must have a title and source. Failure to properly identify the visualization will lose you half a grade.
– You will be required to upload your data set, and be sure that the numbers are properly formatted, the worksheets properly labelled, and a link to the website that houses the data is pasted into the original version, allowing me to verify the source. Failure to do any one of these will lose you half a mark. Please make sure that all the data is in ONE workbook, not several, as this would too time-consuming if everyone submitted their data in this manner.
– You will be required to post the story to the assigned category on our WordPress website

Requirements for those in category two:

Think of this as a detailed pitch that you’d make to an editor or producer during an internship. The pitch MUST contain enough detail to determine its newsworthiness. A pitch is NOT simply identifying a data set, and then telling me who you WOULD interview if you were to do a story. A pitch is identifying the data set(s); crunching the numbers; finding a trend; researching that trend to determine if it’s newsworthy, and then synthesizing that information into a tightly-focused pitch that could even be the story’s lead.

Your research must contain at AT LEAST THREE sources, be they studies or interviews with experts NOT ADVOCATES WITH A SELF-INTEREST. So, for instance, if your story is about the latest trends in marijuana-related offences in Ottawa, you’d have to consult experts or studies or reports to determine if the trend is new and worth pursuing.
Similar to the category above, you will be required to post the material on the assigned category on our WordPress site

As such, your assignment will require the following elements.

– The data set should be up-to-date, the more recent, the better.
– The data set should be large enough to reflect a trend over time, not a snap shot of one month, or one year.
-Although you don’t have to write a story, you MUST conduct research that helps to explain why the trend is newsworthy. As such, your post will contain a tight lead, no more than three sentences.
-The trend from the data must be expressed through at least one visualization using the tools mentioned in the category-one explanation.
- You must have at least one data set, but will earn bonus marks for using additional data that add context. For instance, putting a local trend into a provincial, national or even international context. I’m looking for evidence of legwork, not something cobbled together at the last minute.
-Below the visualization, you will provide links (web address) to at least THREE pieces of research that you’ve depended on to put the data into context and craft the lead.

What you are required to submit if you’re writing a story

– The story uploaded to the assigned category on our WordPress site
-The Excel workbook containing your data set(s) in a succession of properly labelled worksheets. If you’re using tables from different data sets, please copy and paste them into the same workbook, label the worksheet and paste the website’s URL into the first available cell in the table’s first row.
– One email containing the following:

1)your story’s WordPress URL;
2)the URL for your Excel workbook uploaded to your Google drive, or as an attachment;
3) and an explanation of your research methodology (your choice of data, contact information for sources such as URLs and phone numbers) uploaded to your Google Drive as a Word document. The backgrounder should be no more than 500 words. It will help me evaluate your research. Failure to provide it will cost you a full grade.

What you are required to submit if you’re not writing a story

-A blog post containing the lead, visualization, and links uploaded to the assigned category on our WordPress site.
-The Excel workbook containing your data set(s) contained in a succession of worksheets. If you’re using tables from different data sets, please copy and paste them into the same workbook, properly label the worksheet and paste the URL of the website that contains the data.

– One email containing the following:

1)your pitch’s WordPress URL;
2)the URL for your Excel workbook uploaded to your Google drive, or as an attachment;
3) and an explanation of your research methodology (your choice of data, contact information for sources such as URLs and phone numbers) uploaded to your Google Drive as a Word document. The backgrounder should be no more than 500 words. It will help me evaluate your research. Failure to provide it will cost you a full grade.

Marking breakdown

– 5 marks for the dataset

– 5 marks for the analysis

– 5 marks for the visualization

– 10 marks for the research

– 1 bonus mark for each additional dataset up to a maximum of 3

– 1 bonus mark for each additional visualization up to a maximum of 2