Goals

• Create new Deals from CoxReps import file
• Update existing Deals from CoxReps import file

Incoming Data

The file will be manually generated by the customer and will be stored in a FTP server. The file will contain data in Tab delimited format. 

• Example

File Data to Deal Mapping

The following table shows the direct mapping between fields in the import file and Deal properties in Matrix.

Deal properties for which no direct mapping exist in the import file-

• Deal Timeframe: Consists of Month and Year fields per unique Headline. Example: if we are provided two records for April and May 2018 respectively for same Headline 8839199, then we will create a deal with April and May 2018 as the timeframe.
• Deal Status: Always set Status to Pending.
• Deal Stage: Set according to specified Sales Stage in Integrations tab for Admin Settings.
• Deal Name: Set to <AccountName>+" (Cox Deal "+<Headline>+")". Example: Dignity Health (Cox Deal 8839199)
  
  

Import Process

• A taskrunner scheduled task which runs daily will consume the import file from the FTP server and import Deals.
• After consuming an import file, the task will move the file to a History folder under the same directory. It will also create an entry in Integrations_VendorProcessedFiles table to store the processed date of the file.
• At the beginning of each run, the task will remove all the expired processed files (processed more than 30 days ago) from the History folder.
• The task will map each record in the import file to an Avail object. These Avail objects will then be used to create Matrix Deals. If a new Deal record is created, a new entry will be added to the Avails table using the mapped Avail object.
• The task will also fetch all the existing Avails from the Avails table. These existing Avails will be compared with the newly imported Avails to determine when to create new Deals or update existing Deals.
  
  

Importing Scenarios

Create Deals

If the Headline + Outlet + Month + Year combination of an imported Avail is not found in existing Avails, then create:

• a new Deal D (if not exists) for corresponding Headline
• a new Deal Detail (if not exists) for corresponding Outlet under Deal D
• a new Pending Dollar value (if not exists) for corresponding Month, Year and Outlet under Deal D

Then add these records to Avails table.

Examples-

**Encountered Imported Avail = Headline 100, Outlet KABC, Month 1, Year 2015, Dollar 5**

Given that there are no Avails in existing Avails with Headline + Outlet + Month + Year combination and no Deal exists for Headline 100

When importing Deals

Create a new Deal with 1 Deal Detail having Outlet KABC, Month 1, Year 2015, Dollar 5 and Create new Avail record

**Encountered Imported Avail = Headline 100, Outlet KBCD, Month 1, Year 2015, Dollar 5**

Given that there are no Avails in existing Avails with Headline + Outlet + Month + Year combination and a Deal exists for Headline 100 but does not have Deal Detail with Outlet KBCD

When importing Deals

Create a new Deal Detail with Outlet KBCD, Month 1, Year 2015, Dollar 5 for Deal associated with Headline 100 and Create new Avail record

**Encountered Imported Avail = Headline 100, Outlet KBCD, Month 2, Year 2015, Dollar 5**

Given that there are no Avails in existing Avails with Headline + Outlet + Month + Year combination and a Deal having Deal Detail KBCD exists for Headline 100 but does not have a Pedning Dollar value for Month 2, Year 2015

When importing Deals

Create a new Pending Dollar value for Outlet KBCD, Month 2, Year 2015, Dollar 5 for Deal associated with Headline 100 and Create new Avail record

Update Deals

• If the Headline + Outlet + Month + Year combination of an imported Avail is present in existing Avails, then update corresponding Deal and Avail record.
• If the Headline + Outlet + Month + Year combination of an existing Avail is not found in imported Avails, delete corresponding Pending Dollar and Avail record.

Examples-

Considering Account/Agency/Office/Salesperson were not changed

**Encountered Imported Avail = Headline 100, Outlet KABC, Month 1, Year 2015, Dollar 10**

Given that there is an Avail in existing Avails with Headline 100 + Outlet KABC + Month 1 + Year 2015 combination

When importing Deals

Update Pending Dollar to 10 of Outlet KABC, Month 1,Year 2015 for Deal associated with Headline 100 and Update Avail record

Given that there is an Avail record with Headline 100 + Outlet KABC + Month 1 + Year 2015 combination in existing Avails but not in imported Avails

When importing Deals

Delete Pending Dollar value of Outlet KABC, Month 1,Year 2015 for Deal associated with Headline 100 and Delete Avail record

Close-Win Deals

If a Headline is completely missing from an import file, mark the Deal associated with that Headline as Closed-Won.

Example-

Given that there are one or more Avails in existing Avails with Headline 100 but there are no Avails with Headline 100 in imported Avails

When importing Deals

Mark Deal associated with Headline 100 as Closed-Won and Delete all Avail records with Headline 100

Some key points to note:

• Never Delete Deals
• Never Update Deals which have been Reconciled

Mappings (Matrix | CoxReps)

Account | Account
Agency | Agency
Salesperson | Salesperson
Office | Office
Timeframe | see below "Date Range"
Outlet | Station
Dollars | pending_amount
Status | see below "Deal Status"
Stage | see below "Deal Stage"
Unique ID | Headline

Mappings Not Included

• Category
• Weight
• Revenue Type

Additional Details

• Date Range: This will come from the Month and Year fields per unique Headline. Ex: if we are provided April and May 2018 for Headline 8839199 then we will create a deal with April and May 2018 as the timeframe.
• Deal Status: Set every status to Pending. CoxReps does not have a concept of a status like Matrix so we should assume that everything in the file is still pending unless Matrix has reconciled or closed the deal.
• Deal Stage: Set every Stage to the first stage in the list. CoxReps does not have a concept of a Stage like Matrix so we should assume the first stage. *Note: it would be very nice if an admin could tell Matrix which stage to use for any deal created by CoxReps.
• Deal Name: take the <Account>+" (Cox Deal "+<Headline>+")". Ex: Dignity Health (Cox Deal 8839199).

If a headline is not found in the file that we were expecting, it should be assumed that the deal can be closed. Cox does not have a way to give us the status AND does not allow deletes so this will be assumed as winning the deal when a record is missing that was previously found. However if only part of the data is removed but the headline still exists then only remove the data that no longer exists and leave the rest.  Multiple Outlets are expected per file.

General Process Notes

If a new value is found from Account, Agency, Salesperson, Office, Outlet then create new when creating the Deal. Add " (Cox)" to the end of the Salesperson, Office, and Outlet for easier maintenance cleanup. Ex: NTVQ-TV (Cox)

• Ignore blank rows. No reason to log this event.
• Ignore rows without an Account, Salesperson, Office, or Outlet. You may wish to log this even; however, if the entire import fails for any reason, including a file does not exist then we will need to alert people at Matrix. Historically we have done this via email to [email protected] we'll just want to be as clear as possible as to why the failure occurred meaning recipients should know a file doesn't exist vs failure to process or something.

A "Touched" date will exist per unique Headline. Matrix should use this in order to determine if the information from Cox is more current than Matrix. This allows us to import fresh data from Cox while allowing salespeople to manage the deals in Matrix without fear of overwriting each day. If a more recent date is found, we will overwrite the deal with the information from Cox.

Further details can be found in Pivotal story https://www.pivotaltracker.com/story/show/152358625. 

• We do not delete Deals within Matrix when the Deal is not found in the CoxRep file like we do in Katz.