# Google Analytics - 1 Foundations - Module 3
## 0. Overview
>[! quote] Spreadsheets, query languages, and data visualization tools are all a big part of a data analyst’s job. In this part of the course, you’ll learn the basic concepts to use them for data analysis. You’ll also understand how they work through interesting examples.
### Learning Objectives
>[! info] Module 3 Learning objectives
>- [ ] Describe [[Spreadsheet]], [[Query language|query language]], and [[data visualizations]] tools, giving specific examples.
>- [ ] Demonstrate an understanding of the uses, basic features, and functions of a [[Spreadsheet]]
>- [ ] Explain the basic concepts involved in the use of [[SQL]] including specific examples of [[Query|queries]]
>- [ ] Identify the basic concepts involved in [[data visualizations]], giving specific examples.
### Module 3 Glossary
- [[Attribute]]
- [[Observation]]
## 1. Master [[Spreadsheet]] basics
### 1.1 Video: _The ins and outs of core data tools_ (2:00)
This video made some important statements that skills learned for the rest of the course will be built atop skills learned from this module. Skills working in [[Spreadsheet|spreadsheets]] and [[SQL]] with dessert being [[data visualizations|data viz]] !
### 1.2 Reading: _Step-by-Step: Make [[Spreadsheet|spreadsheets]] your friend._
This is the outline for the what will be covered in the video.
>[! cue] Def [[Attribute]]
>[! info] Attribute
>A characteristic or quality of data used to label a column in a table.
The course will cover 5 examples
- entering data & adjusting the size of cells
- adding labels, column labels are usually called [[Attribute|attributes]]
- add more [[attribute|attributes]]
- Organizing data
- using a [[Formula|formula]]
Three main features of a spreadsheet.
- cells
- rows
- columns
A cell reference is `<column letter x row number>`
In a [[dataset]] a row is also called an [[Observation]]
### 1.3 Video: _Make [[Spreadsheet|spreadsheets]] your friend._
In the video, the presenter reviewed the basics of data analysis in a spreadsheet.
### 1.4 Discussion Prompt: _[[Spreadsheet|spreadsheets]] in daily life_
The prompt asks how could I use a spreadsheet in my daily life to simplfy a task or solve a problem.
>[! note] Response
>I use spreadsheets or a version of spreadsheets daily. I use them to help me with personal finances, keeping track of groceries, and keeping track of how many miles i have run in a given week vs my running training plan.
>
>Using spreadsheets in daily life has become so commonplace, it was hard for me to think up how I could use it.
>
>I know from experience that understanding the tabular structure that spreadsheets give data within it will be important as the idea of `<column letter> x <row number>` reference coordinates is essential when working with data with this kind of structure. I never thought I would be excited (again) about spreadsheets!
and then a second response because the courseware was not clearing my first response. It might be because I copied and pasted from Obsidian. who knows.. anyway here is my second answer
>[! note] Response 2
>For some reason I have to re-enter my response.
>
>I use spreadsheets or some version of spreadsheets every single day. A lot of us here have said that we use spreadsheets for finances or meal tracking or grocery list. I appreciate that because I do the same thing. I also know that spreadsheets have become so ubiquitous that we might not really realize that we are working with data or even analyzing data on the spreadsheet. Personally, I also use spreadsheets to keep track of things. I am an amateur astronomer / astrophotographer in my spare time. So I use spreadsheets to help plan an observation session, or a photo shoot.
>
>A funny thing is that I only just learned what an "attribute" is with respect to data analysis. I am relatively new to analytics so this little fact (data point, if you will) was an ah-ha moment for me. :)
>
>For learners who are new at spreadsheets, I know from experience that learning and knowing how to do analysis on a spreadsheet will enable a much easier time in later coursework. Keep at it and good luck.
### 1.5 Quiz: _Hands-On-Activity: Generate a chart from a [[Spreadsheet|spreadsheet]]_
Cool! learned that google sheets has a really easy graph editing modal.
### 1.6 Reading: _More [[Spreadsheet|spreadsheet]] resources_
updating the resource sections of the spreadsheet card.
### 1.7 Quiz: _Test your knowledge on [[Spreadsheet|spreadsheet]] basics_
Graded quiz 75% or higher needed to pass. 4 questions
#### Result
100%
No issues here. The videos well prepared for this quiz. Notable question on [[Attribute|attributes]]
<hr>
## 2. Get started with [[SQL]] and [[data visualizations|data visualization]]
### Video: _[[SQL]] in action_ (3:00)
- [[SQL]] is a [[Query language|query language]]
- SQL does the same stuff that spreadsheets can do but on a bigger scale.
- Store
- Organize
- Analyze
- consider [[Spreadsheet|spreadsheet]] for small datasets, [[SQL]] for larger datasets.
SQL is used to interact with a database.
SQL basically is the same for each type of SQL language.
>[! cue] basic [[Query]] structure
> [! info] Basic Query Structure
```sql
SELECT -- <columns>
FROM -- <db table>
WHERE -- <conditions to filter>
```
- The demo is using [[BigQuery]] a Google product that will have training on in the third Course on the Prepare phase.
- The above is an example of a [[SQL statement|statement]]
- the statement has 3 [[SQL clause|clauses]]
- each clause has 1 [[SQL keywords|keyword]], [[SELECT]], [[FROM]], and [[WHERE]]
### Reading: _[[SQL]] guide: Getting started_
The reading expands the understand and use of [[SQL clause|clauses]] to make some [[Query|query]] [[SQL statement|statements]]
#### Selecting specific column
```sql
SELECT colA
FROM databaseTable
WHERE colA = "something"
```
We specify the column by name `colA`
#### Selecting multiple columns
```sql
SELECT
colA,
colC,
colE
FROM databaseTable
WHERE colA = "something"
```
#### Adding multiple conditions
```sql
SELECT
colA,
colC,
colE
FROM databaseTable
WHERE
colA = "something"
AND colC = "else"
AND colE = "entirely"
```
This example introduces [[SQL Operators|operator]] [[AND]]; though the material calls this "the AND statement" For now though a technical deepdive is not needed. I only need to understand [[SELECT]], [[FROM]], and [[WHERE]]
### Video: _Angie: Everyday struggles when learning new skills_ (1:00)
Very good video where a googler speaks about her struggles when learning new skills.
Her key advice is that one can ask for help. Very touching story.
### Reading: _Endless [[SQL]] possibilities_
Here is another document about writing [[SQL statement|statements]] using appropriate syntax and introducing some new concepts and [[SQL keywords]]
#### Best practices on syntax:
- Write SQL keywords in all caps
- One clause per line.
- Multiple operations should go on their own line though.
Do
```SQL
SELECT
colA,
colB
FROM dbTable
WHERE
colA = "neat"
AND colB = "oh";
```
Don't
```SQL
SELECT colA,colB,colC FROM dbTable WHERE colA="neat" AND colB = "oh"
```
Both will do the same thing and run fine, but the `Do` example is much easier to read.
>[! info] Semicolon
>use a semicolon to end your sql statement.
#### [[WHERE]] bonus
>[! cue] [[LIKE]] and `%` wildcard
Using [[LIKE]] with a WHERE keyword creates a clause that can look for a certain pattern of text, not just strict equivalence.
#### Avoid [[SELECT]] clauses using the `*`
>[! cue] `SELECT *` is bad mmmhkay
Like the heading says. using `SELECT * ... ` will impact query performance and should be used sparingly.
#### Using comments
Use comments to add descriptions within statements to help other humans understand what your code is supposed to be doing.
Use comments smartly. Commenting each line of code is not ideal. Add context where context should go. Otherwise, too many comments clutter codebase.
```SQL
SELECT colA AS "BigClocks" --change field name to big clocks
FROM worldwideclocks;
```
#### Aliases
>[! cue] [[AS]]
The [[AS]] keyword used with [[SELECT]] creates a clause that can rename columns to something temporary to make your results easier to read.
```SQL
SELECT
colA AS "BigClocks", --change field name to big clocks
colB AS "Location" -- location format is city, country
FROM worldwideclocks;
```
The above example would return results labeled BigClocks and Location
>[! cue] See [[SQL#Resources]] for more tips
Visit the [[SQL]] card and the resources section for more details.
### Video: _Becoming a [[data visualizations|data viz]] whiz_ (5:00)
This video introduces visualization tools.
Amazing story regarding [Florence Nightingale](https://en.wikipedia.org/wiki/Florence_Nightingale) drawing a visualization in order to better express her findings to others.
![[Nightingale-mortality.jpg]]
By <a href="https://en.wikipedia.org/wiki/en:Florence_Nightingale" class="extiw" title="w:en:Florence Nightingale"><span title="English social reformer, statistician, and founder of modern nursing (1820–1910)">Florence Nightingale</span></a> - <a rel="nofollow" class="external free" href="https://www.davidrumsey.com/luna/servlet/s/h6xid2">https://www.davidrumsey.com/luna/servlet/s/h6xid2</a>, Public Domain, <a href="https://commons.wikimedia.org/w/index.php?curid=1474443">Link</a>
The presenter went on to making a another chart in the spreadsheets.
### Reading: _Plan a [[data visualizations|data visualization]]_
Wow never heard of _planning a visualization_ before.
As with all things there is a process around a data visualization.
The reading presents an example scenario where you're an analyst tasked with analyzing data from an existing website and sales records. The process below is an example/hypothetical of what an analyst might follow...
#### Step 1. Explore
Explore the data for patterns. Do ANALYSIS
In the example, the hypothetical analyst uncovers patterns like who is using the website most and the relationship to how much they are buying, the geography of large purchases, what is being purchased.
#### Step 2. Plan
- knowing your audience can inform how to visualize data.
- in the example, the audience is sales focussed so showing SALES over time would be a good idea.
- connecting sales to location
- relationship between sales and website use.
- which customers are fueling growth.
#### Step 3. Create
- line chart for sales over time.
- maps to show geography and location influence.
- customer segments with donut charts (EEEEEW)
- bar charts to show relative quantities.
#### The right tool for the job
Spreadsheets suited for smaller datasets and simple visuals (line, bar, donut)
Tableau works with data from nearly any system.
[[R Programming]] and [[Rstudio]]
### Video: _Lilah: The power of a visualization._
Data visualizations are pictures that make your consumable
My key takeaway from this is that I should be attempting to make great visualizations. Like in any data project be thinking of a way to present the point of view effectively through story telling with data.
### Discussion promt: _[[data visualizations]] all around you_
In this discussion prompt we were encouraged to think about how visualizations are important in everyday life.
### Quiz: _Test your knowledge on [[SQL]] and [[data visualizations|data visualization]]_
Untimed Quiz, 75% minimum score needed to pass.
#### Result
100%
<hr>
## 3. Module 3 challenge
### Module 3 Glossary
- [[Attribute]]
- [[Observation]]
### Quiz: _Module 3 challenge_
50 Minutes. 10 questions, Graded
I will need to slow down and practice the strategies learned.
#### Result
85%
A little bummed by this. 8.5/10 one full point deduction and one half-point deduction
both items I missed were with respect to data visualizations.
The full point deduction happened because I did not select the most appropriate answer. This question was one that I changed. I am pretty sure the correct answer is bar chart, but I waffled and went with donut.
The half-point deduction was surrounding a question dealing with selecting data visualization tools. It was one of those `select all that apply` questions and I had one too many selections. This also was against my first gut reaction.
I am starting to think that I don't trust myself as much as I thought I did. I still passed, but this end of module quiz is my lowest score. :(
Onward!
<hr>
## 4. Retro
<hr>
>[!summary-top] Name of the summary
>_This summary will appear at the top.
>Only 3 lines really appear so try to condense as much as possible.
>Summary info goes here when completed._