Detailed Billing Information CSV
Usage fees for SORACOM services are calculated and billed based on a billing period that starts at the beginning of the month and finishes at the end of the month (UTC). The total billing amounts can be verified from the console; however, you can also use the console or the API to output data in CSV format containing a detailed breakdown of all the usage fees. This document shall now explain how to download CSV data containing detailed billing information.
Download Detailed Billing Information CSV Using the Console
You can download detailed billing information for the most recent month from “This Month’s Usage Fees” under the Billing Information tab.
This CSV data corresponds to the most recent month, and is therefore updated over time.
CSV data from previous months can be downloaded by clicking on the months that you are interested in under “Past Usage Fees,” and then clicking on the download button at the bottom of the screen.
Download Detailed Billing Information CSV Using the API
Based on the current API specification, when you are at the beginning of a month, the CSV that can be obtained using exportLatestBilling corresponds to that month. Until the closing of accounts has taken place at the beginning of the month (i.e. the processing of account re-calculations for the previous month) and those billing figures have been finalized, you will not be able to download data for the previous month using the exportBilling API. Closing of accounts at the beginning of the month is generally completed within 1 to 3 business days from the start of the month.
CSV files are UTF-8 encoded (including BOMs).
CSV File Structure
CSV files have the following structure.
See below for a description of each data item.
First Row: Header
The header row contains a number of data items. These are broadly divided into “Amount Data Items” and “Tag/Group Data Items,” and the Amount Data Items are always output before the Tag/Group Data Items. Both Amount Data Items and Tag/Group Data Items are variable, so please refer to the section titled “Important Notes on CSV Parsing” when reading in the data using a program.
The data items displayed under “Amount Data Items” are as shown below.
- The SIM’s IMSI. This data item is empty for any non-SIM related billing data items (VPG, free allowance, etc.).
- The ID of the Virtual Private Gateway (VPG) to be used on SORACOM Canal/Direct.
- This data item is empty for any non-VPG related billing data items (SIM, free allowance, etc.).
- If you are not using SORACOM Canal/Direct, this data item is not output to the CSV.
- For billing data items, dates are written in yyyyMMdd format.
- For free allowance data items, dates are written in yyyyMM format.
- Billing/discount data item names. The following data items are currently available.
Billing Records Description basicCharge-ready Basic fee (SIM usage fee: When Ready) basicCharge-active Basic fee (SIM usage fee: When Active) basicCharge-inactive Basic fee (SIM usage fee: When Inactive) basicCharge-suspended Basic fee (SIM usage fee: When Suspended) subscriberSuspensionCharge Change fee for changing to Suspended uploadDataCharge-xxxx-xxxx Quantity of upstream data communication (from a device to SORACOM). Delimited by a hyphen to specify the speed class (s1.fast, etc.) and the time of day (daytime, nighttime).
downloadDataCharge-xxxx-xxxx Quantity of downstream data communication (from SORACOM to a device). Delimited by a hyphen to specify the speed class (s1.fast, etc.) and the time of day (daytime, nighttime).
customDNSCharge CustomDNS fee per SIM virtualPrivateGatewaySetupCharge Setup fee for Virtual Private Gateway (up to April 2016) virtualPrivateGatewayCanalSetupCharge Virtual Private Gateway setup fee for SORACOM Canal virtualPrivateGatewayDirectSetupCharge Virtual Private Gateway setup fee for SORACOM Direct virtualPrivateGatewayDoorSetupCharge Virtual Private Gateway setup fee for SORACOM Door virtualPrivateGatewayCharge Virtual Private Gateway usage fee per SIM soracomCanalVirtualPrivateGatewayCharge Virtual Private Gateway usage fee for SORACOM Canal soracomCanalVPCPeeringCharge SORACOM Canal fee per VPCPeering soracomDirectVirtualPrivateGatewayCharge Virtual Private Gateway fee for SORACOM Direct soracomDirectVirtualInterfaceCharge SORACOM Direct fee per Virtual Interface soracomDoorVirtualPrivateGatewayCharge SORACOM Door fee per VPN connection soracomDoorVPNConnectionCharge SORACOM Door fee per VPN connection soracomBeamRequestCharge Soracom Beam request usage fee soracomEndorseCharge Soracom Endorse usage fee soracomFunnelRequestCharge Soracom Funnel request usage fee soracomJunctionCharge Soracom Junction usage fee soracomJunctionInspectionCharge Soracom Junction usage fee docomoSMSCharge SMS fee (DOCOMO line) docomoInternatinalSMSCharge International SMS fee (DOCOMO line) kddiSMSCharge SMS fee (KDDI line) Discount Records Description dataTrafficFreeTier Free data communication allowance soracomBeamRequestFreeTier Soracom Beam request free allowance soracomEndorseFreeTier Soracom Endorse free allowance soracomFunnelRequestFreeTier Soracom Funnel request free allowance dataTrafficCoupon Coupon allocation for data communication soracomBeamRequestCoupon Coupon allocation against Soracom Beam request usage fee soracomEndorseCoupon Coupon allocation against Soracom Endorse usage fee soracomFunnelRequestCoupon Coupon allocation against Soracom Funnel usage fee chargeCoupon Coupon allocation against all SORACOM usage fees soracomAirBasicChargeVolumeDiscount Volume discount for basic fee
- Unit price for the specified fee
- For example, for data communication fees, this outputs the cost per byte.
- Quantity applicable to the billing/discount
- For example, for data communication quantities, this outputs the number of bytes transmitted.
- Data item amount. This outputs the unitPrice multiplied by quantity.
The “Tag/Group Data Items” include the tag name and group name configured for each SIM. For example, if you assign a tag name/group name as shown, the data item names and records are output as follows.
- Name : MySIM
- Tag Name: tag1
- Tag Value: foo
- Group ID : d674d68e-da8a-44f6-8868-51d1f069bc06
- Group Name : Group 1
If a SIM has been assigned a tag, or if it is set to belong to a group, the output for that SIM record will include the tag name/group ID or name. These values can then be used to make aggregate calculations for a particular tag value or group name.
Second Row Onward: Data Record Rows
The second row onward includes the actual data rows. The records are divided into “Billing Records” and “Discount Records,” and the Billing Records are always output before the Discount Records.
Billing records are created per IMSI/VPGID, day, and billing data item; and quantity and amount are totaled per day (UTC). Also, if the IMSI or VPG have been assigned tags, their values are output in the Tag/Group Data Items section.
Discount records include data items that are discounted from the usage fees due to free allowances and coupons. The “date” in the discount records specifies the applicable month for the discount. The “amount” specifies the discount amount as a negative number.
CSV data does not include data items corresponding to consumption tax or the total invoiced amounts, so if you want to match the records up with the invoiced amounts, you will need to calculate the total of all the values in “amount,” apply consumption tax, and round up any decimal figures.
Important Notes on CSV Parsing
CSV data is encoded in UTF-8 and includes BOMs, so please make sure to read in the data as UTF-8.
If a tag/group value has a comma (,), the value is enclosed within double quotes. If it has a double quote (“), it is escaped using double quotes. To use these strings, please make sure to use a CSV parser capable of dealing with escaped data.
As the data items are variable —and also as there is always the possibility that new data items, such as ICCIDs, may be added in the future— please make sure to read in the header row and record rows together. For example, if you implement a program that reads in the CSV using fixed definitions, with the first data item set as “imsi” and the second data item set as “date,” when you use SORACOM Canal/Direct, the “vpgId” data item would be output to the CSV, or the ICCID may be output sometime in the future as an additional data item. This means that your program will no longer be able to read the data in correctly. Hence, please process the CSV data first before using it, by converting the records into key/value pairs, using objects, hash tables, associative arrays, etc.
Importing to Excel
If you are using Office 2013 or another old version of Excel, due to encoding issues, text in Japanese and other similar languages may not be displayed correctly when opening the CSV file. In this case, follow the steps below to import the CSV file into Excel.
From the download folder or similar, open the downloaded CSV file using a text editor that supports UTF-8.
Select the text in the text editor and copy it.
Run Excel and open up a new worksheet.
Select the first cell in the worksheet (A1) and paste the text.
Use the Text Wizard.
Select “Delimited” and click on “Next.”
Select comma as a delimiter, untick “Treat consecutive delimiters as one,” and click on “Next.”
Set the data format to “General” and click on “Finish.”
The steps above will allow you to import data into Excel. If the IMSIs are not displayed as numbers, select the IMSI column and change its format to a number.