How to Match Sub-Account Locations to Their Associated Call Sources

Follow

Malia Barnett

Updated

In order to to properly match your sub-accounts to their associated call sources, please follow the instructions below:

  1. Log into the Bandwidth Dashboard and click ‘Reports’ on the top navigation bar and then click ‘Reports Dashboard’ on the sub-navigation bar.
  2. Click the ‘Full Phone Number Details Report’ (without customizing the date-range), select the file type as as ‘CSV’ and click ‘Generate Report’.
  3. Your report will be available on the right hand side of the page. Hover over the generated report and click ‘CSV’ to download the report. 
  4. Next, go back to the sub-navigation bar and click ‘Billing Reports’.
  5. Now select the report type as ‘Statement BDR’, choose the desired time period, and then click ‘Download.’ Your report will be available in your ‘Downloads’ folder on your computer.

*Don’t see the report in your download folder? Make sure your browser is set to allow pop-ups from the Bandwidth Dashboard.

  1. Open the downloaded Statement BDR in Excel (be sure you only have one header row).
    1. From here, select column ‘A’, click the ‘Data’ tab on Excel’s navigation bar, and choose ‘Text to Columns’. The convert text to columns wizard will open. Select ‘Delimited’, click ‘Next’, choose your ‘Delimiter’ as ‘Other’, type in ‘|’ and click ‘Next’. For the ‘Column data format’ select ‘General’ and click ‘Finish’.
    2. Add a Filter to the header row 1 and filter the ‘CallType’ (Column D) to only show Interstate, Intrastate, and Outbound Toll Free.
    3. Insert a new column (as column F), title this column header as New CallSource Field this column will be used to re-format the original CallSource Field. In cell F2, enter the formula ‘=RIGHT(E2,10)*1’; (Column E should be titled CallSource). 

*This formula will remove the 1 from the TN in the CallSource field, so that it will match the format in the Full Phone Number Details Report. Copy this formula down the entire column for all the numbers/calls in question.

    1. Insert two new columns (columns H & I); title these columns Sub-Accounts and Locations, respectively. 
  1. In Column H, the one you titled Sub-Accounts, you'll need to perform a VLOOKUP. The following VLOOKUP formula is an example only because your Full Phone Number Detail Report will have a different file name. Example formula:

‘=VLOOKUP(F2,[select columns A-R on Full Phone Number Detail report],10,FALSE)”. 

  • ‘F2’ = New CallSource Field on your statement BDR
  • Select all of the columns A-R on Full Phone Number Detail report
  • ‘10’ = the 10th column (starting from column A) on Full Phone Number Detail report 
  • ‘False’ = A parameter of FALSE means that VLOOKUP is looking for an EXACT match for the value in the document. 

*You'll type in the above formula and then hit ‘enter’ on the keyboard. This result will return the Sub-Account for this particular Call Source. 

  1. In Column I, the one you titled Locations, you'll need to perform a VLOOKUP. The following VLOOKUP formula is an example only because your Full Phone Number Detail Report will have a different file name. Example formula:

‘=VLOOKUP(F2,[select columns A-R on Full Phone Number Detail report],11,FALSE)”. 

  • ‘F2’ = New CallSource Field on your statement BDR
  • Select all of the columns A-R on Full Phone Number Detail report
  • ‘11’ = the 11th column (starting from column A) on Full Phone Number Detail report 
  • ‘False’ = A parameter of FALSE means that VLOOKUP is looking for an EXACT match for the value in the document. 

*You'll type in the above formula and then hit ‘enter’ on the keyboard. This result will return the Location  for this particular Call Source. 

 

PLEASE NOTE: 

  • If the result is #N/A, the CallSource didn't originate from a Bandwidth Phone number and there is no associated information for that TN.
  • There is a 1 million row limit in excel, so If you have more than 1 million calls given your selected timeframe, you'll need to utilize a CSV splitter to complete the above steps in Excel.
  • Please note that although the individual location line items may be incorrectly displayed on your invoice, the charge summary in your invoice is accurate. 

Article is closed for comments.