/
Ticket Evolution Report

Ticket Evolution Report

Owned by Andrew Pierson (Unlicensed)

Requirements and Purpose

The Ticket Evolution Report is used for reconciliation of Ticket Evolution Core purchase orders in eiBox Office. The report consists of the TEVO transactions and an error report for purchase orders that do not map from eiBox Office to Ticket Evolution orders dashboard or vice-versa. 

Report Definition

  1. Stakeholders
    1. Lisa Gregerson (Administration)
    2. Linsday Nelson (Operations Manager)
  2. Author
    1. Andrew Pierson (Data Analyst)
  3. Subject matter expert(s)
    1. Accounting employees
  4. Data source(s)
    1. dbo.BuyOrder
    2. dbo.BuyOrderTicketGroup
    3. dbo.Event
    4. dbo.Venue
    5. Ticket Evolution Orders API 
  5. Presentation Medium
    1. Excel Workbook
  6. Fields (raw data)
Column NameData TypeDescription
IDNUMBERICThe order identification number indicated by Ticket Evolution, this corresponds to the ExternalReferenceNumber given in eiBox Office
buyer nameVARCHARPurchaser of the tickets, this will always be Ticket Boat
created_atDATETIMEThe date the order was made on Ticket Evolution
buyer_stateVARCHARThe order state according to Ticket Boat
seller_stateVARCHARThe order state according to Ticket Evolution
totalNUMERICThe total purchase amount indicated by Ticket Evolution
balanceNUMERICThe remaining balance needed to pay for the total order amount indicated by Ticket Evolution
occurs_atDATETIMEThe date that the event is occurring according to Ticket Evolution
EventNameVARCHARThe name of the event for the order in eiBox Office
VenueNameVARCHARThe name of the venue that the event is occurring at in eiBox Office
EventDateDATETIMEThe date that the event is occurring in eiBox Office
PONumberNUMERICThe purchase order number give to the order by eiBox Office
TotalAmountNUMERICThe total cost incurred by Ticket Boat when purchasing the order of tickets
BalanceAmountNUMERICThe remaining balance needed to pay for the total amount of the ticket order
FinalizedDateTimeDATETIMEDate and time that the order was purchased by Ticket Boat
CancelDateTimeDATETIMEDate and time that the order was canceled, NULL values indicate the order was not canceled
ExternalReferenceNumberVARCHARAn external identifier provided by the seller that indicates the unique order id for the tickets purchased by Ticket Boat

Calculations

Column NameData TypeCalculationDescription
DateDATETIME

= TE['Date'] = convert_time(TE['Date'])

= TE['Date'] = pd.to_datetime(TE['Date']).apply(lambda x: x.strftime('%m/%d/%Y %H:%M:%S'))

The date time converted to it's equivalent time in CST from UTC, or mean time. The time aspect does not indicate 
PayeeVARCHAR= TEVOLUTIONThis is the indicator for who the payment goes to, it will always be for Ticket Evolution
AccountVARCHAR= 12000 · Inventory-Tickets:12100 · PO'd InventoryThis indicates the type of inventory that is being PO'd

Parameters

  1. Filters
    1. The report show filters the data to show eiBox Office purchase orders created greater than or equal to yesterday and Ticket Evolution purchase orders that are from the previous day
    2. The Ticket Evolution data is filtered to only include a customer field with Ticket Boat in it, and it excludes data with a seller state equal to rejected
  2. Grouping
  3. Page breaks (Dashboards/Worksheets/Etc.)
    1. TEVO: These are the values that match Ticket Evolution purchase orders with eiBox Office purchase orders, they are used to copy and paste data into quickbooks 
    2. ERRORS:This page shows the purchase orders that did not have a matching order either in Ticket Evolution or in eiBox Office.Columns A:G indicate the Ticket Evolution Orders and columns H:U indicate the eiBox Office orders
  4. Intentions for printing vs. online viewing
    1. The report is delivered via email attachment
    2. This report can be accessed here:\\192.168.10.209\TB-Applications\Tableau\Deployed\TicketEvolutionReport
  5. Intended delivery mechanism (run on demand vs. delivered by subscription)
    1. Static email delivery
    2. Every 24 hours from 7:00 AM to 7:00 AM on Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, and Saturday
    3. Delivered by Windows Task Scheduler. NOT by Scheduler CP (TB scheduler)
  6. Future maintenance tasks or manually maintained items
    1. Had an unexpected error on July 25, 2019 where it went undelivered for the next 7 days. It then resumed as normal. Unknown as to why. Windows Task Scheduler reported it running that week.

Results

  1. Data Accuracy
    1. Report is 100% accurate at the time that it runs, this can be deceiving because different results may be achieved if ran manually at a different time.
  2. Limitations

    1. Auto processor is often tardy when processing Ticket Boat's orders therefore some of the Ticket Evolution purchase orders may have not been auto processed into eiBox Office

Change Log

  1. Who requested what change, when, why, and who executed the change

Images

     


Related content