Analytics case study: a board pack from a star schema

The brief. It is early January 2026. Wadi Market's head of e-commerce is preparing the board pack and asks you for six numbers, built on the analytics mart (mart): how the last full fiscal year went, how the current fiscal year is going compared fairly, how the website and app funnel converts, which retail seasons earn the most per day, what marketing returns per riyal, and whether growth comes from new or returning customers.

Before writing anything, read the schema the way an interviewer hopes you will:

• Grain of each fact. fact_sales: one order line. fact_web_sessions: one visit (web, app and social only, since marketplace orders happen on a partner's site). fact_marketing_spend: one month × channel × campaign type. Three different grains: never join two facts directly at line level.
• The fiscal calendar. The fiscal year runs from 1 April to 31 March and is named after the year it ends in, so FY2025 = April 2024 to March 2025, and FY2026 has only reached December 2025. dim_date.fiscal_year does the mapping.
• Special members. customer_key = 0 is ‘Guest checkout’, a real dimension row that is not a customer. dim_customer keeps history (one row per address), so a person is customer_id, not customer_key.
• Returns. is_returned marks lines that came back; net-of-returns revenue excludes them.