Teaching with Data: Organic Search Keyword Analysis

Resources & Strategies for Faculty Teaching Undergraduates

Organic Search Keyword Analysis

Faculty Author: Dana Chinn

Course: JOUR 477 Web Analytics for News and Nonprofit Organizations Department or School: Annenberg Norman Lear Center Media Impact Project Student Population: graduates and undergraduates, mostly seniors    

Duration: 2 weeks


  • Final write-­‐up of findings
  • Excel workbook with sorted groups of branded and unbranded keywords and pivot table

Keywords: website traffic, search engines, keyword data, marketing strategy, aggregate percentages, pivot tables, Google Analytics

Summary:  Students work individually and in groups, during and out of class, to choose a website of a news or nonprofit organization, download 500 organic search keywords from Google Analytics into Excel, code each keyword as branded or unbranded, use a pivot table to calculate the number and percent of sessions from branded vs. unbranded keywords, check and revise coding, recalculate totals and percentages, analyze the data, complete a table of summary metrics, and present findings in written form.

Assignment Goals:  Knowing which keywords people use to get to a site gives invaluable, specific indicators of what people seek and how they perceive an organization. In this assignment you will download the top 500 organic search keywords, classify each as branded vs. unbranded, and use metrics such as bounce rate, the percent of new users and the pages per session to inform your recommendation on how an organization can attract and engage its target audiences.

The purpose of this assignment is for you to define, compare and contrast the different types of website traffic that comes from organic search engines. The analysis will inform decisions on marketing strategy, content and design.

You will also learn how to retrieve raw keyword data from Google Analytics, recalculate aggregate percentages, and use Excel to code, sort and summarize using pivot tables.

Lots of questions come up when you code. We will start this assignment in class so, just as with a data team at an organization, we can develop a code sheet together, discuss ambiguities, agree on definitions, and check each other's codes.

Recommended Tools:

  • Excel
  • Website of one of the news or nonprofit organizations studied in class
  • Google Analytics

Faculty Author Advice:  There are a number of concepts, calculations and Excel functions used in this assignment. Only sorting and pivot tables are new, but at mid-­‐semester students are still struggling with basic quantitative skills and Excel together with Google Analytics (which is very new and strange). Thus, the assignment grade is mostly based on accuracy, attention to detail and expressing data correctly in full sentences more than analytical insights.