A Google Sheets And 5 Short Questions

For this assignment, correlate Google state-level search strength with another state-level social statistic that you think should show a correlation. It’s OK if you don’t find a strong correlation, the point is to try it out

Please allow me to emphasize: you will be correlating ONE Google search term with ONE social statistic, not two search terms with each other.

Start by thinking about what kinds of social data are publicly available by state: polls on the presidential race, high school graduation rates, average or median income, divorce rates, unemployment…. Then think of a search term that 1) you would expect to be related 2) will be a large enough search to generate state-level search results (it’s OK to be missing a few states) and 3) might provide some insight into the statistic you’ve chosen.

Follow these steps in assembling your data:

Get your search information from google.com/trends to find state-level detail on searches. Type in your search, click on the US, then scroll down a little to see the results ranked by state.

If you need help finding state-level data on your other variable, please let me know and I’ll be happy to help.

Organize your information into a spreadsheet (Google Sheets or Microsoft Excel)

Create a column with state names or abbreviations in it

Create two columns with the data for each state in it. If you don’t have data for a state, DO NOT enter zero–no data is not the same as zero! Just leave that cell blank.

Calculate the correlation between the two columns; label the cell next to it and format it in bold so it’s clear what the measure is. You’ll need to figure out how to use the =CORREL() command but it should be fairly intuitive.

Indicate your sources of information to the right of your data columns by describing the source and providing as direct a link as possible.

Create an X-Y scatter plot with trendline to the right of your data. Here’s how to do it in Google Sheets:

https://people.emich.edu/sbecker/excel/ExcelScatterPlot_GoogleOfc2015.pdf (Links to an external site.)Links to an external site.

https://productforums.google.com/forum/#!topic/docs/ygCn5f-trPg

(Links to an external site.)Links to an external site.And here’s how to do it in Excel:

http://www.excel-easy.com/examples/scatter-chart.html (Links to an external site.)Links to an external site. (see “Only Markers”)

https://support.office.com/en-us/article/present-your-data-in-a-scatter-chart-or-a-line-chart-4570a80f-599a-4d6b-a155-104a9018b86e (Links to an external site.)Links to an external site.

Upload the spreadsheet file here as an .xls or .xlsx file (if you use Google Sheets, Download As>>Microsoft Excel).

Then briefly answer the following questions (at least a few sentences each) numbering and separating your responses for easy reading and uploaded as a separate word processing document (two files uploaded for this assignment):

1. What correlation were you investigating, and why/how did you choose to investigate that? What correlation coefficient did you find? Note: as a general guideline, ±(0.1 to 0.3) is a weak correlation, ±(0.3 to 0.5) is a moderate correlation, and ±(.5-1.0) is a strong correlation.

2. What does the correlation suggest about the relationship between your variables?

Since we’ve learned that correlation does not equal causation, discuss the possibilities that:

3a. the correlation is accidental/spurious

3b. the correlation has a common cause, that is, one is not causing the other

3c. the underlying statistic is causing the search term, or vice-versa (reverse causation)

In the event that you cannot find a correlation, spend 3) discussing why you didn’t find the relationship you thought there would be, or perhaps why there may still be a relationship like you were looking for but it wasn’t revealed in this particular analysis. For example, is it possible that a relationship between the things you’re looking at is real but masked by another variable, or lost in the noise of some other data?