Import/Export & Expiring Ratecards

There are three methods that can be used to create new ratecards:

• One can export existing ratecards along with all ratecard lines, edit the exported Excel template and then use that template to import new ratecards.

• One can create new ratecards directly in Excel and import the new ratecard.

• Or the copy tool provides a shortcut for copying existing ratecards to other date ranges or products.

Ratecard Line EXPORT

In the Ad Module, navigate to the menu Setup -> Import/ Export Ratecards.

Under ratecard line import select either Export Standard or Export Listing/Classified Ratecards and select the applicable product(s) and the dates range.

Check the box for the ratecard(s) you wish to export to Excel to use as a template.

Click on the button “Download Selected Ratecards”.

The Excel sheet opens with the header and line details of the Ratecard(s) allowing you to edit information or remove or add ratecard lines. When finished, save the file.

Ratecard Line IMPORT

Select Import Standard Ratecard Lines or Import Listing Ratecard Lines on the left menu, depending on the type of ratecard you plan to import.

For exported ratecards, click the “Select” button and browse to find the file created by the ratecard export process. For new ratecards click the button “Download Template” and fill in the data from the exported file being sure to populate required fields. The format and requirements are included in the section of this document titled “Template Fields”.

Click the button “Test Import File”. If there are errors in the template, then the system will alert you to the errors. Click the OK to the message and the details of the errors displays.

Note - Ratecard ID's must be unique for a new ratecard to be created. If you re-use an existing Ratecard ID, new rate lines will appended to the existing ratecard. Use caution to ensure you are using unique ID's if you are expecting new Ratecards to be created.

Ratecard Line IDs must not contain spaces

Also - if you are appending ratecard lines to an existing ID, ensure that the Product ID is correct for that Ratecard ID.

Correct any errors in the template and re-save. On the import screen remove the erroneous file by using the x remove button. Re-select the corrected file.

Repeat the test import and correction process until all errors are removed, the import will not process if any errors are present.

Click on “Import File” and the system will import the file and give you an import ID.

Ratecard Copy

This feature allows a shortcut to copy existing ratecards and is normally used where different ratecards are needed but ratecard line items are similar across all ratecards.

Navigate to the menu Setup -> Import/ Export Ratecards. On the left side menu, select Ratecard Copy -> Export Standard or Listing Ratecards.

Choose the product(s) from the drop-down menu and choose the appropriate expiration date range then click “Get Ratecards”.

Check the box or boxes for the Ratecards you’d like to export then click “Download Selected Ratecards”.

The spreadsheet displays the ratecard header for exported Ratecards.

Edit the sheet to include only the Ratecards you’d like to import back into the system by changing products or start/end dates. The sheet auto defaults the same product Id and a Start Date and End Date one year from the original Ratecard dates. These can be changed as needed.

On the Copy node screen, select the template and import the file.

You can also Download a new template and fill out the columns or use the sheet which you just exported and edited.

If there are any errors in the file, the system alerts you to the errors and you can then remove the file from the import screen. Then edit the spreadsheet with corrections, save and repeat the upload process. Then click the Import File button again.

The system provides a success message with the new Ratecard ID. Click OK.

Navigate to the Product Setup menu -> Ratecards node and search on the product.

The Ratecard you imported is displayed by the Selection Type and Ratecard ID. Note that the Ratecard edited information from the spreadsheet display including the dates.

View Imported Ratecard

Click the node “List of Imported Ratecard Lines”. Choose the import ID from the drop-down menu and click on “Get Ratecards”. The imported cards display with a hyperlink to each Ratecard ID.

Click the hyperlink to the Ratecard ID, and the details display. You can make any edits and changes to the Ratecard, which is now available for use in the campaign entry screen.

Template Fields - Standard

See Importing / Exporting Digital GAM Rates for additional template fields now supported.

Field Name	Example	Data Source	Mandatory/ Optional
Product ID	BF	This must match the Naviga value of the digital product ID.	Mandatory
Ratecard ID	70	Alphanumeric value of the ratecard ID, with no spaces. Must be unique to create a new ratecard. If not unique it will append the lines to existing ratecard id	Mandatory
Ratecard Description	2016	Alphanumeric Description of the ratecard free entry.	Optional, but strongly recommended or ratecard is difficult to select in campaign entry.
Default Frequency Rate Discount Schedule	20111	This is the discount schedule which must match the Naviga value of the discount setup in the system	Optional
Apply Frequency Discounts without Contract	TRUE	TRUE for yes if you want to apply the default frequency rate of the discount schedule. FALSE for No if you don’t want to apply it.	Optional
Client Type	CORP	This is the corresponding client type ID value of the advertiser ID as defined in the advertiser maintenance. These values are setup in the Setup -> Client Type setup.	Optional
Client	244534, 244654*A1, 265444, 365444*Book	This is the advertiser ID which must match the value in the system. You can also enter multiple client IDs separated by commas. You can also enter client IDs and Brands. You can leave blank if you want the Ratecard to be accessible for all advertisers in order entry.	Optional
Currency ID	EU	Must match the Naviga currency ID.	Optional; and MUST leave blank for local currency.
Edition	Christmas	Must match the Edition ID value in Naviga for this product	Optional and only applies to print ratecards.
Start Date	5/16/2019	MM/DD/YYYY format of the start date when this ratecard can be displayed and used in the campaign entry screen.	Mandatory
End Date	12/31/2019	MM/DD/YYYY format of the end date when the ratecard expires	Mandatory
Original Ratecard ID	102	Ratecard ID that this rate is replacing. This is used by Manage Expiring Ratecards process when you wish to replace the old rate with the new rate in packages, classified templates, etc.	Optional
Color	BW	Value must match the Color ID in the Print Color Setup menu under the Setup -> Advertising Setup menu.	Optional
Line Description	Banner AD	Free entry description of the ratecard line	Mandatory
Allow Description Override	Y	Y for Yes and N for No to allow user in the entry line to override the description	Optional
Section	SAT	Must match the section ID for the product.	Optional.
Position	DISP	Must match the position ID for the product.	Optional.
Ad Type	CPC	Must match AD Type ID value in Naviga in the AD Type table	Mandatory
G/L Type	NL	Must match G/L Type ID value in Naviga in the G/L Type table	Mandatory
Sizes	300x250	Must match Naviga size ID as in the Size table for digital products	Mandatory
Default Production Status	1	Must match the ID for the production status in Naviga	Optional
Columns	3	Numeric value of the number of columns	Optional
Depth	6	Numeric value for the depth (height) value in inches.	Optional
Production Link	https://www.msgl.com	Must match Naviga Production Links entered in the Setup menu	Optional
External ID	123	Matches the external ID of the external ratecard if any.	Optional. This field is not used directly by Naviga Ad, but can be exported to 3rd party systems along with orders using this rateline
Rate	2200	Numeric; free entry for the rate per line.	Mandatory
Use AD Server Inventory	TRUE	TRUE for yes or FALSE for No for the GAM products	Optional
No Tax	Y	Y for No tax for the line and N for yes tax for the line.	Optional
Monday Override Rate	300	Numeric value to override the base rate	Optional
Tuesday Override Rate	299	Numeric value to override the base rate	Optional
Wednesday Override Rate	288	Numeric value to override the base rate	Optional
Thursday Override Rate	270	Numeric value to override the base rate	Optional
Friday Override Rate	250	Numeric value to override the base rate	Optional
Saturday Override Rate	400	Numeric value to override the base rate	Optional
Sunday Override Rate	450	Numeric value to override the base rate	Optional
Weekdays Override Rate	344	Numeric value to override the base rate as well as days of the week’s rates	Optional
Weekends Override Rate	355	Numeric value to override the base rate to override Saturday and Sunday rates	Optional
Special Override Date 1	06/02/2021	Date format for a date where a special override is allowed.	Optional
Special Override Date Rate 1	200	Numeric amount which is a rate that overrides the usual rate only on the Date 1 above.	Optional
Repeat override date and rate 2, 3, 4 and 5		Repeat of the above two columns as needed for more dates and rates	Optional
Cancel at Full Value	FALSE	TRUE for yes or FALSE for No	Optional
Unit of Measure	INCHES	Leave blank for Undefined. Options are INCHES, CENTIMETERS, MILLIMETERS, or AGATES	Optional, but necessary if you use Minimum/ Maximum depth options below.
Minimum Depth	1	Numeric amount of the minimum depth of the ad to be eligible for this rate.	Optional
Maximum Depth	3	Numeric amount of the maximum depth of the ad to be eligible for this rate.	Optional
Use Columns Count in Calc	FALSE	TRUE for yes or FALSE for No. This is used in conjunction with the minimum / maximum above.	Optional
Allow Rate on Packages	FALSE	TRUE for yes or FALSE for No	Optional
Flat Rate Ind	FALSE	TRUE for yes or FALSE for No	Optional
Lines	420	Numeric value. Used in the Page Eq calculation	Optional
Category Tree	AUTO,EMPL	Alphanumeric Category Tree ID. Comma separated if this rate can be used in multiple categories.	Optional
Package Only Ind	FALSE	TRUE for yes or FALSE for No	Optional
Default Qty	1000	Numeric Value. Really only relevant for a CPM, CPC, etc.	Optional
Disc Escalation Qty	15:5;20:10;30:25	Numeric value. Format is Up to Qty : Discount Percent. Separate rows with semi-colon. In the example provided. From 0-15 Column Inches (CM, MM) you would get a 5% discount. From 15-20 Col Inches you would get a 10% discount and from 20-30 Col Inches you would get a 25% discount.	Optional
Disc Escalation Rounding	10	Used in conjunction with above Discount Escalation Qty. Leave blank for No Rounding. 1, 5, or 10 for Rounding to the nearest dollar, 5 dollars or 10 dollars (or other currency)	Optional
Rate Escalation Qty	50000:210; 150000:185;200000:165	Numeric value. Format is Up to Qty : Override Rate. Separate rows with semi-colon. In the example provided a quantity from 0-50,000 would have a rate of $210. From 50,000-150,000 the rate would be $185, and up to 200,000 would get a rate of $165.	Optional
Cost Rate	10	Numeric Value for the Cost Rate (Optional field used in GL Mapping for 3rd party costs)	Optional
(CONTINUES...)		40+ additional template fields specific to the GAM targeting tab are available. See link for details	Optional

Note that if any of the rate columns are left blank, the system applies the base rate value in the column “Rate” to that date.

Also, note that the lines are automatically populated from the sizes provided in the template, pulling the line equivalency from the size table in the menu Setup -> Advertising Setup -> Size Code Setup.

Template Fields - Listing

Field Name	Example	Data Source	Mandatory/ Optional
Product ID	BF	This must match the Naviga value of the digital product ID.	Mandatory
Ratecard ID	70	Alphanumeric value of the ratecard ID, with no spaces. Must be unique to create a new ratecard. If not unique it will append the lines to existing ratecard id	Mandatory
Ratecard Description	2016	Alphanumeric Description of the ratecard free entry.	Optional
Default Frequency Rate Discount Schedule	20111	This is the discount schedule which must match the Naviga value of the discount setup in the system	Optional
Apply Frequency Discounts without Contract	TRUE	TRUE (or Y) for yes if you want to apply the frequency rate of the discount schedule. FALSE (or N) for No if you don’t want to apply it.	Optional
Client Type	CORP	This is the corresponding client type ID value of the advertiser ID as defined in the advertiser maintenance. These values are setup in the Setup -> Client Type setup.	Optional
Client	244534, 244654*A1, 265444, 365444*Book	This is the advertiser ID which must match the value in the system. You can also enter multiple client IDs separated by commas. You can also enter client IDs and Brands. You can leave blank if you want the Ratecard to be accessible for all advertisers in order entry.	Optional
Currency ID	EU	Must match the Naviga currency ID.	Optional; and leave blank for local currency.
Edition	Christmas	Must match the Edition ID value in Naviga for this product	Optional. Note: Editions are not currently supported in Listing type ads, so this should be left blank.
Start Date	5/16/2019	MM/DD/YYYY format of the start date when this ratecard can be displayed and used in the campaign entry screen.	Mandatory
End Date	12/31/2019	MM/DD/YYYY format of the end date when the ratecard expires	Mandatory
Original Ratecard ID	102	Ratecard ID that this rate is replacing. This is used by Manage Expiring Ratecards process when you wish to replace the old rate with the new rate in packages, classified templates, etc.	Optional
Color	BW	Value must match the Color ID in the Print Color Setup menu under the Setup -> Advertising Setup menu.	Optional
Line Description	Banner AD	Free entry description of the Ratecard line	Mandatory
Allow Description Override	Y	Y for Yes and N for No to allow user in the entry line to override the description	Optional
Lines	1	Numeric value of the lines entered	Optional
Section	SAT	Must match the section ID for the product.	Optional.
Position	DISP	Must match the position ID for the product.	Optional.
Ad Type	CPC	Must match AD Type ID value in Naviga in the AD Type table	Mandatory
G/L Type	NL	Must match G/L Type ID value in Naviga in the G/L Type table	Mandatory
Columns	3	Numeric value of the number of columns	Optional
Depth	6	Numeric value for the depth (height) value in inches, cm, or mm.	Optional
Production Link	https://www.msgl.com	Must match Naviga Production Links entered in the Setup menu	Optional
External ID	123	Matches the external ID of the external ratecard if any.	Optional. This field is not used directly by Naviga Ad, but can be exported to 3rd party systems along with orders using this rateline
Base Qty	10	Numeric free entry of base quantity	Optional. This is how many you get (in inches, words, lines, etc.) for the base rate. Anything over this Quantity will be charged the Overage rate below.
Base Rate	200	Numeric free entry for rate per quantity	Mandatory
Overage Rate	2	Numeric value for the overage rate in case there is one.	Optional
No Tax	Y	Y for No tax for the line and N for yes tax for the line.	Optional
Monday Override Base Quantity	10	Override numeric value of base quantity for Monday	Optional
Monday Override Base Rate	300	Numeric value to override the base rate	Optional
Monday Override Overage Rate	12	Numeric value to override the overage rate	Optional
Tuesday Override Base Quantity	10	Override numeric value of base quantity for Tuesday	Optional
Tuesday Override Base Rate	300	Numeric value to override the base rate	Optional
Tuesday Override Overage Rate	12	Numeric value to override the overage rate	Optional
Wednesday Override Base Quantity	10	Override numeric value of base quantity for Wednesday	Optional
Wednesday Override Base Rate	300	Numeric value to override the base rate	Optional
Wednesday Override Overage Rate	12	Numeric value to override the overage rate	Optional
Thursday Override Base Quantity	10	Override numeric value of base quantity for Thursday	Optional
Thursday Override Base Rate	300	Numeric value to override the base rate	Optional
Thursday Override Overage Rate	12	Numeric value to override the overage rate	Optional
Friday Override Base Quantity	10	Override numeric value of base quantity for Friday	Optional
Friday Override Base Rate	300	Numeric value to override the base rate	Optional
Friday Override Overage Rate	12	Numeric value to override the overage rate	Optional
Saturday Override Base Quantity	10	Override numeric value of base quantity for Saturday	Optional
Saturday Override Base Rate	300	Numeric value to override the base rate	Optional
Saturday Override Overage Rate	12	Numeric value to override the overage rate	Optional
Sunday Override Base Quantity	10	Override numeric value of base quantity for Sunday	Optional
Sunday Override Base Rate	300	Numeric value to override the base rate	Optional
Sunday Override Overage Rate	12	Numeric value to override the overage rate	Optional
Weekends Override Base Quantity	10	Override numeric value of base quantity for weekends	Optional
Weekends Override Base Rate	300	Numeric value to override the base rate	Optional
Weekends Override Overage Rate	12	Numeric value to override the overage rate	Optional
Weekdays Override Base Quantity	10	Override numeric value of base quantity for any weekday	Optional
Special Override Date 1	01/03/2022	Override date for which you’ll add an override rate and quantity below	Optional
Special Override Date Base Qty 1	1	Override quantity for the first date	Optional
Special Override Date Overage Rate 1	10	Override overage rate for the Date 1	Optional
Repeat for the above rows till Rate 5
Cancel at Full Value	FALSE	TRUE for yes or FALSE for No	Optional
Unit of Measure	INCHES	Leave blank for Undefined. Options are INCHES, CENTIMETERS, MILLIMETERS, or AGATES	Optional, but necessary if you use Minimum/ Maximum depth options below.
Minimum Depth	1	Numeric amount of the minimum depth of the ad to be eligible for this rate.	Optional
Maximum Depth	3	Numeric amount of the maximum depth of the ad to be eligible for this rate.	Optional
Use Columns Count in Calc	FALSE	TRUE for yes or FALSE for No. This is used in conjunction with the minimum / maximum above.	Optional
Category Tree	AUTO,EMPL	Alphanumeric Category Tree ID. Comma separated if this rate can be used in multiple categories.	Optional
Package Only Ind	FALSE	TRUE for yes or FALSE for No	Optional
Default Qty	1000	Numeric Value. Really only relevant for a CPM, CPC, etc.	Optional
Disc Escalation Qty	15:5;20:10;30:25	Numeric value. Format is Up to Qty : Discount Percent. Separate rows with semi-colon. In the example provided. From 0-15 Column Inches (CM, MM) you would get a 5% discount. From 15-20 Col Inches you would get a 10% discount and from 20-30 Col Inches you would get a 25% discount.	Optional
Disc Escalation Rounding	10	Used in conjunction with above Discount Escalation Qty. Leave blank for No Rounding. 1, 5, or 10 for Rounding to the nearest dollar, 5 dollars or 10 dollars (or other currency)	Optional
Default Production Status	1	Must match the ID for the production status in Naviga	Optional

Managing Ratecard Changes

Ratecards are used not only in order entry where a user manually selects the ratecard and ratecard line, but also in the following:

• Listing ads via the booking wizard where the ratecard is automatically selected based on the template chosen

• In Packages where the ratecard is auto-selected based on the package chosen (both in classified packages and in regular packages)

• On Group Ratecards as an override for a particular product and rateline.

It is because of these various associations that it became necessary to have an easy way to update all of these associations when ratecards are updated.

In both the Standard Ratecards and the Listing Ratecards, there is now a column in the export called “Original Ratecard ID”. This should be manually populated with the Ratecard ID from the current Ratecard, and a new, unique Ratecard ID entered in the “Ratecard ID” column. Then, when it is time to update the associated classified templates, packages, and group rate cards, the Manage Expiring Ratecards process can be run.

This is best explained with an example. Let us assume that you are creating the 2023 ratecards by downloading the 2022 Ratecards and applying a % increase to the prices. Your export will look something like this:

You will want to take the Ratecard ID (102 in the example) and move it to the Original Ratecard ID column. Then enter in a new, unique value for the Ratecard ID which will be used for the new ratecard. Update the start and end dates to reflect when these new rates will be valid and update the prices and anything else that will be changing for the new year.

Import your new ratecard using the steps outlined above in the Digital Ratecard Line Import section.

The next step will be to update the associations. In this example, I will want to update all the packages, classified templates, and Group Ratelines that are using ratecard ID 102 and replace them with the new ratecard.

Navigate to Setup -> Manage Expiring Ratecards. Enter the expiration date of the expiring ratecard and click select data. Optionally, this can be filtered by product group. You will see reflected here any existing rates that are in use in a package, template or group ratecard and you will see if there is a replacement ratecard available.

Along the left side, select the line(s) that you wish to update and then select Update Ratecard Links at the bottom of the screen. The system will then replace the original ratecard and rateline ID with the new ratecard and rateline.

The associated ratecard links are not dated, so it is best to run this process only when you are actually ready for the new ratecard to start to be used.