Data Analysis

1. What Data Analysis Is

Key points

• The goal is a decision, not a chart. A chart is only a tool to explain the answer.
• Raw data → information (organised data) → insight (what it means) → action (what to do).
• Good analysis starts with a clear business question. Example: "Why did orders drop last month?"
• Companies use it everywhere: pricing, marketing, stock planning, fraud checks, hiring.
• Data analysis explains what happened and why. Data science goes further and builds models to predict the future.

Small example

2. The Data Analyst Role

Key points

• Daily work: write SQL queries, clean data in Excel or Python, build dashboards, answer questions from teams.
• Dashboard = a screen of charts and numbers that updates by itself. Built in tools like Power BI or Tableau.
• Stakeholder = the person who asked the question or will use your answer. Example: the marketing head.
• Hard skills: Excel, SQL, basic statistics, one BI tool, some Python.
• Soft skills: asking good questions, clear writing, simple explanations, business sense.
• Common fresher job titles in India: Data Analyst, Business Analyst, MIS Executive, Product Analyst, Analyst Trainee.

3. Types of Analytics

Key points

• Descriptive uses totals, averages and charts. Most fresher work is here.
• Diagnostic uses drill-downs (breaking a number into parts) and comparisons.
• Predictive uses past patterns and models, like regression.
• Prescriptive suggests an action, often by testing options.
• Value and difficulty both go up as you move down the table.

4. The Analysis Process

Key points

• Ask: understand the problem. Use SMART questions: Specific, Measurable, Action-oriented, Relevant, Time-bound.
• Prepare: find and collect the right data. Check it is reliable.
• Process: clean it. Fix blanks, duplicates and wrong formats.
• Analyse: calculate, group, compare, find trends.
• Share: present findings with clear charts and a short story.
• Act: the business makes a decision based on your work.
• CRISP-DM is another famous process with six steps: Business Understanding, Data Understanding, Data Preparation, Modelling, Evaluation, Deployment. It is the same idea, used more in data science.

5. Data Types

Structure

• Structured: tables in Excel or a database. Example: an orders table. Easy to query with SQL.
• Semi-structured: has some labels but no fixed table. Example: JSON files, emails (sender, subject, then free text).
• Unstructured: no fixed format. Example: product reviews, images, call recordings. About 80% of company data is like this.

Qualitative vs quantitative

• Quantitative = numbers you can do maths on. Example: price, age, number of orders.
• Qualitative (also called categorical) = labels or groups. Example: city, gender, payment mode.
• Quantitative data is either discrete (counted, whole numbers: 3 children) or continuous (measured, can have decimals: 64.5 kg).

6. Levels of Measurement

Key points

• True zero means zero = "none at all". ₹0 salary means no money. But 0 °C does not mean "no temperature".
• That is why 40 °C is not "twice as hot" as 20 °C. But ₹40,000 is twice ₹20,000.
• Ordinal gaps are unequal: the jump from "Poor" to "OK" may not equal "OK" to "Good".
• Nominal and ordinal are qualitative. Interval and ratio are quantitative.

7. Data Sources & Collection

Key points

• Internal sources: the company's own databases, CRM (customer records), sales systems, website logs.
• External sources: government data (data.gov.in, Census), industry reports, Kaggle datasets, public APIs.
• Collection methods: surveys, forms, app tracking, sensors, interviews, web scraping (reading data from websites).
• API (Application Programming Interface) = a doorway that lets one program ask another program for data.
• Check data quality with ROCCC: Reliable, Original, Comprehensive, Current, Cited.
• Be careful with personal data. Collect only what you need and keep it safe. India's DPDP Act 2023 sets rules for this.

8. Data Cleaning: Missing Values

Ways to handle missing values

• Delete the row: fine if only a few rows are missing and they are random.
• Delete the column: if most of the column is empty (say 70% blank), it may be useless.
• Fill with mean (average): for numbers without extreme values.
• Fill with median (middle value): for numbers with extreme values, like salary.
• Fill with mode (most common value): for categories, like city.
• Fill with a label: write "Unknown" so the gap stays visible.
• Forward fill: copy the previous value. Good for time data, like daily stock prices.

Worked example

• Known ages: 22, 30, 41. Median = 30 (the middle one). Mean = (22 + 30 + 41) ÷ 3 = 93 ÷ 3 = 31.
• We filled age with the median, 30. Mean (31) would also be fine here.
• Known cities: Pune, Pune, Pune, Nagpur. Mode = Pune. So the blank city becomes Pune.

9. Duplicates, Outliers and Formats

Before cleaning

What we fix

• Duplicate: order 102 appears twice. Keep one. Otherwise sales are counted twice.
• Text format: trim extra spaces, use one case, and map "Bombay" to "Mumbai".
• Date format: convert all dates to one standard, like 2025-01-05 (YYYY-MM-DD).
• Outlier: ₹48,000 is far above the rest. Check it. A bulk order is real. A typo (₹480) should be fixed.
• Invalid value: a negative amount is impossible for a sale. Maybe it is a refund. Ask the data owner.

How to find outliers

• IQR rule: anything below Q1 − 1.5×IQR or above Q3 + 1.5×IQR (see topic 13).
• Z-score rule: anything more than 3 standard deviations from the mean (see topic 12).
• Box plot or scatter plot: outliers stand out visually.

10. Data Wrangling

Common steps

• Filter: keep only rows you need. Example: only 2025 orders.
• Derive a column: make a new column from others. Example: revenue = price × quantity.
• Change data type: text "500" → number 500, text "2025-01-05" → a real date.
• Split / combine: "Asha Verma" → first name and last name. Or join them back.
• Merge (join): combine orders with customer details using a shared ID.
• Aggregate: summarise many rows into a few. Example: total sales per city.
• Bin: put numbers into ranges. Example: age 18–25, 26–35, 36+.
• Pivot: turn row values into columns, like a pivot table.
• Normalise / scale: bring numbers to one range, like 0 to 1, so they are easy to compare.

Small example

Rule used for the band: price under ₹500 = Low, ₹500 or more = High.

11. Mean, Median, Mode

Worked example

Ravi's tea stall sold this many cups in 8 hours: 2, 4, 4, 4, 5, 5, 7, 9 (already sorted).

Key points

• Odd count: the median is the single middle value. Even count: average the two middle values.
• The mean is pulled by extreme values. The median is not. This is why we say the median is robust.
• Example: salaries ₹20k, ₹25k, ₹30k and the boss's ₹5 lakh. Mean = ₹1,43,750. Median = ₹27,500. The median is the honest "typical" salary.
• Data can have no mode, one mode, or two modes (bimodal).
• Use the mode for categories: "Which payment mode is most popular? UPI."

12. Spread: Range, Variance, Standard Deviation

Worked example (same tea data)

Data: 2, 4, 4, 4, 5, 5, 7, 9. Mean = 5.

• Range = max − min = 9 − 2 = 7.
• Population variance = 32 ÷ 8 = 4. Use this when you have all the data.
• Population SD = √4 = 2. So values are typically about 2 cups away from the mean.
• Sample variance = 32 ÷ (8 − 1) = 32 ÷ 7 ≈ 4.57. Use n − 1 when your data is only a sample of a bigger group.
• Sample SD = √4.57 ≈ 2.14.

Key points

• We square the differences so negatives don't cancel positives. (Notice the "x − mean" column adds up to 0.)
• Variance is in squared units ("cups²"), which is hard to read. SD brings it back to normal units ("cups").
• Small SD = values are close together (consistent). Large SD = values are spread out.
• Z-score = (x − mean) ÷ SD. It says how many SDs a value is from the mean. For 9 cups: (9 − 5) ÷ 2 = 2.
• Range is easy but uses only two values, so one outlier can change it a lot.
• In Excel: =VAR.S(), =STDEV.S() for samples; =VAR.P(), =STDEV.P() for populations.

13. Percentiles, Quartiles and IQR

Worked example

11 order values in ₹ hundreds, sorted: 10, 12, 14, 15, 16, 18, 20, 22, 25, 28, 90

90 is above the upper fence of 41.5, so 90 is an outlier. Also notice: mean = 270 ÷ 11 ≈ 24.5, but median = 18. The outlier pulls the mean up.

Key points

• 25th percentile = Q1, 50th = median = Q2, 75th = Q3.
• IQR ignores the top 25% and bottom 25%, so outliers don't affect it. Use it for skewed data.
• Different tools use slightly different methods. Excel's QUARTILE.INC gives Q1 = 14.5 and Q3 = 23.5 for this data. Both are acceptable; just say which you used.
• Business use: "90th percentile delivery time is 40 minutes" means 90% of orders arrive within 40 minutes.

14. Distributions and Skew

Key points

• Normal distribution: bell-shaped and symmetric. Mean = median = mode.
• 68–95–99.7 rule: in a normal distribution, about 68% of values are within 1 SD of the mean, 95% within 2 SD, and 99.7% within 3 SD.
• Example: exam marks with mean 60 and SD 10. About 68% of students score 50–70, and 95% score 40–80.
• Right skew (positive): long tail of big values. Mean > median. Examples: income, house prices, order values.
• Left skew (negative): long tail of small values. Mean < median. Example: marks in a very easy exam.
• Uniform distribution: all values equally likely, like rolling a fair dice.
• Bimodal: two peaks. Often means two groups are mixed, like weekday and weekend orders.

15. Correlation vs Causation

Key points

• The correlation coefficient (r, Pearson's r) goes from −1 to +1.
• +1 = perfect positive (both go up together). −1 = perfect negative (one up, other down). 0 = no straight-line link.
• Rough guide: |r| above 0.7 = strong, 0.3 to 0.7 = moderate, below 0.3 = weak.
• r only measures straight-line links. A curved pattern can have r near 0.
• A confounding variable is a hidden third factor that affects both things (like hot weather above).
• To prove causation, run a controlled experiment, like an A/B test (topic 20).
• In Excel: =CORREL(A2:A6, B2:B6).

Small example

Here r ≈ 0.99, a very strong positive correlation. But the link is still only proven to be a pattern, not a cause.

16. Probability Basics

Key points

• Basic formula: P(event) = favourable outcomes ÷ total outcomes.
• Rolling a 4 on a dice: 1 ÷ 6 ≈ 0.167.
• NOT rule: P(not A) = 1 − P(A). Not rolling a 4 = 5 ÷ 6.
• AND rule (independent events, one does not affect the other): multiply. Two heads in two coin tosses = 0.5 × 0.5 = 0.25.
• OR rule (events that can't both happen): add. Rolling a 1 or a 2 = 1/6 + 1/6 = 1/3.
• Conditional probability P(A | B) = chance of A, given B already happened. Read "|" as "given".
• Expected value = the long-run average result. Sum of (value × probability).

Business example

• P(buy) = 100 ÷ 1000 = 0.10 (10%).
• P(buy | coupon) = 60 ÷ 200 = 0.30 (30%).
• P(buy | no coupon) = 40 ÷ 800 = 0.05 (5%).
• Expected value of a lucky draw: 10% chance to win ₹500, else ₹0. EV = 0.1 × 500 + 0.9 × 0 = ₹50.

17. Sampling

Sampling methods

Key points

• Sampling bias = the sample does not represent the population. Example: an online survey misses people without internet.
• Bigger samples give more reliable answers, but only if they are not biased.
• Margin of error = how far the sample answer may be from the true answer. "40% ± 3%" means the truth is probably 37–43%.
• Central Limit Theorem (simple version): if you take many large samples and calculate each sample's mean, those means form a normal curve. This is why many tests work even when the data is not normal.
• Survivorship bias = studying only the winners. Example: only studying apps that succeeded, ignoring the ones that failed.

18. Hypothesis Testing

Key terms

• Null hypothesis (H0): "no effect, no difference". Example: the new button does not change sales.
• Alternative hypothesis (H1): "there is an effect". Example: the new button changes sales.
• Significance level (α, alpha): how much risk of a false alarm you accept. Usually 0.05 (5%).
• p-value: if H0 were true, how likely is a result at least this extreme? Small p = the result is surprising under H0.
• If p < 0.05 → reject H0. The result is "statistically significant".
• If p ≥ 0.05 → fail to reject H0. Not enough evidence. (We never say "H0 is proven true".)

Steps

1. Write H0 and H1.
2. Choose α (usually 0.05).
3. Pick the right test (see topic 19).
4. Calculate the test statistic and p-value (Excel or Python does this).
5. Compare p with α and decide.
6. Explain the result in plain business words.

Example

A coaching centre says its new method raises average marks. Old average = 60. A sample of new students averages 64. Test gives p = 0.03.

• H0: new method average = 60. H1: new method average ≠ 60.
• p = 0.03 is less than 0.05 → reject H0. The improvement is unlikely to be just luck.

19. Common Tests and Errors

Chi-square worked example

• Expected count if there is no link = (row total × column total) ÷ grand total = 100 × 100 ÷ 200 = 50 for every cell.
• For each cell: (observed − expected)² ÷ expected = (10)² ÷ 50 = 2. Four cells → chi-square = 2 + 2 + 2 + 2 = 8.
• For a 2×2 table at α = 0.05, the cut-off value is 3.84. 8 > 3.84, so p is small (about 0.005). Payment mode is related to city.

Type I and Type II errors

• Type I example: a COVID test says "positive" for a healthy person.
• Type II example: a COVID test says "negative" for a sick person.
• Power = 1 − β = the chance of catching a real effect. Bigger samples give more power.
• Confidence interval: a range that likely holds the true value. "Average spend is ₹450, 95% CI ₹420–₹480."

20. A/B Testing

Steps

1. Pick one goal metric. Example: conversion rate = buyers ÷ visitors.
2. Write H0 ("no difference") and H1.
3. Change only one thing. Example: button colour, or price shown.
4. Split users randomly: A = control (old), B = variant (new).
5. Decide sample size and run time in advance. Run at least one full week to cover weekdays and weekends.
6. Check significance, then decide.

Worked example

• Absolute lift = 5.0% − 4.0% = 1 percentage point.
• Relative lift = 1 ÷ 4 = 25% better.
• A two-proportion z-test gives p ≈ 0.016, which is less than 0.05. So B is a real winner, not luck.

Common mistakes

• Peeking: stopping the test as soon as it looks good. This creates false winners.
• Changing many things at once, so you don't know what worked.
• Too small a sample, or too short a run.
• Ignoring guardrail metrics: metrics that must not get worse, like refunds or app crashes.

21. Regression Basics

Simple linear regression

Formula: y = a + b × x

• a = intercept: the value of y when x is 0 (the base charge).
• b = slope: how much y changes when x goes up by 1 (the per-km rate).

Worked example (study hours data from topic 15)

• Mean of x = 3. Mean of y = 280 ÷ 5 = 56.
• Slope b = Σ(x − x̄)(y − ȳ) ÷ Σ(x − x̄)² = 75 ÷ 10 = 7.5.
• Intercept a = ȳ − b × x̄ = 56 − 7.5 × 3 = 33.5.
• Line: marks = 33.5 + 7.5 × hours. Each extra hour adds about 7.5 marks.
• Prediction for 6 hours: 33.5 + 7.5 × 6 = 33.5 + 45 = 78.5.

Key points

• Residual = actual − predicted. For 3 hours: predicted 56, actual 55, residual = −1.
• The best line makes the sum of squared residuals as small as possible. This is called least squares.
• R² (R-squared) goes from 0 to 1. It is the share of the change in y that the model explains. Here R² ≈ 0.99, which is a great fit.
• Multiple regression uses many inputs: sales = a + b1 × ad spend + b2 × discount.
• Logistic regression predicts a yes/no outcome, like "will this customer leave?"
• Don't predict far outside your data. 20 hours of study won't give 183.5 marks!
• In Excel: =SLOPE(), =INTERCEPT(), =RSQ(), or add a trendline to a scatter chart.

22. Exploratory Data Analysis (EDA)

A simple EDA checklist

1. Shape: how many rows and columns?
2. Columns and types: which are numbers, text, dates?
3. Missing values: how many blanks in each column?
4. Duplicates: any repeated rows?
5. Summary stats: min, max, mean, median, SD for number columns.
6. Counts for category columns: how many orders per city?
7. Univariate (one column at a time): histograms, box plots.
8. Bivariate (two columns together): scatter plots, correlation, grouped averages.
9. Time trends: line chart by day, week, month.
10. Write down questions that come up. They guide the next step.

Example findings from an EDA

23. KPIs and Metrics

Common business metrics

Key points

• Churn and retention are opposites: 5% churn = 95% retention (when you ignore new customers).
• A good KPI is clear, measurable, linked to a goal, and actionable.
• Vanity metric = looks good but doesn't help decisions. Example: total app downloads (many people never open the app).
• North Star metric = the one metric that best shows customer value. Example: for a food app, "orders delivered per week".
• Leading indicator predicts the future (website visits). Lagging indicator shows the past result (monthly revenue).
• Simple LTV above uses revenue. A stricter version multiplies by profit margin.

24. Cohort Analysis

Worked example: monthly retention

How to read it

• Each row is one cohort. Each column is "months since signup".
• Read across a row: how one group drops off. Jan users fall from 100% to 40% in 3 months.
• Read down a column: compare groups at the same age. Month 1: Jan 60%, Feb 56%, Mar 65%.
• March's Month-1 retention is the best. Ask: what changed in March? Maybe a new onboarding flow.
• Blank cells (—) are months that have not happened yet. This makes a triangle shape.
• Cohorts can also be by first purchase month, acquisition channel or city.

25. Funnel Analysis

Worked numbers

Key points

• Step conversion = users at this step ÷ users at the previous step.
• Overall conversion = users at the last step ÷ users at the first step = 6%.
• Here, "view → add to cart" is the weakest step (30%). Ideas: better photos, clearer prices, reviews.
• Split the funnel by device, city or traffic source. Often one segment (like Android) causes the drop.
• Funnels show where users leave. Surveys, session recordings or A/B tests show why.

26. Excel for Analysts

Sample sheet (cells A1:C6)

Must-know formulas

Pivot table

A pivot table summarises a big table by dragging fields. Put City in Rows and Sales in Values (Sum):

Other key points

• VLOOKUP only looks to the right and breaks if you insert a column. INDEX-MATCH and XLOOKUP fix both problems.
• Conditional formatting colours cells by rules. Example: red if sales < 400, or a colour scale (heat map).
• $A$1 is an absolute reference: it does not shift when you copy the formula. A1 shifts.
• Also useful: TRIM (remove extra spaces), CONCAT, LEFT/RIGHT, TEXT, IFERROR, Remove Duplicates, Data Validation, charts.
• Power Query (in Excel) cleans and combines data with clicks, and repeats the steps on new data.

27. SQL Basics for Analysts

Table: orders

Key clauses

• SELECT = which columns. FROM = which table.
• WHERE = filter rows, before grouping.
• GROUP BY = make groups, then use aggregate functions (functions that summarise many rows): COUNT, SUM, AVG, MIN, MAX.
• HAVING = filter groups, after grouping.
• ORDER BY = sort (DESC for biggest first). LIMIT = keep only the first few rows.
• DISTINCT = remove repeated values. CASE WHEN = an IF statement inside SQL.

-- Orders above ₹400, biggest first
SELECT customer, amount
FROM orders
WHERE amount > 400
ORDER BY amount DESC;
-- Result: Neha 700, Kiran 600, Asha 500

-- Total sales per city, only cities above ₹500
SELECT city, SUM(amount) AS total_sales, COUNT(*) AS orders
FROM orders
GROUP BY city
HAVING SUM(amount) > 500
ORDER BY total_sales DESC;
-- Result: Pune 1200 (2 orders), Delhi 900 (2 orders). Mumbai (400) is filtered out.

-- Label each order with CASE WHEN
SELECT customer,
       CASE WHEN amount >= 500 THEN 'High' ELSE 'Low' END AS order_size
FROM orders;

Order in which SQL runs

• This is why you cannot use a SELECT alias (like total_sales) inside WHERE in most databases: WHERE runs before SELECT.
• COUNT(*) counts all rows. COUNT(column) skips NULLs.
• NULL means "unknown". Check it with IS NULL, never = NULL.

28. SQL Joins and Window Functions

Join example

SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

• Find customers who never ordered: LEFT JOIN, then WHERE o.customer_id IS NULL → Ravi.
• One customer with many orders appears many times after a join. Watch out for double counting.
• Self join = a table joined to itself. Example: employees and their managers in one table.

Window functions

Uses the orders table from topic 27. Format: FUNCTION() OVER (PARTITION BY ... ORDER BY ...). PARTITION BY = split into groups. ORDER BY = order inside each group.

-- Rank orders inside each city
SELECT customer, city, amount,
       RANK() OVER (PARTITION BY city ORDER BY amount DESC) AS city_rank
FROM orders;

-- Running total of sales by date
SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

-- Previous order's amount (for day-over-day change)
SELECT order_date, amount,
       LAG(amount) OVER (ORDER BY order_date) AS prev_amount
FROM orders;

• City ranks: Pune → Neha 1, Asha 2. Delhi → Kiran 1, Ravi 2. Mumbai → Arjun 1.
• ROW_NUMBER = 1, 2, 3, 4 (always unique). RANK = 1, 2, 2, 4 (ties share, then skip). DENSE_RANK = 1, 2, 2, 3 (ties share, no skip).
• LAG = previous row's value. LEAD = next row's value.
• GROUP BY squeezes rows into one per group. Window functions keep all rows.
• Classic question: "2nd highest salary per department" → DENSE_RANK() ... = 2 in a subquery or CTE (a named temporary result made with WITH).

29. Python and pandas for Analysts

Core commands

import pandas as pd
import matplotlib.pyplot as plt

df = pd.read_csv("orders.csv")      # load a CSV file into a DataFrame

df.head()                # first 5 rows
df.shape                 # (rows, columns), e.g. (5, 5)
df.info()                # column names, types, non-null counts
df.describe()            # count, mean, std, min, quartiles, max
df.isnull().sum()        # missing values per column

# Select and filter
df["amount"]                          # one column
df[df["amount"] > 400]                # rows where amount is above 400
df[(df["city"] == "Pune") & (df["amount"] > 400)]   # two conditions

# Clean
df = df.drop_duplicates()                               # remove repeated rows
df["amount"] = df["amount"].fillna(df["amount"].median())  # fill blanks with median
df["city"] = df["city"].str.strip().str.title()         # " pune" -> "Pune"
df["order_date"] = pd.to_datetime(df["order_date"])     # text -> real date

# New column
df["high_value"] = df["amount"] >= 500

# Group and summarise (like GROUP BY / pivot table)
city_sales = df.groupby("city")["amount"].sum().sort_values(ascending=False)
# city
# Pune      1200
# Delhi      900
# Mumbai     400

# Join two tables (like SQL JOIN)
customers = pd.read_csv("customers.csv")
merged = df.merge(customers, left_on="customer_id", right_on="id", how="left")

# Simple bar chart
city_sales.plot(kind="bar", title="Sales by city")
plt.ylabel("Sales (₹)")
plt.show()

Key points

• how="left" in merge = LEFT JOIN. Options: "inner", "left", "right", "outer".
• df.pivot_table(index="city", values="amount", aggfunc="sum") = an Excel pivot table.
• value_counts() counts each category: df["city"].value_counts().
• Use & (and), | (or) with brackets around each condition. Plain and gives an error.
• NumPy = fast maths on arrays; pandas is built on it. seaborn = nicer statistical charts.
• Most analysts write Python in Jupyter Notebook or Google Colab: code, output and notes in one page.

30. Common Tools Overview

• ETL (Extract, Transform, Load) = moving data from sources, cleaning it, and loading it into a data warehouse (a big central database for analysis).
• Data lake = a store for raw data of any type, cleaned later.

31. Charts and Storytelling with Data

Choosing a chart

A simple story structure

1. Context: what question were we answering?
2. Finding: the one main insight, in one sentence.
3. Evidence: one or two charts that prove it.
4. Recommendation: what the business should do next.
5. Impact: what we expect to gain (money, users, time).

Good chart habits

• Give charts a title that states the finding: "Android orders fell 23% after the app update", not "Orders by platform".
• Bar charts must start at zero. Otherwise small differences look huge.
• Remove clutter: 3D effects, heavy grid lines, too many colours.
• Use one highlight colour for the thing that matters, and grey for the rest.
• Avoid pie charts with many slices. People can't compare angles well.
• Know your audience. A CEO wants the answer and the action. A fellow analyst wants the method.

32. Case Study: Why Did Orders Drop?

1. Ask

A food delivery app in Pune saw orders fall from 40,000 in February to 34,000 in March. That is a drop of 6,000, or 6,000 ÷ 40,000 = 15%. Question: "Why did March orders fall 15%, and how do we recover?"

2. Prepare

• Pull orders, app sessions and funnel events for Feb and March using SQL.
• Collect context: app release dates, offers, competitor news, weather, holidays.

3. Process

• Remove 300 duplicate test orders. Standardise area names. Convert timestamps to dates.

4. Analyse

Break the drop into segments (a drill-down):

• The whole drop (6,000) is on Android. iOS is flat. So it is not weather, prices or a competitor, which would affect both.
• Funnel check on Android: "cart → payment success" fell from 80% to 62% starting 5 March.
• An Android app update was released on 5 March. Error logs show a UPI payment bug in that version.

5. Share

Headline: "The March drop is caused by a UPI payment bug in the 5 March Android update. Fixing it should recover about 6,000 orders a month." Support it with one line chart of Android vs iOS daily orders and one funnel chart.

6. Act

• Engineering fixes the bug and releases a patch.
• Send affected users a small coupon to win them back.
• Set up an alert: if payment success falls below 75% on any platform, notify the team.
• Check the recovery in April with the same dashboard.

Quick Revision

The most-asked questions, with short and simple answers. Say these in your own words.

Basics & Process

• What is data analysis? Studying data to answer a question and support a decision.
• What does a data analyst do? Collects, cleans and analyses data, then explains findings with charts and dashboards.
• Four types of analytics? Descriptive (what), diagnostic (why), predictive (what next), prescriptive (what to do).
• Steps of analysis? Ask, Prepare, Process, Analyse, Share, Act.
• Structured vs unstructured? Rows and columns vs free form like text, images and audio.
• Levels of measurement? Nominal, Ordinal, Interval, Ratio (NOIR).
• Primary vs secondary data? Collected by you vs collected by someone else.

Cleaning

• How do you handle missing values? Find out why, then delete, or fill with mean, median, mode or "Unknown".
• Mean or median to fill blanks? Median if there are outliers or skew; mean if data is symmetric.
• How do you find outliers? IQR rule, z-score above 3, or a box plot.
• Should you delete outliers? Only if they are errors. Real extremes can be important.
• Cleaning vs wrangling? Cleaning fixes errors. Wrangling reshapes data for analysis.

Statistics

• Mean vs median? Mean is the average and is pulled by outliers. Median is the middle and is robust.
• What is standard deviation? The typical distance of values from the mean.
• Why n − 1 for sample variance? A sample slightly underestimates spread; n − 1 corrects it.
• What is IQR? Q3 − Q1, the spread of the middle 50%.
• Right skew? Long tail to the right; mean > median. Example: income.
• 68–95–99.7 rule? In a normal curve, 68% within 1 SD, 95% within 2, 99.7% within 3.
• Correlation vs causation? Moving together vs one causing the other. Prove cause with experiments.
• What is a p-value? How surprising the data is if the null hypothesis were true. Below 0.05 → significant.
• Type I vs Type II error? False alarm vs missed real effect.
• t-test vs chi-square? t-test compares means; chi-square checks if categories are related.
• What is an A/B test? Random split into control and variant, change one thing, compare a metric.
• What is R²? How much of the change in y the model explains, from 0 to 1.

Business Metrics

• Conversion rate? Buyers ÷ visitors.
• Churn rate? Customers lost ÷ customers at the start of the period.
• CAC and LTV? Cost to win one customer vs total value a customer brings. LTV:CAC above 3 is healthy.
• What is cohort analysis? Tracking groups that started at the same time to compare behaviour fairly.
• What is funnel analysis? Counting users at each step to find where they drop off.
• KPI vs metric? Every KPI is a metric, but only the few most important metrics are KPIs.

Tools

• VLOOKUP vs XLOOKUP? XLOOKUP can look left, defaults to exact match and has a "not found" value.
• Why INDEX-MATCH? Works in any direction and does not break when columns are inserted.
• SUMIF vs SUMIFS? SUMIF takes one condition; SUMIFS takes many.
• WHERE vs HAVING? WHERE filters rows before grouping; HAVING filters groups after.
• INNER vs LEFT JOIN? INNER keeps only matches; LEFT keeps all left rows, with NULLs where no match.
• RANK vs DENSE_RANK vs ROW_NUMBER? 1,2,2,4 vs 1,2,2,3 vs 1,2,3,4.
• pandas groupby? Splits rows into groups and summarises each, like SQL GROUP BY.
• Which chart for a trend? A line chart.

Learning Path

Go step by step. Practise each step on a real dataset (Kaggle or data.gov.in) before moving on.

1. Excel — formulas, XLOOKUP, SUMIFS, pivot tables, basic charts.
2. Basic statistics — mean, median, SD, percentiles, distributions, correlation.
3. SQL — SELECT, WHERE, GROUP BY, HAVING, JOINs. Then window functions and CTEs. Practise daily on LeetCode or HackerRank.
4. One BI tool — Power BI or Tableau. Build one dashboard with KPIs and filters.
5. Business metrics — conversion, churn, retention, CAC, LTV, funnels, cohorts.
6. Python — pandas for cleaning and grouping, matplotlib or seaborn for charts.
7. Hypothesis testing and A/B tests — p-values, t-test, chi-square, sample size basics.
8. Projects — 2 or 3 end-to-end projects (e.g. sales dashboard, customer churn analysis, A/B test report) on GitHub with a short write-up.
9. Communication — practise explaining one project in 2 minutes: problem, approach, finding, impact.
10. Case practice — "Why did metric X drop?" and guesstimates. Talk through your steps out loud.