Matchi Data Share Documentation

What is a Data Share?

A data share is a direct connection to a (Snowflake) database providing read-only access to data from your venue. This connection can be used with any BI tool (like Power BI, Looker, Excel) or programmatic tool (like Python, R) that supports Snowflake connections.

The data is updated daily, typically by early morning, and contains all historical data up to the previous day. This means you'll have access to complete data from yesterday and earlier, but not today's data. All data is filtered so that each data share account can only access rows related to its own facilities.

Note: While data is updated daily, customer support and service-level guarantees are only provided on workdays (Monday-Friday), excluding holidays.

In this documentation, "venue" and "facility" are used interchangeably.

How to Connect

When you receive your data share account, you'll get an email with your specific connection details including:

Using Snowflake Web Interface

  1. Navigate to your provided Snowflake URL
  2. Log in with your username and password
  3. You'll be prompted to change your password on first login
  4. Multi-factor authentication setup is optional and can be added later
  5. Find your data under: Data > Databases > Matchi > External > Views
  6. Use the Projects tab to write and execute SQL queries

Connection Parameters for External Tools

Use these parameters when connecting from BI tools, Excel, Python, R, or other applications:

For questions or support, contact team-data@matchi.com.

Data Structure

Fact tables contain measurable business events and transactions. They are used to define metrics like revenue, number of bookings, or membership counts. Each row represents a specific event (like a booking or payment), which is why they typically have multiple rows per entity (e.g., multiple bookings per customer). Both fact and dimension tables contain the full available history — there is no time limit on the data share.

All fact tables have a many-to-one relationship with the facility dimension. This means that each row in a dimension table belongs to exactly one facility, while a facility can have multiple rows in each dimension table.

Similarly, the customer dimension has a one-to-many relationship with fact tables. This means that a single customer can have multiple rows in fact tables (like bookings, revenue, or memberships), while each row in a fact table belongs to exactly one customer.

To enrich your analysis, you can join fact tables to dimension tables using the following keys:

Use these IDs to combine tables and add extra dimensions as needed. Each model has a latest_sync field that shows the last time the table was updated.

Table of Contents

Suggested Metrics

The following definitions are used by Matchi. Use them as a starting point to create your own metrics.

Table Metric Definition
customer_v1 Active customers count(customer_id) where archived = false and deleted is null
booking_v1 Total number of bookings count(distinct booking_id)
booking_v1 Total played hours sum(duration)/60
customer_participation_log_v1 Participating customers count(distinct customer_id)
customer_participation_log_v1 Participations count(*)
facility_home_vs_visitors_v1 Bookings and activities by player relation sum(bookings_and_activities) group by player_relation
facility_home_vs_visitors_v1 Money spent by player relation sum(money_spent) group by player_relation
membership_history_v1 Active members on date count(distinct customer_id) where state = 'Member'
membership_v1 Total number of memberships count(distinct membership_id)
monthly_coupon_value_v1 Total gift card value sum(remaining_amount)
monthly_coupon_value_v1 Total remaining tickets sum(remaining_nr_of_tickets)
revenue_v1 Total revenue sum(revenue)
schedule_v1 Occupancy sum(played_hours) / sum(bookable_hours)
statement_data_v1 Net position sum(invoicedexvat) group by type

Examples

Here's an SQL example of how to analyze revenue by facility:

-- Calculate monthly revenue by facility
SELECT
    f.facility_name,
    DATE_TRUNC('month', r.invoice_date) as month,
    SUM(r.revenue) as total_revenue,
    COUNT(DISTINCT r.booking_id) as number_of_bookings
FROM revenue_v1 r
LEFT JOIN facility_v1 f ON r.facility_id = f.facility_id
WHERE r.invoice_date >= DATEADD('month', -12, CURRENT_DATE())
GROUP BY facility_name, month
ORDER BY facility_name, month

This query:

Tables

Below are detailed descriptions of each table in the data share, showing their columns and relationships.

Dimension Tables

Dimension tables contain descriptive attributes and are used to categorize, filter, and group fact tables. They typically have one row per entity.

facility_v1

Facility properties.

Changelog:

📋 Table Structure

Column Type Description
active BOOLEAN Whether the facility is currently searchable in matchi.
city TEXT The city where the facility is located.
country TEXT Country where the facility is located.
currency TEXT The currency the facility trades in (SEK, NOK, DKK, EUR, GBP, ...).
date_created DATE The date and time the facility was created.
facility_group_name TEXT The name of the facility group (chain) that the facility belongs to. NULL if the facility is not part of a group.
facility_id INTEGER (PK) The unique identifier for the facility.
facility_name TEXT The full name of the facility.
facility_shortname TEXT The short name of the facility.
latitude FLOAT Latitude of the facility's location, in decimal degrees (WGS84).
longitude FLOAT Longitude of the facility's location, in decimal degrees (WGS84).
municipality TEXT The municipality where the facility is located.
region TEXT The region (county or equivalent administrative subdivision) where the facility is located. NULL when not known.
h3_level_9 TEXT H3 geospatial index at resolution 9 (~0.1 km² hexagon). Most granular level for precise location analysis.
h3_level_8 TEXT H3 geospatial index at resolution 8 (~0.7 km² hexagon). Useful for neighborhood-level analysis.
h3_level_7 TEXT H3 geospatial index at resolution 7 (~5 km² hexagon). Suitable for district-level analysis.
h3_level_6 TEXT H3 geospatial index at resolution 6 (~36 km² hexagon). Good for city-level analysis.
h3_level_5 TEXT H3 geospatial index at resolution 5 (~252 km² hexagon). Regional-level grouping.
h3_level_4 TEXT H3 geospatial index at resolution 4 (~1,770 km² hexagon). Large regional areas.
h3_level_3 TEXT H3 geospatial index at resolution 3 (~12,392 km² hexagon). Country-level grouping.
latest_sync TIMESTAMP_LTZ When this data was last synchronized from the source system.

customer_v1

Customer properties. Each Matchi user has a unique user_id that identifies their account across all facilities. When a user plays at a facility, they are assigned a unique internal customer_id and a customer_number that is non-unique across facilities. The customer_number is what is displayed in the Matchi administration web interface. The customer_number is unique within each facility, but not across all facilities. The customer_id is unique within each facility and within matchi. It is the primary key for the customer table. A user will have multiple customer_ids if they play at multiple facilities.

Example: Player 'Mats Edberg' has user_id 12345 for his Matchi account. At venue X, he has customer_id 4995 and customer_number 55. At venue Y, he has customer_id 50123 and, by coincidence, also has customer_number 55.

Changelog:

📋 Table Structure

Column Type Description
address1 TEXT First line of the customer's postal address.
address2 TEXT Second line of the customer's postal address.
archived BOOLEAN true if the customer has been archived at the facility.
cellphone TEXT The customer's mobile phone number.
city TEXT The customer's city.
country TEXT The customer's country as a two-letter ISO code (SE, NO, FI, DK, GB, ...). NULL when not set.
customer_id INTEGER (PK) The unique identifier for the customer.
customer_number INTEGER The venue specific identifier for the customer.
date_created TIMESTAMP_NTZ The timestamp when the customer was created at the facility.
email TEXT The customer's email address.
facility_id INTEGER (FK to facility) The facility that the customer belongs to. The same person has a separate customer record at each facility; use user_id to link them.
firstname TEXT The customer's first name.
fullname TEXT The customer's full name (first name and last name).
gender TEXT MALE, FEMALE, OTHER, NOT_SPECIFIED, ORGANIZATION or 'n/a' when not set.
lastname TEXT The customer's last name.
telephone TEXT The customer's phone number (landline).
user_id INTEGER The unique identifier for the matchi user.
zipcode TEXT The customer's postal code.
latest_sync TIMESTAMP_LTZ When this data was last synchronized from the source system.

court_v1

Court properties. One row per court.

📋 Table Structure

Column Type Description
archived BOOLEAN true if the court has been archived by the venue and is no longer bookable.
camera_equipped BOOLEAN true if the court has MATCHi TV cameras installed.
court_id INTEGER (PK) The unique identifier for the court.
court_location TEXT The location (indoor or outdoor) of the court.
court_name TEXT The name of the court as shown to players.
court_type TEXT Double/Standard/Mini etc
date_created TIMESTAMP_NTZ The timestamp when the court was created.
description TEXT Free-text description of the court, entered by the venue.
facility_id INTEGER (FK to facility) The facility that the court belongs to.
last_updated TIMESTAMP_NTZ The timestamp when the court was last updated.
restriction TEXT Booking restriction applied to the court. Useful for excluding non openly-bookable courts when calculating online occupancy. One of: - NONE: no restriction; bookable by anyone online. - OFFLINE_ONLY: not bookable online; only facility admins can book it (e.g. courts reserved for walk-ins or internal scheduling). - MEMBERS_ONLY: only customers with an active membership can book online. - REQUIREMENT_PROFILES: requires a specific player profile (see profiles).
sport_name TEXT The sport played on the court (e.g. Padel, Tennis, Badminton).
surface TEXT Hard/Clay/Carpet/Lawn etc
latest_sync TIMESTAMP_LTZ When this data was last synchronized from the source system.

coupon_v1

Coupon properties. Note that coupon is a collective name and there are three classes of coupons:

Changelog:

📋 Table Structure

Column Type Description
archived BOOLEAN true if the coupon has been archived by the venue and can no longer be sold or used.
class TEXT promo_code, coupon, gift_card
code TEXT promo code string e.g. 'SUMMER2025'.
coupon_id INTEGER (PK) The unique identifier for the coupon.
coupon_name TEXT The name of the coupon as set by the venue.
date_created TIMESTAMP_NTZ The timestamp when the coupon was created.
description TEXT Free-text description of the coupon, entered by the venue.
start_date DATE The start date of the coupon.
end_date DATE The end date of the coupon.
facility_id INTEGER (FK to facility) The facility that the coupon belongs to.
max_duration INTEGER How many minutes the coupon can be applied to (30, 60, 90, 120, ...)
max_number_of_usages INTEGER For promo codes, max number of usages per customer.
nr_of_days_valid INTEGER Ho long it is valid (coupon and gift card)
unlimited BOOLEAN true if the coupon can be used an unlimited number of times.
vat_value NUMBER(38,6) The VAT rate in percent applied to the coupon (e.g. 6, 25). NULL when no VAT rate is set.
latest_sync TIMESTAMP_LTZ When this data was last synchronized from the source system.

Fact Tables

Fact tables contain measurable business events and transactions. They are used to define metrics like revenue, number of bookings, or membership counts. Each row represents a specific event (like a booking or payment), which is why they typically have multiple rows per entity (e.g., multiple bookings per customer).

activity_v1

One row for each activity occasion. Includes metadata about the activity and participation in the activity.

Changelog:

📋 Table Structure

Column Type Description
facility_id INTEGER (FK to facility)
activity_id INTEGER The activity identifier.
activity_occasion_id INTEGER (PK) The unique identifier for the activity occasion.
date DATE The date of the activity occasion.
start_time TIME The start time (clock time, local) of the activity occasion. Combine with date for the full timestamp.
end_time TIME The end time (clock time, local) of the activity occasion. Combine with date for the full timestamp.
time_slot TEXT Night, Morning, Pre-lunch, Lunch, Afternoon, Evening, Pre-night. Based on start_time.
time_slot_desc TEXT 0. Night (00:00 - 06:00), 1. Morning (06:00 - 09:00), ... Sortable variant of time_slot with the time range.
duration INTEGER The length of the activity occasion in minutes.
sport_name TEXT The sport of the activity (e.g. Padel, Tennis).
name TEXT The name of activity. e.g. Volley training, Mexicano level 4-6,....
activity_visibility TEXT Public (anyone can sign up) or Private (invitation only).
activity_occasion_type TEXT TRAINING, COMPETITION,...
activity_event_name TEXT AMERICANO, BEAT_THE_BOX,...
external_service TEXT External service the activity is integrated with, e.g. PADELBOARD. NULL if none.
level TEXT The skill level range the activity is intended for, formatted as min-max (e.g. 4.000000-6.000000). NULL if no level is set.
price NUMBER(38,6) The price to participate, including VAT, in the facility's currency.
active_members_price BOOLEAN If members get a discount on the activity.
members_price NUMBER(38,6) The price for members, including VAT. NULL if members pay the regular price.
min_num_participants INTEGER The minimum number of participants required for the activity to take place.
max_num_participants INTEGER The maximum number of participants allowed.
players INTEGER The number of players signed up for the activity occasion.
paying_players_through_matchi INTEGER The number of signed-up players who paid for the occasion through MATCHi.
active_queue BOOLEAN If there are players in queue for the activity.
players_in_queue INTEGER The number of players on the waiting list.
nr_of_courts INTEGER The number of courts reserved for the activity occasion.
deleted BOOLEAN true if the activity occasion was deleted (cancelled) by the venue. Only occasions deleted less than 7 days (168 hours) before their scheduled start are included; occasions deleted earlier than that are not in the share.
deleted_hours_before INTEGER For deleted occasions, the number of hours between the deletion and the scheduled start (0-168, see deleted). NULL if the occasion was not deleted.
status TEXT Completed activity , Automatic cancellation , Manual cancellation
fill_rate TEXT Filled, No participants, Below min participants, Above min participants
latest_sync TIMESTAMP_LTZ When this data was last synchronized from the source system.

booking_v1

Fact table tracking all non-cancelled bookings. Each row represents a single booking session, including metadata about the booking type, timing, and participants.

Key metrics:

Note: Bookings are classified by booking_group_type (BOOKING, ACTIVITY, TRAINING, COMPETITION) and can be made by either the facility or the player.

Note: Bookings on archived courts and on days when the facility was inactive are retained — they are real events tied to orders and payments. The schedule view excludes archived courts from their archival date onwards and inactive facilities for the days they were inactive, so booking hours and schedule played_hours can differ marginally for venues with such activity.

Changelog:

📋 Table Structure

Column Type Description
facility_id INTEGER (FK to facility)
booking_id INTEGER (PK) The unique identifier for the booking.
court_id INTEGER (FK to court)
customer_id INTEGER (FK to customer)
date_created TIMESTAMP_NTZ The timestamp when the booking was created.
slot_start_time TIMESTAMP_NTZ The start timestamp of the booked slot. Local time.
slot_end_time TIMESTAMP_NTZ The end timestamp of the booked slot. Local time.
duration INTEGER The length of the booking in minutes.
booking_group_type TEXT SUBSCRIPTION, ACTIVITY, BOOKING, TRAINING, COMPETITION, ...
booked_by TEXT FACILITY, PLAYER
latest_paid_membership INTEGER (FK to membership) The latest membership that was active at the time of the booking.
customer_category_name TEXT The customer category applied by the pricing engine for this booking (e.g. "Standard", "Adult", "Junior", "Senior"). Reflects which category was actually used to determine the price, not what the customer selected at checkout. Available from 2026-03-05 onwards; NULL for older bookings.
membership_type_name TEXT The name of the membership type applied to the booking (e.g. "Premium", "Senior", "Game4Padel Membership"). Indicates which membership granted the price/access used for this booking. Available from ~2025-03 onwards; NULL for older bookings or when no membership was applied.
time_slot TEXT Night, Morning, Pre-lunch, Lunch, Afternoon, Evening, Pre-night. Based on slot_start_time.
time_slot_description TEXT 0. Night (00:00 - 06:00), 1. Morning (06:00 - 09:00), ... Sortable variant of time_slot with the time range.
day_name TEXT Mon, Tue, Wed, ...
day_name_sort TEXT 1. Mon, 2. Tue, 3. Wed, ... Sortable variant of day_name.
weekend BOOLEAN true (Sat-Sun), false (Mon-Fri)
session_type TEXT private, public
sport_name TEXT The sport of the booked court (e.g. Padel, Tennis).
mtv_stream BOOLEAN true if the booking is streamed on matchi tv
order_origin TEXT web, api (matchi app ar external api), facility
payment_status TEXT Whether the booking is paid, by the same rule the venue admin uses for "Booked - not paid". Set for booking_group_type BOOKING only: 'paid' (the payments registered in MATCHi cover the price, including bookings the venue marked as paid and free bookings), 'unpaid' (nothing, a partial amount or a credited-back payment is registered; a desk payment never marked as paid stays unpaid), 'not_applicable' (any other booking type: subscription, blocked time, training, competition, activity and match slots). As of the latest sync, so a booking later marked as paid moves from 'unpaid' to 'paid'.
booking_client_id TEXT OAuth client identifier of the integration that created the booking. NULL = booked via the matchi.com marketplace (or through the facility admin UI). 'matchi-mobileapp' = booked via the Matchi mobile app. Any other value = booked via an external integration or the venue's own booking widget. Useful for separating widget bookings from marketplace bookings when calculating occupancy, attribution, or commission.
activity_occasion_id INTEGER (FK to activity occasion)
latest_sync TIMESTAMP_LTZ When this data was last synchronized from the source system.

customer_participation_log_v1

Fact table tracking customer participation across all venue activities. Each row represents a single participation event, including bookings, activities, and courses.

This table provides a comprehensive view of customer engagement with venue services, combining data from multiple sources:

Key characteristics:

Use cases:

Changelog:

📋 Table Structure

Column Type Description
date TIMESTAMP_NTZ The date and time when the participation event occurred.
order_date_created TIMESTAMP_NTZ The timestamp when the order was created.
sport_name TEXT The sport associated with the participation event.
court_location TEXT The location of the court (indoor/outdoor).
customer_id INTEGER (FK to customer) The unique identifier for the customer.
age INTEGER The age of the customer at the time of participation.
age_group TEXT The customer's age band AT THE TIME OF PARTICIPATION, bucketed from age. Point-in-time: a 17-year-old playing in 2025 stays in 13-17 for those rows even after turning 18. Use this to follow e.g. junior participation over time. Age bands: 0-12, 13-17, 18-24, 25-34, 35-44, 45-54, 55-64, 65+. The youth split follows the GDPR digital-consent age (13) and legal majority (18); adult bands follow Eurostat broad age groups. NULL when no birth date is known or the implied age is outside 0-105 (sentinel or mistyped birth dates); the age column is not clamped the same way, so a row can carry an implausible age with a NULL age_group. Coverage note: birth date has been mandatory in the MATCHi app since spring 2026, so coverage is close to complete for customers active since then and lower for older, dormant customers. The birth date is taken from the MATCHi user profile, falling back to the venue-entered date of birth on the customer record (e.g. juniors registered by the venue without their own account). Booking participations require a MATCHi account, so the venue-entered fallback only applies to activity and course participations.
article TEXT The type of participation ('BOOKING_OWNER', 'BOOKING_PARTICIPANT', 'ACTIVITY', 'COURSE', etc.).
session_type TEXT The type of session (private, public, etc.).
booking_id INTEGER (FK to booking) The booking identifier, if applicable.
activity_occasion_id INTEGER (FK to activity occasion) The activity occasion identifier, if applicable.
facility_id INTEGER (FK to facility) The facility where the participation occurred.
customer_participation_number INTEGER Sequential number of participation events for this customer.
latest_paid_membership INTEGER (FK to membership) The latest membership that was active at the time of participation.
member_at_order_date BOOLEAN Whether the customer had an active membership at the time of order creation.
customer_category_name TEXT The customer category applied by the pricing engine for the underlying order (e.g. "Standard", "Adult", "Junior", "Senior"). Reflects which category was used to determine the price, not what the customer selected at checkout. Available on BOOKING participations from 2026-03-05 onwards; NULL for older rows and for non-BOOKING participations.
membership_type_name TEXT The name of the membership type applied to the underlying order (e.g. "Premium", "Senior", "Game4Padel Membership"). Indicates which membership granted the price/access used. Available on BOOKING and ACTIVITY participations from ~2025-03 onwards; NULL otherwise.
payment_status TEXT Whether the order behind the participation is paid, by the same rule the venue admin uses: 'paid' (the payments registered in MATCHi cover the price, including orders the venue marked as paid and free orders), 'unpaid' (nothing, a partial amount or a credited-back payment is registered), 'not_applicable' (SUBSCRIPTION_BOOKING and COURSE rows, and sign-ups without an order). BOOKING_OWNER and BOOKING_PARTICIPANT rows reflect the whole court booking (same rule and BOOKING gate as booking_v1), so a participant who paid their share reads 'unpaid' if the booking as a whole is not. ACTIVITY and MATCH rows carry the participant's own order. As of the latest sync. Cancelled and annulled orders are not in this view.

facility_home_vs_visitors_v1

Aggregated data showing home versus visiting player activity for each facility by month.

This table analyzes player engagement patterns by classifying players as either 'home' or 'visiting' based on their main facility. A player's main facility is determined using a rolling 12-month window of activity.

Key metrics:

Use cases:

Note: A player is classified as 'home_player' when they are at their main facility, and 'visiting_player' when at any other facility.

Changelog:

📋 Table Structure

Column Type Description
facility_id INTEGER (FK to facility) The facility where the activity occurred.
month_start DATE The first day of the month for this aggregation.
player_relation TEXT Classification of the player: 'home_player' (playing at their main facility) or 'visiting_player' (playing at a different facility).
bookings_and_activities INTEGER Total count of bookings and activities for this facility-month-player_relation combination.
money_spent NUMBER(19,2) Total amount spent in local currency for this facility-month-player_relation combination.
latest_sync TIMESTAMP_LTZ When this data was last synchronized.

membership_history_v1

Daily snapshot of membership status over time, designed for time-series analysis, for example in Steep metrics.

This table provides one row per customer per membership type per day, tracking how memberships evolve over their lifecycle. It identifies first and last dates of membership periods using LAG/LEAD window functions.

Key characteristics:

The state column indicates the position in a membership period:

Changelog:

📋 Table Structure

Column Type Description
date_actual DATE The calendar date for this membership snapshot.
membership_id INTEGER (FK to membership) The unique identifier for the membership.
facility_id INTEGER (FK to facility) The facility where the membership is valid.
customer_id INTEGER (FK to customer) The customer that holds the membership.
type_id INTEGER (FK to type) The membership type identifier.
state TEXT Position in membership period: 'First_date_as_member', 'Member', or 'Last_date_as_member'.
first_membership_start_date DATE The start date of the customer's first membership within the shared facility scope, computed over the full membership history. Constant per customer. Intended as cohort anchor for member retention analysis: count distinct customers cohorted by this date shows how many members from each starting period still hold an active membership N periods later, independent of renewals creating new membership rows.

membership_v1

Fact table tracking membership transactions and their current status. Each row represents a single membership record, whether active, inactive, or in grace period.

Key characteristics:

Changelog:

📋 Table Structure

Column Type Description
membership_id INTEGER (PK) The unique identifier for the membership.
customer_id INTEGER (FK to customer) The customer that the membership belongs to.
facility_id INTEGER (FK to facility) The facility where the memberships is valid
purchased_at_facility_id INTEGER (FK to facility) The facility that the membership was purchased at.
purchased_at_facility TEXT The name of the facility where the membership was purchased. For group member customers, this may differ from the facility where the membership is valid.
state TEXT grace_period, membership, inactive
type_id INTEGER The membership type identifier. All memberships of the same type share this id and type_name.
type_name TEXT The name of the memberhip given by the venue
category TEXT family, single
start_date DATE The first date the membership is valid.
end_date DATE The last date the membership is valid, before any grace period.
grace_period_end_date DATE The last date of membership privileges.
payment_date DATE The date the membership was paid. NULL if no payment has been recorded (e.g. free or unpaid memberships).
is_active BOOLEAN true if the membership is currently active
is_final_paid BOOLEAN true if the membership is fully paid
payment_type TEXT a list of how the membership was paid for (cash, adyen, invoice, ...)
locality TEXT local (valid at a single facility) or group (valid across the facility group).
family_contact_customer_id INTEGER (FK to customer) The customer that is the contact for the family membership.
contact_email TEXT The email address of the membership contact. For family memberships this is the family contact, otherwise the customer's own email.
order_id INTEGER The order through which the membership was purchased.
order_status TEXT COMPLETED, ANNULLED, CANCELLED
actual_price number(38,6) The true price of the membership for family memberships. Family contact has a non-zero price.
latest_sync TIMESTAMP_LTZ When this data was last synchronized from the source system.

revenue_features_v1

One row per order_id with enrichment features for revenue data. Designed to be extensible — any order-level attribute can be added here. Currently contains membership dimensions (category, locality). When an order has multiple memberships, the most common value is selected (mode).

📋 Table Structure

Column Type Description
order_id INTEGER (PK) The order identifier.
facility_id INTEGER (FK to facility) The facility associated with the membership order.
category TEXT Membership category (e.g. 'Family', 'Single'). Mode value when multiple memberships exist on the same order.
locality TEXT Membership locality (e.g. 'group', 'local'). Mode value when multiple memberships exist on the same order.

revenue_v1

The revenue facts table shows all revenue transactions for each venue. Note that a single order can be paid in multiple transactions, resulting in multiple rows with the same order_id but different order_payment_id. The combination of order_id and order_payment_id is unique.

Orders with payment_type = cash have been marked as 'paid' by the venue in the Matchi administration interface. In practice, these may have been paid using any method; it simply means the payment did not go through Matchi. The invoice_date column indicates when the revenue is attributed to the venue, typically the latest of the payment date and delivery date.

Changelog:

📋 Table Structure

Column Type Description
facility_id INTEGER (FK to facility) The facility that the revenue belongs to.
customer_id INTEGER (FK to customer) The customer that generated revenue.
court_id INTEGER (FK to court) The court that the revenue belongs to.
activity_occasion_id INTEGER (FK to activity occasion) The activity occasion that the revenue belongs to.
booking_id INTEGER (FK to booking) The booking that the revenue belongs to.
order_id INTEGER The order that the revenue belongs to.
order_payment_id INTEGER An order can have multiple payments.
article TEXT ('BOOKING', 'ACTIVITY', 'COUPON', 'MEMBERSHIP',...)
num INTEGER The number of items sold.
num_credited INTEGER The number of items credited.
num_annulled INTEGER The number of items annulled.
price NUMBER(19,2) The price of the item including VAT.
amount NUMBER(19,2) The amount paid for the item including VAT.
vat_pct NUMBER(19,2) The VAT (Value Added Tax) percentage. Controlled by the venue.
latest_paid_membership INTEGER (FK to membership) The latest membership that was active at the time of the order.
customer_category_name TEXT The customer category applied by the pricing engine for this order (e.g. "Standard", "Adult", "Junior", "Senior"). Reflects which category was actually used to determine the price, not what the customer selected at checkout. Available on BOOKING orders from 2026-03-05 onwards; NULL for older orders and for non-BOOKING articles.
membership_type_name TEXT The name of the membership type applied to the order (e.g. "Premium", "Senior", "Game4Padel Membership"). Indicates which membership granted the price/access used for this order. Available on MEMBERSHIP orders historically; on BOOKING and ACTIVITY orders from ~2025-03 onwards; NULL otherwise.
sum_promo_code NUMBER(19,2) The sum paid with the promo code including VAT.
promo_code TEXT The promo code that was used.
coupon_id INTEGER (FK to coupon) The coupon that was used.
currency TEXT SEK, NOK, DKK, EUR, GBP etc
order_date TIMESTAMP_NTZ The timestamp when the order was created. Local time.
invoice_date DATE The date when the revenue is attributed to the venue.
date_delivery TIMESTAMP_NTZ The date the purchased service was delivered, e.g. the date the booking was played.
payment_type TEXT How the order was paid: netaxept/adyen (online card and wallet payments), coupon (punch card), gift_card or cash.
revenue NUMBER(19,2) Venue revenue in local currency including VAT.
vat_amount NUMBER(19,2) The VAT portion of revenue in local currency, derived from vat_pct. Defined so that revenue - vat_amount equals revenue excluding VAT exactly per row. Rows where vat_pct is NULL are treated as 0% VAT. Accuracy depends on the vat_pct configured by the venue for each article, and the rate applies at order level. Not a substitute for VAT reporting; invoiced VAT actuals are in statement_data.
transaction_fee NUMBER(19,2) Matchi's transaction fee for the payment in local currency, excluding VAT. Calculated to correspond to the fee lines on the venue's monthly statement: includes gift card and punch card fees (usage and admin-created sales), and is zero for credited orders since those fees are withheld from the customer refund rather than billed. Invoiced actuals are in statement_data.
latest_sync TIMESTAMP_LTZ When this data was last synchronized from the source system.

statement_data_v1

Combined statement data including both fees charged to customers and revenue paid out to venues. Each row represents a single invoice line item from either receivable or payable invoices.

This table combines data from two sources:

Key characteristics:

Use cases:

Changelog:

📋 Table Structure

Column Type Description
facility_id TEXT (FK to facility) The facility that the statement data belongs to.
statementid TEXT The statement identifier grouping related invoice lines.
transaction_type TEXT 'fees' (charged to customers) or 'revenue' (paid to venues)
invoiceno TEXT The invoice number from Sage system.
invoicerowno NUMBER(38,0) The line number within the invoice.
currency TEXT The currency of the amounts (SEK, EUR, NOK, DKK, etc.)
article_nr NUMBER(38,0) The Sage article number/product code.
productdescr TEXT The description of the product/service.
quantity NUMBER(38,6) The quantity of items invoiced.
invoicedexvat DECIMAL(38,6) The invoiced amount excluding VAT. Negative for revenue (outgoing payments).
vatamount DECIMAL(38,6) The VAT amount. Negative for revenue (outgoing payments).
invoicedate TIMESTAMP_TZ The date of the invoice.
latest_sync TIMESTAMP_LTZ When this data was last synchronized from the source system.

Helper Tables

Helper tables provide additional functionality for data analysis and reporting.

date_spine

Helper table for date dimension. Can be used to join with other tables to get date dimension.

📋 Table Structure

Column Type Description
date_actual DATE Format 'YYYY-MM-DD'
day_name TEXT Mon, Tue, Wed, ...
month_actual INTEGER 1,2,3,...,12
year_actual INTEGER 2020, 2021, 2022, ...
quarter_actual INTEGER 1,2,3,4
day_of_week INTEGER 1,2,3,...,7
month_name TEXT January, February, March, ...

monthly_coupon_value_v1

Helper table providing monthly snapshots of coupon values and status. One row per coupon per month, showing the value and status of all coupons at the end of each month.

Key information:

Note: Join to coupon table to get the coupon details.

Suggested metric: Total gift card value = sum(remaining_amount) Suggested metric: Total remaining tickets = sum(remaining_nr_of_tickets)

📋 Table Structure

Column Type Description
coupon_id INTEGER (FK to coupon) The coupon that the value belongs to.
date DATE The date of the value.
facility_id INTEGER (FK to facility) The facility that the coupon belongs to.
nr_of_active_coupons INTEGER The number of coupons that are active at the end of the month.
nr_of_deleted_coupons INTEGER The number of coupons that are deleted at the end of the month.
nr_of_expired_coupons INTEGER The number of coupons that are expired at the end of the month.
nr_of_locked_coupons INTEGER The number of coupons that are locked at the end of the month.
remaining_amount NUMBER(19,6) The remaining amount of active gift cards.
remaining_nr_of_tickets INTEGER The remaining number of tickets for active coupons.
tot_nr_of_coupons INTEGER The total number of coupons of this kind.
latest_sync TIMESTAMP_LTZ When this data was last synchronized from the source system.

customer_features_v1

Helper table providing pre-calculated customer behavioral features and preferences. One row per customer.

This table contains derived metrics and features calculated from customer interaction history, designed to simplify customer analysis and reporting. It includes pre-computed metrics like preferred sports, activity patterns, and membership status.

Key features:

Note: The features are updated daily to reflect the latest customer behavior patterns.

Changelog:

📋 Table Structure

Column Type Description
customer_id INTEGER (PK) The unique identifier for the customer.
facility_id INTEGER (FK to facility)
main_sport TEXT Based on the customer's prior bookings.
first_interaction TIMESTAMP_NTZ The first time the customer made or joined a booking/actiivty/course.
latest_interaction TIMESTAMP_NTZ The latest time the customer made or joined a booking/actiivty/course.
current_inactive_days INTEGER The number of days the customer is inactive.
current_inactive_days_grp TEXT Groupinb based on the customer's inactive days.
current_membership INTEGER The current membership id of the customer.
facility_user BOOLEAN True if the customer is an administrator at the facility.
favorite_playing_time TEXT The customer's preferred playing time slot.
participations_per_week_r12 NUMBER(19,2) Average number of participations per week over the last 12 months.
player_segment TEXT The player segment classification for this customer.
first_article TEXT The first article type purchased by the customer.
age_group TEXT The customer's CURRENT age band, computed from their birth date as of today (it shifts as customers age). For age at the time of play use customer_participation_log_v1.age_group instead. Age bands: 0-12, 13-17, 18-24, 25-34, 35-44, 45-54, 55-64, 65+. The youth split follows the GDPR digital-consent age (13) and legal majority (18); adult bands follow Eurostat broad age groups. NULL when no birth date is known or the implied age is outside 0-105 (sentinel or mistyped birth dates); the age column is not clamped the same way, so a row can carry an implausible age with a NULL age_group. Coverage note: birth date has been mandatory in the MATCHi app since spring 2026, so coverage is close to complete for customers active since then and lower for older, dormant customers. The birth date is taken from the MATCHi user profile, falling back to the venue-entered date of birth on the customer record (e.g. juniors registered by the venue without their own account).
latest_sync TIMESTAMP_LTZ When this data was last synchronized from the source system.

schedule_v1

Helper table providing pre-aggregated court occupancy metrics across different time dimensions. This table simplifies the calculation of court utilization by providing pre-calculated hours for each time slot.

The data is aggregated across multiple dimensions:

Time slots are grouped into larger buckets (e.g., 'Evening' for 16:00-22:00). Each slot has two key metrics:

Note: Courts classified as 'Facility resource' (e.g., doors, office, ball machines) are excluded from this view.

Note: Slots on archived courts are excluded from their archival date onwards, so archived courts do not contribute bookable hours. History from before the archival date is kept.

Note: Slots on days when the facility was inactive are excluded, so deactivated venues do not contribute bookable hours. History from when the facility was active is kept.

Note: This view is not limited in time — it exposes full slot history back to 2012. No external share view carries a time limit.

To calculate occupancy (percentage of time courts are booked):

  1. Group by your desired dimensions (e.g., facility, date, time slot)
  2. Sum the played_hours and bookable_hours
  3. Divide: sum(played_hours) / sum(bookable_hours)

Suggested metric: Occupancy = sum(played_hours) / sum(bookable_hours)

Changelog:

📋 Table Structure

Column Type Description
facility_id INTEGER (FK to facility)
court_id INTEGER (FK to court)
activity_occasion_id INTEGER (FK to activity occasion)
sport_name TEXT The sport of the court (e.g. Padel, Tennis).
date DATE The date of the schedule slot.
booking_group_type TEXT The state of the slot: UNBOOKED, UNAVAILABLE or the type of booking that occupies it (BOOKING, SUBSCRIPTION, TRAINING, ACTIVITY, COMPETITION, MATCH, ...).
booked_by TEXT Who made the booking: PLAYER (booked online by the player) or FACILITY (booked by venue staff). NULL for unbooked slots.
day_name TEXT Mon, Tue, Wed, ...
day_name_sort TEXT 1. Mon, 2. Tue, 3. Wed, ...
weekend BOOLEAN true (Sat-Sun), false (Mon-Fri)
time_slot TEXT Night, Morning, Pre-lunch, Lunch, Afternoon, Evening, Pre-night
time_slot_description TEXT Night (00:00 - 06:00), Morning (06:00 - 09:00),...
bookable_hours NUMBER(19,2) Number of hours that are available for booking.
played_hours NUMBER(19,2) Number of hours that have been booked
latest_sync TIMESTAMP_LTZ When this data was last synchronized from the source system.