🎉 Bedrock Data is now Formstack Sync! · Learn More 🎉

Jeremy Martin | July 30, 2018

Analyze & Visualize NetSuite Data in Looker

NetSuite is rich with inventory, financial, e-commerce, and customer relationship data. But reporting on NetSuite data can be complicated. You must either run bulk extracts, build a custom API integration, or customize NetSuite’s native reporting. And while NetSuite lets you report on transactions, expenses, and accruals with various dimensions and filters, at its core NetSuite is ERP software — it wasn’t built for interactive visualizations and SQL queries; Looker is.  

  

Connect a NetSuite Data Warehouse to Looker

Fusion loads all your NetSuite data into a data warehouse so you can query that data in tools like Looker. As a MySQL solution hosted on AWS RDS, Fusion provides you total relational database support for structured columnar stores and the SQL language. It also buffers you from hitting your API limits.

amazon rds

logo-mysql-170x115

After you connect NetSuite to Looker, you can also centralize data from many systems into a single database, then use Looker to build your reporting logic into LookML (the modeling layer). With LookML, you can then create LookML dashboards — a collection of SQL queries displayed as visualizations on a page — for e-commerce reports with: 

  • Orders and order fulfillments
  • Invoices
  • Sales quotes
  • Sales opportunities
  • Accounts receivable (A/R) aging
  • NetSuite & Quickbooks data 

 

Fusion also refreshes every 30 minutes, saving you time from having to recreate and schedule reports or backup data. Your LookML dashboards will therefore show the latest NetSuite data, with accurate financial visualizations you’d need build once and only once. 

 

Mappings

To save you time from always having to write joins, Fusion brings like objects together by mapping NetSuite objects to “fused objects”. These mappings also come in handy should you ever want to combine NetSuite data with other data sources like Quickbooks.

Here’s how Fusion maps NetSuite objects into Fused objects:

NetSuite Object Fusion Object 
vendor, account, subsidiary Company

workOrder, salesOrder, invoice,

interCompanyTransferOrder,

purchaseOrder, transferOrder,

vendorPayment, charge

Order

task, calendarEvent, phoneCall, note,

returnAuthorization, journalEntry, 

inventoryAdjustment, estimate,

depositApplication, deposit, creditMemo,

cashSale, cashRefund, calendarEvent

Activity
supportCase Ticket
employee, customer Owner
 opportunity Opportunity

In Looker, to access your NetSuite data you would query tables named:

  • fused_company
  • fused_order
  • fused_activity
  • fused_ticket
  • fused_owner
  • fused_opportunity

Then you could build a LookML dashboard with NetSuite orders, fulfillments, opportunities, or any other e-commerce info, which would look like this:

 setup-new-database-connection-510

Now let’s explore a few flavors of LookML dashboards you could build with NetSuite data. 

 

1. NetSuite Sales Quotes, Order Fulfillments, & Invoices

Most NetSuite users want to know the lines from sales orders, item fulfillments, and invoices so they can have a more comprehensive backlog report than what they can currently produce in NetSuite. They want to use the line data from purchase orders, quotes and sales orders to populate an inventory predictor or pipeline type report. When sales quotes turn into order fulfillment used for invoicing and payment, NetSuite lets you connect these records, but not visualize the full flow from ordering, fulfillment, to billing. It’s possible to connect two records, but not see the full flow of ordering, fulfillment, and billing.

Fusion’s data warehouse lets you track the full flow of ordering, order fulfillment, and billing. With all your sales orders, fulfillment records, and invoices stored in one place, you can track records from sales to finance. This ability helps you improve your sales pipeline forecasts and mitigate billing errors, perfect revenue recognition, accuracy, and organizational efficiency.

 

In terms of LookML, you could either create a general dashboard of NetSuite sales orderItems, like the one above, or you could get more detailed.

One possibility is linking the fused_order table with the fused_activity table via the links_order_activity table. This way you could run custom SQL queries on NetSuite objects like: 

  • workOrder
  • salesOrder
  • invoice
  • purchaseOrder
  • transferOrder
  • vendorPayment
  • charge
  • deposits

And once you save those queries as LookML dashboards, your visualizations would automatically refresh, allowing you to analyze: 

  • Fulfilled & non-fulfilled invoices
  • Total orders
  • Average order sale price
  • Total sales, year over year
  • Average order profit
  • Number of first purchasers
  • 30 day repeat purchase rate
  • Total spend by cohort
  • Purchaser demographic and geographic profiles 

 

2. Opportunities

If you want to tie marketing data (e.g. Marketo, HubSpot), sales data (e.g. Salesforce), and ERP data (Oracle/NetSuite) together into a single central warehouse, connecting Fusion to Looker acts like a Looker Block by giving you access to a blended data spectrum for measuring opportunities. Specifically, you can great a LookML dashboard with the following information:

  • Campaign ROI
  • Campaign/Offering
  • Conversion Rate
  • New Opportunities Created
  • Closed-Won vs. Closed-Lost / Lead to Win Funnel

 

 

 

3. Financial KPIs

Similarly, with NetSuite data in a warehouse you can build LookML dashboards that contain basic financial KPIs such as:

  • Revenue
  • Gross Margin
  • CAC
  • MTR
  • MRR/ARR 

One of the best things about executive-level financial dashboards is you can reuse them at the close of every fiscal period, drastically cutting the time to value for Monthly/Quarterly/Yearly KPIs and reducing human error. Once accounting closes the fiscal period, all you need to do is run your financial dashboard — no exporting .csv files or manipulating spreadsheets in Excel.

 

4. Accounts Receivable (A/R) Aging Report

Accounts receivable aging reports show unpaid customer invoices and unused credit memos by date ranges. This aging report is the primary tool used by collections personnel to determine which invoices are overdue for payment.

Building an A/R Aging Report in Looker will help you track account balances for overdue payments and credit memos. Such simple reports reduce the number of sent invoices and to see the exact amount customers owe you for any given period.

Because Fusion gives you access to NetSuite invoice, purchaseOrder, vendorPayment, and charge data, you could track late payments and charges with such metrics as:

  • Total unpaid invoices
  • Total unpaid invoice amount
  • Average days until payment received
  • Total gross charges
  • Total failed charges
  • Total refunded charges
  • Total net charges
  • Total charges 

  

5. Analyze NetSuite & Quickbooks Data — Together

Quickbooks is great for managing accounting, payroll, invoices, and billing. While valuable alone, QuickBooks data is even more valuable when combined with data from the other SaaS applications you use.

Once your Fusion data warehouse is connected to Looker, you can build constructs known as explores, which add an existing view to the Explore menu of Looker. These SQL views could contain both NetSuite data (Invoice, Bill, Bill payment, salesOrder, etc.) and Quickbooks data (Customer, Deposit, Estimate, Journal entry, Vendor, etc.) because Fusion de-dupes values and maps these objects into its fused_contacts and fused_order tables. (For more on how to analyze Quickbooks data with Fusion, read our post.) 

These suggestions offer just a taste of how you can analyze NetSuite data in Looker, with Fusion as your data warehouse. You can set up a free trial account of Fusion and be up and running quickly. Give Fusion a try today.

 

NetSuite-Looker

 

Subscribe Here!