How Much Profit Does a Fan Generate on Game Day?

A ChatGPT + Power BI + Copilot Walkthrough

What if every fan who enters your stadium is a micro-business unit? In this post, I walk through how you can compute per-fan profitability on game day using ChatGPT, Power BI, and Copilot. You will see the full pipeline: data modeling, prompt engineering, narrative generation, and pitfalls to avoid.

---

1. Why This Matters (and Why It’s Fun)

Sports franchises have long tracked aggregated revenue: tickets, merch, concessions, etc. But per-fan profitability is a more surgical metric. It helps answer questions like:

• Which membership tier yields the best margin?

• Which fans or games are dragging performance?

• Where should upsell, pricing, or operations focus attention?

By modeling this in a dashboard and overlaying narrative (via Copilot), you can transform raw numbers into actionable storytelling for executives or operations teams.

---

2. Defining the Metric: FanProfit

A simplified formula:

FanProfit = RevenueFromFan – CostAllocatedToFan

Where:

• RevenueFromFan can include:

  • Ticket price

  • Spending (merchandise, food & beverage)

  • Parking / transit

  • Premium upgrades (VIP, suites, experiences)

• CostsAllocatedToFan include:

  • COGS (costs to produce goods sold)

  • Variable per-fan costs (security, cleaning, staffing)

  • A share of fixed costs (stadium maintenance, utilities) divided over attendees

You may adapt this depending on data quality and business logic. The goal is to make the metric transparent and defensible.

3. Data Model Sketch

A simplified star schema you might build. (Feel free to change or expand.)

Table Key Columns Purpose

Fans FanID, Membership Tier, Region Attributes to segment

Games GameID, Date, Opponent, Attendance Link to each event

TicketSales FanID, GameID, TicketPrice, TicketType Core ticket revenue

Spending FanID, GameID, Category, SpendAmount Merch, F&B, etc.

Parking FanID, GameID, ParkingFee Transportation revenue

Upgrades FanID, GameID, UpgradeFee Premium upsell revenue

Costs GameID, FixedCost, VariableCostPerFan Venue & game-day cost structure

MarginLookup Category, COGS, MarginRate Link spending categories to cost basis

With relationships in place (FanID to all “fan-level” tables, GameID to Costs, etc.), you will have a model where each Fan × Game can be evaluated in revenue & cost context.

---

4. Step A: ChatGPT to Generate DAX (Scaffold, Not Final)

ChatGPT Prompt Suggestion:

I have a Power BI model with tables: TicketSales, Spending, Parking, Upgrades, Costs, and MarginLookup.  

I want a DAX measure called FanProfit that for each fan in a game computes:

TicketSales[TicketPrice] + SUM(Spending[SpendAmount]) + Parking[ParkingFee] + SUM(Upgrades[UpgradeFee])

  minus

(COGS from spending categories + (FixedCost + VariableCostPerFan) allocated per fan)

Write me a DAX measure (with reasonable assumptions) and a plain English explanation. Also include a version that handles missing or zero-spend fans.

Hypothetical ChatGPT output:

FanProfit = 

VAR Rev =

    SUM(TicketSales[TicketPrice])

    + SUM(Spending[SpendAmount])

    + SUM(Parking[ParkingFee])

    + SUM(Upgrades[UpgradeFee])

VAR CostOfGoods =

    SUMX(

      Spending,

      RELATED(MarginLookup[COGS])

    )

VAR GameCostAlloc =

    VAR TotalFans = CALCULATE(COUNTROWS(TicketSales), ALLEXCEPT(TicketSales, TicketSales[GameID]))

    VAR Fixed = RELATED(Costs[FixedCost])

    VAR Variable = RELATED(Costs[VariableCostPerFan])

    RETURN

      Fixed / TotalFans + Variable

RETURN

    Rev - CostOfGoods - GameCostAlloc

Plain English Explanation (possible ChatGPT explanation):

1. We sum up all revenue streams for that fan (ticket + spending + upgrades + parking).

2. Then we subtract the cost of goods sold (mapped via a margin table) and allocate per-fan fixed + variable game costs (fixed divided by total fans, plus a per-fan variable cost).

3. For fans with zero spend or missing data, the measure would degrade to ticket minus cost allocations.

   You would paste this into your model, test it, refine as needed. (ChatGPT code is a starting point, always test it since it is not a guarantee.)

---

5. Step B: Build Report + Publish with Copilot Ready Setup

• Create visuals like:

  • FanProfit by Membership Tier

  • Top N fans by profit (or percentile buckets)

  • Profit variance vs expected

  • Scatter: spend vs cost per fan

  • Trend over games

• Publish to a workspace that supports Copilot / AI narrative visuals

  • According to Microsoft, report workspaces must be on a paid Premium or Fabric capacity, with Copilot enabled. Microsoft Learn

  • Use the Narrative visual with Copilot to summarize your report page in natural language. Microsoft Learn

  • You can customize tone, focus (e.g. “executive summary”) via prompt editing. Microsoft Learn+1

Make sure your visuals are clearly named, axes labeled, and synonyms in model are clean. Copilot’s narrative visual leverages what is on the report canvas.

---

6. Step C: Copilot Prompts & Narrative Interaction

Once your page is live with visuals and the Copilot narrative pane:

• Ask:

“Summarize fan profitability by tier over the last 5 games.”

• Ask follow-up:

“Which tier had the steepest decline, and what contributed?”

• Or refine tone:

“Rewrite this as a one-paragraph summary for the CFO.”

Copilot will parse visuals, metadata, and the query context to generate a narrative. You can adjust it in the narrative visual using custom prompts.

Sample narrative you might see (simulated):

“Over the last 5 games, Premium-tier fans had the highest average FanProfit (~$72), driven by strong upgrade uptake and stable cost allocation. Regular-tier fans averaged $23 profit, and Silver-tier fans saw a 12% decline in Game 4 due to weaker merchandise spend and slightly higher relative cost per fan. The biggest drop was in Silver tier, where revenue dropped ~8% while game costs held steady.”

You can polish this further, anchor it to business actions, or add it to a presentation.

---

7. Humanizing & Storytelling Touches

To make the content feel human (beyond code and visuals), sprinkle in:

• A personal anecdote: “I remember once talking to a stadium ops manager who baffled over why VIP guests didn’t always yield higher margins — now you can compute exactly when they do (or don’t).”

• Persona names: e.g. “Let’s imagine Jordan, a Silver-tier attendee, whose spending dipped last game.”

• Inject questions to the reader: “Would you have guessed that fixed cost allocation would drown out a small upsell?”

• Use plain-language transitions so non-technical readers feel included.

---

8. Pitfalls & Caveats (don’t skip this)

• Garbage in, garbage out: If your margin table or cost allocations are sloppy, your FanProfit is suspect.

• ChatGPT hallucinations: Always inspect DAX it suggests; syntax or logic may be flawed.

• Zero-spend fans / missing data: You need fallback logic or filters.

• Cost allocation assumptions matter: Different spreading rules yield different “truths.” Be transparent about your method.

• Narrative brittleness: Copilot output can vary over time or with small data shifts.

• Performance & latency: Big models, complex relationships, many visuals may slow narrative generation.

• Governance: Don’t let automated narratives become exec deliverables without review.

---

9. What This Enables — And What’s Next

Once you have this baseline:

• Trigger alerts: if a tier’s FanProfit drops > 15%, notify ops

• Simulate “what if” tweaks: reduce per-fan cost, adjust pricing, optimize merchandise margins

• Chain with Power Automate / Azure OpenAI to embed more advanced narrative flows (e.g. push narrative to email or Ops dashboard) Microsoft Fabric Community

• Use this model across venues, seasons, or fan cohorts

---

10. Call to Action

Try this yourself on a dataset. Start small: get the DAX running, build a chart, add Copilot narrative, see what it says. Then share: post your prompt, your narrative, and what surprised you.