Initial Configuration Guidelines

As part of the initial implementation of ACME at NHMLAC, the ticketing team reviewed the then-current configuration and business rules to guide the setup of the new ticketing software. All decisions related to ACME’s implementation are documented here.

Translating Galaxy Configuration to ACME

Galaxy is ticket first (most things depend on the ticket configuration) while ACME is event first (everything relies on the event configuration). During the implementation of ACME, the ticketing team wanted to ensure the following (at a minimum):

1. All features that current Galaxy users liked were maintained to the best of our ability in ACME
2. The overall configuration was simplified
3. All critical data points were maintained.
4. The initial configuration was scalable and flexible enough for any new future event types and constituant groups.

The ticketing team pulled all event types, ticket types, price variations, discounts/promotions, resources, custom fields and rosters that were in Galaxy to see how best to restructure the new system. In this document and the Business Rules documen, we will detail each of our configuration decisions.

Initial Data Import

In order to better map customer data across the two systems (Galaxy and ACME), we decided to preload Member, Organization/Group and Nonmember records into ACME. Below are the time frames used for each data set:

• Members - 7/1/2017 to 9/7/2022
• Organizations/Groups - 7/1/2017 to 9/7/2022
• Nonmembers - 7/1/2019 to 9/7/2022

For all constituant groups, we choose these dates to ensure we had information that wasn’t too outdated, but could still ensure we avoided duplicated customer records to better track historical purchases and renewals.

Once the data lists were pull (please see below for the sql queries), there was some manual data cleanup needed.

We followed this heirarchy of customers to decide which list a customer record should show up on if they were on multiple: Members, Organization Customers, Nonmembers. If someone showed up on both the members and nonmembers list, they would be deleted from the nonmembers list.

Members

1. Matched old membership levels to relaunched membership levels
2. Manually inputted “Lapsed” and “Active” for Status to match ACME’s designation

Organizations

1. Matched organizational category to the list we decided to move forward with in ACME
2. For the contacts on these organizations, deleted any records with no email then cleared out the address if its was 900 Exposition
3. Checked phone number for correct formatting
4. We removed 1214 Organizations that did not have a contact (primary contact required to import into ACME). We had an additional 79 schools and 78 community organizations that we asked the respective teams to review.

Nonmembers

1. Instead of pulling from Galaxy, we decided to pull this information directly from Microsoft Dynamics to ensure the customer contact ID matched with what Dynamics had after the de-dupe.
2. Using the Active Contacts view, I added the additional columns needed (contact ID and Rollup Last Purchase Date) and filtered based on the Rollup Last Purchase Date.
3. Once I had the list for the appropriate time period, I removed any records with no email.
4. I also removed any records with an email from an OTA (since that is not the actual contact’s email).
5. Lastly, I removed any phone numbers or addresses that were not in the correct format or did not have enough information (i.e. for phone numbers less than 10 digits, for addresses, just a number).
6. The MSD list will have duplicate emails but we left those as-is.

SQL Queries for Data lists

Memberships

Select Passacct as ‘Membership ID’, Passno as ‘Membership Import ID’, Convert(Date, DateOpened) as ‘Date Joined’, Status as ‘Standing’, Passkinds.Name as ‘Category’, ‘Individual’ as ‘Record Type’, Convert(Date, Passes.ValidUntil) as ‘Membership Expiration Date’, ContactID as ‘Customer ID’, ContactID as ‘Customer Import ID’
From Passes
INNER JOIN PassKinds on Passes.Kind = PassKinds.ID
Where Convert(Date, Passes.ValidUntil) >= ‘7/1/2017’ and passes.status = 0

Joint Members

Select cc.CustContactID as ‘Customer ID’, cc.CustContactID as ‘Customer Import ID’, ‘Yes’ as ‘Primary 01’, MAX(LTRIM(RTRIM(cc.FirstName))) AS ‘Primary First Name’, MAX(LTRIM(RTRIM(cc.LastName))) AS ‘Primary Last Name’, ‘No’ as ‘Primary 02’, MAX(LTRIM(RTRIM(cc1.FirstName))) AS ‘Joint Member First Name’, MAX(LTRIM(RTRIM(cc1.LastName))) AS ‘Joint Member Last Name’, p.PassNo as ‘Membership Import ID’, Convert(Date, p.ValidFrom) as ‘Membership Card Valid From Date’, Convert(Date, p.ValidUntil) AS ‘Membership Card Valid To Date’
From Passes as p (nolock)
LEFT OUTER JOIN (Select MAX(PassNo) as ‘PassNo’, Passacct from Passes Group By Passacct) p2 on p2.Passacct = p.Passacct and p2.Passno = p.Passno
INNER JOIN CustContacts cc (nolock) on p.ContactID = cc.CustContactID
LEFT OUTER JOIN JointMembers jm (nolock) on p.PassNo = jm.PassNo and p.ContactID <> jm.ContactID
LEFT OUTER JOIN CustContacts cc1 (nolock) on jm.ContactID = cc1.CustContactID and cc1.AgeGroup in (0)
Where Convert(Date, p.ValidUntil) >= ‘7/1/2017’and p.Status = 0
Group By Convert(Date, p.ValidUntil), cc.Email, p.Passno, cc.CustContactID, Convert(Date,p.ValidFrom)
Order By [Primary First Name]

Customers for Memberships

Select ContactID as ‘Customer ID’, ContactID as ‘Customer Import ID’, cc.FirstName as ‘First Name’, cc.LastName as ‘Last Name’, cc.MiddleName as ‘Middle Name’, Passes.Phone as ‘Phone Number’, Passes.Email as ‘Email’, Addresses.AddressID as ‘Address Import ID’, Addresses.Street1 as ‘Address Line 1’, Addresses.Street2 as ‘Address Line 2’, Addresses.State as ‘Address State’, Addresses.Postal as ‘Address ZIP’, Addresses.CountryCode as ‘Address Country’
From Passes
INNER JOIN CustContacts cc on Passes.ContactID = cc.CustContactID
INNER JOIN Addresses on cc.AddressID = Addresses.addressID
Where Convert(Date, Passes.ValidUntil) >= ‘7/1/2017’ and Passes.Status = 0

Organizations

Select Customers.CustomerID as ‘Org ID’, Customers.CustomerID as ‘Org Import ID’, CategoryID, CustName as ‘Organization’, CustCategories.Description as ‘Cateories’, Addresses.Street1 as ‘Address Line 1’, Addresses.Street2 as ‘Address Line 2’, Addresses.State as ‘Address State’, Addresses.Postal as ‘Address ZIP’, Addresses.CountryCode as ‘Address Country’, Phone, Email
From Customers
INNER JOIN CustCategories on Customers.CategoryID = CustCategories.CustcategoryID
INNER JOIN Addresses on Customers.AddressID = Addresses.AddressID
INNER JOIN Orders on Customers.CustomerID = Orders.CustomerID
Where CategoryID in (26,3,4,6,7,8,10,11,12,13,14,15, 16, 17, 18, 20,48,53,54,55,119,144,154,155,156,163,89,111,28,29) and Convert(Date, Orders.OpenDate) >= ‘7/1/2017’
Group By Customers.CustomerID, CategoryID, CustName, CustCategories.Description, Addresses.Street1, Addresses.Street2, Addresses.State, Addresses.Postal, Addresses.CountryCode, Phone, Email

Customers for Organizations

Select Customers.CustomerID as ‘Org ID’, cc.CustContactID as ‘Customer ID’, cc.CustContactID as ‘Customer Import ID’, cc.FirstName as ‘First Name’, cc.LastName as ‘Last Name’, cc.MiddleName as ‘Middle Name’, cc.Phone as ‘Phone Number’, cc.Email as ‘Email’, Addresses.AddressID as ‘Address Import ID’, Addresses.Street1 as ‘Address Line 1’, Addresses.Street2 as ‘Address Line 2’, Addresses.State as ‘Address State’, Addresses.Postal as ‘Address ZIP’, Addresses.CountryCode as ‘Address Country’, cc.PrimaryContact as ‘Primary’
From Customers
INNER JOIN CustCategories on Customers.CategoryID = CustCategories.CustCategoryID
INNER JOIN ContactConnections on Customers.CustomerID = ContactConnections.ConnectionID
INNER JOIN CustContacts cc on ContactConnections.ContactID = cc.CustContactID
INNER JOIN Addresses on cc.AddressID = Addresses.AddressID
INNER JOIN Orders on Customers.CustomerID = Orders.CustomerID
Where CategoryID in (26,3,4,6,7,8,10,11,12,13,14,15, 16, 17, 18, 20,48,53,54,55,119,144,154,155,156,163,89,111,28,29) and Convert(Date, Orders.OpenDate) >= ‘7/1/2017’
Group By cc.PrimaryContact, Customers.CustomerID, cc.CustContactID, cc.FirstName, cc.LastName, cc.MiddleName, cc.Phone, cc.Email, Addresses.AddressID, Addresses.Street1, Addresses.Street2, Addresses.State, Addresses.Postal, Addresses.CountryCode

Nonmember Customers

Select MAX(ccid) as ‘Customer ID’, FirstName, LastName, Phone, Email, cc.AddressID as ‘Address Import ID’, Addresses.Street1 as ‘Address Line 1’, Addresses.Street2 as ‘Address Line 2’, Addresses.State as ‘Address State’, Addresses.Postal as ‘Address ZIP’, Addresses.CountryCode as ‘Address Country’, rownum
From (Select row_number() over(partition by FirstName, LastName, Email Order By MAX(CustContactID) DESC) as ‘rownum’, FirstName, LastName, MAX(CustContactID) as ccid, Phone, Email, AddressID, ContactType From CustContacts cc Group By FirstName, LastName, Email, Phone, AddressID, ContactType) cc
INNER JOIN Orders on cc.ccid = Orders.ContactID
INNER JOIN Addresses on cc.AddressID = Addresses.AddressID
Where Convert(Date,Orders.OpenDate) >= ‘7/1/2019’ and cc.ContactType not in (7,8) and rownum = 1
Group By fFirstName, LastName, Email, Phone, Addresses.Street1, Addresses.Street2, Addresses.State, Addresses.Postal, Addresses.CountryCode, rownum, cc.AddressID

Data in ACME vs. Data in CRM

With the parallel implementation of the musuem’s CRM, we took this opportunity to clearly line out what data belonged in the ticketing system and what data belonged in the CRM. With ACME being a transactional system, this would ensure we don’t use fields that were made for another purpose to fit our reporting needs.

Going forward, ACME will be the system of record for the following:

• Event information (i.e. Capacity, Dates, Schedules)
• Number of Tickets Sold
• Number of Tickets Used
• Number of Members and their basic membership information (for transactional and deduping purposed)
• Membership Lifecycle Actions
• Order information (number of tickets, ticket types, event types)
• Any information collected on forms built in ACME and attached to an ACME event
• Financial transactions relating to tickets, memberships, and annual fund donations

Financial Discussions

In Galaxy, the two main ways Finance pulled data was through the GL String (Chart of Accounts in Galaxy) or by using the Reporting Groups (Members, Paid, Unpaid, Schools, Events and Others, Offsite and Non Attendance). Finance indicated that those reporting groups still worked for their purposes.

A big change between the two systems is that Galaxy is access code/ticket first while ACME is event first. To account for this, we decided the following:

1. We would utilize the custom field on the events to categorize the event into one of the following categories
   1. General Admission
   2. School General Admission
   3. Event
   4. Non-Attendnace
   5. School Non-Attendance
   6. Offside
   7. Voucher
2. To get further information about the attendee, we would look at ticket type, discounts, price, event name or if it was transacted under a membership.
3. For GL Strings, the ticketing team worked with Finance to remove any old GL Strings and map the remaining to each event type. For any ticket or event that has deferred revenue, Finance is aware that that will have to tracked in their accounting system.
4. For memberships, we cannot add a GL String so we will report based on level and sales location.

Data Collection and Report Building in ACME

ACME will collect any and all data related to ticketing, membership and donation transactions, in addition to any programatic information that is linked to a ticket. All other information will be collected and stored in a different platform.

ACME’s native report building allows reports on all transactional and customer data collected in ACME in one of the following streams: Membership, Transactions, Sale, Events, Donations, TicketAnalytics, OrderRequests, PassAnalytics, and Forms.

Data from each stream cannot be combined in the same report in ACME’s backend reporting. All data can be exported out of ACME to report on further.

All requests to edit or build a new report will go through the ticketing team.

Timed vs. Untimed Tickets

Prior to reopening after the closure in 2021, NHMLAC general admission tickets were “untimed” - meaning the customer could buy a ticket and use that ticket to come in any date/time. For better ticketing and financial* reporting, NHMLAC will not longer have tickets that are not tied directly to an event date.

Complimentary, VIP, and donation request tickets that most commonly used the untimed model will now either be issued as a voucher (that has to be redeemed onsite) or be asked to pick an attendance date. Tickets can easily be rebooked into different event time slots.

*Finance needs to know the number to valid, unredeemed tickets at the end of the year. If tickets are not attached to an event, this number is hard to report.

Mapping Galaxy ticket reporting groups to ACME event reporting groups

Constituant Groups

At the time of launch, these were the following constituant groups accounted for:

• General Public (LA County Resident, Military, EBT, Teacher, nonmember, etc.)
• Individual Members
• Corporate Partners
• Community Partners
• School Groups (Field Trips, Mobile Musuems, Offsite Programs)
• VIPs/Business Guests
• General Public Group
• Tour Group
• OTA

Ticket types, discounts/coupons, events and purchase paths were created for each group (some will overlap) and will be detailed in the Business Rules document.

The following are the organizational categories (created to be able to pull attendance and revenue by various groups and to be able to apply organizational discounts if needed):

• Public School
• Private School
• Home School
• Charter School
• Summer Programs/Summer School
• School Affiliated Club
• Early Childhood Centers
• Adult Education
• Community Groups
  • Community based organizations
• Non-Profit
  • Certified 501(c)(3) organization
• Paid Groups
  • Private groups, Tour groups, OTAs
• Corporation
  • For Profit Corporations

We will collect further information on schools during their registration process.

If for any reason a customer does not really fit into any of these constituant groups or organizational categories:

• If it is a one-off and we will not be building a long term relationship with that customer/organization, fit them into the next-best category.
• If we will be building a long term relationship with the customer/organization, discuss with the department that is bringing on the group to see how they would like to track and report them.

Galaxy Tickets post ACME launch

Tickets will only be sold in Galaxy until September 5, 2022. If a customer comes in with a Galaxy ticket* after the ACME launch, they will be rebooked with a special discount code (GALAXY-REBOOK) until the end of 2022.

For any old gift memberships and membership groupons, (pending discussion)

*This will include tickets that were purchased for a pre 7/5/22 date, untimed comped tickets, or membership guest passes