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

Note: Using this process will show what is true based on the time the Full Phone Number Details Report is downloaded, not true to the time of the call. The Full Phone Number Details Report shows what Sub-account/Location a phone number is on at the time the report is downloaded. The Sub-Accounts and Locations are not guaranteed to populate/match with this process.

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

1. Log in to the Bandwidth App.
2. In the side navigation bar, select Insights.
3. Click Reports and select Full Phone Number Details

4. Select the CSV file format and complete other fields, but don't customize the date ranges. Click Start Report.

5. Your report will be available on the right side of the page. Click the download icon highlighted below to download it.

6. Once you have downloaded the Full Phone Number Details report, open the spreadsheet and save this as an .xls file. You can rename it to what you'd like.
7. Return to the side navigation bar and click Billing.
8. Under Type, 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 Current Month option shows the traffic that aligns with the current month's invoice.
9. Click Submit. This will download an Excel file into your web browser. If you don't see the file, please check the Downloads section in your web browser or the Downloads folder on your computer. 

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))
    • Once you paste the VLOOKUP formula, hit enter. This will prompt a pop up for you to select a file. Select the Full Phone Number Details report you saved as an .xls file.
    • This pop up will come up one more time. Choose the same Full Phone Number Details .xls file again. This will then populate the cell with the sub-account information. 
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))
    • Once you paste the VLOOKUP formula, hit enter. This will prompt a pop up for you to select a file. Select the Full Phone Number Details report you saved as an .xls file.
    • This pop up will come up one more time. Choose the same Full Phone Number Details .xls file again. This will then populate the cell with the location information. 

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.
• If the Sub-Account and Location show #N/A, the TN may no longer be on your account and therefore will no longer be listed in your Full Phone Number Details report. The Full Phone Number Details Report provides the phone number on a sub-account/location at the time the report is pulled.
• 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.