Standard Media Interface

Summary: A description of the third version of the "standard media interface" for linking data from broadcast orders, print orders, and vendor media invoices from media buying systems like TAPSCAN, SmartPlus, and Strata into accounting programs like Clients & Profits. Revision History: 12/9/97 - first release 2/4/98 - added field 23 for "Market" and field 24 for "Revision" number 9/8/98 - added field specifications for SMI interface for print orders 9/8/98 - changed the broadcast buy text file name extension from ".BUY" to ".TBY" to distinguish TV buys from Print buys. 9/10/98 - added field specifications for SMI interface for vendor media invoices The "Standard Media Interface" (or "SMI") standardizes the communication between Media Buying Systems, such as SmartPlus, TAPSCAN, Strata, and CoreSpot, and advertising agency accounting programs, like Clients & Profits. Currently, all systems export data from media buys for use in accounting. But there is no consistently between these export files, which causes end-user confusion, training and support inefficiencies, and preventable errors. Note: While developed by Clients & Profits, Inc., the Standard Media Interface is an open standard. It can be used by software companies royalty-free. How the SMI works: A media buyer enters a broadcast plan into a Media Buying System. The broadcast plan is composed of one or more media buys. Each media buy contains details about the buy, including station, run dates, run days, run times, the ad name, the rating, and the cost per spot (at net and gross). When the plan is approved, the buyer prints the station order. At that time the media buying system would automatically (or maybe prompt the user) to update the accounting system. The Media Buying System then creates a standard tab-delimited text file containing the station order. The export file is saved to a preset directory on the file server. The Accounting System will then check this directory as regular intervals (or let a user check manually as needed) then import any new station orders. The station orders appear in the accounting program as Broadcast Orders. What's involved: Integrating data from media buying systems to accounting systems involves four components: (1) A Media Buying System that creates tab-delimited text files based on the SMI specification, (2) text files containing data from broadcast orders, print orders, and vendor invoices, (3) a "drop" folder (sub-directory) located on a file server that stores the exported media files, and (4) an Accounting System that can import and process tab-delimited text files based on the SMI specification Work flow: The basic integration between the media buying system and the accounting system works like this: (1) A media buyer finalizes a broadcast or print media plan then prints the station orders, (2) the Media Buying System automatically creates a separate tab-delimited export text file for each station/publication on the order, saving the files into the "drop" folder, (3) the Accounting System checks the contents of the "drop" folder, looking for newly-saved export files, (3) if files are found, the Accounting System imports the media buys, (4) the Account System moves the successfully-imported text files into a different "imported_buys" folder. General Tips and Notes: -- The media buying system should create tab-delimited ASCII text files for simplicity. Tab-delimited files can be opened and easily viewed and edited in any spreadsheet, unlike fixed-length text files. Also, it is much easier to add a new field or modified an existing field on tab-delimited files, since the literal character position isn't important. Currently, modifying the contents of a fixed-length export file requires recoding the import procedure. -- The name of the exported media text files should end with .TBY (broadcast buys), .PBY (print buys), or .VAP (vendor invoices) to distinguish them from other files. -- The name of the .TBY, .PBY, or .VAP files shouldn't exceed 20 characters (including the extension). -- The Media Buying System should implement tables for stations, clients, and media buyers. Each station, client, and media buyer record should be identified with a short code (or ID number), which should correspond with station, client, and buyer codes in the accounting system. When media buys are imported, these codes will be a more reliable lookup to the accounting system's data. Unless codes are exported, a media buyer's misspellings of a client name or station name will cause the accounting system's import to fail. -- The media buying system and accounting system will share a common "drop" folder (sub-directory) on the agency's file server. This drop folder, named something like "media_buys", would be used to store broadcast buy, print buy, and vendor invoice text files exported from the media buying system. The accounting system will look inside this folder (manually or automatically at specified intervals) for incoming data. -- The "drop" folder will be empty once all of the media text files are imported. -- A special "import_buys" folder will stored all imported media text files. These files will be stored as backups in case one needs to be re-imported (due to an error, for example). -- A copy of each station order should be printed for the Accounting Department. The Accounting Department should compare each day's printed station orders to the imported broadcast/print insertion orders in the Accounting System to ensure their accuracy. Broadcast Buys (TV/Radio/Cable): The broadcast order BUYS data file format (a uniquely named text file ending in ".TBY") ) should contain these fields: #: Field Name: Description: Field Type: Column*: 1 Order Number Unique ID number Char 10 A 2 Order Date Date of station order Date B 3 Buyer's Initials Initials of the media buyer placing the order Char 4 C 4 Client Number Client ID (call letters or numbers) Char 10 D 5 Vendor Number Vendor ID (call letters or numbers) Char 10 E 6 Job Number Job number for cost accounting/billing Char 10 F 7 Job Task Task code for cost accounting/billing Char 4 G 8 Flight Dates Range of dates the spots will run Char 25 H 9 Broadcast Month Month or months the spots will run Char 15 I 10 Ad Name Name of the ad Char 30 J 11 Product Product ID Char 15 K 12 Line Number The buy's line number on the order Number 0dps L 13 Run Dates Run dates for each media buy line Char 20 M 14 Run Days Days of the week (i.e., MTWTFSS) Char 10 N 15 Run Times Times the spots should run Char 20 O 16 Program Name/Daypart Daypart or program name Char 20 P 17 Rating Rating points per spot Number 2dps Q 18 Spot Length Length of spots Char 10 R 19 Quantity Number of spots to run Number 0dps S 20 Commission Media commission percent (e.g., 15.00) Number 2dps T 21 Total Net Total net cost, w/o commission, for all spots Number 2dps U 22 Total Gross Total gross cost, incl. commission, for all spots Number 2dps V 23 Market The market name for the broadcast order Char 30 W 24 Revision The order's revision number Number 0dps X * These are the columns used by the media buy text file if it is opened in Microsoft Excel Broadcast Buy Notes: -- Each station order must have a unique order number (like a purchase order number). This number is essential for tracking buys, clears, costs, and billings throughout the entire process. It is especially important that each buy and clear record contain the order number; without it, the record won't update the accounting system correctly. -- Each station order should be exported as a separate tab-delimited text file. The accounting system should store imported station orders in a separate sub-directory of "imported orders" for better accountability. This way, if an import fails, the station order export file can be easily found and imported over again. If export files always have the same name (e.g., FINANCE.DAT) there's no way distinguish one by from another. -- If a station order is revised, the entire order should be exported again. When the accounting system imports the revised station order, it should know that the order already exists; if so, the old order will be replaced by the new one. -- The Accounting System shouldn't allow imported station orders to be edited. If so, the order in the accounting database won't match the order in the Media Buying System. The Media Buying System's data should always take priority over the Accounting System. Print Buys (Newspapers/Magazines/Periodicals): The print media buy export file format (a uniquely named text file ending in ".PBY") should contain these fields: #: Field Name: Description: Field Type: 1 Order Number Unique ID number Char 10 2 Order Date Date of station order Date 3 Buyer's Initials Initials of the media buyer placing the order Char 4 4 Client Number Client ID (call letters or numbers) Char 10 5 Vendor Number Vendor ID (call letters or numbers) Char 10 6 Job Number Job number for cost accounting/billing Char 10 7 Job Task Task code for cost accounting/billing Char 4 8 Line Number The buy's line number on the insertion order Number 0dps 9 Publication Publication Name Char 15 10 Issue Date Date the ad runs (e.g., "November 98") Char 15 11 Ad Name/Caption Name or caption of the ad Char 40 12 Ad Size Size of the display ad Char 15 13 Space Closing Date the ad's space closes (optional) Date 14 Materials Due Date ad materials are due to pub (for traffic) Date 15 Position/Spec. Instructions Free-form notes about the print buy (opt.) Char 200 16 Quantity/Length Number of ads on this buy, or column inches Number 2dps 17 Net Cost Cost to agency less commissions Number 2dps 18 Commission Commission % (e.g., 15.00) Number 2dps 19 Gross Costs Cost to client, including commission Number 2dps 20 Bill Net Bill net, not gross option (0=No,1=Yes) Number 0dps Print Buy Notes: -- Each publication order must have a unique order number (like a purchase order number). This number is essential for tracking buys, clears, costs, and billings throughout the entire process. It is especially important that each buy and clear record contain the order number; without it, the record won't update the accounting system correctly. -- Each print insertion order should be exported as a separate tab-delimited text file. The accounting system should store imported station orders in a separate sub-directory of "imported orders" for better accountability. This way, if an import fails, the station order export file can be easily found and imported over again. If export files always have the same name (e.g., FINANCE.DAT) there's no way distinguish one by from another. -- If a print insertion order is revised, the entire order should be exported again. When the accounting system imports the revised station order, it should know that the order already exists; if so, the old order will be replaced by the new one. -- The Accounting System shouldn't allow imported print media orders to be edited. If so, the order in the accounting database won't match the order in the Media Buying System. The media buying system's data should always take priority over the accounting system. Vendor Media Invoices (Accounts Payable): The vendor media invoice export file format (a uniquely named text file ending in ".VAP") should contain these fields: #: Field Name: Description: Field Type: 1 Vendor ID Vendor code or account number Char 10 2 Invoice Number Vendor's invoice number (from printed invoice) Char 20 3 Invoice Date Date from printed invoice Date 4 Accounting Period Period in G/L to be affected (1-24); optional Number 0dps 5 Invoice Total Due Total balance due from agency Number 2dps 6 Order Number Insertion order number Char 10 7 Line Number Line number from insertion order Number 0dps 8 Quantity Number of spots/ads that actually ran Number 0dps 9 Gross Cost Cost to client including commissions Number 2dps 10 Commission Commission % (e.g., 15.00) Number 2dps 11 Net Cost Cost to agency less commissions Number 2dps Vendor Media Invoices Notes: -- The "vendor ID" value should match a vendor record in the Accounting System. If not, it shouldn't be imported. -- If the "accounting period" field is empty, the current period from the Accounting System will be entered automatically. -- The "invoice total due" should equal the sum of the "net cost" amounts on vendor media invoice's line items. -- The "order number" and "line number" are critical, since it tells the Accounting System to point at a specific media buy on a broadcast order or insertion order. These two fields are required for reconciliation ordered quantities with actual quantities, which affects media billing.