Google Sheets Reporting

Solve Report Builder

Solve Report Builder brings you an integrated reporting system, enabled in Google Sheets. It’s your personalized (and easy on the eyes) assistant for your unique business needs. Pull data directly from your Solve account into Google Sheets and easily generate personalized reports.

  • Summarize and visualize business health
  • Filter and analyse data
  • Update reports in one-click

Just like our old friend Excel, Solve for Sheets gives you the opportunity to leverage your data into insightful reports and charts. Unlike Excel, integrating with Google Sheets makes it easy to share and update your reports in real-time, without the old constraints.

“Why settle for someone else’s reports when you can easily configure your own?”

With Solve’s Report Builder, we’re offering reporting with the same qualities that has helped Solve drive your business: flexibility, simplicity, customization and usefulness. It’s the same here. Bulky generic reports distract you from your original question. Statistics have no value unless you know where they come from. Trim off the fat that’s slowing you down, reduce data-entry, and trade-up for your own leaner model that delivers exactly what you need.

Need more help? Secure your spot in our FREE Report Builder Webinar

You made it, so you’ll trust it and have confidence in it to make crucial informed decisions. Sounds like the personalized service we all love to have.

Penda Learning uses Solve’s Report Builder to shorten their sales cycle by 50%


Lets look at some useful types of reports & charts

Solve Reports are as flexible as Solve itself. Behold… we’ll start with the typical suspects, then you can get creative and make them your own.

First, setting the stage by monitoring your Contact leads

Sales and marketing teams follow a series of steps to convert suspects > prospects > leads into paying clients. In this example, you’ll create two custom fields that your team will use to track the progress of each lead. These fields will provide the data used in the next two sales charts.

A) Sales pipeline

Your Sales Team systematically nurtures leads from one stage of the sales cycle to the next, until they close or fall out of play. Too many sales one month and too few in the next is difficult to manage. A pipeline report shows how many leads are in each stage right now. Knowing where leads are in your sales cycle and who to phone shows you which stage needs attention to ensure a steady flow of sales and a continually engaged team.

B) Sales funnel

Your Marketing Team promotes your business to generate new clients through a set of processes for creating, delivering and communicating value to customers. They need to know how effective specific tactics are at generating leads and how many leads got to a certain stage in the sales process. The funnel highlights opportunities for improvement in our communications and sales approach.

Second, focusing on Opportunity sales performance

Once prospects are qualified, you can recognize and promote more information about them in a “Track a revenue opportunity” activity saved on their record. This is a great way to pull important deals up-front into a separate list to be managed by “the closers”… segmenting them from the rapid-fire action of prospecting. We’ll use opportunities as the underlying data for the next two sales charts.

C) Sales team performance

“Rainmakers” are competitive SOBs. Selling is like a race and knowing who is winning creates momentum. Perks aren’t enough - work that’s challenging and that stimulates internal competition keeps the energy high, helps people improve and moves the needle for your business.

D) Quarterly sales by source

Top managers are obsessed with fluctuations in sales. From one period to the next, they want to know how things are trending, so they can adjust their tactics and forecast accordingly. This next example is a big deal - with our quarterly sales report we’ll combine sales data from two sources to provide essential context: 1) the opportunity activities, and 2) fields of data from the contact record related to that opportunity. So now you know how much you sold, when you sold it and through what sales channel… Or, break it down any way you want it.

Lets create a report …


Creating your report

Ensure you are using the new version of Google Sheets.

Ensure you have add-ons enabled for your G Suite domain.

Activating a Google worksheet

  1. Open the Solve Report Builder add-on page and click the “Install App” icon (top-right) …

  2. Follow the prompts to authorize the add-on.

  3. Open a Google Sheet by clicking the “Launch app” icon that just appeared in the Report Builder add-on page …

  4. Fill-in Add-ons > Solve Report Builder > Customize report.

  5. Click “Sign-in” to authorize and access your Solve account.

You’re done activating, now lets make a report …


A) Sales pipeline

Your Sales Team systematically nurtures leads from one stage of the sales cycle to the next, until they close or fall out of play. Too many sales one month and too few in the next is difficult to manage. A pipeline report shows you how many leads are in each stage right now. Knowing where leads are in your sales cycle and who to phone shows you which stage needs attention, to ensure a steady flow of sales and a continually engaged team.

You’ve already activated a Google Sheet, now let’s make a pipeline report.

1) Customize Solve contacts with fields used in the Pipeline report

Solve menu > Customize Form Fields > Editing Contact fields

  • Create two new Select option custom fields with options for Sales Stage and Sales Status by selecting: Add a field > Custom fields > Select option for each
  • Enter values for these fields inside Solve contacts as illustrated below

2) Pull custom Solve data into sheet

Return to the activated Google Sheet you created

  • Add-ons > Solve Report Builder > Customize report

    Select “Contacts” as your report type and set …

    • Add criteria > Custom field > Sales Stage > is not empty > Accept
    • Click “Add criteria” and set this one to > Custom field > Sales Status > is not empty > Accept

    In the Columns section set

    • Leave Standard fields with just “Name” selected
    • For Custom fields > edit icon > select two fields Sales Stage and Sales Status > Accept
    • For System columns > deselect “ID” > Accept
  • Click “Run report”
  • Admire your table of data …

3) Summarize the sheet data

Continuing with the Google Sheet

  • Select the two columns headers B & C (Sales Stage and Sales Status)
  • Sheet menu > Data > Pivot table …
  • In the Report Editor panel set

    Rows = Sales Status and deselect “Show Totals”

    Columns = Sales Stage and deselect “Show Totals”

    Values = Sales Stage and “Summarize by” = “COUNTA”

    Filter = Sales Stage and under “All Items” deselect 6-Client

  • Admire your summary report …

4) Visualize summarized sheet data as a chart

Continuing with the Google pivot table sheet

  • Sheet menu > Insert > Chart
  • Charts tab > Bar > Stacked bar chart
  • Deselect the option “Switch rows / columns”
  • Customization tab > set …

    Title = “Sales Pipeline”

    Horizontal axis Title = “Leads”

    Left vertical axis = “Status”

  • Insert
  • Celebrate your chart

You just grabbed bonus value from your Solve data by laying the ground for a consistent sales flow, that neatly displays leads currently in each stage. Lets see that same data from a different angle, with a Sales Funnel chart …


B) Sales funnel

Your Marketing Team promotes your business to generate new clients through a set of processes for creating, delivering and communicating value to customers. They need to know how effective specific tactics are at generating leads and how many got to a certain stage in the sales process. The funnel highlights opportunities for improvement in your communications and sales approach.

You’ve already activated a Google spreadsheet, now let’s make a funnel report.

1) Customize Solve contacts with field used in the Funnel report

Solve menu > Customize Form Fields > Editing Contact fields

  • Create a new Select option custom field with options for Sales Stage by selecting: Add a field > Custom fields > Select option
  • Enter values for this field inside Solve contacts as illustrated below

2) Pull custom Solve data into sheet

Return to the activated Google Sheet you created

  • Add-ons > Solve Report Builder > Customize report

    Select “Contacts” as your report type and set …

    • Add criteria > Custom field > Sales Stage > is not empty > Accept

    In the Columns section set

    • Leave Standard fields with just “Name” selected
    • For Custom fields > edit icon > select only Sales Stage > Accept
    • For System columns > deselect “ID” > Accept
  • Click “Run report”
  • Admire your table of data …

3) Summarize the sheet data

Continuing with the Google Sheet

  • Rename sheet to “data”
  • Create a new sheet
  • Copy/paste the entire table below into cell A:1

    Stage
    1-Lead =sumif(A:A,">="&A2,C:C) =countif(data!B:B,A2)
    2-Prospect =sumif(A:A,">="&A3,C:C) =countif(data!B:B,A3)
    3-Qualified =sumif(A:A,">="&A4,C:C) =countif(data!B:B,A4)
    4-Negotiating =sumif(A:A,">="&A5,C:C) =countif(data!B:B,A5)
    5-Closing =sumif(A:A,">="&A6,C:C) =countif(data!B:B,A6)
    6-Client =sumif(A:A,">="&A7,C:C) =countif(data!B:B,A7)
  • Admire your summary report …

4) Visualize summarized sheet data as a chart

Continuing with your current Google Sheet

  • Select A1:B7
  • Sheet menu > Insert > Chart
  • Charts tab > Bar > Bar chart
  • Select the option “Switch rows / columns”
  • Customization tab

    Title = “Sales Funnel”

    Horizontal axis = “Leads”

    Left vertical axis = “Stages”

  • Insert
  • Celebrate your chart

You’ve just validated the effectiveness of your marketing team to pull in new leads while highlighting which stages need a little TLC. As focus switches to sales, let’s keep this party going and rile up your sales team with a Sales Team Performance chart …


C) Sales team performance

“Rainmakers” are competitive SOBs. Selling is like a race and knowing who is winning creates momentum. Perks aren’t enough - work that’s challenging and that stimulates internal competition keeps the energy high, helps people improve and moves the needle for your business.

Activate another Google spreadsheet, now let’s make a Sales Team report.

1) Track sales data in Solve

Solve menu > Manage Contacts > (open/create record) > Add an Activity

2) Pull custom Solve data into sheet

Return to the activated Google Sheet you created

  • Add-ons > Solve Report Builder > Customize report

    Select “Opportunities” as your report type and set …

    • For > edit icon > select “contacts” only > Accept
    • And > edit icon > Change “do not filter by status” to Won > Accept
    • Having closing date of > edit icon > between > Set range for “closing date” > Accept

    In the Columns section set

    • For Standard fields > edit icon > select two fields Potential Value and Responsible > Accept
    • Leave Custom fields, Related parent data and Related parent system columns as “None” selected
    • For System columns > deselect “ID” > Accept
  • Click “Run report”
  • Admire your table of data … and format cells to your taste ($)

3) Summarize the sheet data

Continuing with the Google Sheet

  • Select the two columns headers A & B (Potential Value and Responsible)
  • Sheet menu > Data > Pivot table …
  • In the Report Editor panel set

    Rows = Responsible and deselect “Show Totals”

    Values = Potential Value and “Summarize by” = “SUM”

  • Admire your summary report …

4) Visualize summarized sheet data as a chart

Continuing with the Google pivot table sheet

  • Sheet menu > Insert > Chart…
  • Charts tab > Other > Gauge chart
  • Customization tab > set ranges and colours
  • Insert
  • Celebrate your chart

This chart instills each member with a “desire to excel” by celebrating individual results. Give your team an extra boost by combining opportunity and contact data… Forecast trends in sales, find your most effective sales channels, or determine when you are most profitable. Lasers on.


D) Quarterly sales

Top managers are obsessed with fluctuations in sales. From one period to the next, they want to know how things are trending, so they can adjust their tactics and forecast accordingly. This next example is a big deal - with our quarterly sales report we’ll combine sales data from two sources to provide essential context: 1) the opportunity activities, and 2) fields of data from the contact record related to that opportunity. So now you know how much you sold, when you sold it and through what sales channel… Or, break it down any way you want it.

Activate another Google spreadsheet, now let’s make a Quarterly Sales report.

1) Track sales data in Solve

Solve menu > Customize Form Fields > Editing Contact fields

  • Create a new Select option custom field with options for Lead Source by selecting: Add a field > Custom fields > Select option
  • Enter values for this field inside Solve contacts as illustrated below
  • Add Opportunity activity to the contact and set status to “Won”

2) Pull custom Solve data into sheet

Return to the activated Google spreadsheet you created

  • Add-ons > Solve Report Builder > Customize report

    Select “Opportunities” as your report type and set …

    • And > edit icon > Change “do not filter by status” to Won > Accept
    • Having closing date of > edit icon > between > start and end of a year > Accept

    In the Columns section set

    • For Standard fields > edit icon > select two fields Potential Value and Closing Date > Accept
    • Set Related parent data to “Related Parent fields” > Accept
    • For System columns > deselect “ID” > Accept

    In the Format section set

    • Closing Date > edit icon > Quarter > Accept
  • Click “Run report”
  • Admire your table of data …

3) Summarize the sheet data

Continuing with the Google Sheet

  • Select all rows and columns (CTRL-A / CMD-A)
  • Sheet menu > Data > Pivot table …
  • In the Report Editor panel set

    Rows = Closing Date and deselect “Show Totals”

    Columns = (Contact) Lead Source

    Values = Potential Value and “Summarize by” = “SUM”

  • Admire your summary report …

4) Visualize summarized sheet data as a chart

Continuing with the Google pivot table sheet

  • Sheet menu > Insert > Chart…
  • Charts tab > Line > select “Combo Chart”
  • Customization tab

    Title = “Quarterly Sales by Source”

    Horizontal axis > Title = “Quarter”

    Left vertical axis > Title = “Sales ($)”

  • Series

    Change “All Data series” to Grand Total > Type = Columns

    Magazine, Referral & Website > Type = Line

  • Format to your exact specification
  • Insert
  • Idolize your chart in form of tribal ritual/sacrifice

In analyzing your personalized charts, perhaps you’ve already noticed something intriguing, or were previously unaware of. These charts give you proof. Go make those calls to address this new information. We’ll still be here after you’ve taken that superhero S off your chest.

Feel confident with your understanding of Solve for Sheets. Continue to tweak and expand these reports with your unique business data. Pull in and integrate your other system’s data to transform your reports into a full-blown dashboard. Continual improvement, enter Kaizen.

As an example, pinpoint the most lucrative opportunities for your field team, capitalizing on their travel time, by integrating Google Maps.

The reports you’ll create with Solve for Sheets are attractive, colourful, and invoking as charts and dashboards should be. More importantly, they’re only the covers to the stories told within. Stories that inspire new processes, change the way you see aspects of your organization, bring attention to areas formerly disregarded, and reaffirm practices your company counts on. “The real value is behind the cover, turn the page…”

Posted by