Concrete answers to the questions on the module's front page. Each one names the datasets, the filters and the aggregation — including the part that is easy to get wrong.

All of them assume SaleIsTest eq false is already applied.

No-show rate

How many people bought a ticket and never came?


Datasets	`Tickets` left join `TicketUsages`
Extract	`Tickets`: `TicketInternalId, FaireventInternalId, TicketIsCancelled, ArticleType` `TicketUsages`: `TicketInternalId`
Filter	`TicketIsCancelled eq false and ArticleType eq 'TICKET'`
Compute	tickets with no matching usage ÷ all tickets

The left join is the whole recipe. An inner join drops exactly the tickets you are trying to count. Exclude cancelled tickets, or you will count refunds as no-shows, and restrict to ArticleType eq 'TICKET' so vouchers and service fees stay out of the denominator.

Daily attendance

How many distinct people were on site each day?


Dataset	`TicketUsages`
Extract	`TicketInternalId, Timestamp, IsFirstOfDay, IsEntry`
Filter	`IsFirstOfDay eq true and IsEntry eq true`
Compute	count distinct `TicketInternalId`, grouped by the date part of `Timestamp`

IsFirstOfDay is what stops a visitor who stepped out for lunch counting twice. Convert Timestamp to the event's local zone before taking the date, or your day boundaries will be wrong.

Entrance load by hour

Where and when do queues form?


Dataset	`TicketUsages`
Extract	`Timestamp, Entrance, EntranceTerminalName, IsEntry`
Filter	`IsEntry eq true`
Compute	count rows, grouped by `Entrance` and hour

Here you want raw scans, not distinct people — a returning visitor is genuine load on the door.

Campaign attribution

Which campaign sold the tickets?


Dataset	`Tickets`
Extract	`SaleUtmSource, SaleUtmMedium, SaleUtmCampaign, TicketGrossPrice, CurrencyShort, SalePaymentStatus`
Filter	`TicketIsIssuedByOrganizer eq true`
Compute	count tickets and sum `TicketGrossPrice`, grouped by the UTM columns and `CurrencyShort`

Restrict to organiser-issued tickets: exhibitor invitations carry no campaign and would dilute every rate. Null UTM values are direct or offline sales — report them as their own bucket rather than dropping them. For revenue rather than volume, add SalePaymentStatus eq 'PAID'.

Exhibitor quota usage

What did each exhibitor hand out, and how much was redeemed?


Datasets	`Tickets` left join `TicketUsages`
Extract	`Tickets`: `TicketInternalId, ExhibitorInternalId, ExhibitorName, TicketIsIssuedByExhibitor`
Filter	`TicketIsIssuedByExhibitor eq true`
Compute	per exhibitor: tickets issued, tickets with at least one usage, ratio

Never benchmark this ratio against organiser sales — free invitations redeem far lower, and comparing them makes both numbers meaningless.

Revenue by article

What actually sells?


Dataset	`Tickets`
Extract	`ArticleInternalId, ArticleName, ArticleType, TicketGrossPrice, TicketNetPrice, CurrencyShort, SalePaymentStatus`
Filter	`TicketIsCancelled eq false`
Compute	count and sum `TicketGrossPrice`, grouped by article and `CurrencyShort`

Free tickets have a gross price of zero and carry SalePaymentStatus eq 'NOT_REQUIRED' — they are not errors. If you want paid revenue only, filter SalePaymentStatus eq 'PAID' and say so in the report title. Group by ArticleType to keep service fees separable from tickets.

Refund exposure

How much has been refunded, and how much is in flight?


Dataset	`Tickets`
Extract	`TicketGrossPrice, CurrencyShort, SalePaymentStatus, SaleStatus`
Compute	sum `TicketGrossPrice` grouped by `SalePaymentStatus` and `CurrencyShort`

Five of the payment tokens are refund states — REFUNDED, PARTIALLY_REFUNDED, REFUND_PENDING, PARTIAL_REFUND_PENDING, PARTIAL_REFUND_MANUAL_PENDING. Settled and pending are different money; report them separately rather than lumping them into "refunded". Column values has the full list.

Audited attendance by statistics group

The numbers that feed the FKM report.


Datasets	`Tickets` left join `StatisticGroups` on `ArticleInternalId`
Extract	`Tickets`: `TicketInternalId, ArticleInternalId, TicketIsCancelled` `StatisticGroups`: all seven columns
Filter	`TicketIsCancelled eq false`
Compute	count distinct `TicketInternalId` where `IsFkmTotal eq true`, and separately where `IsFkmVisitor eq true`

Two things carry this recipe. Count distinct tickets, not rows — an article in three groups produces three rows and would triple your attendance figure. And report the two flags independently; never subtract one from the other, because they are not a partition. Articles with no group at all need a deliberate decision: give them their own bucket rather than letting the left join hide them.

Year-over-year segment growth

Which visitor segments grew?


Datasets	`Tickets`, optionally left join `StatisticGroups`
Extract	`FairBrandName, FaireventYear, ArticleName, ArticleInternalId, TicketInternalId`
Filter	`TicketIsCancelled eq false`
Compute	count tickets grouped by `FairBrandName`, `FaireventYear` and `ArticleName` — or by statistics group `Name` for a coarser view

Compare within a FairBrandName, never across brands. If an event moved dates between editions, compare full editions rather than calendar periods. When you group by statistics group, count distinct tickets for the reason above.

Survey results by segment

What did your visitors tell you, and who were they?


Datasets	`Surveys` join `Tickets`
Extract	`Surveys`: `SubmissionInternalId, AnswerInternalId, QuestionText, AnswerDisplayValue, TicketInternalId` `Tickets`: `TicketInternalId, ArticleName, TicketIsIssuedByExhibitor`
Compute	count distinct `AnswerInternalId`, grouped by `QuestionText`, `AnswerDisplayValue` and your segment

Distinct AnswerInternalId, never rows — a multi-select answer is several rows. Percentages should divide by distinct SubmissionInternalId, not by rows.

Recipes