What Analytics Owns

By the end of this session you will be able to:

• Turn a vague request into a written question spec: metric, grain, window, filter, decision, owner
• Show on your own machine that two defensible definitions of "active user" produce two different numbers from identical rows
• Say where your job ends and the pipeline's job begins, and name which failures are yours to fix

The job in one sentence

Analytics takes a question in and hands back a number someone acts on.

Every part of that sentence carries weight. A question means a person asked, not that you noticed something interesting in a dataset and made a chart about it. A number means one value, or a small set of values, that fits in a sentence a human can repeat. Someone acts on means there is a named person whose behaviour changes depending on what you hand back.

Notice what is not in the sentence. Nothing about pipelines, clusters, file formats or streaming. Those are real, and by phase 3 your work runs nightly on a warehouse you configured, but they are the road, not the delivery.

There is a clean way to tell the two apart: ask what breaks. If the pipeline is late, somebody waits, notices, and complains. If your number is wrong, nobody complains. They act on it. Six weeks later a budget has moved, a campaign has run, and no one can say why the result looks strange. A late pipeline is loud. A wrong definition is silent, and silent failures are the expensive kind.

The question behind the question

Real requests arrive like this:

Can you send me our active user count for last month?

Four things in that sentence are undefined. Active could mean opened the app once, or opened it on two separate days, or completed a purchase. User could mean a registered account, a device, or a paying customer. Count could be a total or a per-day average. Last month could mean July, or the last 30 days, or the last four full weeks.

Pick differently on any one of those and you get a different number. All of them are defensible. None of them is the answer until someone chooses.

So before you write a query, ask one question back:

What will you do differently if the answer is 40,000 instead of 25,000?

If the honest reply is "nothing, I just want to know", you have found a request that does not need to exist yet. If the reply is "if weekly actives dropped more than 10 percent, we run the re-engagement campaign in September", you now have a decision, a threshold and a deadline, and the right definition of active falls out of it. A re-engagement campaign targets people who came back and then stopped, so repeat visits are the definition that matches. Anyone who opened the app exactly once was never engaged, and counting them tells the campaign nothing.

Write the question down before you write the query

Six lines, in a file, before any SQL:

metric:   users who opened the app on at least 2 distinct days
grain:    one row per user
window:   the 30 days ending 2026-08-01
filter:   exclude internal and test accounts
decision: run the September re-engagement campaign, or do not
owner:    Rana, growth marketing

That block is the deliverable of this session. It takes four minutes and it separates an analyst from a query-writing service.

Now see why the metric line matters. Install DuckDB, the local database you use for all of phase 1:

curl https://install.duckdb.org | sh

Confirm it runs. The -csv flag prints plain text instead of a drawn table, and -c runs one statement and exits:

duckdb -csv -c "SELECT 42 AS the_answer"

the_answer
42

Save this to active_users.sql. You are not expected to write SQL like this yet, that is what the next three sessions are for. Read it as a demonstration:

WITH app_open(user_id, event_date) AS (
    VALUES
        (1, DATE '2026-07-28'), (1, DATE '2026-07-30'), (1, DATE '2026-08-01'),
        (2, DATE '2026-07-29'),
        (3, DATE '2026-06-02'), (3, DATE '2026-06-03'),
        (4, DATE '2026-07-31'), (4, DATE '2026-07-31'),
        (5, DATE '2026-07-05'), (5, DATE '2026-07-10'), (5, DATE '2026-07-20'),
        (6, DATE '2026-07-26'), (6, DATE '2026-07-27')
),
in_window AS (
    SELECT user_id, count(DISTINCT event_date) AS active_days
    FROM app_open
    WHERE event_date >= DATE '2026-07-02'
    GROUP BY user_id
)
SELECT
    count(*)                                 AS opened_at_least_once,
    count(*) FILTER (WHERE active_days >= 2) AS opened_on_two_days
FROM in_window;

Run it:

duckdb -csv -c ".read active_users.sql"

opened_at_least_once,opened_on_two_days
5,3

Thirteen rows, six users, one window, and two honest answers that differ by 40 percent. User 3 was only there in June and misses the window. User 4 opened the app twice on the same day, which is one day, not two.

If you email "5" to Rana and she reads it as "5 people worth re-engaging", she has a true number and a false impression. Nobody lied. The definition was never written down.

Moving the data is a different job

Getting bytes from a source system into a place you can query is a discipline of its own: ingestion, streaming, orchestration, retries, schema drift. It has its own course. Confusing it with this one is the most common way analysts lose the plot, because pipeline work is visibly urgent and definition work is not. The alerts fire, the queue backs up, and after a year you are a pipeline operator who is no longer asked questions.

Here is the test that keeps you honest. A pipeline that is green, on time, correctly typed and fully tested still delivers a wrong number if active meant one thing to you and another to Rana. No monitor catches that. The six-line spec, agreed before you ran anything, is the only thing that does.

This is also why phase 1 has no pipeline at all. DuckDB 1.5.5 runs entirely on your laptop with no server, no account and no cloud bill, so for five sessions the only thing that can possibly be wrong is your reasoning.

Try it

Take the request "send me our active user count for last month" and do the whole job.

1. Write the six-line spec in a file. Every line filled, no TBD. Invent the decision and the owner if you have to, but they must be specific enough that someone could disagree with them.
2. Run active_users.sql and record both numbers.
3. Change the one line WHERE event_date >= DATE '2026-07-02' to DATE '2026-07-25', which narrows the window from 30 days to 7, and run it again.

Success condition: you have four numbers, 5,3 and 4,2, and one written sentence naming which single number you would send to the owner in your spec and why the other three are wrong for that decision.

If you cannot justify the choice from the decision: line of your spec, the spec is too vague. Rewrite it and try again.

Common mistakes

• Answering the question as asked. The person asking is describing a problem in the vocabulary they have. Your first move is a question back, not a query. The cost of asking is thirty seconds. The cost of not asking is a rebuilt report and a lost afternoon.
• Defining the metric after seeing the data. If you try three definitions and send the one with the nicest-looking number, you are not measuring, you are choosing. Write the spec first so that the definition is fixed before the result is visible.
• Treating "no decision attached" as fine. Requests with no decision behind them multiply, because they are easy to ask for and nobody ever tells you they stopped reading. Every one you build is a thing you now maintain.
• Assuming everyone means the same thing by a common word. Active, user, revenue, churn, session and month all have several meanings inside one company. Two teams quoting different numbers in the same meeting usually agree on the data and disagree on the noun.

Where this goes next

Next session, Getting Data In, replaces the hand-typed VALUES block with real files: loading CSV and Parquet into DuckDB, fixing the column that arrived as text when it should have been a date, and looking hard at a dataset before you trust a row of it. Keep DuckDB installed and keep your spec.