Skip to main content

Archtics Realtime Attendance

Ashleigh Rentz
  • Updated

Note: The realtime integration is currently limited to clients in the US.

Overview

The Archtics Realtime Attendance integration supplements our main Archtics integration. It makes data from Ticketmaster available very shortly after a customer's ticket is scanned at the gate. We ingest new data every three minutes.

KORE collects data via this integration beginning five hours prior to the event’s start time. We continue collecting data until six hours after the event’s start time. Because more complete data is available through the standard Archtics integration after events, KORE only stores data from this integration for seven days.

To set up this integration, contact your KORE Success Manager—we will coordinate with Ticketmaster to enable the necessary endpoints. Also specify the event category(s) to monitor: due to resource requirements, Ticketmaster requests we only enable this for events where realtime data will be useful (such as sports games or concerts). Many clients have a variety of other events as well.

If you need to change the event categories in the future, this is controlled by a record in the korebi.birulesettings table. Use this SQL query to verify changes to the criteria before editing the setting:

SELECT event_name, event_date, event_time, event_day, team,
   major_category, minor_category, season_name
FROM archtics.event
WHERE enabled = 'Y' AND event_date >= current_date
   AND event_date <= current_date + 8
   AND /* INSERT CRITERIA HERE */ ;

Example criteria:

  • total_events = 1 AND major_category IN ('SPORTS','CONCERTS')
  • major_category = 'SPORTS' AND minor_category = 'NHL PRO HOCKEY'

(Since Ticketmaster removes data from this feed after one week, please don't alter the date range above in either your query or the setting.)

 

Limitations

Ticketmaster does not make all scan events available to this integration. DWA only receives initial entry scans (result_code = 0) at standard gate scanners (scan_type = 'S').

Because we cannot know with certainty who actually used a particular ticket, multiple tickets assigned to the same person will all be linked to that individual—not the people they provided their extra tickets to. This person may be:

  • the original purchaser,
  • the last person who purchased it on Ticket Exchange, or
  • the recipient of a ticket transfer.

 

Using the integration

Important: We recently released an update to this integration and are gradually migrating existing customers onto it. This page describes both versions. If you have a view in the archtics schema named attendance_realtime_v, you are on the new version.

 

New version

The new version of our integration can ingest data up to 10x faster by retrieving up to 10,000 fans per 3 minutes (instead of 1,000 previously).

Raw data from Archtics is stored in the archtics_[teamname]_rtx schema, which now supports organizations with more than one team or business unit. However, we do not recommend using those tables directly. Instead, use the attendance_realtime_v view under the archtics schema which combines the realtime data with pre-existing Archtics data. The view also transforms the timestamps into your preferred time zone.

Additionally, the korebi schema is no longer used with this integration except as described above for setting the desired event categories.

Data dictionary

Schema View Column Data type Ordinal position
archtics attendance_realtime_v archtics_event_name varchar(50) 1
archtics attendance_realtime_v section_name varchar(50) 2
archtics attendance_realtime_v row_name varchar(50) 3
archtics attendance_realtime_v gate varchar(50) 4
archtics attendance_realtime_v scan_type varchar(50) 5
archtics attendance_realtime_v seat_num int4 6
archtics attendance_realtime_v acct_id int4 7
archtics attendance_realtime_v event_time timestamp 8
archtics attendance_realtime_v ticket_scan_time timestamp 9
archtics attendance_realtime_v name_first varchar(100) 10
archtics attendance_realtime_v name_last varchar(250) 11
archtics attendance_realtime_v email_addr varchar(100) 12

 

Example queries

View the attendance for a particular section:

SELECT COUNT(*)
    FROM archtics.attendance_realtime_v
    WHERE section_name = '308';

 

See how many people arrived after the event start time:

SELECT COUNT(*)
    FROM archtics.attendance_realtime_v
    WHERE CAST(event_time AS date) = '2022-01-01'
    AND ticket_scan_time > event_time;

 

Determine if a particular ticket purchaser has arrived:

SELECT *
    FROM archtics.attendance_realtime_v
    WHERE email_addr = 'username@example.com'
    AND CAST(ticket_scan_time AS DATE) = CURRENT_DATE;

 

 

Old version

Due to the realtime nature of this integration, it may be difficult to formulate and test queries. KORE provides sample data to use for this purpose. The sample data is stored in the realtime_attendance_sample and realtime_attendance_report_sample tables.

Within DWA, Archtics Realtime Attendance data is stored in an external table. This is accessed through the archtics_rtx schema and can be queried through Redshift, but some software tools (such as Aginity) may not support viewing it in the object browser. If your preferred tool doesn't support this, KORE recommends DBeaver or Tableau Desktop. We also provide views in the korebi schema.

Since the data in this feed is transient, Ticketmaster does not provide a method to validate it.

Data dictionary

Live data:

Schema Table Column Data type Ordinal position
archtics_rtx attendance result_code varchar(50) 1
archtics_rtx attendance gate varchar(50) 2
archtics_rtx attendance scan_type varchar(50) 3
archtics_rtx attendance row_name varchar(50) 4
archtics_rtx attendance archtics_event_name varchar(50) 5
archtics_rtx attendance action_time varchar(50) 6
archtics_rtx attendance seat_num varchar(50) 7
archtics_rtx attendance section_name varchar(50) 8
archtics_rtx attendance channel_ind varchar(50) 9
archtics_rtx attendance attendance_seq_id varchar(50) 10
archtics_rtx attendance event_name varchar(50) 11
korebi dwa_archtics_realtime_attendance_view archtics_event_name varchar(50) 1
korebi dwa_archtics_realtime_attendance_view section_name varchar(50) 2
korebi dwa_archtics_realtime_attendance_view row_name varchar(50) 3
korebi dwa_archtics_realtime_attendance_view seat_num int4(32,0) 4
korebi dwa_archtics_realtime_attendance_view acct_id int4(32,0) 5
korebi dwa_archtics_realtime_attendance_view event_time timestamp 6
korebi dwa_archtics_realtime_attendance_view ticket_scan_time timestamp 7
korebi dwa_archtics_realtime_attendance_view name_first varchar(100) 8
korebi dwa_archtics_realtime_attendance_view name_last varchar(100) 9
korebi dwa_archtics_realtime_attendance_view email_addr varchar(300) 10

 

Sample data:

Schema Table Column Data type Ordinal position
korebi realtime_attendance_report_sample archtics_event_name varchar(50) 1
korebi realtime_attendance_report_sample section_name varchar(50) 2
korebi realtime_attendance_report_sample row_name varchar(50) 3
korebi realtime_attendance_report_sample seat_num int4(32,0) 4
korebi realtime_attendance_report_sample acct_id int4(32,0) 5
korebi realtime_attendance_report_sample event_time timestamp 6
korebi realtime_attendance_report_sample ticket_scan_time timestamp 7
korebi realtime_attendance_report_sample name_first varchar(100) 8
korebi realtime_attendance_report_sample name_last varchar(100) 9
korebi realtime_attendance_report_sample email_addr varchar(300) 10
korebi realtime_attendance_sample result_code varchar(50) 1
korebi realtime_attendance_sample gate varchar(50) 2
korebi realtime_attendance_sample scan_type varchar(50) 3
korebi realtime_attendance_sample row_name varchar(50) 4
korebi realtime_attendance_sample archtics_event_name varchar(50) 5
korebi realtime_attendance_sample action_time varchar(50) 6
korebi realtime_attendance_sample seat_num varchar(50) 7
korebi realtime_attendance_sample section_name varchar(50) 8
korebi realtime_attendance_sample channel_ind varchar(50) 9
korebi realtime_attendance_sample attendance_seq_id varchar(50) 10

 

Example queries

View the attendance for a particular section:

SELECT COUNT(*)
FROM archtics_rtx.dwa_archtics_realtime_attendance_view
WHERE section_name = '308';

 

See how many people arrived after the event start time:

SELECT COUNT(*)
FROM korebi.dwa_archtics_realtime_attendance_view
WHERE CAST(event_time AS date) = '2022-01-01'
  AND ticket_scan_time > event_time;

 

Determine if a particular ticket purchaser has arrived:

SELECT *
FROM korebi.dwa_archtics_realtime_attendance_view
WHERE email_addr = 'username@example.com'
  AND CAST(ticket_scan_time AS DATE) = CURRENT_DATE;