Practice three skills: define the metric, recalculate it from source records, and turn an anomaly into something the team can investigate. Audit a dashboard for seven-day new-user paid conversion. Write a metric definition, calculate 60% from five fictional users, find out why an AI draft might report 100%, and explain what the business should check next.
Fill this out before opening a chart or writing SQL:
Metric definition card | What decision does it inform? Who is in the denominator? What qualifies for the numerator? Which time zone, observation window, and unit apply? Which cases are excluded? Who approves it? Include a sample you can calculate by hand and its expected result.
Turn “How is new-customer conversion doing?” into a measurable question
Suppose an operations lead wants to know whether to prioritize fixing the purchase flow after signup. They ask how new-customer conversion is doing. Does a new customer count from their first valid signup or their first store visit? Does conversion mean payment or fulfillment? Either choice can change the numerator or denominator. To see what happens after signup, group users by when they signed up. Grouping by payment date answers which payments happened that week.
In this fictional project, operations and analytics agree to select signup dates in local time, keep each person’s first valid signup, and exclude flagged internal test accounts. The numerator counts those users with at least one completed payment within 168 hours of signup that was not fully refunded as of the snapshot. Count each user once. Decide how to handle partial refunds in advance. This exercise includes orders with a positive net payment. The observation window starts at signup and ends just before hour 168. A payment at exactly hour 168 falls outside it. Users who haven’t reached 168 hours at the snapshot are excluded from the denominator. Later refunds may alter a later version. Agree on the rules before naming and calculating the metric.
Document its purpose too. The dashboard compares signup cohorts whose observation windows have closed and helps flag groups that need investigation. If the rate falls, check factors such as checkout, acquisition channels, and pricing before deciding where to investigate first. Save the approved definition, source table names, timestamp fields, exclusions, snapshot time, and version. Check whether an existing metric uses the same name. A calendar-day metric and a rolling 168-hour metric may both be correct without being directly comparable.
Check an AI-written query with five users
The data below are fictional. The five eligible users are from the same signup cohort, and all have completed 168 hours of observation. The hours are relative to each signup. All refund statuses use the same snapshot. The table lists every illustrative paid order for those users.
| User | Observed orders | Counted in the numerator? |
|---|---|---|
| A | One payment at hour 24 and another at hour 48. The second order has two line items. No refunds. | Yes, one user |
| B | One payment at exactly hour 168. No refund. | No, outside the window |
| C | One payment at hour 72, fully refunded before the snapshot. | No |
| D | One payment at hour 12. No refund. | Yes, one user |
| E | One payment at hour 144. No refund. | Yes, one user |
The expected denominator is five, and the numerator is three users: A, D, and E. The rate is 3 ÷ 5 = 60%. There are four eligible orders: two for A, one for D, and one for E. Joining orders to line items yields five rows because A’s second order has two items. An AI-generated query that divides those five rows by five signups shows 100%. The percentage looks plausible, but the numerator counts items. Counting distinct orders still gives 4 ÷ 5, or 80%.
Try it in a spreadsheet before writing SQL. Select the five users from registrations, filter eligible orders by time and refund status, then flag each user who has at least one qualifying order. When you join back to the signup cohort, retain all users, including those without a qualifying order. Sum the flags for the numerator. Joining all the raw rows first and filtering orders in the final query may also drop unpaid users from the denominator. A note under the chart can’t repair that calculation. For merged or duplicate signups, recalculate with the business-approved user identifier.
Document what a row represents: a signup record, an order, or a line item. Before and after joining, compare row counts, distinct orders, and distinct users, and check whether amounts or flags changed. Microsoft’s Power BI star schema guidance separates grain, relationship cardinality, and filter paths. Check them in spreadsheets, SQL, and BI models. Before sharing a query, test one user with multiple orders, one order with multiple items, a payment exactly on the boundary, and zero eligible orders. When the denominator is zero, show that no rate can be calculated and avoid presenting an empty cohort as zero conversion.
When a dashboard flags a drop, check three possible explanations
First, check whether the calculation changed. Run the old and new calculations on the same signup cohort, with the same snapshot and filters. If line items were counted as users, correct the calculation and identify the affected charts and reports. Record it as a correction to how the metric was implemented.
Next, check whether the data are complete. Verify when payment and refund records last refreshed, whether a source partition is missing, and whether collection is delayed. Trace several rows from the aggregate back to order status. If a cohort has only four days of observation at the snapshot, three days remain. Mark it as incomplete or define a separate comparison between cohorts at the same age. Distinguish “not available yet” from “observed zero” in the chart, tooltips, and exports. Check real personal or customer records in an authorized environment, and share only approved aggregates or synthetic examples.
If the figures still disagree, get both sources to count the same thing. In a separate fictional example, each of two weeks has 100 new users with full observation. Last week, the dashboard and payment system agreed on 40 paying users. This week, the dashboard shows 28, but the payment system still yields 40 using the same user criteria, time window, and refund rules. Match the lists. The dashboard’s 28 users are among the 40 in the payment system. The other 12 are missing from the client-side success event table used in the metric. Nine used the new checkout entry point, while no shared condition has been identified for the other three. That makes the new entry point worth investigating without establishing that it caused the missing records.
Write an update the business team can act on: “With the specified snapshot and common definition, the payment system still shows 40 of 100 this week. The dashboard omitted 12 eligible users, so we can’t infer a loss in conversion from the chart. Ask the instrumentation owner to check events from the new entry point and trace them into the data warehouse. Then compare users on the same client version who used the old entry point.” Attach the two numerators, denominators, snapshots, and affected dashboards. If definitions still differ, document the discrepancy before replacing any figures. If both sources confirm a decline, check failed payments, offer eligibility, or traffic shifts and agree who will investigate and when. The business can then decide whether to fix data collection first or examine checkout.
How do you turn the exercise into a junior analyst portfolio project?
Make it reviewable in five minutes. Start with the decision and metric definition. Show the five records, the expected 60%, and what your calculation returned. Add a brief before-and-after explanation. Finish with a dashboard image labeled with the sample version, snapshot, and filters. Give an interviewer a test they can change: move B’s payment from hour 168 to hour 167, still with no refund, and expect the rate to move from 3 ÷ 5 to 4 ÷ 5. Rerun the query and record which outputs change. If you only calculated by hand, say so.
AI can draft a query, suggest edge cases, or sketch a chart. Give it the approved definition, then check it against hand calculations, original records, and reruns. If it assumes every order represents a different person, identify the relevant code, the data that triggers the error, and how you’ll check downstream reports after the correction. Power BI documentation describes verified answers, which authors can approve for common questions under the relevant settings and permissions. A document or spreadsheet is enough to record expected results in this exercise.
Pick tools based on the target duties. If the posting asks you to retrieve data, prepare a runnable query and an edge-case test. If it asks you to maintain dashboards, document the version, time, and filters. For investigative analysis, show an anomaly check with a clear stopping condition. Mainland China’s human resources authority lists data collection and cleaning, expressions, charts, and reports at relevant levels in its National Occupational Standard for Business Data Analysts, 2024 (PDF). The US O*NET Business Intelligence Analysts task profile includes maintaining reports and dashboards. Check actual postings for hiring requirements. Read the business intelligence analyst career profile and how to turn job requirements into evidence of skills to connect the exercise with a real posting. The 94-question FermatMind Career Test can help you explore preferred activities, working styles, and conditions. Assess SQL or analysis skills with verifiable tasks and acceptance criteria.


