Custom New Business Reporting

Posted by: Gina Bennett Date Posted: December 8, 2025
Back to Case Studies

Issue

MPL Associates wanted a Closed Won revenue reporting system that allowed them to see how much NEW business they were getting from the website.  I initially recommended the deal PIT Contact Stage, but I found some issues/bugs with the PIT Contact Stage that caused the reporting to be inaccurate.  With an unclear timeline of how long that fix would take, I decided to create my own system!

 

Solution

I used Looker to create the custom raw report I needed and Google Sheets to extract the earliest deal for each unique contact.

Looker Report: I used this over the Deals Dashboard because the Deals Dashboard doesn’t export ContactId (which I need for deduplicating), and it ensures consistent data formatting.

Google Sheets: For the most part, the AM just needs to upload data into the 1st tab of the Google Sheet.  The other 2 tabs (which extract the earliest deal for each unique contact based on Open and Close Date) will populate automatically with the dynamic filters and functions!

How the auto-populating tabs work

  • Column A deduplicates ContactIds and gives us a list of each unique ContactId
  • Column B gives us the earliest date (open or close) for that ContactId
  • Column C gives us the External Deal ID for the ContactId and Date pairing
    • Note: if there’s two matching Deals, it will pull the 1st one in the sheet
  • Columns D – O gives us the rest of the information for the Deal

Achievement

Created a method for accurate New Business reporting, without reliance on a client-owned field or PIT Contact Stage field, that allows the client and IM team to see the 1st deal for each contact

Snippet/Screenshot/Link

Here’s how you can mimic this for your client!

  • Create a copy of this Looker Report
    • Rename it with the client’s name
    • Edit the “Deals – [Client] Only” filter with your client’s RCFX Site ID
  • Create a copy of this Google Sheet
  • Follow the instructions below (or share them with the AM if they’ll be completing this ongoing!)
  • Report out of the First Order for Each Contact ID tabs
  • If you want to take this a step further, you could create a Looker chart that uses the First Order for Each Contact ID tabs as a data source!

Instructions:

  • Export the raw data
    • Go to this Looker report (insert link here)
    • Edit
    • Right-click the chart
    • Export Data > Google Sheets
  • Format the data in the Google Sheet
    • Select Columns F and G (Open Date and Close Date)
    • Format > Number > Date
  • Copy/Paste the data into the “Export from Looker – All Won Deals” tab of this sheet (link to the Google Sheet)
    • Overwrite existing data!
  • The other tabs of the sheet will update to show the first deal we have for each contact