Archtics ticket coding ('korebi' schema)

Overview

Many organizations that use Archtics customize their price codes and/or event codes to represent a wide assortment of details. The structures of these codes are completely customizable and unique to each organization. For example, price code "S301" could represent reduced student pricing for a single-game ticket in section 3, while "S310" might represent season tickets for the same section at student pricing. Organizations typically have hundreds of price codes.

By sharing your structure with KORE, you can use the coded information within DWA. This data is used in three standard dashboards: primary ticket, secondary market, and broker analysis. You can also take advantage of this data in your own custom Tableau reports.

Ticket coding is stored in the korebi (KORE Business Intelligence) schema. This is a special schema within DWA—you will be able to insert and delete rows within a table so that you can keep the structure up to date, but you cannot create or drop tables within the schema.

Create the initial schema

Complete the workbook

Download Excel workbook

The workbook contains several worksheets (tabs). In the Q&A worksheet, answer the questions in the provided column. If instructed in the follow-up section, enter additional details at the bottom of the worksheet. Next, complete the tables on each of the other worksheets.

Typically, an organization's box office staff maintains the ticket coding structure and can help you complete the workbook.

lkup_tix_manifest worksheet

In Archtics, the manifest code represents a high-level group of ticket sales. Most organizations use this to group together all the ticket sales for one season. When an organization owns more than one team or uses more than one venue, they normally use a different manifest code for each unique combination of season, venue, and team:

lkup_tix_section_type worksheet

Complete one row for every range of seats in the venue. All seats within a range must share identical details (same reporting category, parent code, ADA/SRO flags, etc.) For example, suppose a section has accessible seating at the ends of each row:

(If you prefer, you may alternatively complete a table row for every individual seat in the venue.)

Submit to KORE

Provide these resources to your KORE Success Manager:

• The completed Excel workbook
• An Archtics Seats Sold report:
  • No exclusion filters
  • A completed, regular season game
  • From the current or most recent season
• A box office report for the same game showing ticket sales broken out by your organization's key ticket types (such as full season, partial season, individual, group, comp, etc…)

KORE will create the schema and tables using your completed workbook, then verify the data using the provided reports. Due to the amount of manual work involved, please allow us 2-3 weeks to complete this.

Update the data

During the initial setup, KORE will create several SQL queries used to write data into the tables. We will provide these queries to you after setup is complete. When your organization makes changes to ticket coding in the future, you can modify these queries and run them to update the tables.

For example, suppose that your box office adds a new price code category. You would take this SQL query provided by KORE, add the new category 'Supporters Section' to it, and run the query:

-- First, delete the old values:
DELETE korebi.lkup2_tix_pricecode_category;

-- Next, add all the values (new and old alike) to the empty table:
INSERT INTO korebi.lkup2_tix_pricecode_category
  (pricecode_categorysid, pricecode_category_desc)
  VALUES
    (1,'Suites'),
    (2,'Premium'),
    (3,'General Admission'),
    (4,'Supporters Section')  -- NEW CATEGORY
;

-- Finally, verify that the results match expectations:
SELECT TOP 100 * FROM korebi.lkup2_tix_pricecode_category;
SELECT COUNT(*) FROM korebi.lkup2_tix_pricecode_category;

The purpose of deleting existing data is to avoid duplicating records within the table. The query you are modifying is the same one KORE used to initially populate the table—since you're adding the same data back again (along with your updates), no information is lost.

Queries for the lkup tables are frequently more complex than those for the lkup2tables. A KORE representative will meet with you to review the SQL queries when we provide them to you.

Schema structure

The korebi schema has two main types of tables: lkup (look up) and lkup2. The main lkup tables contain various "code" fields that act as foreign keys to the corresponding lkup2 tables. The lkup2 tables contains text descriptions for many (but not all) of the codes. For example:

By using code fields in this manner, it is much easier and faster to change a code's description. It also helps avoid data entry deviations (like capitalization differences or typos) that could occur if the description were directly included in each row of a lkup table.

ERD and data dictionary

(For clarity, some column names are omitted from this ERD.)

Data dictionary

Table descriptions