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). For performance reasons, fact tables contain data for the past 24 months, while dimension tables include all historical data.

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
customer_v1 count(customer_id) where archived = false and deleted is null
booking_v1 count(distinct booking_id) as total_number_of_bookings
booking_v1 sum(duration)/60 as total_played_hours
customer_participation_log_v1 count(distinct customer_id) as participating_customers
customer_participation_log_v1 count(*) as participations
facility_home_vs_visitors_v1 sum(bookings_and_activities) group by player_relation
facility_home_vs_visitors_v1 sum(money_spent) group by player_relation
membership_v1 count(distinct membership_id) as total_number_of_memberships
monthly_coupon_value_v1 sum(remaining_amount) as total_gift_card_value
monthly_coupon_value_v1 sum(remaining_nr_of_tickets) as total_remaining_tickets
revenue_v1 sum(revenue)
schedule_v1 sum(played_hours) / sum(bookable_hours) as occupancy
statement_data_v1 sum(invoicedexvat) group by type for net position

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
country TEXT Country where the facility is located.
currency TEXT
date_created DATE The date and time the facility was created.
facility_group_name TEXT
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
longitude FLOAT
municipality TEXT
region TEXT
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

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
address2 TEXT
archived BOOLEAN
cellphone TEXT
city TEXT
country TEXT
customer_id INTEGER (PK) The unique identifier for the customer.
customer_number INTEGER The venue specific identifier for the customer.
date_created TIMESTAMP_NTZ
email TEXT
facility_id INTEGER
firstname TEXT
fullname TEXT
gender TEXT
lastname TEXT
telephone TEXT
user_id INTEGER The unique identifier for the matchi user.
zipcode TEXT
latest_sync TIMESTAMP_LTZ

court_v1

Court properties. One row per court.

📋 Table Structure

Column Type Description
archived BOOLEAN
camera_equipped BOOLEAN
court_id INTEGER (PK) The unique identifier for the court.
court_location TEXT The location (indoor or outdoor) of the court.
court_name TEXT
court_type TEXT Double/Standard/Mini etc
date_created TIMESTAMP_NTZ
description TEXT
facility_id INTEGER (FK to facility) The facility that the court belongs to.
last_updated TIMESTAMP_NTZ
restriction TEXT Members only, offline only, etc
sport_name TEXT
surface TEXT Hard/Clay/Carpet/Lawn etc
latest_sync TIMESTAMP_LTZ

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
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
date_created TIMESTAMP_NTZ
description TEXT
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
vat_value NUMBER(38,6)
latest_sync TIMESTAMP_LTZ

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
start_time TIME
end_time TIME
time_slot TEXT
time_slot_desc TEXT
duration INTEGER
sport_name TEXT
name TEXT The name of activity. e.g. Volley training, Mexicano level 4-6,....
activity_visibility TEXT
activity_occasion_type TEXT TRAINING, COMPETITION,...
activity_event_name TEXT AMERICANO, BEAT_THE_BOX,...
external_service TEXT
level TEXT
price NUMBER(38,6)
active_members_price BOOLEAN If members get a discount on the activity.
members_price NUMBER(38,6)
min_num_participants INTEGER
max_num_participants INTEGER
players INTEGER
paying_players_through_matchi INTEGER
active_queue BOOLEAN If there are players in queue for the activity.
players_in_queue INTEGER
nr_of_courts INTEGER
deleted BOOLEAN
deleted_hours_before INTEGER
status TEXT Completed activity , Automatic cancellation , Manual cancellation
fill_rate TEXT Filled, No participants, Below min participants, Above min participants
latest_sync TIMESTAMP_LTZ

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.

📋 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
slot_end_time TIMESTAMP_NTZ
duration INTEGER
booking_group_type TEXT SUBSCRIPTION, ACTIVITY, BOOKING, TRAINING, COMPETITION, ...
booked_by TEXT FACILITY, PLAYER
latest_paid_membership INTEGER
time_slot TEXT
time_slot_description TEXT
day_name TEXT
day_name_sort TEXT
weekend BOOLEAN
session_type TEXT private, public
sport_name TEXT
mtv_stream BOOLEAN true if the booking is streamed on matchi tv
order_origin TEXT web, api (matchi app ar external api), facility
activity_occasion_id INTEGER (FK to activity occasion)
latest_sync TIMESTAMP_LTZ

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.
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) 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.

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 NUMBER 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_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
type_name TEXT The name of the memberhip given by the venue
category TEXT family, single
start_date DATE
end_date DATE
grace_period_end_date DATE The last date of membership privileges.
payment_date DATE
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
family_contact_customer_id INTEGER (FK to customer) The customer that is the contact for the family membership.
contact_email TEXT
order_id INTEGER
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

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) The latest membership that was active at the time of the order.
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 TIMESTAMP_NTZ The timestamp when the revenue is attributed to the venue.
date_delivery TIMESTAMP_NTZ
payment_type TEXT
revenue NUMBER(19,2) Venue revenue in local currency including VAT.
latest_sync TIMESTAMP_LTZ

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 The line number within the invoice.
currency TEXT The currency of the amounts (SEK, EUR, NOK, DKK, etc.)
article_nr NUMBER The Sage article number/product code.
productdescr TEXT The description of the product/service.
quantity NUMBER 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
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: sum(remaining_amount) as total_gift_card_value Suggested metric: sum(remaining_nr_of_tickets) as total_remaining_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

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.
latest_sync TIMESTAMP_LTZ

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.

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: sum(played_hours) / sum(bookable_hours) as occupancy

📋 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
date DATE
booking_group_type TEXT
booked_by TEXT
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