Every project produces data: website visits, sales, survey answers, support tickets, workout logs, job applications, monthly spending. Most of it goes unread, and when someone does open a spreadsheet, it's easy to stare at numbers, build a chart, and still not know what to do differently.
This guide gives you a method that works on any project, whether it's a website, a small business, a side project, or your own budget. It's the first step in our Data Analytics learning path, and it sticks to a short list of free or widely used, well-established tools: Google Sheets or Microsoft Excel, Google Analytics 4, Google Search Console, Looker Studio, and Power BI.
What is data analysis?
Data analysis is the process of turning raw data into an answer you can act on. In practice that means asking a clear question, gathering the right data, cleaning it, looking for patterns and differences, checking whether those patterns are real, and explaining what they mean for a decision.
Analysis is different from reporting. A report says what happened ("we had 4,200 visitors last month"). Analysis says why it happened and what to do about it ("visitors from search fell 30% after we changed page titles; reverting the three biggest pages is the fastest fix").
The method at a glance
| Step | What you do | Output |
|---|---|---|
| 1. Decide | Name the decision and the question behind it | One sentence |
| 2. Measure | Choose a main metric, supporting metrics, and guardrails | A short metric list |
| 3. Collect | Find or start collecting the data | A raw dataset and notes on where it came from |
| 4. Clean | Fix duplicates, gaps, formats, and outliers | A clean, documented table |
| 5. Explore | Summarize, chart distributions, look for oddities | A feel for the data |
| 6. Analyze | Compare, segment, and check trends | Findings with evidence |
| 7. Visualize | Pick the right chart for each finding | A few clear charts |
| 8. Communicate | Answer first, then evidence, then caveats | A one-page summary |
| 9. Act and repeat | Make the change and measure again | A decision and a follow-up date |
Step 1: Start with the decision, not the data
The most common analysis mistake happens before anyone opens a spreadsheet: there's no clear question. "Let's look at the data" produces charts. "Should we keep paying for this ad campaign?" produces a decision.
Write one sentence:
We need to decide [decision] by [date], so we need to know [question].
Examples:
- We need to decide which two blog topics to write next month, so we need to know which existing topics bring the most engaged visitors from search.
- We need to decide whether to keep the Saturday opening hours, so we need to know whether Saturday sales cover the extra staffing cost.
- We need to decide where to cut $200 a month, so we need to know which spending categories grew the most this year. (Our budgeting guide covers the rest of that decision.)
If nobody would act differently whatever the answer, the analysis isn't worth doing yet.
Step 2: Choose the right metrics
A metric is a number you track. Good analysis uses a small, deliberate set:
- Main metric: the one number that best reflects success for this question, such as monthly sales, newsletter sign-ups, or job interviews booked.
- Supporting metrics: numbers that explain the main metric, such as traffic by source, conversion rate, or average order value.
- Guardrail metrics: numbers that must not get worse while you improve the main one, such as refund rate, page load time, or unsubscribes.
Two distinctions keep you honest:
- Leading vs lagging: lagging metrics (revenue, churn) tell you what already happened; leading metrics (trial sign-ups, demo requests) move earlier and are easier to influence.
- Actionable vs vanity: total page views or follower counts feel good but rarely change decisions. Rates and outcomes usually do: conversion rate, retention, revenue per visitor.
Define each metric precisely before you calculate it. "Active user" can mean visited once this month, logged in this week, or completed a key action. Teams argue about results surprisingly often because they're using different definitions.
Step 3: Collect the data
Start with data you already have before collecting anything new:
| Project | Where the data often lives |
|---|---|
| Website or blog | Google Analytics 4, Google Search Console, your site platform's dashboard |
| Online store | Your store platform's reports, payment processor exports, Google Analytics 4 |
| Small business | Point-of-sale exports, invoices, booking software, a customer list |
| Survey or research | Google Forms or Microsoft Forms responses exported to a spreadsheet |
| Personal finance | Bank and card transaction exports (CSV) |
| Job search or side project | A spreadsheet you keep yourself: date, action, outcome |
When you collect data yourself, write a short tracking plan first: what you'll record, how each field is defined, and in what format (for example, dates as YYYY-MM-DD). Ten minutes of planning prevents hours of cleaning later.
Record where every dataset came from, when you pulled it, and any filters you applied. Future you, or a colleague checking your work, will need it.
Respect privacy
Collect only what you need for the question. If you're handling personal data about customers, users, or employees, check the privacy laws that apply where you and they are located, keep the data access-controlled, and remove names and contact details from analysis copies when you don't need them. Website analytics tools that use cookies generally require visitor consent in the EU and UK, so check your consent setup before relying on the numbers. And don't paste personal or confidential data into an AI chatbot without checking your organization's rules and the tool's data policies.
Step 4: Clean the data
Analysts often say most of the work is cleaning, and it's true. Messy data quietly produces wrong answers. Work on a copy of the raw data, never the original, and keep a short log of every change.
A cleaning checklist:
- Remove exact duplicates (both Sheets and Excel have built-in tools to remove duplicates).
- Standardize formats: dates, currencies, units, and capitalization. "NY", "New York", and "new york" should be one value.
- Handle missing values: decide whether to leave blanks, fill them with a clear placeholder, or exclude those rows, and note what you chose.
- Fix obvious errors: negative ages, test orders, internal staff traffic, dates in the future.
- Check outliers: a $40,000 order in a shop that averages $40 might be a typo, a test, or your best customer. Investigate before deleting.
- Trim whitespace and split combined fields, such as "City, State" into two columns.
- Check totals against the source: if the store dashboard says 1,240 orders and your sheet has 1,198, find the gap before going further.
Step 5: Explore before you conclude
Before testing ideas, get a feel for the data:
- Counts and totals: how many rows, what date range, how many unique customers or pages.
- Averages and medians: the mean (average) is pulled by extreme values; the median (middle value) isn't. For incomes, order values, and time on page, the median is often the more honest "typical" number.
- Distributions: a histogram shows whether most values cluster together or spread out, and whether there are two different groups hiding in one average.
- Over time: a simple line chart by day, week, or month reveals trends, seasonality, and sudden breaks (often caused by a tracking change, not real behavior).
Pivot tables are the most useful exploration tool most people never learn. In Google Sheets or Excel, a pivot table summarizes thousands of rows in seconds: totals by month, averages by category, counts by source. If you learn one spreadsheet skill this year, make it this one.
Step 6: Analyze: compare, segment, and test
A number on its own means almost nothing. "We got 3,000 visitors" is neither good nor bad until you compare it with something.
Compare against a baseline
- Previous period: this month vs last month.
- Same period last year: controls for seasonality, which matters for retail, travel, education, and taxes.
- Target or benchmark: your goal, or an industry figure from a reliable source.
Segment
Break the total into groups: traffic source, device, new vs returning, region, product, customer type. Averages hide differences. Segmenting is where most real insights come from, like discovering that mobile visitors convert at half the rate of desktop visitors because the checkout button is below the fold.
Look at funnels and cohorts
- A funnel shows how many people complete each step toward a goal, revealing where they drop off.
Visited product page ############################## 1,000 (100%)
Added to cart ############ 400 ( 40%)
Started checkout ###### 200 ( 20%)
Purchased ### 100 ( 10%)
^ biggest drop: page to cart
- A cohort groups people by when they started (for example, everyone who signed up in March) and tracks them over time. Cohorts show whether retention is actually improving, which a single total can hide.
Don't confuse correlation with causation
Two numbers moving together doesn't mean one causes the other. Sales and website traffic might both rise in December because of the holidays, not because of each other. Before claiming a cause, look for:
- A third factor (seasonality, a promotion, a news story) driving both.
- Timing: did the cause actually come before the effect?
- A tracking change: sudden jumps often come from a new tag, filter, or definition.
The most reliable way to show cause is a controlled test: change one thing for one group (an A/B test) and compare it with an unchanged group over the same period. Make sure each group is large enough; tiny samples produce big, random swings.
Watch for classic traps
- Simpson's paradox: a trend that appears in every group can reverse when groups are combined, if the groups are very different sizes. Segment before you trust a total.
- Survivorship bias: analyzing only the customers who stayed, or the projects that succeeded, hides why others didn't.
- Small numbers: a jump from 2 sign-ups to 4 is "+100%" and means very little.
- Cherry-picking: choosing the date range that tells the story you want. Decide the range before you look.
Step 7: Visualize with the right chart
| You want to show | Use | Avoid |
|---|---|---|
| Change over time | Line chart | Pie charts, 3D charts |
| Comparison between categories | Bar chart (sorted) | Unsorted bars, too many colors |
| Parts of a whole (few parts) | Stacked bar, or a pie with 2–4 slices | Pies with many slices |
| Distribution | Histogram or box plot | Averages alone |
| Relationship between two numbers | Scatter plot | Dual-axis line charts that imply causation |
| Steps toward a goal | Funnel or bar chart | Separate unconnected numbers |
Label axes and units, start bar charts at zero, and put the takeaway in the chart title ("Mobile checkout converts at half the desktop rate") instead of a generic label ("Conversion rate by device"). Our stock market guide shows how much axis scale changes what a chart seems to say.
Step 8: Communicate the answer
Decision-makers read the first paragraph. Structure your summary as:
- Answer: the finding in one or two sentences.
- Recommendation: what to do, with expected impact if you can estimate it.
- Evidence: two or three charts or numbers that support it.
- Caveats: data gaps, assumptions, and what would change your mind.
- Next step: who does what, and when you'll measure again.
Keep the method and full tables in an appendix for anyone who wants to check your work.
Step 9: Act, then measure again
An analysis that doesn't lead to a change is just a report. Make the decision, write down what you expect to happen, and set a date to check. Over time, comparing expectations with results is how your judgment, and your analysis, gets better.
The tools: a short, proven toolkit
You don't need many tools. These are widely used, well documented, and either free or commonly available through work or school.
Google Sheets and Microsoft Excel: for almost everything
Spreadsheets handle most beginner and many professional analyses: cleaning, formulas, pivot tables, and charts.
- Google Sheets: free with a Google account, easy to share and collaborate on, and connects directly to other Google tools.
- Microsoft Excel: the workplace standard in many organizations, with powerful features for larger datasets, including Power Query for repeatable cleaning steps.
Skills worth learning first: sorting and filtering, SUMIFS/COUNTIFS/AVERAGEIFS, lookups (XLOOKUP in Excel, VLOOKUP or XLOOKUP in Sheets), pivot tables, and basic charts.
Google Analytics 4: for websites and apps
Google Analytics 4 (GA4) is Google's free web and app analytics platform, and it replaced Universal Analytics, which stopped processing data for standard properties in July 2023. GA4 measures events (page views, scrolls, clicks, sign-ups, purchases) rather than sessions alone.
Where to look first:
- Reports → Acquisition: where visitors come from (search, social, email, referral, paid).
- Reports → Engagement: which pages and events get attention.
- Key events: the actions that matter to you, such as a sign-up or purchase, marked as key events (previously called conversions).
- Explorations: build custom funnels, segments, and path analyses.
Setup tips that save a lot of pain:
- Define key events on day one; GA4 can't measure history you didn't track.
- Raise data retention in the admin settings; the default keeps detailed user-level data for exploration reports for only two months.
- Use UTM parameters (
utm_source,utm_medium,utm_campaign) on links in emails, social posts, and ads, so traffic is attributed correctly. Google offers a free campaign URL builder. - Filter internal traffic so your own visits don't skew the numbers.
- Set up consent properly where required, and remember that visitors who decline cookies, or use blockers, won't appear in full. GA4 numbers are a strong guide, not a perfect count.
- Link Search Console to see search queries alongside on-site behavior.
Google's free Skillshop courses cover GA4 in depth.
Google Search Console: for search traffic
Search Console is Google's free tool for site owners. Its Performance report shows the search queries and pages that earn impressions (how often you appeared), clicks, click-through rate, and average position in Google Search, with about 16 months of history. It also flags indexing and page-experience problems. For content and SEO projects, it answers questions GA4 can't, such as which searches you appear for but rarely get clicked.
Looker Studio: for free, shareable dashboards
Looker Studio (formerly Data Studio) is Google's free dashboard tool. It connects to GA4, Search Console, and Google Sheets, among many other sources, and turns them into shareable, auto-updating reports. Use it once you find yourself rebuilding the same charts every month.
Microsoft Power BI: for business dashboards
Power BI is Microsoft's business intelligence platform and a common requirement in analyst job postings. Power BI Desktop is free to download for Windows; sharing reports across an organization typically requires a paid license. It's especially strong when your organization already runs on Microsoft 365. See our Microsoft Power BI Data Analyst review for a structured way to learn it.
When you outgrow spreadsheets
Once datasets reach hundreds of thousands of rows, or you need repeatable analysis, the next steps are SQL (the standard language for querying databases) and, later, a programming language such as Python. GA4 can export raw event data to Google's BigQuery data warehouse, which you query with SQL. Both of the entry-level certificates in this path teach SQL.
A worked example: a small website
Say you run a blog or small business site and want to decide which topics to write about next quarter.
- Decision: choose three topics for next quarter.
- Metrics: main metric, engaged sessions from organic search per article; supporting, Search Console impressions and click-through rate by query; guardrail, key event completions (for example newsletter sign-ups).
- Collect: export the last 12 months of page-level data from GA4 and query-level data from Search Console into Google Sheets.
- Clean: merge duplicate URLs (with and without a trailing slash), remove internal and test pages, and standardize page titles.
- Explore: a pivot table of engaged sessions by topic, and a line chart by month to spot seasonality.
- Analyze: compare topics on engagement per article (not just totals, since some topics simply have more articles), and use Search Console to find queries with high impressions but low click-through rate, which are opportunities.
- Visualize: a sorted bar chart of engagement per article by topic, and a table of high-impression, low-click queries.
- Communicate: "Write next about topics A, B, and C. They earn twice the engagement per article of other topics, and Search Console shows 40 related queries where we already appear but rarely get clicked."
- Act: publish, then check the same metrics after 60 to 90 days.
The same nine steps work for a shop's sales data, a survey, or your monthly spending. Only the data sources change. For two more worked examples, see how to analyze a rental property, which applies the method to a real estate deal, and our health literacy guide, which applies it to health studies and headlines.
Common mistakes to avoid
- Analyzing without a decision in mind.
- Trusting totals without segmenting.
- Reporting percentages on tiny numbers.
- Comparing periods of different lengths, or holiday weeks with ordinary weeks.
- Ignoring tracking changes, which create fake jumps and drops.
- Over-decorating charts with 3D effects, dual axes, and too many colors.
- Burying the answer below pages of methodology.
- Treating analytics numbers as exact, when consent choices, blockers, and sampling mean they're estimates.
Frequently asked questions
What is the best free tool for data analysis?
For most beginners, a spreadsheet: Google Sheets is free, and Excel is widely available through work or school. For website data, add Google Analytics 4 and Google Search Console, both free. Looker Studio turns them into free dashboards.
Do I need to learn SQL or Python?
Not to start. Spreadsheets handle most beginner projects. Learn SQL when datasets get large or live in a database, and Python when you want to automate repeated analysis or go into data science.
How accurate is Google Analytics?
GA4 is a reliable guide to trends and comparisons, but not a perfect count. Visitors who decline cookie consent, use ad blockers, or browse in some privacy modes may be partly or entirely missing. Compare GA4 with other sources, such as sales records, for important decisions.
How do I get better at data analysis?
Practice on data you care about: your budget, a hobby, or a project at work. Follow the same steps every time, write down what you expect before you look, and compare. A structured course, such as the Google Data Analytics Certificate, adds the vocabulary and a portfolio project.
Check your understanding
Answer each question from memory before opening it. Recalling, not rereading, is what makes learning stick (here's why).
1 What should every analysis start with?
The decision it should inform, written as: we need to decide X by a date, so we need to know Y.
2 What are guardrail metrics?
Numbers that must not get worse while you improve your main metric, such as refund rate or unsubscribes.
3 Why is the median often better than the mean for things like order value or income?
The mean is pulled up or down by extreme values. The median is the middle value, so it better represents a typical case.
4 Two metrics rise together. Name two reasons that doesn't prove one causes the other.
Any two of: a third factor like seasonality drives both, the timing is wrong, the change came from a tracking change, or it's chance in a small sample.
5 Why are Google Analytics numbers estimates rather than exact counts?
Visitors who decline cookie consent, use blockers, or browse in some privacy modes may be partly or entirely missing.
Where to go next
You've finished step 1 of the Data Analytics path. Next:
- Google Data Analytics Certificate review: spreadsheets, SQL, Tableau, and R in a beginner program.
- IBM Data Analyst Professional Certificate review: Excel, SQL, and Python.
- Microsoft Power BI Data Analyst review: dashboards for business.
Sources
- Google Analytics Help Center: GA4 setup, key events, data retention, and BigQuery export.
- Search Console Help: the Performance report and its metrics.
- Looker Studio Help: connecting data sources and building reports.
- Google Skillshop: free Google Analytics training.
- Microsoft Learn: Power BI: official Power BI documentation and training.
- Microsoft Excel help: PivotTables, Power Query, and functions.