What the job actually is

An omnichannel retail analyst turns raw POS + web + inventory + fulfillment data into answers for the people running a brick-and-click retailer. A day might include reconciling four-channel order volumes against the CMO's growth narrative, building the store-level CM2 cut that exposes BOPIS labor allocation, flagging hot SKUs over-stocked in low-velocity regions for the rebalance call, computing ship-from-store ROI vs the DC-fulfillment counterfactual, or auditing same-SKU cross-channel cannibalization for the category teams. The data shape is multi-grain: per-order fact (order_id), daily inventory snapshots (store × sku × day), fulfillment events (1:N per order with split-ship), BOPIS pickup logs (subset of orders).

The role varies by employer

Big-box omnichannel (Target / Best Buy / DSG)
Hundreds-of-stores national footprint with mature DTC. The analyst owns one functional cut (BOPIS program, regional inventory, fulfillment economics, store P&L). Deep tooling, mature data layer (Snowflake / BigQuery + dbt + Looker), strong stakeholder demand from regional VPs.
Regional / specialty retailer (Total Wine, Sephora, REI-style)
10s-of-stores per region. Smaller analytics team (1–4 analysts) means broader scope per analyst — you'd own BOPIS + inventory + carrier scorecard simultaneously. Less tooling, more SQL gymnastics directly against the warehouse.
Department store transformation (Macy's / Kohl's)
Legacy store footprint pivoting hard into omnichannel. The analyst's job is partly forensic: where is the channel cannibalization, which stores are pulling vs being pulled by web, where should the next BOPIS pilot expand. Lots of brown-bag presentations to senior leadership.
Grocery / drug retail (Kroger, CVS-shape)
Different mechanics — perishability, delivery integration (Instacart / DoorDash partnerships), pharmacy-side analytics layered in. The omnichannel core (4 channels + BOPIS + SFS) still applies, but with category-specific gotchas (food-safety SLAs, controlled-substance handling).
Platform-side / data team at the retailer
Data engineering, dbt modeling, BI tooling, Customer 360 / CDP. Not the business-facing analyst role but adjacent — useful path for analysts who want to step into modeling-layer ownership.

Skills that actually get hired

SQL (non-negotiable)

Postgres or Snowflake or BigQuery — modern retail warehouses live in dbt-modeled stacks.
Multi-grain joins (order × store × sku × fulfillment × pickup) without double-counting.
Window functions: ROW_NUMBER for first-event-per-order, NTILE for store ranking buckets, PARTITION BY sku_id for z-score-style inventory imbalance.
Conditional aggregation: SUM(CASE WHEN channel = ... THEN ... ELSE 0 END) for channel splits in a single GROUP BY pass.
Multi-CTE pipelines: M14 (z-score), M15 (efficiency), M21 (CM2 composite), M25 (allocation capstone) are 3–5 CTEs deep.
CROSS JOIN for counterfactual baselines — M24 ship-from-store ROI uses this pattern.
Date arithmetic (EXTRACT(EPOCH ...) for hours-to-ship, DATE_TRUNC for monthly buckets).

Retail domain fluency

POS + e-commerce data shapes — knowing which fields live in which fact (units 0 = foot-traffic, gross_amount = net of discount).
Channel taxonomy: web_ship, web_bopis (BOPIS), store_walk_in, store_ship_from_store (SFS). Some retailers add curbside, MFC-fulfilled (micro-fulfillment).
Unit economics: revenue, COGS, fulfillment cost, store labor allocation, CM1/CM2/CM3 ladder.
Inventory layers: on-hand, allocated, reserved (in-transit), safety stock. Understanding when each applies to a question.
Carrier zones and weight bands — drives the ship-from-store cost spread question (M13).

Ops / merchandising fluency

BOPIS conversion rate definition: pickups completed / pickups initiated (no_show_flag = 0).
Inventory turns: units sold / avg units on hand over a window.
Sell-through, weeks of supply, dead-stock identification.
Allocation efficiency = sold / allocated; pairs with z-score-style cross-store imbalance for rebalance decisions.

BI / tooling

Looker — most-common at large national retailers (Target, Best Buy use Looker heavily).
Tableau — older deployments, common at department stores and grocery.
dbt — the analytics engineering layer. Most retailers have 200+ dbt models.
Snowflake — the warehouse standard in retail; Redshift legacy at older shops.

Stakeholder skills

Translating a regional VP question ("are we cannibalizing web with BOPIS?") into the right cannibalization audit.
Defending a fulfillment cost allocation against a category manager (especially BOPIS labor allocation, which is the M16 trap).
Writing a 1-page board brief — store-level CM2 with the labor adjustment is the chart every audit committee wants.

The interview loop

1
Recruiter screen (30 min)
Fit check. Be ready to explain why omnichannel retail specifically (vs pure DTC, vs pure store ops). Big-box retailers want to hear depth on store-level economics; regional / specialty wants breadth across BOPIS + inventory + carrier.
2
Hiring manager (45–60 min)
Behavioral + domain depth. Expect a case-style walkthrough: "BOPIS conversion just dropped from 95% to 91% in Pacific — what would you investigate?" They're testing whether you reach for pickup_window aging (staffing crunch), no_show drivers (inventory accuracy), or channel-shift cannibalization (orders flowing to web_ship instead) — fluently — without prompting.
3
Technical SQL screen (60–90 min, usually take-home)
Postgres or Snowflake schema with 4-channel fact_orders + inventory snapshots + fulfillment events. Common asks: write the channel cannibalization audit at SKU grain, build the store-level CM2 by channel mix, surface the foot-traffic-no-purchase funnel. Edge cases matter — "what does units=0 mean in fact_orders? Should they count toward your store traffic metric?"
4
Cross-functional round (operations / merchandising / finance)
A non-analyst asks you to defend a metric or recommend a policy. Inventory Lead will grill you on z-score methodology and rebalance prioritization; BOPIS Program Manager will challenge no-show definitions; CFO will probe the BOPIS labor allocation arithmetic.
5
Director / VP round
Strategy and prioritization. Common ask: "If you had 8 weeks, what would you build first as our new omnichannel analyst?" The good answer is always "the per-(store, channel) CM2 reconciliation including BOPIS labor allocation, before any new analysis." That's the artifact every quarterly review needs.

Questions you’ll actually be asked

“What's the BOPIS labor allocation problem and how do you handle it in CM2?”: fact_orders.fulfillment_cost on web_bopis is the platform / handling cost ($1.80–2.40 typically) — but the store labor for picking and staging the pickup isn't in that number. A CASE WHEN channel = 'web_bopis' THEN ~$5 ELSE 0 inside the CTE injects the implicit labor. CM2 without this adjustment OVERSTATES BOPIS margin by ~$5/order. M16 surfaces this; M21 carries it into the store P&L composite.
“How do you compute channel cannibalization at SKU grain?”: Conditional aggregation per SKU: web_rev, store_rev, web_orders, store_orders. Cannibal ratio = 100 * LEAST(web_rev, store_rev) / NULLIF(web_rev + store_rev, 0). Large LEAST = both channels carrying material volume = cannibalization. Filter to SKUs with >= 5 orders in each channel so trivial overlaps drop out. M20 builds this; M22 extends to (SKU, store) grain.
“Why are foot-traffic-no-purchase rows in fact_orders, and what should I do with them?”: They're seeded as channel='store_walk_in', units=0, gross_amount=0 to model in-store browse events without a purchase. Naive COUNT(*) on fact_orders inflates store_walk_in volume by ~20% if you don't filter units > 0. The store-conversion question (M18) requires KEEPING them in the denominator (foot traffic) and KEEPING them OUT of the numerator (purchases). Every retail analyst learns this distinction in the first month.
“Walk me through inventory z-score-style imbalance detection.”: Two CTEs: avg_inv at (sku, store) grain over a 6-month window, then sku_stats with the per-SKU mean and stddev across stores. Per-(sku, store) z-score = (avg_units - mean_units) / NULLIF(stddev, 0). High positive z = over-allocated relative to peers; high negative z = stocked-out or under-allocated. ABS(z) > 2 is the typical rebalance candidate threshold. M14 builds this; M25 capstone composes it with allocation efficiency to recommend HOARD/REBALANCE/BOOST/STABLE.
“How do you compute ship-from-store ROI vs the DC-fulfillment counterfactual?”: CROSS JOIN to a single-value baseline (avg web_ship fulfillment_cost) inside a CTE, then substitute that baseline cost for the actual SFS cost in the margin formula. Difference = ROI lift. The pattern is: SUM(actual_margin) vs SUM(counterfactual_margin) per region. Positive ROI means SFS beats DC for that region; negative means the DC zone tariff is winning. M24 is the canonical mission.
“What's the difference between order_ts and event_ts when computing shipping SLA?”: order_ts = when the customer placed the order (fact_orders). event_ts = when the fulfillment event fired (fact_fulfillment_events: shipped / picked_up / ship_from_store). SLA = event_ts - order_ts in hours. EXTRACT(EPOCH FROM (event_ts::timestamp - order_ts::timestamp)) / 3600 is the Postgres idiom. Filter event_type = "shipped" to exclude in_transit secondaries — those are the split-ship leg, not the primary SLA event. M5 builds this; M13 extends to per-zone cost spread.

What it pays

Level	Range	Notes
Entry-level (0–2 yr)	$65k–$95k	Regional / specialty retailers at the low end. Big-box (Target, Best Buy) opens at $80k+ for new grads with a quantitative degree. Hybrid is the norm; fully remote is rare in retail headquarters.
Mid-level (2–4 yr)	$95k–$135k	Omnichannel Analyst, BOPIS Program Analyst, Store Operations Analyst. Big-box pays 10-15% premium for multi-channel fluency; regional retailers slightly below.
Senior Analyst (4–7 yr)	$125k–$170k	Senior Omnichannel Analyst, Senior Store Operations Analyst, Senior Inventory Analyst. Specialty inventory roles at Target / Best Buy can crack $180k for proven impact on regional CM2.
Manager / Lead (5–8 yr)	$150k–$200k	Running a team of 2–5 analysts focused on a functional area (BOPIS, store ops, inventory, fulfillment). Owns the dashboard layer and the playbook for your function.
Director Analytics	$180k–$260k	Top-of-house at large retailers ($1B+ revenue) or VP-track at smaller specialty retailers. Owns the omnichannel metric layer, dbt project, and analyst hiring.
VP Analytics / Head of Omnichannel	$220k–$380k+	Big-box retailers (Target, Best Buy, DSG, Macy's, Kohl's). Equity / RSU is meaningful — especially at Target where the stock has been a multi-year favorite. Cross-functional reach into merchandising, marketing, supply chain.

Certifications — honest take

dbt Analytics Engineering certification
Gold standard
Most large retailers run dbt as the modeling layer. The cert is signal but the portfolio of dbt models matters more. Build a public sample dbt project for a fictional omnichannel retailer.
Looker LookML developer certification
Gold standard
Target, Best Buy, and most large omnichannel retailers run Looker. Free LookML training; certification cost is moderate. Strong signal for big-box retailer roles.
SnowPro Core (Snowflake)
Nice to have
Useful at retailers on Snowflake warehouses (most national retailers). Demonstrates warehouse fluency without being mandatory.
Tableau Desktop Specialist
Nice to have
Useful at retailers still on Tableau (older big-box deployments, grocery). Lower signal than Looker for omnichannel roles specifically.
Google Analytics 4 / GA4 certification
Skip
Omnichannel analytics relies on the retailer's own warehouse, not GA4. Skip in favor of dbt or Looker.

How long it takes

E-commerce or BI analyst with basic SQL: 4–8 months prep — author 2–3 portfolio analyses on a fictional omnichannel retailer, get fluent with the channel cannibalization audit and the BOPIS-with-labor CM2 walk, apply to mid-stage specialty retailers or big-box associate analyst roles. Store ops analyst transitioning: 2–4 months — your store-level fluency transfers; the gap is the 4-channel SQL idiom. Non-analyst with a quantitative degree: 9–14 months prep — start at a regional specialty retailer, learn the data shape on the job, transition into a big-box omnichannel role within 18–24 months.

Common mistakes to avoid

Treating fact_orders.fulfillment_cost as the complete BOPIS cost. The seed deliberately omits store labor allocation — students who SUM fulfillment_cost only OVERSTATE BOPIS CM2 by ~$5/order. M16 is built around this gotcha.
Including units=0 foot-traffic-no-purchase rows in COUNT(*) order metrics. That inflates store_walk_in volume by ~20%. Filter units > 0 — except in the foot-traffic funnel (M18), where you WANT those rows in the denominator.
Computing cannibalization with GREATEST instead of LEAST. LEAST(web_rev, store_rev) high = BOTH channels carrying material volume = cannibalization. GREATEST inverts the signal.
Using AVG inventory when the question is 'how much was allocated'. Allocation discussions need a fixed-point snapshot (e.g., 2025-10-01 day 1 of the window), not an average across the period.
Joining dim_stores to web_ship orders. web_ship has store_id NULL — INNER JOIN drops those rows (which is usually what you want for region-cuts), LEFT JOIN keeps them as a no-region bucket (which is rarely what you want).
Computing BOPIS conversion as no_show_flag = 1. The 'completed' definition is no_show_flag = 0 — students who flip the predicate report ~5% conversion instead of ~95%.
Forgetting that gross_amount in fact_orders is already net of discount. Subtracting discount_amount again in CM2 double-counts and collapses margin.
Filtering event_type to 'shipped' for ALL fulfillment SLA questions. shipped is correct for web_ship; picked_up for web_bopis; ship_from_store for SFS. The channel determines the event_type — match the filter to the question.
Using ROW_NUMBER() OVER (PARTITION BY) when conditional aggregation would work. PARTITION BY is for rank-style ranking; aggregation is for collapsing groups. Pick the simpler idiom.
Over-indexing on Python early. Omnichannel retail analytics rewards rock-solid SQL + store-level domain fluency + stakeholder skills first; Python is the Year-3 lever.

The trajectory

Stage	Years	Comp
Omnichannel Analyst I	0–2 yr	$65k–$90k
Omnichannel / Store Operations Analyst	2–4 yr	$90k–$125k
Senior Omnichannel Analyst	4–7 yr	$120k–$165k
Manager / Lead	5–8 yr	$150k–$200k
Director Analytics	8–12 yr	$180k–$260k
VP Analytics / Head of Omnichannel	12+ yr	$220k–$380k+

How the caseSQL curriculum maps to this

The caseSQL Omnichannel Retail path can't hand you Target's actual warehouse or Best Buy's allocation tooling — but the schema is the right shape, the gotchas are the same gotchas (BOPIS labor allocation, foot-traffic-no-purchase, cross-channel cannibalization, ship-from-store cost spread, inventory imbalance), and the canonical missions force the SQL skills the hiring loop screens on. The Hard tier (M11–M15) is the closest approximation of the live SQL screen; the Expert and Master tiers (M16–M25) are the take-home territory. M21 — the omnichannel store P&L composite with BOPIS labor allocated — is the question you'll be asked in some form on every senior omnichannel retail analyst loop.