Goal Seek in Google Sheets

Goal Seek for Sheets is an Add-On for Google Sheets for doing Goal Seek type data analysis.

In October 2019, Google launched an official Add-On, called “Goal Seek for Sheets”, and it is that Add-On that this tutorial references.

1. What is Goal Seek?

It’s a tremendously powerful and useful technique in data analysis. It’s a process where you set an output you want to achieve (e.g. break even, sell 10k units, save $1m) and let the computer find the input value that will get you there (e.g. 500 attendees, $100k capital lump sum, save $8k/year).

There are three components: 1) the unknown input variable, 2) the equation or calculation that is performed on the input variables to get the output, and 3) the known output.

The Goal Seek algorithm performs a series of “what-if” calculations by plugging in different input values. Each guess (hopefully) gets closer and closer to the solution.

For example, a classic use case of Goal Seek is to determine the number of sales required to break even, given other variables like fixed costs etc.

2. How do you use Goal Seek in Google Sheets?

Imagine Jennifer runs an annual conference for Google Sheet developers called “Sheet Freakz 🀟”.

She has a great venue picked out with room for 500 and she’s confident she can fill it. She knows what her costs are — the rental fee for the room, the cost of catering, the cost of promoting the conference — and she has agreed a $1,500 fee with 15 Google Sheets experts to come and talk about the latest and greatest in Sheets developments.

(Editor note: I wish this was a real conference!! πŸ˜„)

What price must she charge to cover her costs?

This is a classic break even cost analysis example that the Goal Seek Add-On is ideally suited for solving.

Setting up the Sheet

The first step is to simply add all of the known variables into a sheet, like so:

Goal Seek Variables in Google Sheet

These are the variables that Jennifer knows at the start of her problem.

Next, add a line for the registration fee per attendee, but set it to 0 for now. I’ve highlighted the cell yellow to indicate that it’s the solution cell that I want Goal Seek to solve for:

Goal Seek Variables

Finally, add a profit line, which is my revenue (# of attendees * registration fee) less expenses (fixed costs + (# of speakers * speaker fee)):

= ( B7 * B8 ) - ( B4 + ( B5 * B6 ) )

Goal Seek setup

Of course, initially, my profit is -$47,500 because I have no attendees and hence $0 revenue.

It’s time to use Goal Seek and let it find the break even registration fee for us.

How do you add Goal Seek in Google Sheets?

Goal Seek is an Add-On, which means you need to add it to your Google Sheet before you can use it.

Search for “Goal Seek” in the Add-Ons marketplace, found under the menu Add-ons > Get add-ons

The official Google Add-On information page will appear.

Click to install. That’s all it takes to add it to your Sheets.

Goal Seek for Sheets

You can also find it in the G Suite marketplace directly by clicking here.

Using Goal Seek

Open the Goal Seek sidebar: Add-ons > Goal Seek > Open

There are three pieces of information you need to enter:

i) Set Cell

Jennifer wants to know what price to charge to break even. In other words, what’s the minimum ticket price to ensure her profit is $0 and she doesn’t lose any money on the conference.

The “Set Cell” is the one we want to specify a value for. It’s the target we’re aiming for.

It’s the cell with the calculation formula in.

** With the Goal Seek sidebar open, click on the cell with the formula, which is cell B10 in this case (1). Add that reference to the Goal Seek solver by clicking on the grid icon next to the Set Cell box (2). This will auto-populate with the cell B10 reference. **

Select cells in Goal Seek for Sheets

ii) To Value

Next, type in the value of the output you want to achieve in the “Set Cell” box.

In this example, we want to set the profit value to 0, so we simply type 0 into this input box.

iii) By Changing Cell

What input variable do we want to vary to solve our equation?

In this case it’s the registration fee. It’s the variable that Jennifer is trying to find, such that her profit is $0 and she breaks even.

Select the cell that holds this variable and then click on the grid icon next to the input field to auto-populate it.

Read it out loud to understand what you’re asking the application to do: “Set Cell X To Value Y By Changing Cell Z”.

In our case, “Set Cell Profit To $0 By Changing Cell Registration Fee” or even cleaner “Set Profit To $0 By Changing Registration Fee”.

When you have all three inputs filled in for the Goal Seek application, the Solve button will turn blue and become active.

Press it.

The registration fee value will start jumping around all over the place as the computer tries different guesses to see what brings the profit value closer to 0.

Eventually it’ll find a solution and notify you that it’s done!

Goal Seek to determine break even cost

In this example, it’s found that the break even registration fee is $94.99999 dollars, or $95. Great!

Click here to open a Google Sheet template with all the examples from this tutorial >>

(Feel free to make your own copy: File > Make a copy…

If the file won’t open without permission, please open in an incognito window and copy from there.)

Manual Checks

It’s always a good idea to check the final solution that the Add-On finds, and not just trust it blindly.

In our example, it’s very simple to check that the two sides of the equation balance.

Jennifer’s expenses to run the conference are:

Expenses
$25,000 fixed costs + ( 15 speakers @ $1,500 each ) = 25,000 + ( 15 * 1,500 ) = $47,500

And her revenue on the other side of the equation will be:

Revenue
500 attendees * $94.99 registration fee (Goal Seek solution) = $47,499.99

The difference is simply a rounding error.

This is good.

The result from the Goal Seek is indeed a solution that gets Jennifer the break even registration price.

Another way to look at how the Goal Seek solver works is to visualize it. A very simplified version might look something like this:

Attempt 1

The computer has no idea what the solution is, so it makes a guess. For example, it might overestimate the result:

Guess 1: Overestimate

Attempt 2

The computer makes another guess. This time it might underestimate the result:

Guess 2: Underestimate

(It won’t always be a neat over/under/over/under guess. For example, if it guesses low, it might take many guesses before the first “over” guess happens. So this over/under flow is just to illustrate the concept.)

Attempt 3

With each additional “guess” the computer gets more accurate, because it uses the information from prior guesses to get closer to the solution. It might still overestimate, as shown here, but it’s getting closer to the solution:

Guess 3: Overestimate

Attempts 4 onwards

So on and so forth, as the computer makes guesses that get closer and closer, under, over, under, over, under, over, etc. until the solution is found:

Converging on solution

The program converges on the solution.

3. Other features of the Goal Seek Add-On

These features are all found in the sidebar underneath the input section for the Goal Seek variables.

Options

Under the Options menu, you can adjust the default settings for the Goal Seek solver.

You can change the 1) max number of iterations, 2) the tolerance (how accurate you need to be) and 3) the maximum time limit for the process, in seconds.

Goal Seek Settings

I’d suggest that the default settings will suffice for the majority of scenarios, but it’s good to know that you can make these changes should you need to.

Solve Status

The Solve Status box displays helpful information about the current Goal Seek solution.

It lets you know when the algorithm is finished, what the final status is, how many iterations it required and how long it took.

Solve Status in Goal Seek for Sheets

History

Access previous runs of the Goal Seek solver under the History menu.

Use the drop down menu to choose a prior solution based on a timestamp.

Goal Seek History

Error Messages

Occasionally the Goal Seek solver fails to find a solution.

One reason might be that the computer guesses get successively further away from the actual solution. They diverge away from the solution.

Should this happen, you’ll see an error message like this:

Goal Seek error message

4. More Goal Seek examples

Conference Break Even Example For Attendee Numbers

In the conference example above, instead of knowing how many people would attend, suppose Jennifer knew the registration fee.

She charges $299 for the registration fee and wants to know how many attendees she requires to break even?

The “changing cell” in this case becomes the number of attendees, rather than the registration fee.

The setup would be:

Goal Seek conference Example 2

The formula in cell B10 does not change from the original example. It’s still:

= ( B7 * B8 ) - ( B4 + ( B5 * B6 ))

The Goal Seek settings are:

Settings for conference example 2

Run Goal Seek and the answer comes back as 158.86.

In other words Jennifer needs 159 attendees paying a registration fee of $299 to break even.

Mortgage Calculation Example

Suppose you’re looking to buy a new house. You have an upper limit for your monthly payments of $1,500.

The other known variables in this case are: it’s a 30 year term with an annual interest rate of 4.5%.

What’s the maximum amount you can borrow?

Goal Seek can solve this for you.

Firstly, setup the sheet with the known variables in your Sheet:

Mortgage Goal Seek Example

The payment equation uses the PMT function in Google Sheets in cell B8:

= -PMT( B6 / 12 , B5 , B7 )

In Goal Seek, set the “Set Cell” to be this equation in cell B8.

Set the “To Value” to $1,500 (the maximum monthly payment that can be tolerated).

Set the “By Changing Cell” to the Amount Borrowed in cell B7 (currently $0.00).

Click Solve and let Goal Seek find your solution.

The algorithm will churn through the possible solutions until it settles on one that satisfies your tolerance (accuracy) setting.

In this case, the maximum amount you could borrow is $296,041.75.

Mortgage Solver Solution

Retirement Calculation Example

Suppose you want to retire with a pot of $1.5m in 40 years time. You’re confident of getting a 5% return on your investments.

What’s the annual contribution you need to make each year to hit this target?

Let’s use Goal Seek to find out.

Firstly, setup the sheet with the known variables in your Sheet:

Retirement Goal Seek Example

The calculation of the retirement pot value uses the Future Value function, the FV function, in Google Sheets in cell B7:

= FV( B5 , B4 , -B6 , 0 , 0 )

In Goal Seek, set the “Set Cell” to be this equation in cell B7.

Set the “To Value” to $1,500,000 (your target retirement pot).

Set the “By Changing Cell” to the Annual Contribution in cell B6 (currently $0.00).

Click Solve and let Goal Seek find your solution.

In this case, you need to contribute $12,417 each year to hit your retirement pot target of $1.5m.

Retirement Solution

Click here to open a Google Sheet template with all the examples from this tutorial >>

(Feel free to make your own copy: File > Make a copy…

If the file won’t open without permission, please open in an incognito window and copy from there.)

Resources

Google Documentation on the Goal Seek feature.

What the world’s richest man can teach us about averages

This is a story about a bar, 10 regular folks and the world’s richest man. Somewhere along the way, we’ll seek to demonstrate the robustness of the different average measures, but more on that in a minute.

I want you to picture your favourite bar or pub.

For me, it might be a pint of ale at The Dickens Inn, near the River Thames in London:

Dickens Inn London pub

I should just finish this blog post here, and we could all spend the rest of the day in happy reverie, supping our favourite tipple.

Alas, that won’t do! We have work to do and things to learn, so let’s get started.

The dataset

Imagine ten friends, all regular folks, sitting at the bar, eating and drinking, chatting and laughing. A most convivial scene. The beer tastes delicious of course, the floor is dappled with sunlight and the comforting aroma of Pie & Mash wafts by their nostrils. Anyway, I digress.

Let us play a little game. Our subjects don’t mind because they’re fictional.

We ask them all to write down their salaries in our Google Sheet, so we have the following results:

Ten salary values

Good. That’s our dataset.

Calculating the averages

Continue reading What the world’s richest man can teach us about averages

Filtering with dates in the QUERY function

If you’ve ever tried to filter on a date column in the Query function in Google Sheets, then you know how tricky it can be.

In a nutshell, the problem occurs because dates in Google Sheets are actually stored as serial numbers, but the Query function requires a date as a string literal in the format yyyy-mm-dd, otherwise it can’t perform the comparison filter.

This post explores this issue in more detail and shows you how to filter with dates correctly in your Query formulas.

The problem

You might for example try the following syntax:

=QUERY(Data!$A$1:$H$136,"select C, B where B > '1/1/2000'",1)

Unfortunately, the output of such a query is blank:

Incorrect dates in the Query formula

If instead we remove the single quotes from around the date and try again, we get a #VALUE! error because the Query formula can’t perform the comparison:

Incorrect dates in the Query function

Alas, what are we to do!

Neither of these “standard” formats work, because the dates are not in the correct format for the Query function.

Correct syntax for dates in the Query function

Per the Query Language documentation, we need to include the date keyword and ensure that the date is in the format yyyy-mm-dd to use a date as a filter in the WHERE clause of our Query function.

Putting aside the Query function for a moment, let’s consider that "select..." string.

The new syntax we want will look like this:

date_column > date '2000-01-01'

Our challenge is to create a text formula to create this syntax for us, inside our query function.

Dealing with the text function first, starting with our required date of 1/1/2000 and working outwards:

First, we convert it to a serial number format with the DATEVALUE() wrapper:

=DATEVALUE("1/1/2000")

The output of this formula is a number:

36526

Then the TEXT() function converts it to the required format for the Query formula by specifying a format of "yyyy-mm-dd":

=TEXT(DATEVALUE("1/1/2000"),"yyyy-mm-dd")

The output of this formula is a date in the desired format:

2000-01-01

Next we add single quotes around the new date format, with the "'" syntax. Finally, we insert the word date into the query string, to give:

="select C, B where B > date '"&TEXT(DATEVALUE("1/1/2000"),"yyyy-mm-dd")&"'"

which gives or desired output:

select C, B where B > date '2000-01-01'

That’s the syntax challenge done!

We can now plop that string into the middle argument of our Query function as per usual, and it’ll do the trick for us.

In this case, I was using a table of Space Shuttle mission data from Wikipedia, which contains a column of launch dates.

I used the IMPORTHTML() function to import that table into my Google Sheet, into a tab called Data in the range A1:H136. There’s a link to this dataset and worksheet at the end of the post.

This Query formula returns all of the Space Shuttle missions after 1 January 2000:

=QUERY(Data!$A$1:$H$136,"select C, B where B > date '"&TEXT(DATEVALUE("1/1/2000"),"yyyy-mm-dd")&"'",1)

The output of our formula is now returning the correct, filtered data:

Correct syntax to use dates in the query function

Referencing a date in a cell

The formula is actually simpler in this case, as we don’t need the DATEVALUE function. Assuming we have a date in cell A1 that we want to use in our filter, then the formula becomes:

=QUERY(Data!$A$1:$H$136,"select C, B where B > date '"&TEXT(A1,"yyyy-mm-dd")&"'",1)

Example showing filter between two dates

Again, it’s relatively simple to extend our formula by adding a second date clause after the AND keyword:

=QUERY(Data!$A$1:$H$136,"select C, B where B > date '"&TEXT(A1,"yyyy-mm-dd")&"' and B <= date '"&TEXT(B1,"yyyy-mm-dd")&"'",1)

Using today’s date as a filter

Substitute the TODAY() function into our formula:

=QUERY(Data!$A$1:$H$136,"select C, B where B > date '"&TEXT(TODAY(),"yyyy-mm-dd")&"'",1)

Can I see an example worksheet?

Yes, here you go.

How to build a real-time sales dashboard for E-junkie using Google Sheets and Apps Script

It’s day two of a four day product launch. You’ve worked hard all year to create a fantastic product, test your sales systems and tell the world about this amazing offer. You know you’ve sold 100 products so far, but…

…you don’t know whether your ads are effective, which affiliates are really killing it versus which have forgotten about your launch, or even whether your own emails are converting.

Looking at your sales log only, and having to decipher what’s happened since the last time you looked an hour ago, is like trying to drive in the dark without headlights.

Thankfully there is a better way to track your sales, so you can see your data, get insights about what’s working and what’s not, and immediately act to increase your bottom line.

This post looks at how to build a real-time dashboard for the E-junkie digital sales platform using Google Sheets:

Google Sheet e-junkie real-time dashboard
Google Sheet e-junkie real-time dashboard (fictitious data)
Want your own copy of this dashboard?
Click here to access your copy of this template >>

E-junkie is a digital shopping cart, used for selling digital products and downloads. The system handles the shopping cart mechanics, but does not do any data analytics or visualizations.

You can view a transaction log (i.e. a list of all your sales) but if you want to understand and visualize your sales data, then you’ll need to use another tool to do this. Google Sheets is a perfect tool for that.

You can use a Google Sheet to capture sales data automatically in real-time, and use the built-in charts to create an effective dashboard.

You’d be crazy not to have a tracking system set up, to see and understand what’s going on during sales events or product launches. This E-junkie + Google Sheets solution is effective and incredibly cheap ($5/month for E-junkie and Google Sheets is free).

The Write Life ran a Writer’s Bundle sale this year, during the first week of April. It’s a bundled package of outstanding resources for writers, including ebooks and courses, heavily discounted for a short 4-day sales window.

I created a new dashboard for The Write Life team to track sales and affiliates during the entire event. This year’s dashboard was a much improved evolution of the versions built for the Writer’s Bundle sales in 2014 (which, incidentally, was my first blog post on this website!) and 2015.

The biggest improvement this year was to make the dashboard update automatically in real-time.

In previous years, the dashboard was updated every 3 hours when new data was manually added from a download of E-junkie sales data. This time, using Apps Script, I wrote a script and set up E-junkie so that every sale would immediately appear in my Google Sheet dashboard.

So, it truly was a real-time dashboard.

Animated e-junkie sales chart in Google Sheets
Animated chart showing fictitious sales data during a flash sale

The real-time dashboard in Google Sheets

Here’s the final dashboard, annotated to show the different sections:

Annotated Google Sheets dashboard
Annotated Google Sheets dashboard with fictitious data (click to enlarge)

There are too many steps to detail every single one, but I’ll run through how to do the E-junkie integration in detail and then just show the concepts behind the Google Sheet dashboard setup.

How to get data from E-junkie into Google Sheets

New to Apps Script? Check out my primer guide first.

Open up a new Google Sheet and in the code editor (Tools > Script editor…) clear out any existing code and add the following:

function doPost(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  
  if (typeof e !== 'undefined') {
    var data = JSON.stringify(e);
    sheet.getRange(1,1).setValue(data);
    return;
  }
}

Save and then publish it:

Publish > Deploy as a web app...

and set the access to Anyone, even anonymous, as shown in this image:

access Google sheets web app

You’ll be prompted to review permissions:

Apps script review permissions

followed by:

Apps script confirm permissions

Click Allow.

This is a one-time step the first time you publish to the web or run your script.

Now we want to test this code by sending a POST request method to this Sheet’s URL to see if it gets captured.

We’ll use a service called hurl.it to send a test POST request.

Open hurl.it, select the POST option in the first dropdown menu, add the URL of your published sheet into the first text box and, for good measure, add a parameter, like so (click to open large version):

Hurl to test POST request

Click “Launch Request”, head back to your Google Sheet and you should now see some data in cell A1 like this (click to open large version):

POST data in Google Sheet

where the data is something like this (the custom parameter shown in red):

{"parameter":{"testValue":"Ben"},"contextPath":"","contentLength":13,"queryString":null,"parameters":{"testValue":["Ben"]},"postData":{"type":"application/x-www-form-urlencoded","length":13,"contents":"testValue=Ben","name":"postData"}}

Voila! The data sent by the POST action is sitting pretty in our Google Sheet!

It’s essentially exactly the same mechanics for the E-junkie example.

So now we know it’s working, let’s change the Sheet and the code to handle E-junkie data.

For the Sheet: Delete the data in cell A1, and add a row of headings that match the headings in lines 12 to 33 of the code below (omitting the data.):

E-junkie Google Sheet set-up

(This screenshot doesn’t show all the columns, some are off-screen to the right.)

For the code: Delete all the previous code and replace with this (note that I’m still referring to Sheet1, so if you’ve changed the name of your Sheet to something else you’ll need to change it in the code on line 4 as well):

function doPost(e) {
  
  var ss= SpreadsheetApp.openById("<Sheet ID>");
  var sheet = ss.getSheetByName("Sheet1");
  
  var outputArray = [];
  
  if(typeof e !== 'undefined') {
    var data = e.parameter;
    
    outputArray.push(
      data.ej_txn_id,
      data.invoice,
      data.payment_date,
      data.item_name,
      data.from_email,
      data.receiver_email,
      data.discount_codes,
      data.mc_gross,
      data.payment_type,
      data.payer_id,
      data.payer_email,
      data.first_name,
      data.last_name,
      data.residence_country,
      data.payer_status,
      data.payment_status,
      data.payment_gross,
      data.affiliate_id,
      data.item_affiliate_fee_total,
      data.mc_currency,
      data.payer_business_name,
      data.payment_fee
    );
    
    sheet.appendRow(outputArray);
  }
  
  return;
}

Here’s the e-junkie code on GitHub.

Save your code and update the published web app (Publish > Deploy as a web app… and click Update).

Copy this updated application URL to your clipboard. Log in to E-junkie and navigate to Edit Preferences:

E-junkie menu

Paste in your URL into the Custom/Third-Party Integration Common Notification URL text box:

E-junkie custom integration

And that’s it!

Whenever you make a sale through E-junkie, you should now see the data show up in your Sheet.

Further reading on the E-junkie integration: Official E-junkie integration documentation

Thanks:

Thanks to Google Developer Expert RiΓ«l Notermans who pointed out how to fix the redirect issue I was dealing with in earlier versions of the script.

Thanks to all the kind folks on Stack Overflow who helped me get this working.

Build Business Dashboards With Google Sheets and Data Studio

Digital marketing dashboard in Google Sheets
Learn how to build beautiful, interactive dashboards in my online course.
9+ hours of video content, lifetime access and copies of all the finished dashboard templates.
Learn more

How I created the dashboard in Google Sheets with E-junkie data

Really the crux of this whole example was getting the E-junkie data into my Google Sheet in real-time. Once I had that up and running, I was free to do anything I wanted with the data.

Data staging

The E-junkie sales data looked like this in my Google Sheet once the transactions started to come in (blurred to hide details):

E junkie data

From this raw data, I created a staging table for the line tracking chart:

staging table for line chart

This table was driven by checking whether the hour date in column E of the current row (green) was less than the current time, and then counting sales up to that point. The 2014 and 2015 datasets were included for reference.

The extra columns J, K and M were created for the chart annotations (for more information on how to create annotations on your Google Sheets charts, check out this post).

To ensure everything updated in near real-time, I changed the calculation settings (File > Spreadsheet settings... > Calculation) to: On change and every minute

Progress tracking chart

The line tracking chart showed progress during the sale period, against the 2014 and 2015 benchmarks.

In action, it looked like this (showing fictitious data, and sped up to show progress through the sale period):

Animated Apps Script chartAnimated Apps Script chart
Read more about how the progress tracking line chart was created (including animation) here.

Revenue/profit bar chart

The revenue/profit chart was a standard bar chart showing total revenue and total profits against the target metrics (fictitious numbers):

Revenue and Profit bar chart

Sales channel metrics

The lower portion of the chart was a breakout of all the different sales channels we tracked, everything from individual emails, to ads (Facebook and Pinterest) and different affiliates.

Every one of these channels had their own row, showing the actual sales performance against that channel’s projected sales (click to view larger version; numbers are fictitious):

Example of sales data

The key formula driving this section of the dashboard was a simple COUNTIF formula, labeled 1 above, which counted the number of sales in the E-junkie dataset attributed to this channel:

=COUNTIF('ejunkie staging data'!$S$4:$S,D25)

The sparkline formula for the green bar charts, labeled 2 above, was:

=IF(J25=0,"",SPARKLINE(J25,{"charttype","bar";"max",MAX($G$25:$G$36);"color1","#4caf50"}))

Note: both of these formulas work for the data in row 25.

In action

One fun thing we did with the dashboard this time around, which I’d never done previously, was to show it full screen on a big TV during the sale, using an Apple TV and AirPlay:

Google Sheet e-junkie dashboard on big screen

Cool! πŸ™‚

Google Sheets and Mailchimp integration using Apps Script

In this post, we’re going to see how to setup a Google Sheets and Mailchimp integration, using Apps Script to access the Mailchimp API.

The end goal is to import campaign and list data into Google Sheets so we can analyze our Mailchimp data and create visualizations, like this one:

Google Sheets and Mailchimp integration for campaign data analysis

Mailchimp is a popular email service provider for small businesses. Google Sheets is popular with small businesses, digital marketers and other online folks. So let’s connect the two to build a Mailchimp data analysis tool in Google Sheets!

Once you have the data from Mailchimp in a Google Sheet, you can do all sorts of customized reporting, thereby saving you time in the long run.

I use Mailchimp myself to manage my own email list and send out campaigns, such as this beginner API guide (Interested?), so I was keen to create this Mailchimp integration so I can include Mailchimp KPI’s and visualizations in my business dashboards.

For this tutorial I collaborated with another data-obsessed marketer, Julian from Measure School, to create a video lesson. High quality video tutorials are hard to create but thankfully Julian is a master, so I hope you enjoy this one:

(Be sure to check out Julian’s YouTube channel for lots more data-driven marketing videos.)

If you’re new to APIs, you may want to check out my starter guide, and if you’re completely new to Apps Script, start here.

Otherwise let’s begin.
Continue reading Google Sheets and Mailchimp integration using Apps Script