Level 1 · Frame the mobile conversion question

Why framing comes first

“Conversion on mobile looks bad” is an observation, not a question. Before you write a query you need to agree what an answer would look like. SMART gives the checklist:

• Specific: which sessions. All web sessions from 2023 to 2025, split by device.
• Measurable: which numbers. Not one conversion rate but one rate per stage of the funnel.
• Achievable: can the data answer it? web_sessions stores the device, the pages viewed, whether the visitor was signed in and whether the session converted. It has no order ids, so it cannot say what people bought, only whether they did.
• Relevant: what decision does it feed? Jordan will approve, shrink or redirect the mobile budget.
• Time-bound: three years of data, answer by the planning meeting.

If you cannot write the question in one sentence that contains all five, it is not ready yet.

What a funnel adds

A conversion rate is the last number of a chain. A visitor arrives, looks around, signs in, adds to the basket, pays. A funnel counts how many sessions survive each stage:

stage	definition in this data
sessions	every row of web_sessions
engaged	pages_viewed >= 3
known and engaged	engaged, with a customer_id
converted	converted is true

If mobile loses people at the engaged stage, the pages are the problem and a rebuild might be right. If mobile keeps up until the last stage, the problem is the basket, the sign-in or the payment form: a much smaller job.

Compared with what?

Two comparisons are needed. Devices side by side shows where the gap opens. Year by year guards against a trap: the share of mobile traffic has been rising, and mobile converts less, so the overall rate can stand still even when every device is improving. Looking only at the total, you would call that “flat”.

What the plan looks like

Step	What you do	Output
Check	Duplicates, empty sessions, who can convert	one row of counts
Analyse	Funnel by device; conversion by device and year	two small tables
Show	One chart: the browsing and checkout rates by device	a column chart
Recommend	Which step to fix, with caveats	a short paragraph

Interview note

“Our mobile conversion is low. How would you investigate?” is a common analyst interview question. A strong answer splits the rate into funnel stages, compares segments at each stage, checks whether the mix of traffic has shifted over time, and names the data limits: tracking gaps, anonymous visitors, people who browse on one device and buy on another.