Data Analytics Foundations

Data Analytics Foundations Skill

Overview

Master the foundational concepts of data analytics including data types, collection methods, spreadsheet fundamentals, and basic data manipulation techniques.

Core Topics

Data Fundamentals

• Understanding data types (quantitative, qualitative, structured, unstructured)
• Data sources and collection methods
• Data quality dimensions (accuracy, completeness, consistency, timeliness)

Spreadsheet Proficiency

• Excel fundamentals and advanced formulas
• Google Sheets collaboration features
• Data cleaning and transformation in spreadsheets
• Pivot tables and data summarization

Data Collection

• Survey design and implementation
• Web scraping basics
• API data extraction
• Database querying fundamentals

Learning Objectives

• Understand core data analytics terminology and concepts
• Master Excel and Google Sheets for data analysis
• Implement effective data collection strategies
• Apply data quality assessment techniques

Error Handling

Related Skills

• databases-sql (for advanced data querying)
• statistics (for data analysis techniques)
• visualization (for presenting insights)

Data Collection

Quick Start

Data collection is the foundation of analysis. In your first week, you'll identify data sources, connect to APIs, and perform basic web scraping. By week three, you'll build automated pipelines that ingest data from multiple sources into a central repository.

First Task (30 minutes): 1. Identify 3 data sources relevant to your domain (public APIs, databases, files) 2. Access one public API (e.g., OpenWeather, CoinGecko) and retrieve sample data 3. Save the data to a CSV file 4. Load and inspect the data in a spreadsheet or Python

Key Concepts

1. Data Sources Classification

What it is: Understanding where data originates and how to access it.

Types of sources:

1. APIs (Application Programming Interface)
   - RESTful APIs: HTTP requests, JSON responses
   - GraphQL APIs: Flexible query structure
   - SDK-based: Language-specific libraries

2. Databases
   - Relational (SQL): PostgreSQL, MySQL
   - Cloud (BigQuery, Redshift)
   - NoSQL: MongoDB, DynamoDB

3. File-based
   - CSV/Excel files
   - JSON, XML, Parquet
   - Unstructured logs

4. Web Sources
   - HTML scraping
   - RSS feeds
   - Publicly available datasets

5. Real-time Streams
   - Event streams (Kafka)
   - WebSockets
   - Message queues (RabbitMQ)

2. API Integration

What it is: Using HTTP requests to fetch data from web services programmatically.

Example (Python with requests library):

import requests
import json

url = "https://api.example.com/data"
headers = {"Authorization": f"Bearer {API_KEY}"}
params = {"date": "2024-01-01", "limit": 100}

response = requests.get(url, headers=headers, params=params)
data = response.json()
print(data)

Common API patterns:

GET /users          # Retrieve list
GET /users/123      # Retrieve specific item
POST /users         # Create new record
PUT /users/123      # Update record
DELETE /users/123   # Delete record

3. Web Scraping

What it is: Extracting structured data from HTML web pages programmatically.

Example (Python with BeautifulSoup):

from bs4 import BeautifulSoup
import requests

url = "https://example.com/data"
response = requests.get(url)
soup = BeautifulSoup(response.content, "html.parser")

# Find all table rows
rows = soup.find_all("tr")
for row in rows:
    cells = row.find_all("td")
    data = [cell.text.strip() for cell in cells]
    print(data)

When to use: Competitor monitoring, real estate listings, news aggregation, public data without API.

4. Data Ingestion Pipelines

What it is: Automated processes that extract, transform, and load data (ETL).

Pipeline architecture:

Source System → Extract → Transform → Load → Target Database
   (API)        (fetch)   (clean, map) (store) (Data warehouse)
     ↓
Scheduling (daily, hourly)
   ↓
Error handling & logging
   ↓
Data quality checks

Example workflow:

1. Schedule: Run at 2 AM daily
2. Extract: Fetch data from 3 APIs
3. Transform: Clean, deduplicate, merge
4. Load: Insert into PostgreSQL
5. Validate: Check row counts, data types
6. Alert: Notify team if issues detected

5. Data Quality & Validation

What it is: Ensuring collected data meets standards before analysis.

Validation checks:

1. Schema validation: Right columns, data types
2. Completeness: Required fields not null
3. Uniqueness: No duplicate records
4. Range validation: Values within expected bounds
5. Format validation: Dates are dates, emails are valid
6. Referential integrity: Foreign keys match
7. Freshness: Data within acceptable age

Example (Python with Great Expectations):

from great_expectations import dataset

df = pd.read_csv("data.csv")
data = dataset.PandasDataset(df)

# Validate
data.expect_column_values_to_not_be_null("user_id")
data.expect_column_values_to_be_between("age", 0, 120)
data.expect_column_values_to_match_regex("email", r"^[\w\.-]+@[\w\.-]+\.\w+$")

Tools and Resources

API Tools:

• Postman: Test APIs interactively (free)
• Insomnia: REST client with environment support
• curl: Command-line HTTP client

Python Libraries:

• requests: HTTP requests
• beautifulsoup4: Web scraping
• selenium: Browser automation for JavaScript-heavy sites
• pandas: Data loading and manipulation
• sqlalchemy: Database connections

Services & Platforms:

• Zapier: No-code automation
• Make (formerly Integromat): Workflow automation
• Fivetran: Managed ETL service
• Apache Airflow: Open-source workflow orchestration

Public Datasets:

• Kaggle.com: Datasets with notebooks
• data.gov: Government datasets
• GitHub: Trending datasets
• your_industry_specific_sites

Best Practices

1. Respect Rate Limits: Check API documentation and implement backoff strategies 2. Use API Keys Securely: Store in environment variables, never commit to git 3. Implement Error Handling: Retry logic, fallback sources, error logging 4. Cache When Possible: Avoid redundant API calls; store intermediate results 5. Monitor Data Quality: Implement automated validation checks 6. Document Data Sources: Keep record of field definitions, update frequencies 7. Obtain Permissions: Ensure you have rights to collect and use the data 8. Version Your Data: Track when data was collected, what version of API used 9. Plan for Scalability: Design pipelines to handle growth in data volume 10. Log Everything: Track successes, failures, and data volumes for auditing

Next Steps

1. Week 1-2: Connect to 2-3 public APIs and understand rate limits 2. Week 2-3: Build first basic web scraper with error handling 3. Week 3-4: Create simple daily ingestion pipeline (spreadsheet or database) 4. Week 4-5: Add data quality validation checks 5. Week 5-6: Schedule automated ingestion with error alerts 6. After: Learn SQL for database storage, Python for complex transformations 7. Progression: Basic APIs → Advanced ETL → Stream processing (Kafka, Spark)

Excel Fundamentals

Quick Start

Excel is the foundational tool for data analysts. In your first session, you'll create a spreadsheet, enter data, write basic formulas, and format cells. By the end of week one, you'll handle real datasets with cleaning, sorting, and filtering.

First Task (15 minutes): 1. Open Excel and create a new workbook 2. Enter sample sales data (date, product, quantity, price) 3. Create a formula to calculate total sales (quantity × price) 4. Apply conditional formatting to highlight top performers

Key Concepts

1. Formula Fundamentals

What it is: Expressions that perform calculations or manipulate data in cells.

Example:

=SUM(A1:A10)           # Sum range
=AVERAGE(B2:B20)       # Calculate average
=IF(C5>100, "Yes", "No")  # Conditional logic
=CONCATENATE(A1, " ", B1)  # Join text

When to use: Whenever you need to automate calculations or create dynamic references instead of hardcoding values.

2. Pivot Tables

What it is: Dynamic summaries that automatically organize and summarize large datasets by dimensions and metrics.

Example:

Raw data: Date, Product, Region, Sales
Pivot table:
  Rows: Product
  Columns: Region
  Values: SUM(Sales)
Result: Sales by Product and Region cross-tabulation

When to use: Analyzing sales by category/region, summarizing customer data, trend analysis, quick reporting.

3. VLOOKUP & Data Relationships

What it is: Looks up values from one table and returns corresponding values from another (vertical lookup).

Example:

=VLOOKUP(A2, ProductList, 3, FALSE)
Finds product code in column A within ProductList range
Returns value from 3rd column of that range
FALSE ensures exact match

When to use: Matching customer IDs to names, product codes to descriptions, joining data from different ranges.

4. Data Cleaning Techniques

What it is: Processes to standardize, remove duplicates, and prepare raw data for analysis.

Key techniques:

• TRIM(): Remove leading/trailing spaces
• UPPER()/LOWER(): Standardize text case
• Find & Replace: Fix formatting inconsistencies
• Remove Duplicates: Data tab → Remove Duplicates
• Text to Columns: Split data by delimiters (comma, space)

Example workflow:

Raw: "  Product A  ", "  Product A  ", "$1,234"
Clean: "Product A" (no duplicates), 1234 (numeric)

5. Advanced Filtering & Sorting

What it is: Filter data by criteria and organize by multiple columns with custom sort orders.

Example:

AutoFilter: Show only Sales > $10,000 AND Region = "West"
Sort: By Date (oldest first), then by Sales (highest first)
Custom: Sort by custom lists (Jan, Feb, Mar, etc.)

When to use: Focusing on specific segments, preparing data for presentations, finding anomalies.

Tools and Resources

Microsoft Excel:

• Excel Desktop (Windows/Mac)
• Excel Online (free with Microsoft account)
• Built-in Help: Ctrl+F1

Recommended Learning Resources:

• Microsoft Excel Training Hub: https://support.microsoft.com/en-us/excel
• ExcelJet.net: Formula reference and shortcuts
• YouTube: "Excel Formulas for Data Analysis" courses

Essential Shortcuts:

• Ctrl+H: Find & Replace
• Ctrl+Shift+L: Toggle AutoFilter
• Alt+D+P+P: Insert Pivot Table (Windows)
• F2: Edit cell formula

Best Practices

1. Use Meaningful Headers: Create clear column names for data organization 2. Keep Raw Data Separate: Store original data in one sheet, analysis in another 3. Avoid Hardcoding: Use cell references in formulas for flexibility 4. Validate Data Types: Ensure dates are dates, numbers are numbers (not text) 5. Document Complex Formulas: Add comments explaining logic 6. Create Data Validation: Set rules for cells to ensure data quality 7. Use Named Ranges: Instead of A1:A100, use descriptive names like "Sales2024" 8. Format for Readability: Use consistent fonts, colors, and number formats

Next Steps

1. Week 2-3: Master VLOOKUP, INDEX/MATCH, and advanced formulas 2. Week 4: Build your first pivot table dashboard 3. Week 5-6: Create a complete analysis project (sales report, inventory management) 4. After: Move to Google Sheets collaboration or SQL for larger datasets 5. Progression: Advanced Excel → Python/R for statistical analysis

Google Sheets

Quick Start

Google Sheets enables real-time team collaboration and automation. Within your first week, you'll share sheets, use QUERY functions, and create automated reports. By week three, you'll build Apps Scripts to pull data from APIs and trigger automated workflows.

First Task (20 minutes): 1. Go to sheets.google.com and create a new spreadsheet 2. Share it with a teammate for editing 3. Add sample data and apply conditional formatting 4. Use QUERY function to filter and summarize data 5. Create a simple chart

Key Concepts

1. Cloud Collaboration

What it is: Real-time simultaneous editing with version history and comment capabilities.

Features:

• Share & Permissions: Set editor, viewer, or commenter access
• Version History: Restore previous versions (View → Version history)
• Comments & Tasks: @mention colleagues, assign tasks
• Simultaneous Editing: See cursor positions of team members live

When to use: Team projects, client deliverables, dashboards requiring real-time updates.

2. QUERY Function

What it is: SQL-like syntax to filter, sort, and aggregate data without pivot tables.

Example:

=QUERY(A:D, "SELECT A, SUM(D) WHERE B='North' GROUP BY A")
Filters data where column B = 'North'
Sums column D values and groups by column A

When to use: Creating dynamic reports, filtering by user input, building dashboards from raw data.

3. Apps Scripts & Automation

What it is: JavaScript-based automation to extend Google Sheets with custom functions and workflows.

Example:

function updateReport() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getRange("A1:D100").getValues();
  var today = new Date();
  sheet.getRange("F1").setValue("Last updated: " + today);
}

When to use: Auto-sending reports, pulling data from APIs, creating custom functions, scheduled updates.

4. API Integration

What it is: Connect external data sources to Google Sheets for real-time imports.

Common integrations:

=IMPORTJSON(url, "/path/to/field")    # Import JSON data
=IMPORTHTML(url, "table", index)       # Scrape HTML tables
=GOOGLEFINANCE("GOOGL")                # Stock prices
=IMPORTDATA(url)                       # CSV or TSV files

Example workflow:

Source: REST API returning JSON sales data
Target: Google Sheet that auto-updates hourly
Use: Apps Script with UrlFetchApp.fetch()

5. Add-ons & Extensions

What it is: Third-party tools that extend Google Sheets functionality.

Popular Add-ons:

• Data Studio: Create interactive dashboards
• Supermetrics: Pull marketing data (Google Ads, Facebook)
• Mailmodo: Collect form responses
• Lucidchart: Embed diagrams
• Pivot Table: Enhanced pivot functionality

Tools and Resources

Google Sheets Platform:

• sheets.google.com (free with Google account)
• Mobile apps (Android/iOS)
• Offline editing support

Developer Resources:

• Google Sheets API Documentation: https://developers.google.com/sheets
• Apps Script Documentation: https://developers.google.com/apps-script
• Sample Scripts: GitHub google/apps-script-samples

Useful Add-ons:

• Data Studio: Free dashboard builder
• Supermetrics: Social media & marketing data
• Polymorphic: Dynamic form responses

Best Practices

1. Set Clear Permissions: Define who can edit vs. view to prevent accidental changes 2. Use Naming Conventions: Name sheets and ranges descriptively (e.g., "RawData", "Dashboard") 3. Create Data Validation: Restrict entries to predefined lists for consistency 4. Separate Layers: Keep raw data, working sheets, and dashboards in different tabs 5. Document Scripts: Add comments in Apps Scripts explaining logic 6. Test Before Automation: Manually verify QUERY and formula logic before automating 7. Monitor API Quotas: Track API usage to avoid hitting limits 8. Archive Old Versions: Keep team folders organized with clear naming conventions

Next Steps

1. Week 1-2: Master QUERY, IMPORTRANGE, and basic filtering 2. Week 2-3: Build a collaborative dashboard with charts 3. Week 3-4: Create first Apps Script for API data ingestion 4. Week 4-5: Set up automated reports with scheduled triggers 5. After: Move to Data Studio for advanced dashboard design 6. Progression: Google Sheets → Python/Pandas for complex transformations

Data Analyst Foundations Guide

Data Types

• Numerical: Continuous (age, salary), Discrete (count)
• Categorical: Nominal (color), Ordinal (rating)

Excel Essentials

=VLOOKUP(value, range, col, FALSE)
=SUMIF(range, criteria, sum_range)
=PIVOT TABLE for aggregation