Structuring Data for Analysis

The week lost to reshaping exports

A consumer electronics brand delayed a pricing decision because the analytics team spent six days harmonizing campaign spreadsheets. Each regional marketer had exported a different tab: mismatched column names (spend vs cost_usd), dates as text, wide layouts with one column per week, and summary rows mixed into data rows. No one was lazy. The data was simply not structured for analysis. By the time spend and conversion sat in one long table at consistent grain, the promotional window was half over.

From Lessons 1–3, you can ask decision-grade questions, classify fields, and audit quality. This lesson covers the shape data must take before those skills matter. Structuring data is not an aesthetic preference. It is how you prevent double counting, enable cohort retention views, and make SQL and spreadsheets reproducible. You do not need to be an engineer to demand the right export. You need vocabulary: tidy tables, grain, keys, wide vs long, star schemas, and feature tables.

You do not need to code to benefit from this mental model. You need to recognize when data is in the wrong shape and ask for the right export. The ask should be specific: "Send a long-format table at store-week grain with these columns" beats "clean this up."

Analyst time is expensive. Six days of reshaping is six days not testing pricing, retention, or staffing decisions from Lesson 1. Structure is an investment with compound returns when the same question is asked every quarter.

Tidy data and the observational unit

Tidy data (Hadley Wickham) means: each variable is a column, each observation is a row, and each type of observational unit has its own table. Violations are everywhere in managerial life: multi-header Excel files, KPIs stuffed into one cell, customer attributes repeated on every order line without thinking.

The observational unit is the "thing" a row represents. If the unit is "customer-month," a row should not also try to store "product SKU" unless you move to "customer-month-SKU" grain. Mixing units in one table invites pivot errors.

Tidy does not mean "never wide." It means wide layouts must still map cleanly to variables. Month columns in a wide revenue table are really one variable (revenue) indexed by another (month). Analysis-friendly form is long: customer_id, month, revenue.

Managers benefit from tidy thinking even in Excel. Ask analysts: "What is one row? List columns and types. Which table is the system of record?" If answers differ across people, structuring work comes first.

Poor structure also amplifies quality problems from Lesson 3. Duplicate customer rows at different grains make deduplication impossible without rebuilding tables. Summary rows embedded in data tabs look like real observations and inflate counts during quick pivots. Fixing structure and fixing quality are related workstreams, not sequential afterthoughts.

Wide versus long format

Wide format puts time or categories across columns: one row per customer; columns jan_rev, feb_rev, mar_rev. Humans read wide tables quickly in board handouts.

Long format stacks those months: columns customer_id, month, revenue; three rows per customer for three months. Software filters, groups, and charts time paths more easily in long form.

Consider a regional sales VP who loves a wide workbook: stores across rows, months across columns. The VP can scan seasonality by eye. The analyst, asked to compute year-over-year growth by region, must unpivot to long form or write twelve formulas per store. When the company adds a thirteenth month column, every formula breaks. Long form appends rows; filters and pivot tables absorb new months without structural surgery.

Pivot tables bridge wide and long in spreadsheets. In SQL warehouses, long form is standard. Reproducible analysis prefers long because adding April does not require new columns; it appends rows. Teach regional leaders that wide handouts can be reports generated from long sources, not the source itself.

Conversion prose example: take a wide sheet with store_id and weekly sales columns w1–w52. Unpivot to store_id, week_number, sales. Check: sum of sales across weeks for each store equals the wide row total ✓.

When reviewing a dashboard, trace whether it was built from a wide marketing export pasted manually. That is a maintenance liability. Ask for the long source table behind the chart so next month's refresh is append-only, not another six-day reshape.

Grain: the most important word in analytics

Grain declares what one row represents. Write it in line one of every analysis doc.

Examples:

• Order line item (SKU-level margin)
• Order header (one basket total)
• Customer-day (active flag per calendar day)
• Store-week (rollups for ops reviews)

Mixing grains causes double counting. Suppose you join a customer-level NPS (Net Promoter Score, percent promoters minus percent detractors) table to order-line revenue without care. Each customer's NPS repeats on every line, inflating correlation between satisfaction and revenue. Fix: aggregate revenue to customer level first, then join NPS on customer_id, or keep line grain but analyze NPS as a customer attribute with line revenue as repeated measure knowingly (mixed models later).

Grain also governs denominators from Lesson 2. Churn rate at customer-month grain differs from logo churn at customer grain.

SQL smell test: if COUNT(*) after joins is much larger than base fact table rows, grain broke.

Document grain in dashboard subtitles, not only analyst README files. Executives should see "Store-week, U.S. company-owned only" on the chart they approve. Hidden grain is how two departments argue from the same screen with different mental models.

Keys and relational structure

Relational data connect tables with keys.

A primary key uniquely identifies a row in a table (order_id, store_id). A foreign key references another table's key (customer_id on the orders table points to customers).

Cardinality matters:

• One-to-many: one customer, many orders
• Many-to-one: many order lines, one product
• Many-to-many: students and courses (needs a bridge table listing pairs)

Star schema is the warehouse pattern: a fact table at the finest business grain (sales lines) surrounded by dimension tables (customer, product, date). Facts hold numeric measures; dimensions hold descriptive attributes. This speeds consistent joins and prevents every analyst from rebuilding the same logic.

Spreadsheet equivalent: one tab fact_sales with only keys and measures; tabs dim_customer, dim_product with attributes; use XLOOKUP or Power Query merges on keys. Never merge on customer name strings without normalization.

In prose, imagine building a monthly board pack. You want revenue by region and product line. The fact table holds order_line_id, order_date_key, customer_key, product_key, quantity, net_revenue. Dimensions hold descriptive attributes: dim_customer.region, dim_product.line. Analysts join once through keys instead of merging twelve exports. When revenue disagrees with finance, reconcile at fact totals first: sum of net_revenue for March must match GL (general ledger, the official accounting record) March product revenue after known timing differences ✓.

Cohort tables and retention analysis

Retention, LTV (lifetime value, expected profit over a customer's relationship), and payback questions need cohort grain: rows indexed by signup period and time since signup.

Typical columns: cohort_month (signup month), month_since_signup (0, 1, 2, ...), customers_active, revenue.

Cohort month = when the customer first paid. Month 3 retention for the January cohort compares to February cohort without blending unlike vintages.

Without cohort structure, blended averages hide deterioration: new cheap signups mask churn among older high-value users.

Cohort tables also clarify payback analysis. If CAC (customer acquisition cost, spend to win a customer) is spent in cohort_month 0, comparing cumulative revenue through month 6 by cohort shows whether a cheaper acquisition month produced weaker monetization. Finance and growth teams stop arguing about blended LTV when cohort curves sit side by side.

Spreadsheet logic: assign cohort_month per customer from first payment date; for each calendar month, compute month_since_signup; flag active if paid in that month; pivot average retention by cohort and month index.

SQL plain English: WITH first_pay AS (...), activity AS (...) SELECT cohort_month, month_since_signup, COUNT(DISTINCT customer_id) FROM activity WHERE active_flag=1 GROUP BY 1,2.

Check: cohort sizes at month 0 equal new customers acquired that month ✓.

Cohort exports should ship with a README sentence in row 1 of the dictionary tab: "Grain = cohort_month x month_since_signup; active = paid MRR > 0 in calendar month." That sentence prevents executives from misreading a cohort table as customer-level churn.

Even simple retention mistakes have dollar stakes. If product reports "90% month-1 retention" but defines active as "any login" while finance defines active as "paid," two teams celebrate the same chart with different grains. Align definitions before structuring cohort tables.

When two departments fight over retention numbers, the fix is usually a grain and definition workshop, not a fancier chart. Write the agreed grain in the meeting notes.

Feature tables for modeling and decisions

Machine learning and regression need one row per decision unit with predictors and an outcome label. This is structuring, not algorithms.

Example churn model: row per customer at month-end; features = logins last 30 days, tenure months, plan tier, support tickets; label = churned in next 30 days (yes/no). Features must be knowable at prediction time (no future leakage).

Even without ML, decision memos benefit from feature tables: one row per store-week with promo spend, weather index, staffing hours, and next-week sales helps compare policies consistently.

Document prediction time and label window. A classic error is training on post-churn support tickets to predict churn.

For spreadsheet-based teams, feature tables often live as one exported CSV per month. Version those files. If March features accidentally include April columns because of a bad formula, model metrics look brilliant until deployment fails. A simple check column: feature_window_end must equal month-end date for every row ✓.

Entity-relationship discipline for managers

You do not need formal ERD (entity-relationship diagram, a map of tables and links) software to think structurally. Sketch boxes: Customers, Orders, OrderLines, Products, Tickets. Draw lines with cardinality notes. Label grain on each box.

Ask where double counting could occur: joining Tickets to OrderLines without aggregating tickets per order duplicates revenue when summing.

Governance: name a data owner per subject area who approves grain and key choices. The owner signs off when a new dashboard grain differs from finance reporting grain. Misalignment between ops dashboards and GL totals is often a grain dispute, not bad faith.

Normalization, naming, and documentation habits

Messy text breaks joins. customer_name values "Acme Corp", "ACME CORP.", and "Acme Corporation" look distinct to software. Normalization rules (trim spaces, uppercase codes, strip punctuation) belong in the data pipeline, not in one analyst's memory.

Naming conventions reduce errors: is_ prefix for yes/no flags, _at suffix for timestamps, _id suffix for keys, _usd for currency fields. A column named date is ambiguous (order date? ship date?). Prefer order_date.

Maintain a living data dictionary with grain, keys, and freshness SLA (service level agreement, promised update timing). When onboarding a new analyst, the dictionary is faster than tribal knowledge.

Version exports. campaign_events_v2026q1 beats final_final2.xlsx. Git for code; dated folders for immutable monthly snapshots if no warehouse yet.

Power Query and similar tools help non-coders build repeatable unpivot and merge steps. Invest once in a documented query rather than manual copy-paste each month. The query name should include grain (unpivot_store_week_sales).

Reproducibility and the handoff to Lesson 5

Structured data supports audit. Ethics and governance in Lesson 5 ask who may see which columns. Structure choices interact with privacy: storing free-text support tickets at line grain may expose personal health information in ticket bodies; aggregating to category counts reduces exposure.

Before sharing externally, apply de-identification (removing direct identifiers) and test whether combinations of fields still identify people (small zip + rare job title). Quality memos should note re-identification risk.

Bridge tables and many-to-many joins in practice

Consider students and courses: one student enrolls in many courses; one course has many students. A naive join of students to courses without a bridge creates a Cartesian product: every student row pairs with every course row. Row counts explode; sums of tuition or attendance are nonsense.

The fix is a bridge table (also called an associative table): enrollments with student_id, course_id, enrollment_date, grade. Grain of enrollments is student-course pair. Facts about attendance live here or in a child table at class-session grain if needed.

Spreadsheet users see this when merging two tabs without a shared key. If you merge "customers" (500 rows) with "products" (2,000 SKUs) hoping to get "customer-product purchases," you create one million rows unless you merge through orders. Always merge through keys that match the business event.

SQL plain English for revenue by course: SELECT c.course_name, SUM(p.amount) FROM enrollments e JOIN courses c ON e.course_id=c.course_id JOIN payments p ON p.enrollment_id=e.enrollment_id GROUP BY c.course_name. Check: revenue total equals payments table total after filters ✓.

Slowly changing dimensions (preview)

Customer attributes change: a buyer moves from SMB to enterprise tier mid-year. If you overwrite tier in dim_customer without history, Q1 analysis uses the new tier for old orders. Slowly changing dimension (SCD, methods for tracking attribute history) techniques keep tier_at_time_of_order on the fact row or maintain history rows. Managers should ask: "Does this report use current segment labels on past events?" Misattribution has launched many wrong sales compensation plans.

When compensation, litigation, or regulatory reporting is involved, insist on point-in-time attributes on the fact table. It is easier at ingest than forensic reconstruction later.

Ridgeway Home Goods runs eight regional campaigns. The CMO receives one workbook with eight tabs, different schemas, and wide weekly columns.

Part A: Problem statement

Business question (Lesson 1): Which channels delivered qualified leads under $120 CAC (customer acquisition cost, spend to win one customer) in Q1 2026?

Current pain: Analyst cannot compute CAC by channel without manual rework; quality risk from Lesson 3 (inconsistent definitions).

Part B: Target schema

Design one campaign_events long table:

Grain: one row per campaign-day-channel (or per campaign-day if channel is in rows). No summary rows embedded.

Part C: Spreadsheet conversion and checks

Steps: standardize column names; unpivot weeks to event_date; stack tabs; remove total rows; validate spend_usd non-negative.

Before: 8 tabs, 640 manual cells touched weekly. After: append-only import to one table.

Check: sum of spend_usd in long table = sum of wide tabs after unpivot ✓ for pilot region West ($412,500) ✓.

SQL plain English: SELECT channel, SUM(spend_usd)/NULLIF(SUM(qualified_leads),0) AS cac FROM campaign_events WHERE quarter='2026-Q1' GROUP BY channel HAVING SUM(qualified_leads)>0.

Results: paid social $148 CAC; search $102; display $210.

Part D: Managerial read

Shift Q2 budget toward search where CAC clears hurdle; cap display unless creative test changes lead quality. Invest once in schema design to avoid quarterly heroics. Data engineering owns the table; marketing owns definitions of qualified_leads (Lesson 3 alignment).

HarborStack from Lesson 1 needs cohort retention, not blended churn.

Part A: Grain choice

Unit: customer-month. cohort_month from first paid month. active if MRR > 0 in calendar month.

Part B: Cohort table excerpt

Check: month 0 counts match new logos from billing export ✓ (500 and 620).

Part C: Misleading blended metric

Blended month-3 retention averaging 82% and 73% without weights ignores cohort size and price mix. November cohort started larger but retained worse; product changes in November (new onboarding) require investigation.

Join cohort view to support tickets at month 1 to test service load hypothesis (structure enables analysis; quality audited per Lesson 3).

Part D: Managerial read

Pause company-wide churn celebrations. Focus on November cohort remediation. Board metric: month-3 retention by cohort and segment, not one blended KPI.

Part E: SQL cohort snippet (prose)

Analyst documents: "From subscriptions, compute first_paid_month per customer_id. Expand calendar months from first paid through March 2026. Left join payments; set active=1 if payment in month. Aggregate counts by cohort_month, month_since_signup." Reviewer verifies month-0 equals new logos ✓ before trusting month-3 retention slides.

BloomB2B sells wholesale office supplies. Exports include:

• orders (order_id, customer_id, order_date)
• order_lines (order_id, sku, quantity, unit_price)
• customers (customer_id, segment, nps_score)

Analyst sums revenue as SUM(quantity * unit_price) after joining customers to order_lines on customer_id without aggregating.

Tasks:

1. State grain of each table.
2. Explain the double-counting or inflation risk in the join described.
3. Write correct plain-English SQL or spreadsheet steps to compute revenue by segment for Q2 2026.
4. Where should nps_score live in the join path?

Solution

1. Grain: orders = order header; order_lines = line item; customers = customer.

2. Risk: Joining customers directly to order_lines repeats nps_score and customer fields on every line. If you later average nps_score weighted by line revenue without care, you overweight multi-line orders. Summing revenue at line level is OK only if each line appears once; joining unnecessary tables can duplicate lines if keys wrong.

Correct path: compute line_revenue = quantity * unit_price at line grain; join lines to orders for order_date; filter Q2; join orders to customers for segment; aggregate SUM(line_revenue) by segment.

3. Logic: Spreadsheet: create lines_with_segment via merges on order_id then customer_id; pivot sum of line_revenue by segment with date filter. SQL:

SELECT c.segment, SUM(l.quantity*l.unit_price) AS revenue FROM order_lines l JOIN orders o ON l.order_id=o.order_id JOIN customers c ON o.customer_id=c.customer_id WHERE o.order_date BETWEEN '2026-04-01' AND '2026-06-30' GROUP BY c.segment.

Check: segment revenues sum to total Q2 line revenue ✓.

4. nps_score: customer-level attribute; join at customer grain after aggregating outcomes if analyzing NPS vs revenue per customer, not per line.

Sketch restructuring for a wide table: rows = product_id; columns = 2026-01_units, 2026-02_units, ... 2026-12_units.

Tasks:

1. Name wide vs long versions with column list.
2. Give one visualization or analysis that is easier in long form.
3. Write a check to run after unpivot.

Solution

1. Wide: as given. Long: columns product_id, month (date or YYYY-MM), units_sold.

2. Easier in long: 12-month trend line for one SKU; month-over-month growth rates across catalog with one filter.

3. Check: for each product_id, sum of units_sold in long equals sum across month columns in wide ✓.

Explain why: Long format turns twelve month columns into one variable (month), which is how SQL GROUP BY month and line charts expect data. The check prevents dropped columns during unpivot.

Support tickets arrive as JSON with nested arrays of messages. Leadership wants average time to first response by product area.

Tasks:

1. What is the observational unit for the business question?
2. Describe flattening steps to a tidy table (prose).
3. One quality check from Lesson 3 to run after flattening.

Solution

1. Unit: one support ticket (or one customer issue if tickets merge). Metric: minutes from ticket_created_at to first_agent_response_at (ratio).

2. Flatten: explode JSON to rows with ticket_id, product_area, created_at, first_response_at; filter bots; compute response_minutes per ticket; aggregate AVG(response_minutes) by product_area.

3. Uniqueness check: COUNT(DISTINCT ticket_id) should equal ticket rows; duplicates inflate averages.

Key takeaways

• Declare grain explicitly; mixed grains cause double counting and false correlations.
• Prefer tidy, long tables for time series, cohorts, and reproducible SQL.
• Use keys and star-schema thinking: facts at finest grain, dimensions for attributes.
• Build cohort tables for retention and LTV; blended averages hide vintage effects.
• Invest in standard schemas once instead of repeated spreadsheet rescue missions.

1. What grain is your most-used dashboard built on? Is it documented?
2. Sketch a three-table ERD for your business with grains labeled.
3. Continue to Lesson 5: Ethics and Governance in Business Data.