Cleansing complex Vendor Usage Reports

Guide to getting the most out of CSV import profiles for those complex vendor usage reports.

Are you a scholar and a prophet, wanting to dive right in and do the dirty nerdy? Perhaps you just want to skip the details, or simply want to watch a video?

Choose your learning path below:

 

Overview

Let's do the dirty nerdy!

In lieu of vendor integrations, the CSV import profiles are a great alternative. Let's face it, you're probably already manually reconciling some, if not all, of your resale stack. Oh, the billing drudgery! Whether you're reconciling à la carte services, tiers, service bundles or packages, CSV import profiles are flexible. Helping you streamline billing reconciliation, so you're not going cross-eyed cross-referencing complex vendor usage reports, cursing at your monitors and on the verge of flipping your desk.

Let's be flock'n real here for a second. Vendor usage reports can be complex! Our flock is constantly improving the Synthesize billing module. Working towards the ultimate utopia of a world, where you'll never have to reconcile again! We haven't achieved that ultimate goal (yet), but you can easily streamline your billing processes by simply massaging or finessing your vendor usage reports. 

Let's get started by reviewing the minimum requirements. 

Minimum requirements

Map Data Fields

In Step 2 of the CSV Mapping Profile Wizard in Synthesize billing module, the following fields must be mapped to the corresponding CSV import file fields (headers) on the right. The data from those mapped fields in the import file are then used to map accounts and services in the following steps of the CSV Mapping Profile Wizard.

  • Account Name (company)
  • Service Name (product description or product SKU)
  • Quantity 

Note Quantity must be aggregated per product, per company.

Some usage reports may require finessing of data in order to be used in the Synthesize billing module.

Click here, to learn more.

Cleansing complex vendor usage reports

Now, let's get a little nerdy and put our Excel wizardry to the test! In a matter of minutes, you should have a cleansed report, so you can import usage into Synthesize billing module.

Let's flock'n roll, by following these few steps below:

  1. Download the vendor usage report into a .csv or .xlsx file format.
  2. Create a new sheet in your vendor usage report.
  3. Add three headers (columns) to the new sheet
    • Account Name
    • Service Name
    • Quantity
  4. Under the Account Name header of the new sheet you've created, enter the following formula – referencing the product SKU or product description header (column) from the source data and the company name header. This formula allows you to return non-adjacent values. In this sample formula, we are returning the company name (column 1) and product SKU (column 5)
    =SORT(UNIQUE(INDEX(Table1,SEQUENCE(ROWS(Table1)),{1,5})))
  5. Great, we now have to aggregate the quantities per product and company. Under the Quantity header in your new sheet, enter the following formula – referencing the quantity header from the source data. Press Enter and Double-Click on the AutoFill Handle to apply the formula to the entire column.
    =SUMIFS(Table1[quantity],Table1[sku],B2,Table1[company_name],A2)
  6. Save the new sheet as a .csv file format, and upload it into Synthesize billing module.
  7. Finally, create a new CSV import profile, or select from an existing profile. 
  8. Continue through the wizard and import usage into Synthesize billing module. Huzzah!