↵ select ↓ ↑ navigate esc close

Week 3 - Counting Tickets Without Counting Them Twice

Practical Data Modeling ·

Read Chapters 7–9, printed pages 125–193: Relationships: Tying It Together; Grain: Choosing Your Level of Detail; Counting and Aggregation: Controlling the Grain.

One support ticket mentions two products and contains ten chat messages. Join everything together, count the rows, and you can end up with a very impressive ticket count. That’s neat, but the customer doesn’t care. Unfortunately, the customer still contacted you about one ticket.

Practical Data Modeling is a reader-supported publication. To receive new posts and support my work, consider becoming a free or paid subscriber.

Relationships, grain, and aggregation belong together because a perfectly valid relationship can produce misleading results when you aggregate across it. Chapter 7 gives us ways to represent the connections. Chapter 8 makes us declare exactly what a record represents. Chapter 9 asks whether we can combine the groups and measures the way we intend.

For our online retailer, the Chapter 12 example uses TicketProducts to represent tickets involving multiple products. A ticket-product pair has a different grain from a ticket. Chat messages have another grain. These distinctions matter when someone wants to know which products generate complaints or how quickly agents (human or AI) respond during a conversation.

Let’s add a reporting request: the support manager wants ticket counts by product and a total across the business. If one ticket concerns two products, it can legitimately appear in both product groups. Adding those groups won’t give the distinct ticket total. The same care applies to resolution rates. A team that resolves nine of ten tickets and a team that resolves one of two tickets don’t have an overall rate of 70 percent just because that’s the average of their percentages. We need the underlying counts.

Questions for discussion

  1. What does one record represent in Tickets, TicketProducts, and a message-level transcript dataset?
  2. How would you label product-level counts so people understand why they may not add up to the distinct ticket total?
  3. Which parts of a resolution rate should you preserve so you can recompute it correctly for a larger group?

Optional exercise: Invent three tickets, with one involving two products. Write the ticket-product pairs on paper, then calculate tickets by product and the distinct total. Label the grain beside each result. A spreadsheet is optional.

The public comments are open for your examples and questions. If you’re behind, three tickets are enough to join the conversation. Please use fictional or sanitized data.

November 4 is the target reading week, and I’m traveling that day. I’ll probably do the book club from my phone, since I’ll be boarding the plane during book club.

Paid Practical Data Modeling subscribers can find the confirmed live day, time, and Google Meet link in the paid subscriber chat on Substack. Recordings are included. Message me on Substack if you need the calendar or Meet link.

Practical Data Modeling is a reader-supported publication. To receive new posts and support my work, consider becoming a free or paid subscriber.