How to clean ticket sales and attendance data before comparing events

You open last month's ticket report, put it next to this month's, and something looks wrong. Sales are up, attendance is down, and the income figure doesn't agree with the payment report. Before you decide the new programme attracted the wrong crowd, check what each report actually counts.

To clean event data for comparison, preserve the original records, agree what each row represents, and apply the same rules for dates, payments, ticket status and attendance to every event. Match the results back to the systems that recorded them, and keep unresolved records visible so you know which conclusions can wait.

The interesting part is how easily a perfectly ordinary export can change the story. An order containing several tickets can appear several times after you attach entry scans. A refunded ticket can remain in gross sales. A later download can include changes that weren't available when you reviewed the previous event.

This guide walks through preparing those records for a useful comparison. We'll follow a clearly invented workshop example, including the decisions that make its numbers comparable. If you're choosing the measures to report, our guide to event KPIs and metrics covers that wider question. Here, we're checking whether the records underneath those measures support the answer.

Start with the comparison you actually need

Before downloading anything, write down the decision. Perhaps you're choosing whether to repeat a workshop, increase capacity, change a ticket release or keep paying a promoter. The question determines which records need checking. You don't need a complete history of everyone who has ever bought from you to compare the income retained from two workshop dates.

Suppose you ran the same paid workshop in September and October. You want to know whether October's higher sales justified booking the larger room. You'll need comparable ticket quantities, ticket income and attendance. You'll also need the room cost and other relevant costs before making the booking decision, although those costs won't come from cleaning a ticket export.

Keep the comparison narrow enough to finish. Start with those two dates and the measures that could change your choice. If a separate question emerges about repeat customers, record it for another review. Trying to solve every reporting problem at once gives you more opportunities to make assumptions nobody remembers approving.

The UK's Government Data Quality Framework describes quality in terms of fitness for purpose and distinguishes completeness, accuracy, consistency and other dimensions. That's useful here because a filled spreadsheet can still contain the wrong values. Your test is whether the records are reliable enough for this particular decision.

Preserve the exports and record when you took them

The first thing you want to do is keep an untouched copy of each source. Record the event, source system, download time, filters and reporting cutoff. Work from a separate copy or an existing reporting process that preserves the originals. If someone asks why your total changed, you should be able to reproduce the earlier result.

A filename alone won't explain what the download includes. September's report might contain refunds processed a fortnight after the workshop, while October's was downloaded the morning after doors closed. That difference matters even if both files are called final. Agree whether you're comparing the position after a fixed interval or the latest available position for each event.

For an early operational review, you might compare both events using records available seven days after they finished. That is an example reporting choice, not a recommended refund deadline. For a later financial review, use updated records and label the remaining open items. Don't describe a report as settled while a material payment issue remains unresolved.

Keep a short note of the rules alongside the working data. Another colleague should understand what you included without reconstructing your conversation. Record changes to those rules too. If you decide to include door sales halfway through the review, apply the change to both events before comparing their totals.

Decide what one row means before joining files

Open each source and ask what a row represents. In an orders report, one row might be one purchase. In a ticket report, it might be one admission. In a scan history, it might be one attempt to scan a code. Those records describe different things, even when they share an order number.

Imagine an invented order for four workshop tickets costing $50 each, with no discounts or additional charges in this example. The order total is $200. If you attach that order total to all four ticket rows and add the column, you'll report $800. The join worked technically, but you multiplied the money by the ticket quantity.

Keep order totals at order level. Keep ticket quantities and admission status at ticket level. If you need income by ticket type, use ticket level amounts supplied by the source or document how you allocate an order discount. Don't divide every order equally when its tickets have different prices unless that allocation is explicitly appropriate for your purpose.

Scan records need similar care. A valid admission might have an initial successful scan and later attempts, including a permitted return. Match the relevant entry outcome to the ticket without turning each scan into another ticket sale. Keep the original scan history available for investigation, separate from the prepared comparison of admissions.

You'll usually want event ID, order ID and ticket ID wherever the source supplies them. An ID is the system's reference for a particular record. Keep the source name too, because two systems can issue the same reference independently. Combining source and ID avoids treating their unrelated records as the same order.

Protect identifiers when importing spreadsheets

A ticket reference that looks numeric still identifies a ticket; you aren't going to calculate its average. Import identifiers as text so their original characters survive. Check a few known references immediately after import, especially ones beginning with zero or containing a long string of digits. Problems are easier to catch before you've matched several files using them.

Microsoft's guidance on leading zeros and large numbers explains that Excel can remove leading zeros and change long numerical values. It recommends treating these values as text when preserving their original form. Changing the appearance later won't reliably recover an identifier that was already altered. Return to the original export and import it correctly.

Watch for dates and amounts changing meaning during import too. A date written as 10/11 can be read differently depending on the file and spreadsheet settings. A comma can mean a decimal separator or separate thousands. Confirm the source format before converting the column, and keep the original value if your process creates a cleaned version.

Use a small check that reflects your actual records. Find an order containing several tickets, a refund, a complimentary ticket and an admission with more than one scan attempt. Follow each through the preparation. A tidy total at the bottom of the sheet tells you less than seeing these known cases behave correctly.

Map ticket names and statuses without losing meaning

Your two workshops might use Early Bird in one report and Advance in another. That doesn't automatically make the tickets equivalent. Compare their access rights, release timing, prices and conditions before placing them in the same reporting group. A cheap ticket for a shorter session shouldn't disappear into a general admission category simply because both events sold it early.

Create a small mapping that preserves the original label and adds the group you'll use for comparison. For example, two releases offering the same workshop access can both belong to standard paid admission, while their release names remain available for price analysis. When a ticket has no clear equivalent, keep its own group and explain the difference.

Status labels deserve the same attention. Paid, completed, confirmed and valid may describe different stages depending on the source. A completed payment doesn't necessarily mean the ticket remains usable after a refund. Write the inclusion rule using the actual fields your source provides, then test that rule against known orders.

Separate paid admissions, complimentary admissions and operational passes. A zero value can come from a complimentary allocation, a full discount or a ticket issued for staff access. Those reasons can matter when you interpret demand. Preserve the reason when it's recorded, and leave it unknown when the source doesn't tell you.

Don't silently relabel old categories to make the events look similar. If October introduced a ticket including materials that September sold separately, record that change. Cleaning can make the records consistent enough to examine; it can't make the two offers identical. You may need to compare equivalent admissions separately before discussing the whole event.

Handle refunds, cancellations and transfers as different changes

A refund changes money, but its effect on admission depends on what happened to the ticket. A cancellation changes whether a ticket remains valid. A transfer changes the holder. Keep those facts distinct instead of using one broad excluded category for everything that happened after purchase. Otherwise you'll struggle to explain both sales and attendance from the same prepared records.

For the invented September workshop, suppose 180 paid tickets were issued and 12 were fully refunded and cancelled before entry. There are 168 valid paid tickets remaining. If eight complimentary tickets were also valid, the event had 176 valid admissions across those two groups. These figures describe the example only; they aren't attendance targets or industry benchmarks.

A partial refund needs an amount and a reason. If you return $10 after a programme change but the buyer keeps their $50 ticket, the income falls while the valid admission remains. If one ticket in a group order is cancelled, identify that ticket rather than excluding the entire order. Ask the responsible team when the source doesn't make the relationship clear.

A transferred ticket should remain linked to its purchase history while the current holder changes. Don't count the replacement credential as an extra sale or delete the original transaction because its holder changed. You can prepare an event comparison using ticket and order references without first resolving every variation in a person's name across your customer records.

Keep a separate record of refunds or disputes still in progress at the cutoff. State how you treated them in the provisional result and who will check their final status. You shouldn't have to guess the outcome to finish an early review. You do need to stop that provisional number being mistaken for the final amount retained.

Remove repeated records only after checking why they repeat

An export can contain repeated rows because somebody combined overlapping downloads. It can also contain several legitimate records for the same ticket, such as a purchase followed by a refund. Before removing anything, identify which situation you're looking at. Deleting repeated ticket references from a transaction history could remove the very adjustment needed to calculate the correct amount.

For a current ticket list, compare repeated ticket IDs within the same event and source. Check whether the rows are identical copies or different versions. If the source provides an updated timestamp and a documented rule for the current version, use that rule. Otherwise, return to the source rather than deciding that the bottom row must be the newest.

For an activity history, a repeated ticket ID can be expected. Use the activity's own reference, type and time to understand what it represents. Two refunds against one purchase may be legitimate partial refunds. Two payment attempts may include a failure followed by success. Your preparation needs to preserve those differences before it produces a single result for the order.

Check for overlapping files before merging new exports into an existing report. A report covering the whole sales period already contains orders from last week's download. Adding both files together doesn't update the total; it repeats the earlier orders. Prefer replacing the reporting snapshot with a fresh complete one, or use your existing process for applying identified changes.

Keep a count of removed copies and the reason for each removal rule. Then check the resulting order, ticket and money totals again. If removing duplicate rows changes valid admission inventory unexpectedly, stop and inspect the affected records. It is much easier to explain a documented duplicate export than to reconstruct genuine transactions after somebody deleted them.

Put timestamps on the same basis

Choose which timestamp answers the question before converting dates. Purchase time helps compare booking pace. Event date identifies the performance. Refund time explains when money was returned. Scan time describes recorded entry. Using the last updated timestamp for all four can move an old purchase into this week's sales because someone changed the ticket holder yesterday.

Keep the original timestamp and its stated time zone or offset. Then produce the reporting date using the same event time zone for every comparable record. The RFC 3339 timestamp standard describes timestamps with a stated relationship to UTC. You don't need to read the technical specification to run a workshop, but your reporting process needs that relationship to interpret times unambiguously.

An order just after midnight in the source's reporting zone can fall on the previous calendar date at your venue. That can affect a daily sales comparison without changing total sales. If the source doesn't identify its time zone, confirm it before assigning the order to a day. Don't infer it from where the buyer lives.

For booking pace, compare sales at the same number of days before the event, and record when each event went on sale. October having more tickets sold a month before doors tells you something different if it had already been available twice as long. Keep sales stage and time on sale visible when explaining the difference.

Reconcile ticket money before comparing revenue

Choose a clearly named amount, such as ticket charges after discounts and completed refunds, and document what it includes. Keep taxes, buyer fees, fees you absorb and promoter commissions in identifiable fields where the source provides them. Work with finance on the basis that matches the decision. Calling every money column revenue makes a comparison unnecessarily confusing.

A bank payout is a different record from a ticket sale. It can include fees, refunds and transactions for several events, and it can arrive after the purchase date. Stripe's payout reconciliation documentation explains matching the transactions in an automatic payout to the deposit. That is useful for checking cash received, while your ticket records explain which event and admissions generated the sales.

Suppose our invented September workshop sold its 180 tickets at $50 each and fully refunded the 12 cancelled tickets. Ticket charges after those refunds are $8,400, before any other deductions or additions. That follows from $9,000 charged less $600 returned. A different bank deposit doesn't disprove the calculation; it requires an explanation of the other transactions and timing.

Compare amounts in the same currency and on the same stated basis. If events used different currencies, retain their original amounts and ask finance to specify the conversion method for the comparison. Don't add two currencies together because the export stores both in a column called amount. Also check whether the source stores whole currency units or smaller units.

Before accepting the total, match ticket amounts to their orders and orders to their payment references where those relationships are available. Investigate unmatched records rather than changing amounts until everything balances. A duplicate payment reference, a refund missing its original order or a large unexplained adjustment needs a person who understands the transaction to resolve it.

Prepare attendance records without inventing arrivals

For this comparison, decide what you mean by attended. Count tickets valid at entry with a recorded admission, including documented manual entry. Later refunds must not erase recorded attendance. Apply the same rule to both dates. Keep sessions, repeat entry attempts and staff access separate so their activity doesn't increase the count of paying admissions.

Suppose September recorded first admission for 151 of its 168 valid paid tickets. The paid ticket attendance rate is approximately 89.9%. If six of the eight complimentary tickets also recorded admission, total recorded attendance across these groups is 157. Dividing that combined attendance by paid tickets alone would mix populations and produce a misleading percentage.

If a ticket has no scan, you know it has no recorded scan. You don't automatically know the person stayed home. Check whether all entrances used the agreed process and whether manual admissions were captured. Preserve an unknown outcome when evidence is missing. A full investigation of conflicting door counts is a separate task; your comparison should clearly identify the affected records.

Use ticket references to associate admissions with the right event and ticket group. An email address repeated across four tickets doesn't mean the same person entered four times. It may mean one buyer booked for friends. This is why cleaning an attendance comparison doesn't require merging everyone who shares contact information or redesigning the registration list.

Where operations already records ticket activity in one system, start with that system's reports. Loopyah event software connects ticket orders, customer records and the door list, with QR scanning by door managers. That reduces the need to rebuild those relationships manually. Your team still decides whether the events used comparable admission rules and investigates exceptions that affect the review.

Check whether the ticket buys the same amount of attendance

A weekend pass and a ticket for one evening shouldn't become interchangeable because both occupy one row. If your events include several days or sessions, keep the admission entitlement explicit. You may compare passes sold, admissions recorded on each day or attendance at a particular session, but those counts need different names and different inclusion rules.

For example, someone holding one weekend pass might enter on both Saturday and Sunday. That's one pass sold and two recorded daily admissions. It doesn't establish two unique paying people. If another event sells separate daily tickets, compare the appropriate daily admissions or explain why a pass comparison cannot answer the same question. Don't force a shared label over different offers.

Ticket bundles need similar treatment. An order for a table might cover several admissions, while the export lists one purchased item. Confirm the included admission quantity from the source before comparing it with individual ticket sales. Preserve the table purchase as an order item and its associated admissions separately, so neither income nor headcount multiplies accidentally when you prepare the report.

If the historical source doesn't preserve those relationships, be explicit about the limit. You can compare the recorded purchases and money on their stated basis while withholding an unreliable headcount comparison. Ask whether the missing detail could change the decision. If it could, investigate the source before recommending a larger venue or committing to more catering. If it couldn't, explain the uncertainty and proceed with the measures that are supported.

Keep missing information visible

Missing values need a reason before they need a replacement. An empty ticket price might indicate an incomplete export, a complimentary ticket recorded differently or a field that isn't applicable. Those situations require different treatment. Replacing all blanks with zero can turn missing financial information into apparently free admissions and change the average price.

The same applies to sales channels. An unknown channel doesn't establish that a sale came through direct traffic or word of mouth. Keep unknown as its own category and report its size when channel performance matters. If October has much less channel coverage than September, be careful about comparing the named channels as though their totals describe all purchases.

Website analytics can provide useful supporting evidence, but validate purchase recording before treating it as another sales ledger. Google Analytics documentation on transaction IDs explains how matching transaction IDs help deduplicate web purchase events, with limits on where that works. If repeated purchase signals appear, investigate the tracking setup and use completed ticket orders to establish the sales total.

Don't resolve uncertain records by choosing whatever makes the report look reasonable. Keep a separate list with the record reference, issue, affected measure and person checking it. If you later resolve the issue, record the evidence and update the comparison. You can then explain exactly why the number changed instead of producing another unexplained final file.

Check the cleaned totals against the originals

After preparing the data, compare counts and amounts before and after each major step. If joining orders to tickets increases the number of orders, check why. If grouping ticket labels reduces the ticket total, find the records that lost their group. The point is to catch an unintended change while you still know which step caused it.

Write down the expected relationships in plain language. Every included ticket should belong to the intended event. Every included order should appear once in an order count. A cancelled ticket shouldn't be part of current valid admission inventory. A recorded refund should affect the appropriate money total once. Test exceptions individually rather than assuming every source uses identical rules.

Use both totals and examples. Two mistakes can cancel each other out in a final total, while a join assigns money to the wrong ticket group. Trace known transactions through the working data, then check the overall sums against their sources. Where they disagree, retain the difference and explanation until the responsible person resolves it.

Avoid setting an arbitrary tolerance that hides inconvenient errors. Whether a difference matters depends on the decision and its possible effect. A small unresolved amount might leave a venue choice unchanged. A missing block of admissions can undermine the attendance comparison even when ticket income reconciles perfectly. Explain that distinction in the review note.

Ask a colleague to reproduce one reported figure using the preserved source and written rules. If they need your memory to complete the calculation, clarify the instructions before sharing the comparison with the wider team.

Compare the events with their remaining differences attached

You can now compare the workshop dates using consistent definitions. Show gross paid tickets, valid paid tickets, complimentary admissions, recorded attendance and the chosen ticket income measure separately. Keep the cutoff visible. That lets a colleague distinguish stronger sales from fewer refunds or better recording, without reading your entire preparation process.

Next, check what changed in the offer and operation. October's larger room might have changed capacity, costs or the experience. It might also have sold a different mix of ticket releases. Reliable records help you investigate those explanations, but a difference between two events doesn't prove which change caused the result. Make the next booking decision with those limits in mind.

Work through the larger room decision

Now extend the invented workshop example. October issued 210 paid tickets at the same $50 price and fully refunded and cancelled 10. That leaves 200 valid paid tickets and $10,000 of ticket charges after refunds, before other additions or deductions. Suppose 180 of those paid tickets recorded admission. The paid ticket attendance rate is 90%, close to September's approximately 89.9%.

At this point, October has $1,600 more ticket income on the stated basis and 29 more recorded paid admissions. That is a useful improvement to investigate. It doesn't support saying the new room improved the attendance rate. The difference in recorded attendance mainly reflects more valid paid tickets, while the proportion recording admission is almost unchanged in this invented example.

To decide whether the larger room was worthwhile, bring in its additional costs. If the larger room and related changes cost an extra $2,000, the additional ticket charges alone don't cover that increase. Other relevant income or cost changes could alter the decision. Confirm them using the budget and financial records rather than assuming a higher ticket count establishes a better result.

You can also see why the refund treatment matters. Comparing September's valid paid tickets with October's gross issued tickets would exaggerate the increase. Comparing September after completed refunds with October before processing its cancellations would exaggerate retained ticket charges too. The cleaning rules make those inconsistencies visible before they influence your next venue commitment.

Share the prepared comparison with the person responsible for the budget, together with unresolved issues that could change the choice. Keep personal details out of that report unless someone needs them to investigate a specific record. Our guide to attendee data retention explains how to decide what to keep when the operational job is finished.

Before the next workshop goes on sale, fix the recurring source of confusion. That might mean agreeing ticket groups in advance, documenting the reporting cutoff or checking how manual admissions are recorded. Repeat the same preparation on the next event and keep the rules available. You'll spend less time arguing about the export and have a clearer basis for deciding which event deserves another date.