How do I match voice usage to sub-accounts and locations?

Follow

Roy Kasher

Updated

In order to match your voice usage to your sub-accounts and locations, perform the following steps:

  1. Log into the Bandwidth Dashboard.
  2. In the top navigation bar, select Insights.
  3. Navigate to the Reports tab and select Full Phone Number Details.

Screen_Shot_2020-05-26_at_6.23.08_PM.png

  1. Select the CSV file format but don't customize the date range. Click Start Report.

Screen_Shot_2020-05-26_at_6.18.04_PM.png

  1. Your report will be available on the right side of the page. To download it, click the download icon.

Screen_Shot_2020-05-26_at_6.16.15_PM.png

  1. Return to the top navigation bar and click Billing.
  2. From the Type drop-down, select Statement BDR. Then use the Date range to select your desired interval.
    • Note: If you select Previous Month, it will pull the traffic that aligns with last month's invoice and not necessarily the previous month's usage. The Month to date option shows the traffic that aligns with the current month's invoice. For example, the screenshot below shows Month to Date: May 1 - May 26, which will generate a Statement BDR that aligns with your May invoice (and April usage)
  3. Click Submit. This will download an Excel file into your web browser. Double click to open it.
    • Note: If you don't see the file, check the Downloads section in your web browser or the Downloads folder on your computer. 

Screen_Shot_2020-05-26_at_7.09.17_PM.png

Once you open the Excel file, perform the following steps:

  1. Select Column A, click Data in Excel’s navigation bar, and select Text to Columns. The convert text to columns wizard will open.
  2. Select Delimited and click Next.
  3. Choose your Delimiter as Other, type in a vertical bar (|), and click Next.
  4. For the Column data format, select General, and click Finish.
  5. Hover over Column E and right click. Select Insert. In the newly created E2 insert the following formula: =Right(F2,10)*1.
  6. In the bottom right corner of the cell highlight until your cursor becomes a + and double click to populate the rest of the column with the formula. Name this column New CallSource.
  7. Hover over Column G and right click. Select Insert. In the newly created G2 insert the following formula: =Right(H2,10)*1.
  8. In the bottom right corner of the cell highlight until your cursor becomes a + and double click to populate the rest of the column with the formula. Name this column New CallDestination
  9. Hover over Column I and insert. Then hover over the newly created column I again and insert.
  10. Name the newly created Column I Sub-Account and Column J Location
  11. In Column I, the one you titled Sub-Account, you'll need to perform a VLOOKUP. The following VLOOKUP formula is just an example because your Full Phone Number Details Report will likely have a different file name:
    • =IF(OR(D2="INTRASTATE",D2="INTERSTATE",D2="OUTBOUND TOLL FREE"),VLOOKUP($E2,'full-phone-number-details-20200615-164000 subaccountingtest.csv'!$A:$R,10,FALSE),VLOOKUP(G2,'full-phone-number-details-20200615-164000 subaccounting test.csv'!$A:$R,10,FALSE))
  12. In Column J, the one you titled Location, you'll need to perform a VLOOKUP as well. Again, the following VLOOKUP formula is just an example because your Full Phone Number Details Report will likely have a different file name:
    • =IF(OR(D2="INTRASTATE",D2="INTERSTATE",D2="OUTBOUND TOLL FREE"),VLOOKUP($E2,'full-phone-number-details-20200615-164000 subaccountingtest.csv'!$A:$R,11,FALSE),VLOOKUP(G2,'full-phone-number-details-20200615-164000 subaccounting test.csv'!$A:$R,11,FALSE))

Quick tips and information

  • Make sure that you're applying the formula to the entire column
  • D2 = Call Type on your statement BDR
  • E2 = New CallSource Field on your statement BDR
  • G2 = New CallDestination Field on your statement BDR
  • Select columns A-K on Full Phone Number Details report
  • 10 = the 10th column (starting from column A) on your Full Phone Number Details Report
  • 11 = the 11th column (starting from column A) on Full Phone Number Details report 
  • A parameter of FALSE means that VLOOKUP is looking for an EXACT match for the lookup value (in this case, the phone numbers). 

Additional notes

  • If the call is Termination and the CallSource did not originate from a Bandwidth TN, then #N/A will appear. Alternatively, if the call is Origination and the CallDestination did not originate from a Bandwidth TN, then #N/A will appear. While this is rare, it's possible that call legs in a call forwarding scenario will appear this way. If the call originated from your IP Address, it's still a valid call and will be billed accordingly.
  • There's a 1 million row limit in Excel, so if you have more than 1 million calls given in your selected time frame, you'll need to utilize a CSV splitter to complete the above steps in Excel.
  • 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.