Data Cleaning and Pivot Tables in Google Sheets — My New Course has launched!

Data Cleaning in Google Sheets

There’s a joke in the data community that goes:

“You’ll spend 90% of your time cleaning data, and the other 10% of your time complaining about that…”

Do you deal with badly formatted data that takes you hours to tidy up, preventing you from making progress on your “real” work?

Ever wondered what the heck a Pivot Table is, and why you should care about them?

Do you ever find yourself repeating a process manually, like extracting zip codes from addresses?

This new course is designed to help you work with messy, real-world datasets. It’ll teach you techniques and best practices for cleaning data and preparing it for analysis and reporting.

This new course is aimed at beginner-to-intermediate Google Sheets users.

Continue reading Data Cleaning and Pivot Tables in Google Sheets — My New Course has launched!

How to add a total row to a Query Function table in Google Sheets

This article looks at how to add a total row to tables generated using the Query function in Google Sheets. It’s an interesting use case for array formulas, using the {...} notation, rather than the ArrayFormula notation.

So what the heck does this all mean?

It means we’re going to see how to add a total row like this:

How to add a total row to a Google Sheets QUERY table
Table on the left without a total row; Table on the right showing a total row added

using an array formula of this form:

= { QUERY ; { "TOTAL" , SUM(range) } }

Now of course, at this stage you should be asking:

“But Ben, why not just write the word TOTAL under the first column, and =SUM(range) in the second column and be done with it?”

Well, it’s a good question so let’s answer it!

The reason for using this method is because the total line is added dynamically, so it will be appended directly at the end of the table, and won’t break if the table expands or contracts, if more data is added.

It’ll always move up or down, so it sits there as the final row.

Continue reading How to add a total row to a Query Function table in Google Sheets

How to reply to Google Forms survey responses quickly with Sheets & Apps Script

Google Forms survey email tool system
1. User submits Google Forms survey
2. Response logged in Google Sheet
3. Google Apps Script parses responses and sends emails
4. Original user receives reply!

You’re happy!

You sent out a Google Forms survey to your customers and received hundreds of responses.

They’re sitting pretty in a Google Sheet but now you’re wondering how you can possibly reply to all those people to say thank you.

Manually composing a new email for each person in turn will take forever. It’s not an efficient use of your time.

You could use an ESP like Mailchimp to send a bulk “Thank You” message, but it won’t be personal. It’ll be a generic, bland email and nobody likes that. It won’t engage your customers and you’ll be missing an opportunity to start a genuine conversation and reply to any feedback from the survey.

Thankfully, there is another way.

Of course there is, otherwise why would I be writing this tutorial! 😉

By adding a reply column to your Google Sheet, next to the Google Forms survey responses, you can efficiently compose a personal response to every single survey respondent.

Then, using Google Apps Script (a Javascript-based language to extend Google Apps, start here if you’re new), you can construct an email programmatically for each person, and send out the responses in bulk directly from your Google Sheet.
Continue reading How to reply to Google Forms survey responses quickly with Sheets & Apps Script

How to create an annotated line graph in Google Sheets

Animated e-junkie sales chart in Google Sheets
Animated line chart in Google Sheets

This post looks at how to create a more advanced line graph in Google Sheets, with comparison lines and annotations, so that the viewer can absorb the maximum amount of insight from a single chart.

For fun, I’ll also show you how to animate this line graph in Google Sheets.

Want your own copy of this line graph?
Click here to access your copy of this template >>

This chart was originally developed for The Write Life during their 4-day product sale earlier this year. It featured as part of a dashboard that was linked to the E-junkie sales platform and displayed sales data in real-time:

Google Sheet e-junkie real-time dashboard
Google Sheet e-junkie real-time dashboard

Continue reading How to create an annotated line graph in Google Sheets

Dashboard Design Checklist: From a Blank Google Sheet to Business Insights

Dashboard design is hard.

You start with a blank canvas, or a blank Google Sheet in this case, and you have to somehow turn that into business insights, which will grow your bottom line, make your organization more efficient or help you understand your customers better.

Dashboard design process
Notice the blank sheet is Draft 1 and the final dashboard is Draft 4. It’s an iterative process. Click to open larger image in new browser tab.
Want your own copy of this Facebook dashboard?
Learn more about the template & Supermetrics here >>

The blank screen stares back at you, waiting for you to do something.

It feels overwhelming.

You clasp your hands around the back of your head, lean back in your chair and rue the day you mentioned building a dashboard to your boss.

It was supposed to be easy. Easy to create a masterpiece, a thing of beauty to wow your team.

Meanwhile, that blank Sheet continues to stare back at you, emptier than ever.

Dashboard design starts with a blank Google Sheet
Dashboard design starts with a blank Google Sheet

Continue reading Dashboard Design Checklist: From a Blank Google Sheet to Business Insights

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.
Continue reading How to build a real-time sales dashboard for E-junkie using Google Sheets and Apps Script

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

Connecting to the Crunchbase API with Google Sheets

Crunchbase API with Google Sheets

How to import data from the Crunchbase API into Google Sheets

Crunchbase is a business information platform; a sort of giant database of information on organizations and people in the business world, especially the digital/technology/startup world.

They have an API so you can programmatically access and retrieve business data. There’s a free tier, which is what I’ll show in this article, and a paid, pro tier, which has a much richer dataset available through the API.

On the free tier, you’re limited to data on organizations and people profiles, through their Open Data Map. It’s a RESTful API with two endpoints (/odm-organizations and /odm-people) and you need to apply for basic access first to get a user key for access.

Continue reading Connecting to the Crunchbase API with Google Sheets

How to extract email addresses tagged with a specific Gmail label

Extract email from Gmail

Everyone uses email in different ways. For me, email is at the very centre of my business. Everything flows through my Gmail account.

Being able to categorize all work enquiries or questions with a specific label, for example Queries (which I nest under my Work label) is one of the most useful features of Gmail.

Recently I needed to extract all of the email addresses for the hundreds of messages under this label. Super tedious to do manually, but thankfully there’s a much quicker way using Apps Script.

In this post, we’ll see how to setup a Google Sheet to extract all the email address for a specific Gmail label. I’ll share the code and walk through it with you.

How to use the Gmail Service with Apps Script and Google Sheets

Step 1: Set up the Google Sheet

The Google Sheet for this example is super simple for a change.

Cell B1 is where we type the label that we want to extract emails from, and then on row 3 are the two column headings, Name and Email.

The sheet looks like this:

Setup sheet for gmail app

Really important note

In this example I have a nested label, where I want email addresses from the label “queries” which belongs to the parent label “work”. Hence I need to write the label with a dash to show this relationship, with the parent label listed first, hence: “work-queries”.

If you are just looking at a single label with no relationship with other labels, then you can just type that label, e.g. “work”.

Continue reading How to extract email addresses tagged with a specific Gmail label

How to import social media statistics into Google Sheets: The Import Cookbook

Google Sheets has a powerful and versatile set of IMPORT formulas that can import social media statistics.

This article looks at importing social media statistics from popular social media channels into a Google sheet, for social network analysis or social media management. If you manage a lot of different channels then you could use these techniques to set up a master view (dashboard) to display all your metrics in one place.

The formulas below are generally set up to return the number of followers (or page likes) for a given channel, but you could adapt them to return other metrics (such as follows) with a little extra work.

Caveats: these formulas occasionally stop working when the underlying website changes, but I will try to keep this post updated with working versions for the major social media statistics.

Example workbooks: Each example has a link to an associated Google Sheet workbook, so feel free to make your own copy: File > Make a copy....

Continue reading How to import social media statistics into Google Sheets: The Import Cookbook