¶ Basic Start: Data Imports
You can import and bulk edit data from spreadsheet files (.csv) using data import. This feature can be very helpful when setting up the smartbilling data from other systems. To start, navigate to the "Imports" through the Administration UI panel:
Smartbilling separates the data imports into the various relevant data objects:
Core components of the data types in red will be reviewed as part of this Basic Start section.
NOTE: All data import CSV files can also contain columns for Custom Fields created by the SB user.
¶ Common Data Import Functions
Regardless of the data object type, all data imports function in a similar manner.
Once the user has navigated to the Imports page, the data import object types will appear on the left-hand panel. To illustrate the common data import functions, let us use the example of importing customer account information; a common use case.
¶ Use case: importing basic customer account data
Click on the "Accounts" link and the panel will expand to show the various customer account-related data objects:
- Click on "Customers"
- Click on "Add Import" on the next screen
The user will be presented with a screen which will allow the upload of a CSV file:
¶ General
- Import file: click on the "Choose File" button to select a file to upload. If there is an existing file already selected, the filename will appear instead of the button. As well, the mapping for the CSV and system data object will appear.
- Template: Some data object require specific templates to be selected. If directed by your smartbilling contact, use this field. Note: this is usually for customized data object types.
- On duplicate records: (Skip / Merge / Overwrite): What behaviour to follow in the event that records to import are determined to already have a duplicate, existing record in the system.
-
Skip: the system will skip the duplicate record
-
Merge: the system will add any field value wherein there is currently a null value in the record. For example, if "Contact - First Name" in the accounts data object is null, and the csv file contains value of "John". Note that if there is already an existing value, then the CSV data will be ignored. Note that the system will update any field contained in the import file based on the unique keys marked in the import, for example:
-
Overwrite: the system will overwrite any field value that is currently defined in the system with any value, including null that it finds in the CSV file. For example, if the CSV contains a column called "Invoicing Start Date" and this column is blank, then all customer account records found will have their "Invoicing Start Date" values set to null.
-
Note: the CSV file can contain columns that are not to be mapped. This is sometimes helpful to have during onboarding to provide additional annotation and information for records.
Merging and overwriting existing data objects in a working production instance can be dangerous. Consult with your smartbilling contact prior to doing so.
¶ File Format
- Click here for a list of available fields: Click on this link to bring up a data object-specific window listing the list of available fields for this data object that can be used as column headers in the data import CSV file.
- Download File Sample: On click, makes a CSV file available for download with all valid fields (including custom fields) for the data object. For example, on click for the "Customer" data object, a blank customer.csv file will be made available. This CSV file will have column headers defined, which are the available fields for the customer data object.
¶ Screen change on upload
When a file which meets the preliminary validation checks is loaded, the column headers and data mappings are presented for verification. For example:
Once the mappings are verified, click on "Save" at the bottom of the screen to start the import process.
It is highly recommended that users upload a small subset of data (10-20 records) and verify that outcomes are as expected; prior to loading larger data sets. Upon successsful test, users can delete the import to remove the data from the system.
¶ Processed CSV file
Once a file is processed, the user can review whether all records were successfully imported:
Error details can be obtained by either clicking on the "note" icon (will export errors as a csv file); or by clicking on the red number denoting the number of errors (will open new UI screen to show error details).
Users can also click on the "X" icon to completely delete the import and remove the imported data from the system.
If deleting multiple files, ensure that they are deleted while respecting the import order. Else, data corruption can be encountered.
¶ Onboarding: order of data imports
When creating a new test or live version of a smartbilling instance, the following sequence of file imports is recommended:
If this sequence is not followed, then the user may encounter errors because of broken data relationship dependencies.
¶ Data imports: Accounting
While not required, if a user requires charges or customer accounts to be linked to an internal GL account, then the GL account values must be loaded first.
¶ G/L Accounts (optional)
GL account.csv can be used to mass upload GL accounts instead of adding them one-by-one in the Administration panel. Please find below, the details for each field of the GL account.csv import file:
¶ Data imports: Accounts
This section describes the requirements to mass import basic customer information into the smartbilling application. Primarily, the customer.csv and location.csv file uploads.
Reminder: All data import CSV files can also contain columns for Custom Fields created by the SB user.
¶ Customer.csv
Customer.csv can be used to create customer records via CSV import. Please find in the PDF below, the details for each field of the customer.csv import file:
¶ Customer location.csv
Customer location.csv can be used to update existing customer records with additional locations. Please find below, the details for each field of the customer locations.csv import file:
¶ Data imports: Subscriptions
Once the catalog services, customer accounts, and customer locations are loaded into the system, then service or plan subscriptions can created.
Mass import of subscriptions requires at the minimum, two (2) files:
- subscription.csv (Subscriptions): This will create the primary container subscription data object.
A subscription data object will map a service to a customer account and location
- charge.csv (Charges): to add charges, such as one-time or monthly recurring charges (MRCs) or fees to bill in the subscription. The charge must be a valid charge within the existing service data object
Additional types of charges will require other CSV files to be uploaded:
- usage rate.csv (Rates): A commonly used file. Used to add usage rates and if required, define the usage rate plan for the subscription. The usage rate must be a valid charge within the existing service data object
- product.csv (Products): used to add product or equipment charges for a subscription. Typically, these are one-time charges and not usually mass-uploaded. The format of the product.csv file is similar to that of the charge.csv file
- contract.csv (Contracts): If the user wishes to use the smartbilling contracts features and functions; then this data import can be used to add contract types to multiple subscriptions
Product sub-charges and Charges sub-charges can also be used if a Service is configured to include these types of data objects. If to be used, they should be uploaded after subscription Products and Charges have been successfully loaded.
At least one of subscription-charges, subscription-products, and subscription-usage-rates child CSV file is required when creating a new subscription
The only valid charges, products, and usage rates are those that already exist within the defined service.
Reminder: All data import CSV files can also contain columns for Custom Fields created by the SB user.
¶ Subscription.csv
Subscription.csv can be used to add or update subscriptions en masse. The child CSV files: subscription-charges, subscription-products, and subscription-usage-rates can be used as required if the user requires adding those record types to newly created subscriptions. Please find below, the details for each field of the customer subscription.csv import file:
¶ A note about billing period configuration for usage
Within either the subscription or the subscription.usage rate (or both), a SB user will need to define what billing period the system will assign to uploaded usage records.
For example, on upload for the record below, the system marked the usage record with Bill_period = "2023-11-01 00:00:00". This was determined by the system based on the call (start) date; here: "2023-10-10 12:34:30"; as well as the parameters used for the subscription usage cut-off: "1st of Period" and the subscription.usage rate billing period offset: "1 period in arrears".
Note the billing period is defined at hour "00:00:00" (server time). If the user wants the usage up to 23:59:59 (11:59 PM) of that day, they must define the billing period date as the following day.
The related subscription header configuration:
The related subscription.usage rate configuration:
Note that the field "usage cut-off" can be defined either at the subscription level (which sets the default for all usage rate-level values); or at the usage rate level. Defining at the usage rate level allows the user to be able to have different configurations for different usages; for example for data vs. mobile minutes; or for local vs. international usage.
¶ (Subscription) charge.csv
(Subscription) charge.csv can be used to add or update subscription charges. Please find below, the details for each field of the (subscription) charge.csv import file:
Depending on the charge type (recurring, minimum recurring, one-time), other fields may be available.
¶ (Subscription) usage rate.csv
(Subscription) usage rate.csv can be used to add or update subscription charges related to variable billed (or unbilled) service usage. Please find below, the details for each field of the (subscription) usage rate.csv import file:
