The Data Analytics Process

-- Collect August and July orders for the revenue decline analysis
SELECT
    DATE_FORMAT(order_date, '%Y-%m')  AS month,
    category,
    city,
    customer_segment,
    COUNT(*)                           AS order_count,
    SUM(amount)                        AS revenue,
    SUM(CASE WHEN returned = 1 THEN 1 ELSE 0 END) AS returns
FROM orders
WHERE order_date BETWEEN '2026-07-01' AND '2026-08-31'
GROUP BY
    DATE_FORMAT(order_date, '%Y-%m'),
    category, city, customer_segment
ORDER BY month, revenue DESC;

-- Common data cleaning operations in SQL

-- 1. Standardise city names (case + spelling variations)
UPDATE orders
SET city = CASE
    WHEN LOWER(city) IN ('delhi', 'new delhi', 'ncr', 'delhi ncr') THEN 'Delhi NCR'
    WHEN LOWER(city) IN ('mumbai', 'bombay')                        THEN 'Mumbai'
    WHEN LOWER(city) IN ('bangalore', 'bengaluru')                  THEN 'Bangalore'
    ELSE city
END;

-- 2. Remove test orders
DELETE FROM orders WHERE customer_id LIKE 'TEST_%';

-- 3. Check for and handle NULLs in key columns
SELECT
    COUNT(*)                                         AS total_rows,
    SUM(CASE WHEN category IS NULL THEN 1 ELSE 0 END) AS null_category,
    SUM(CASE WHEN amount   IS NULL THEN 1 ELSE 0 END) AS null_amount,
    SUM(CASE WHEN city     IS NULL THEN 1 ELSE 0 END) AS null_city
FROM orders
WHERE order_date BETWEEN '2026-07-01' AND '2026-08-31';

-- 4. Remove duplicates (keep the row with the latest updated_at)
DELETE o1 FROM orders o1
INNER JOIN orders o2
    ON o1.order_id = o2.order_id AND o1.updated_at < o2.updated_at;

-- Revenue analysis: MoM comparison by category
SELECT
    category,
    SUM(CASE WHEN month = '2026-07' THEN revenue ELSE 0 END)       AS july_revenue,
    SUM(CASE WHEN month = '2026-08' THEN revenue ELSE 0 END)        AS aug_revenue,
    ROUND(100.0 * (
        SUM(CASE WHEN month = '2026-08' THEN revenue ELSE 0 END) -
        SUM(CASE WHEN month = '2026-07' THEN revenue ELSE 0 END)
    ) / NULLIF(SUM(CASE WHEN month = '2026-07' THEN revenue ELSE 0 END), 0), 1)
                                                                    AS mom_pct_change,
    -- Volume vs value breakdown
    SUM(CASE WHEN month = '2026-07' THEN order_count ELSE 0 END)   AS july_orders,
    SUM(CASE WHEN month = '2026-08' THEN order_count ELSE 0 END)    AS aug_orders,
    ROUND(
        SUM(CASE WHEN month = '2026-07' THEN revenue ELSE 0 END) /
        NULLIF(SUM(CASE WHEN month = '2026-07' THEN order_count ELSE 0 END), 0)
    , 0)                                                             AS july_aov,
    ROUND(
        SUM(CASE WHEN month = '2026-08' THEN revenue ELSE 0 END) /
        NULLIF(SUM(CASE WHEN month = '2026-08' THEN order_count ELSE 0 END), 0)
    , 0)                                                             AS aug_aov
FROM monthly_summary     -- the aggregated table from step 2
GROUP BY category
ORDER BY mom_pct_change ASC;   -- worst performers first

-- Step 6: Follow-up measurement query (run in September)
SELECT
    DATE_FORMAT(order_date, '%Y-%m-%d')   AS week_start,
    ROUND(SUM(amount) / COUNT(*), 0)      AS electronics_aov,
    COUNT(*)                              AS order_count,
    SUM(amount)                           AS revenue
FROM orders
WHERE category   = 'Electronics'
  AND order_date BETWEEN '2026-09-01' AND '2026-09-30'
GROUP BY DATE_FORMAT(order_date, '%Y-%m-%d')
ORDER BY week_start;
-- Compare to: July benchmark AOV ₹8,200 | August (problem) AOV ₹5,400
-- Target for September: ≥ ₹7,000

What are the steps of the data analytics process?

The data analytics process has 6 steps: (1) Define the question — what business problem are you solving? (2) Collect the data — identify sources and pull the relevant data. (3) Clean the data — fix errors, handle missing values, standardise formats. (4) Analyse the data — apply calculations, aggregations, and statistical methods to find patterns. (5) Visualise and communicate — present findings in charts, dashboards, or a summary that non-technical stakeholders can act on. (6) Make a decision and measure — the analysis must lead to a specific action, and the outcome of that action should be tracked. Most beginners spend too long at step 3 and skip step 6 entirely.

How long does the data analytics process take?

The time depends entirely on the complexity of the question and the state of the data. A simple analysis — for example, which product category had the highest return rate last month — might take 30 minutes in SQL and Excel if the data is already clean. A complex analysis — predicting customer churn using 18 months of transaction data — might take 2–3 weeks including data collection, cleaning, model building, and validation. In practice, data cleaning (step 3) takes the most time — often 50–70% of the total time on a new dataset. Analysts who have worked with a data source before move faster because they know where the quality problems are.

What is the most important step in data analytics?

Step 1 — defining the question — is the most important and most frequently skipped. Every other step depends on it. If the question is vague ("analyse sales") the analysis will be unfocused and the insight will be unusable. A precise question ("which product categories had a >15% MoM revenue decline in the last 3 months, and is the decline concentrated in specific cities?") guides exactly what data to collect, which columns to clean, what calculations to run, and what the answer looks like. Analysts who start with clear questions consistently produce more actionable output than those who start with data exploration and hope to find something interesting.

What is data cleaning and why does it take so long?

Data cleaning is the process of fixing errors, inconsistencies, and gaps in raw data so it can be analysed correctly. Real-world data is almost always dirty: amounts stored as text with currency symbols ("₹1,200"), city names with typos ("Delhi" and "delhi" and "New Delhi" all meaning the same thing), dates in inconsistent formats ("15-08-2026" and "Aug 15 2026" in the same column), duplicate rows from multiple system exports, and missing values where a field was left blank. Data cleaning takes a large proportion of analysis time because fixing one issue often reveals another. Experienced analysts know the common issues in their data sources and build reusable cleaning pipelines so they only solve each problem once.