Business Context

BrightCart is a mid-market ecommerce company that recently redesigned its website and marketing dashboards. The Head of Growth wants a candidate who can use Google Analytics, Tableau, or Excel to define the right KPIs, calculate them consistently, and explain performance changes to non-technical stakeholders.

Metric Scenario

In the last month, BrightCart recorded 1.8M website sessions, 720K product detail page views, 126K add-to-cart events, 54K checkout starts, and 27K completed orders. Revenue was $2.43M, down 6% month over month, while sessions were up 12%. Paid traffic sessions increased from 420K to 560K, but paid conversion rate fell from 2.4% to 1.6%. Mobile accounted for 68% of sessions and had a 1.1% conversion rate versus 2.8% on desktop. Leadership wants to know whether the issue is traffic quality, funnel friction, or a reporting-definition problem.

Requirements

1. Define the primary KPI you would use to evaluate site performance and explain why.
2. Calculate the current value of that KPI and at least three supporting metrics.
3. Decompose the KPI to identify the most likely driver of the revenue decline.
4. Explain how you would validate that Google Analytics, Tableau dashboards, and Excel exports are using consistent metric definitions.
5. Recommend 3 specific actions and the guardrail metrics you would monitor.

Data Available

• ga_sessions: session_id, user_id, session_start, device_type, channel, landing_page, country
• ga_events: session_id, event_time, event_name, page_type, product_id
• orders: order_id, user_id, session_id, order_time, gross_revenue, discount_amount
• marketing_spend: date, channel, campaign_id, spend, clicks, impressions
• dashboard_definitions: metric_name, formula_text, owner, last_updated