Overview
Ticketing vendors maintain enormous amounts of data, and DWA integrations copy that data into the warehouse essentially as-is. Vendors' tables are designed for their software's needs, not those of clients and business intelligence analysts. KORE's ticketing data model provides a consistent and more user-friendly way to view data independently of which vendor provides it. This also bridges the gap if your organization changes ticketing vendors—the ticketing data model allows you to unite current and historical data from different vendors.
This model first organizes data into several fact and dimension tables within the dwa schema. KORE then provides several views (stored SQL queries which act like tables) which combine data from various fact and dimension tables. These views make it easier and more efficient for BI analysts to write SQL queries to answer complex business questions.
Like with our DWA integrations, you can connect to Amazon Redshift and query the database directly. We strongly recommend using views (not the tables) so you can take advantage of our transformations.
This model also uses data from Eloqua, if available.
Views and ERDs
Views are organized into three categories: fact (vwfact), dimension (vwdim), and lookup (vwlkup). In most cases, you will use a vwfact view to find data of interest. Many (but not all) sid fields used within the vwfact views are surrogate keys: generated primary keys with no inherent meaning outside of this schema. They may be used to obtain additional data from the vwdim views.
Note: Because this is a standardized model that supports several different vendors and use cases, it is normal for some views to contain no data.
Note: For clarity, some fields are omitted from these diagrams. Arrow colors have no meaning; they are only used to make the diagrams easier to follow.
vwfact_activity_transaction
This view lists email activities from Eloqua, if available. activitysid is a surrogate key.
vwfact_attendance_transaction
This view provides details attendance details for tickets used at an associated event. attendtransactionsid is a surrogate key.
vwfact_manifest_transaction
A manifest is the list of all seats available for sale at a given event, whether sold or not. This view provides the latest details for each seat at each event. ("Transaction" here means adding a seat to the manifest, not selling the seat to a customer.) If the seat was sold, pricing and attendance details are provided. manifesttransactionsid is a surrogate key.
vwfact_ticket_transaction
This view lists all the primary market transactions involving any ticket. A ticket typically only has one such transaction (the initial sale) but could have more (such as a refund followed by a sale to a different customer). tickettransactionsid is a surrogate key.
Some KORE clients may have additional views with a suffix which contain details specific to a non-Archtics ticketing system. For example, vwfact_ticket_transactione shows Veritix purchases expanded by price components, and vwfact_ticket_transactionfs shows all Flash Seats actions (purchases, postings, forwards, etc.).
vwfact_ticketexc_transaction
This view lists all the Ticket Exchange (secondary market) transactions involving any ticket. ticketexchangesid is a surrogate key.