GitHub commits dashboard, built in Data Studio using a native Data Connector
Data Studio is relatively new dashboard tool from Google, launched in mid-2016. It’s a superb tool for creating professional looking reports, easily and quickly, and it connects seamlessly to other Google data sources (e.g. Analytics, AdSense, Sheets, …).
Do you work with data outside of Google’s ecosystem though?
I’ll go out on a limb here, and say, yes, most likely you do.
Perhaps you’re a digital marketing analyst looking at Facebook, Twitter, Instagram, MailChimp data (etc…) for example.
Many of us work with other web services and that data has been unavailable or difficult to display in Data Studio (requiring a sub-optimal workaround of importing it into a Google Sheet and connecting that to Data Studio).
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 shows you how to connect a Google Sheet to GitHub’s API, with Oauth and Apps Script. The goal is to retrieve data and information from GitHub and show it in your Google Sheet, for further analysis and visualization.
If you manage a development team or you’re a technical project manager, then this could be a really useful way of analyzing and visualizing your team’s or project’s coding statistics against goals, such as number of commits, languages, people involved etc. over time.
Note, this is not a post about integrating your Apps Script environment with GitHub to push/pull your code to GitHub. That’s an entirely different process, covered in detail here by Google Developer Expert Martin Hawksey.
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.
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:
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.
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.
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.
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.
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.
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 (fictitious data)
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.
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:
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.
