Zendesk SELL stuff
      Back to home
      1. PS Internal Support Documents
      2. [INTERNAL] Processes, FAQ and other info
      3. Zendesk SELL stuff

      [REPORT] Value of Deals

       

      1. Create your "base" smart list

      If you do not already have a smart list created, you can use this template I created in Sell as your "base" smart list. 

      Note: The template can be edited by an Admin.

       

      To start, navigate to Deals($) then click the hamburger icon to open the Working Center. 

       

       

      The Working Center is where all of your Deal-related smart lists live.
      Note: Each Sell section (Leads, Contacts, Deals) has a working center where your smart lists reside.

      Now that you are here you want to create a new smart list, and you want to use the template.  To do so, click Templates.

      mceclip0.png

      Upon clicking Templates the available templates will display -> select BARTER Deals [expiring].

       

      The template will load, then click Use Template.

      Fields included

      • Deal Name
      • Company (should be the station call sign)
      • Barter Minutes per Day
      • Daypart
      • Contract End Date
      • Contract Start Date
      • Payment Form (Cash or Barter)

       

      2. Modify your new smart list to show only the Deal data you wish to see

      Now that you have created your "base" smart list you need to modify the Contract End Date filter to reveal only the Deal data you wish to see.

       

      In the template in the example above, I have clicked the Filter icon in the Contract End Date column of my smart list to reveal my filtering options.

      I have chosen to show deals that have a value in this field, and the value falls between the Fixed Date of 10/1/23 and 9/30/24.

       

      3. Name your smart list

      Give your smart list a name so you can easily find it.

       

      ...and now here it is in the Working Center.  You can use this smart list moving forward as a base, updating the dates and filtering any way you like.

      4. Export your smart list

      5. Get your data ready

      • Copy/paste DEALS smart list data into Sheet 1
      • Copy/paste ALL station data (from SELL) into Sheet 2
        • Company (KBRG-FM)
        • Parent Company (iHeartMedia, Inc. - Waco TX)
        • Market Name (Waco, TX)
        • Market Rank (195)
        • Current AQH MSu6a12m (900)

      • Set Up The Spreadsheets

        • In Spreadsheet 1, create new columns where you want data from Spreadsheet 2 to appear

          • Parent Company (iHeartMedia, Inc. - Waco TX)
          • Market Name (Waco, TX)
          • Market Rank (195)
          • Current AQH MSu6a12m (9000)

      6. Using VLOOKUP to combine the data   

      • Click on the first cell in "Sheet1" where you want the data from "Sheet2" to appear Enter the VLOOKUP formula:  =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) 

        • lookup_value: This is the identifier. For instance, if the radio station call letters are in column A of "Sheet1", this would be the reference to the first cell of column A (e.g., A2).      -
        • table_array: This is the range of cells in "Sheet2" where the data is. Make sure you include both the column with the radio station call letters and the column with the data you want to pull.
        • col_index_num: This is the number of the column in your `table_array` where the data you want to pull is.
        • [range_lookup]: Input FALSE to ensure an exact match.        Example:    If the call letters are in column A of both sheets and you want to pull data from column B of "Sheet2" to column C of "Sheet1", the formula in "Sheet1" would be: =VLOOKUP(A2, Sheet2!$A$2:$B$100, 2, FALSE) =VLOOKUP(A1, Sheet2!$A$3:$B$3880, 2, FALSE)

      • Drag The Formula Down
        • Once you've entered the formula for the first cell, drag the fill handle (small square at the bottom-right corner of the selected cell) down to fill the cells in the column with the VLOOKUP formula. This will pull the corresponding data for all radio stations.