Exploratory Analysis: The First 5 Steps

By MCP Analytics Team | Published , updated

Exploratory checks can catch catastrophic errors before they become expensive mistakes.

Worked Example: A Missing Month and One Huge Sale

Question: Did the import record zero sales in October, or leave October out?

Inputs: Simulated data, seed 27. The script makes daily sales rows for two store tiers from September 1 to November 30, 2025. It leaves October out on purpose and types in one very high Premium sale on November 25. No real store data is used.

Method: Run these checks: a revenue histogram, a date gap check, a correlation table (a measure of how two values move together), a monthly total, and revenue quartiles by tier (values that split a group into four parts). The script also repeats the correlation table and the November total without the typed-in sale. The full results are below.

Complete Python script (run with python3 -I):

import numpy as np
import pandas as pd

# Simulated daily sales. October rows are skipped on purpose
# and one very large Premium sale is typed in on 25 November.
seed = 27
rng = np.random.default_rng(seed)
dates = pd.date_range('2025-09-01', '2025-11-30', freq='D')
rows = []
for day in dates:
    if day.month == 10:
        continue
    for segment, base in [('Basic', 900), ('Premium', 1400)]:
        orders = int(rng.integers(25, 56))
        visits = int(orders * rng.uniform(5.0, 8.0))
        revenue = float(base + orders * rng.uniform(12, 22)
                        + rng.normal(0, 90))
        if day == pd.Timestamp('2025-11-25') and segment == 'Premium':
            revenue = 52000.0
        rows.append((day, segment, orders, visits, revenue))

df = pd.DataFrame(rows, columns=['date', 'segment', 'orders',
                                 'visits', 'revenue'])

print(f'Simulated data, seed {seed}')
print(f'Rows: {len(df)}; dates: {df.date.min().date()} '
      f'to {df.date.max().date()}')
print('\n1. Revenue histogram (counts by range)')
bins = [0, 1000, 2000, 3000, 4000, 60000]
labels = ['0-999', '1000-1999', '2000-2999', '3000-3999',
          '4000 and above']
hist = pd.cut(df.revenue, bins=bins, labels=labels,
              right=False, include_lowest=True)
for label, count in hist.value_counts(sort=False).items():
    print(f'{label}: {count}')
print(f'Revenue median: {df.revenue.median():.1f}; '
      f'max: {df.revenue.max():.1f}')

print('\n2. Missing-date check (expected daily dates)')
expected = pd.date_range(dates.min(), dates.max(), freq='D')
seen = pd.DatetimeIndex(df.date.unique())
missing = expected.difference(seen)
print(f'Expected dates: {len(expected)}; '
      f'dates with rows: {len(seen)}; absent dates: {len(missing)}')
oct_rows = df.loc[df.date.dt.month == 10, "date"].nunique()
print(f'October dates with rows: {oct_rows}')

cols = ['orders', 'visits', 'revenue']
print('\n3. Correlation matrix')
print(df[cols].corr().round(2).to_string())
print('Same matrix without the 52000 row')
print(df[df.revenue < 10000][cols].corr().round(2).to_string())

print('\n4. Monthly time series (row count and revenue sum)')
monthly = df.groupby(df.date.dt.to_period('M')).agg(
    rows=('revenue', 'size'), revenue_sum=('revenue', 'sum'))
for month, row in monthly.iterrows():
    print(f'{month}: rows={int(row.rows)}, '
          f'revenue_sum={row.revenue_sum:.1f}')
nov = df[(df.date.dt.month == 11) & (df.revenue < 10000)]
print(f'2025-11 without the 52000 row: {nov.revenue.sum():.1f}')

print('\n5. Revenue by segment (quartiles)')
for segment, group in df.groupby('segment'):
    q1, median, q3 = group.revenue.quantile([.25, .5, .75])
    print(f'{segment}: n={len(group)}, q1={q1:.1f}, '
          f'median={median:.1f}, q3={q3:.1f}, '
          f'max={group.revenue.max():.1f}')

Printed output:

Simulated data, seed 27
Rows: 120; dates: 2025-09-01 to 2025-11-30

1. Revenue histogram (counts by range)
0-999: 0
1000-1999: 75
2000-2999: 44
3000-3999: 0
4000 and above: 1
Revenue median: 1853.0; max: 52000.0

2. Missing-date check (expected daily dates)
Expected dates: 91; dates with rows: 60; absent dates: 31
October dates with rows: 0

3. Correlation matrix
         orders  visits  revenue
orders     1.00    0.84     0.18
visits     0.84    1.00     0.17
revenue    0.18    0.17     1.00
Same matrix without the 52000 row
         orders  visits  revenue
orders     1.00    0.84     0.47
visits     0.84    1.00     0.45
revenue    0.47    0.45     1.00

4. Monthly time series (row count and revenue sum)
2025-09: rows=60, revenue_sum=109521.5
2025-11: rows=60, revenue_sum=161688.2
2025-11 without the 52000 row: 109688.2

5. Revenue by segment (quartiles)
Basic: n=60, q1=1419.7, median=1565.4, q3=1730.2, max=2162.4
Premium: n=60, q1=1957.2, median=2122.8, q3=2263.5, max=52000.0

Result: There are 120 rows across 60 dates. The expected date span has 91 dates, with 31 dates absent and no October dates with rows. That is a gap in the data, not sales recorded as zero, and the monthly table has no October line at all. The histogram has one value in its "4000 and above" range, with a median of 1853.0. The Premium tier has a median of 2122.8 and a max of 52000.0. Both the gap and the large sale were built in, so the checks find what was put in. The one large sale also moves the numbers: orders and revenue correlate at 0.18 with it and 0.47 without it. November shows 161688.2 against 109521.5 for September, but without that one row November is 109688.2, so the apparent jump is that row. This shows why a group summary and a chart can tell different parts of the story.

Limit: This made-up set shows what the checks can find in this example. It cannot say what happened in a real store.

Picture a team that spent three weeks building a revenue forecasting model. Beautiful regression. Checked assumptions. Executive presentation ready.

Then someone looked at a histogram.

In the example above, the import left October out, so there were no October rows. The missing month was not recorded as zero sales. A model fitted to that file would never see October, and its November rebound would look like a jump out of nowhere.

Three weeks of work, built on a file with a missing month. One look at the data would have shown it.

Why Check First

Here's the habit we suggest for any data you plan to analyze: check the data now or rebuild your work later.

Exploratory analysis isn't about making your analysis prettier. It's about not building on quicksand.

Signal: These checks can help you spot gaps, unusual values, trends, and group differences.

Noise: Everything else until you've run these checks.

Check 1: Distribution Histogram (Spot Outliers and Skewness)

First thing I do with any dataset: plot the distribution of key metrics.

Take a sales file where revenue per order looks normal in the summary stats. The histogram can tell a different story: a few very large orders, far from the rest, can sit behind an ordinary-looking average. In the worked example, one Premium sale of 52000.0 sits in the "4000 and above" bin while the median is 1853.0.

A very large order may be a data entry error, a wholesale order that should be filtered out, or a real sale. It can skew a churn rate analysis or cohort metrics, so find out which it is before you analyze.

What to look for:

Check 2: Missing Data Heatmap (See What's Not There)

Here's what matters: you can't analyze data you don't have.

A missing data heatmap shows you holes in your dataset. Not as a percentage buried in a report, but visually: white squares where you should have values.

I've seen this catch:

The pattern matters. A 5% missing rate alone does not show whether gaps affect the result. Check what is missing and how the analysis uses it. Missing all data for specific dates, customers, or products? That's a problem.

Check 3: Correlation Matrix (Find Surprising Relationships)

This is where exploratory analysis gets interesting.

A correlation matrix shows you which variables move together. Sometimes you find what you expect. Sometimes you find something that changes your entire analysis.

Here is a hypothetical example: a SaaS company analyzing churn. They expect usage metrics to correlate with retention. They do.

But the correlation matrix shows something else: customer support tickets have a strong positive correlation with retention.

Wait, what? More support tickets = better retention?

After digging deeper, they might find that power users file more tickets because they are pushing the product harder. Low-ticket customers weren't engaged enough to even ask for help. They just churned quietly.

An insight like that could change a retention strategy, and a correlation matrix is where it can first show up. A correlation alone does not say why, so follow it up. Outliers matter here too: in the worked example, one large sale pulls the orders and revenue correlation from 0.47 down to 0.18.

Skip to the bottom line: Don't guess which variables matter. Let the data show you.

Check 4: Time Series Plot (Check for Trends and Seasonality)

If your data has dates, plot it over time. Every single time.

Three things you need to know:

When people upload CSV files for cohort analysis churn retention metrics, they often want to jump straight to retention curves. I always tell them: look at the time series first.

Why? Because if you launched a major feature in June, your pre-June and post-June cohorts aren't comparable. Your retention analysis will be mixing two different products.

The time series plot shows you those inflection points immediately.

Check 5: Box Plot by Group (Compare Distributions Quickly)

Final check: compare distributions across groups.

Box plots show you the median, quartiles, and outliers for each segment side by side. It's the fastest way to answer: "Are these groups actually different?"

I use this constantly for:

Suppose a team is convinced their premium tier has much higher retention than their basic tier. Box plots could show huge overlap, similar medians, and just a few outliers driving the premium average up. In the worked example, the Premium median is 2122.8 and its max is 52000.0.

Saved them from building an entire growth strategy on a statistical mirage.

What These Checks Show in the Example

Back to the missing month in the worked example.

Here's what would have caught it:

Missing data heatmap: A check for missing dates would have shown that October had no rows.
Time series plot: Would have shown a gap where October should be.

Any one of these checks would have shown the problem early.

Without them, a team can spend three weeks building a model on a broken file.

This is why I'm obsessive about exploratory analysis. It's not about being thorough. It's about not wasting time building on garbage data.

Upload a CSV for Cohort and Retention Analysis

When you want to run cohort analysis on retention data, or look at Shopify churn rate analysis tools, the first thing to do is run exploratory checks.

Upload your CSV. The checks show:

Then, and only then, run the actual cohort analysis.

Because retention metrics built on bad data aren't insights. They're expensive mistakes.

MCP Analysis: Run These Checks on Your Data

Here's what we built: upload data to our free data profile tool and check the basics before you do anything else.

Why? Because we got tired of seeing smart people waste time on avoidable errors.

These checks can help you spot:

Signal: Checking the data before you build on it.

Noise: Weeks of work on a broken foundation.

What to Remember

1. Never skip exploratory analysis. Every dataset has surprises. Find them before they find you.

2. These checks can help you spot gaps, unusual values, trends, and group differences. Distribution histogram, missing data heatmap, correlation matrix, time series plot, box plot by group.

3. Make it a habit. Don't make exploratory analysis optional. Build it into your workflow so it happens every time.

TL;DR

Jumping straight to regression? You'll miss outliers, missing data, and skewed distributions.

Run these checks first:

  1. Histogram (spot outliers)
  2. Missing data heatmap (see gaps)
  3. Correlation matrix (find relationships)
  4. Time series (check trends)
  5. Box plots (compare groups)

Ready to run exploratory analysis on your data? Open the free data profile tool to check your CSV. Catch data problems before they become analysis disasters.

Profile your data free →

Your turn

Bring your own data and the question you actually need answered.

MCP AnalyticsData science by email Send us your data and your question, and we will reply. ds@mcpanalytics.ai