Custom Integrations & Looker Reporting for Online & Offline Revenue

Posted by: Gina Bennett Date Posted: January 19, 2024
Back to Case Studies

Issue

Smoke Alert Fire Safety has a few different sources of revenue and needed a way to report on each separately so they can better understand where their revenue is really coming from.

  • Offline / Service Revenue – Stored in Quickbooks Online.  They have 3 main service areas they need reported on separately: Charlotte, Raleigh, and Atlanta
  • Online Revenue – From selling products via WooCommerce on the site.

Solution

We had to complete a few steps to get this up and running:

  1. Custom Quickbooks Online integration – with location segmentation
  2. WooCommerce integration
  3. Setting up the Looker Reports

 

Step One: We created a custom Quickbooks Online integration (now available for all clients on a Foundation plan) to pull in open and paid invoices.  Since QuickBook’s API doesn’t send the invoice’s status, we had to get a little creative with the mapping we used.

  • If an Invoice exists = Deal status is Open
  • If the Invoice balance is $0 = Deal Status is Closed Won

 

We also used the C360FX Deal’s business_unit field to record the service location.  Since Quickbooks doesn’t have a native way to store the location each person was serviced by, we determined it based on the contact’s zipcode.

  • If the contact’s zip / postal code starts with 30 > set C360FX Deal business_unit to “Atlanta”
  • If the contact’s zip / postal code starts with 28 or 29 -> set C360FX Deal business_unit to “Charlotte”
  • If the contact’s zip / postal code starts with 26 or 27 -> set C360FX Deal business_unit to “Raleigh”

 

Step Two: Standard WooCommerce Integration – no explanation needed here

Step Three: I created a separate Looker report for service revenue and online revenue.

To only see data for the service report, I filtered out ingestion data as well as open and closed lost deals.  I filtered to only pull in deals with a close date from the last month, and then filtered to only see the appropriate location’s data using the business_unit field.

To only see data for the online revenue reporting, I filtered to only Closed Won ingestion deals with a close date from the last month.

 

Achievement

Monthly reporting on offline and online sales data using IntegrationsFX data & Looker