The data analysis process: 4 stages from question to delivered insight

Stage 1: Plan the question

Analysis starts before any data, with three decisions.

The goal. What decision will this analysis inform, and who makes it? “Understand our marketing” informs nothing. “Decide whether to move 20% of search budget to social next quarter” does.

The question. Turn the goal into one question the data can answer: “Which campaigns returned the most revenue per dollar over the last 28 days?” A good question names the metric, the grouping and the period.

The metrics. Pick the ones that change the decision. Two common traps:

• Vanity metrics move without changing anything: page views, followers, total sessions. Prefer metrics tied to the decision, such as revenue per campaign or cost per order.
• Metrics vs dimensions. A metric is a number you add up or average (revenue, orders). A dimension is how you group it (campaign, country, week). Mixing them up, for example averaging an average, produces numbers that look precise and mean little.

Write the definition of each metric down at this stage. “Revenue” with or without returns is a decision, not a detail, and it is much cheaper to make it before the work than to discover it in the meeting.

Stage 2: Collect the data

Collection means getting every source the question needs into one place, at the grain the question needs.

Where to put it:

• Spreadsheets work for a few thousand rows and one person. They break on history, size and repeated refreshes.
• A data warehouse holds every source at full grain and keeps history. It is where joins between sources happen reliably.
• The GA4 export to BigQuery is the usual way to get web behaviour as raw events; see the GA4 export schema.

Three habits save time later: keep external data at its finest grain (daily per campaign, not monthly totals), keep the number of connectors you maintain small, and keep the raw data as loaded, so any number can be rebuilt. What is raw data explains why aggregates cannot replace it.

Stage 3: Prepare the data

Preparation turns loaded rows into something the question can be asked of. It has three parts:

1. Cleaning. Remove duplicates, fix types, handle missing keys, apply status rules (which orders count).
2. Blending. Join sources: spend to orders, sessions to customers. This is where most silent errors enter, because two tables at different grains join without complaint. Common data quality issues shows a join on demo data that understated a quarter’s spend by 24%.
3. Modelling. Shape the result into entities and metrics people can reuse: one orders table, one customers table, one definition of revenue. A star schema is the common pattern.

Spreadsheets can do small versions of all three with lookups and pivot tables. In a warehouse they are SQL, written once and run on schedule. Either way, check totals before and after every step: if revenue changes between cleaning and blending, you need to know why before anyone sees it.

AI assistants can draft cleaning and join SQL quickly. They cannot know which join is correct for your business unless someone has written it down; review what they produce against the definitions from stage 1.

Stage 4: Deliver the insight

Delivery has three jobs: show the result, get it to the people who decide, and help them act on it.

Show it

Choose the chart for the comparison the question asks: bars for ranking, lines for change over time, a table when people need exact values. Lead with the answer, not the method. Data visualization and bad data visualization examples cover the craft.

Share it

Put the result where the decision-maker already works: a sheet they open every Monday, a dashboard on the team screen, a message in their channel. Match the refresh to the decision: a weekly decision needs a weekly refresh, not a one-off export.

Help them act on it

State the recommendation, its owner and the next step. Note what the analysis does not cover. Keep two or three validations ready, such as a total that matches finance, because the first question in the meeting is usually “is this right?”.

Why delivered insights go stale: one analysis, re-run weekly

Most process guides end at “present the findings”. The problem is what happens a week later. We took one question and answered it every week for a quarter on the [Demo] E-commerce data: which ad campaign had the best return on ad spend over the previous 28 days? Revenue is completed orders attributed to the campaign of the session they came from; spend is the campaign’s ad spend. Campaigns that spent $100 or less in the 28 days are left out, which excludes a paused campaign and a near-dormant one that would otherwise top the list on tiny spend.

The answer changed 7 times in 12 weeks. Four different campaigns were “the best” at some point. Three times the winner led by less than half a point of ROAS (14 July, 4 August, 29 September), well within normal week-to-week movement.

The week’s winner was still the winner four weeks later in only 3 of 9 cases.

A slide delivered on 4 August saying “brand search - exact is our best campaign” was true that day. Acted on four weeks later, it would have moved budget to a campaign that had fallen to eighth of twelve, at a ROAS of 4.9. Meanwhile the campaign that topped the list on 29 September had ranked tenth on 1 September. The analysis was right; the delivery treated a moving answer as a fixed one.

The query, so you can run the same check on your own data:

WITH revenue AS (
  SELECT o.order_date AS day, s.campaign,
    SUM(p.line_revenue) AS revenue
  FROM `owox-demo.ecommerce.orders` AS o
  JOIN `owox-demo.ecommerce.sessions` AS s USING (session_id)
  JOIN `owox-demo.ecommerce.purchases` AS p USING (order_id)
  WHERE o.status = 'Completed'
  GROUP BY day, campaign
),
ad_spend AS (
  SELECT date AS day, campaign, SUM(spend) AS spend
  FROM `owox-demo.ecommerce.ad_performance`
  GROUP BY day, campaign
),
weeks AS (
  SELECT week_end
  FROM UNNEST(GENERATE_DATE_ARRAY(
    '2026-07-07', '2026-09-29', INTERVAL 7 DAY)) AS week_end
),
spend_28d AS (
  SELECT w.week_end, a.campaign, SUM(a.spend) AS spend
  FROM weeks AS w
  JOIN ad_spend AS a ON a.day BETWEEN
    DATE_SUB(w.week_end, INTERVAL 27 DAY) AND w.week_end
  GROUP BY week_end, campaign
),
revenue_28d AS (
  SELECT w.week_end, r.campaign, SUM(r.revenue) AS revenue
  FROM weeks AS w
  JOIN revenue AS r ON r.day BETWEEN
    DATE_SUB(w.week_end, INTERVAL 27 DAY) AND w.week_end
  GROUP BY week_end, campaign
)
SELECT week_end, campaign,
  ROUND(revenue / spend, 1) AS roas
FROM spend_28d
JOIN revenue_28d USING (week_end, campaign)
WHERE spend > 100
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY week_end ORDER BY revenue / spend DESC) = 1
ORDER BY week_end

What a delivered insight should carry

From that run, a checklist. Every insight that leaves the analyst should include:

1. The question and the period, stated exactly: “best 28-day ROAS, week ending 4 August”.
2. The margin, not just the winner. “Leads by 0.4” reads very differently from “leads by 3”.
3. The definitions: what revenue includes, how campaigns get credit.
4. The data-through date of every input. A ratio is only as fresh as its stalest input; see data freshness.
5. An owner who answers when it is questioned.
6. A way to re-run it, so next week’s reader gets next week’s answer instead of last month’s slide.

The sixth point changes the shape of delivery. An insight that will be acted on more than once should not be a screenshot or an export. It should be a report that refreshes from the same query.

How OWOX Data Marts handles the last two stages

In OWOX Data Marts, stage 3 ends in Data Marts: the analyst writes the cleaning and blending SQL once in your warehouse, describes it, and declares how Data Marts relate, which forms the data model. The definitions from stage 1 live there, next to the data.

Stage 4 becomes a report on a Data Mart, delivered to Google Sheets, Microsoft Excel, Data Studio, Slack, Microsoft Teams, Google Chat or email, and refreshed on a schedule by a report trigger. The person who asked gets the same question answered again next week, from the same definition, and each run records when the source tables were last updated. The analyst stays the owner of the logic without being the person who re-runs it every Monday.

You can try OWOX Data Marts on your own warehouse.

GA47 Analytics Tactics to Get More Traffic and Sales Using GA4April 30, 2024→Analytics ToolsData Analysis Tools in 2026: 19 Tools by the Job They DoOctober 2, 2026→Marketing AnalyticsMarketing Analytics: What It is and How It Drives Better DecisionsOctober 14, 2026→

See all articles →