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

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 (see how to create an annotated line graph in Google Sheets).

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

Introduction to Google Data Studio: a free BI tool for small businesses

Regular readers will know of my enthusiasm for building dashboards, especially using Google apps (like this one or this how-to article).

So I was super excited in May of this year (2016) when Google launched Data Studio, a free data visualization and dashboard tool to compete against incumbent dashboard vendors Microsoft PowerBI, Tableau and Qlickview.

Here, I’m excited to share my initial impressions and show you some of the basics steps to build dashboard reports using this tool.

I’ve been using it these past few days to build several different test dashboards.

First impressions: I love it. It’s simple and intuitive. Impressive.

Of course, since it’s a beta launch and it’s a nascent product, there are still areas where it’s lacking sufficient depth or flexibility and it’s a little buggy in places (see discussion below) but overall it’s a significant entrance into the Business Intelligence world for Small and Medium Businesses.

For Enterprise businesses, Google has Data Studio 360 – the full-fat, paid platform.

This article focusses on the free Data Studio version.

Data Studio: The smart way to build dashboards and reports with your Google data

So, another data analysis tool? What’s the value proposition here then?

The premise is that you can connect all of your disparate business data sources (only Google services at the moment but there are other non-Google data connections coming soon) and easily build beautiful, interactive dashboard reports to display that information. And all atop Google’s super-reliable, powerful, scalable architecture. Plus, as with so many Google products, it’s free.

It’s a simple drag-and-drop process to add charts and build reports, and it doesn’t require any knowledge of coding. That makes it super quick to create and modify reports. There’s a boat-load of data and formatting options so you can customize reports to match your needs.

Example: Social Media Reporting Dashboard with Google Data Studio

Want to see what this tool is really about? Heck yeah, I thought so!

Well there’s no better way to do this than seeing a real dashboard with real data.

So I’ll show you a dashboard for reporting social media referral traffic of a mid-size website (~500k pageviews a month). I’ll show some of the steps below and discuss how easy it is.

Ready?

Great, well here’s our dashboard:

Data Studio Social Referral Dashboard
Data Studio Social Referral Dashboard – Page 1
Data Studio Social Referral Traffic Dashboard - Page 2
Data Studio Social Referral Traffic Dashboard – Page 2

I’m not going to go through every step in detail, instead I’ll mention a few of the main steps and key points to keep in mind.

There are three steps to using Data Studio: 1) connecting to a data source, 2) creating visualizations in a dashboard, and 3) sharing your finished creation.

Step 1: Setting up Data Studio and connecting to a data source

Start by signing up or logging in to Data Studio here: https://www.google.com/analytics/data-studio/

You’ll be taken to your home page, showing a bunch of example Google templates (well worth a look) and any of your own dashboards:

Data Studio homepage

Clicking the big blue plus in the bottom right of the home page creates a new dashboard from where you can connect to different Google data sources:

Data Studio data connectors
Data Studio data connectors

I’ve chosen to connect to Google Analytics and then to a specific web property, as shown in the following image:

Data Studio with Google Analytics

This imports all of the data into my report and makes it available for charting or displaying on my dashboard. I discuss these steps in more detail in the video at the beginning of this post.

Step 2: Creating visualizations and creating a dashboard

In the control bar along the top of the window, there are 9 chart types available (line, bar, pie, table, geographic, scorecard, scatter plot, bullet chart and area chart) and the ability to add images, text, rectangles and circle shapes. Plus there are two filters available: date and a general “type” filter.

Data Studio Control Bar

Adding charts is as simple as selecting the one you want in the toolbar, then creating a container for it somewhere on your dashboard canvas. Google will then build the chart, with some default data (which you can easily modify). The following GIF shows this step, along with changing the data being displayed and formatting the chart:

Adding chart to data studio

I’d encourage you to play around with the tool for a while at first, adding and deleting different charts to get a feel for what they look like and what sort of data they can display. There’s more info in the video at the start of this post.

For the Social Media dashboard above, I’ve made use of line charts, tables, scorecards and a geographic map. I’ve added a filter to the data source to restrict the data to show Social visits only:

Data filter

Once you’ve finished building, switch to view mode to lock everything down and remove the gridlines. The filters still work in this view mode.

Switching between editing and viewing mode is done by toggling the blue toggle switch in the top R corner of the window, as shown in the following GIF:

Editing and viewing in Data Studio

Step 3: Sharing your dashboard

As a final step, you’ll probably want to share your dashboard with colleagues, clients or friends. Click the big Share button in the top R corner of the screen to bring up the share menu pane.

Here, you can enter email addresses of people you want to share the dashboard with or, as shown in the following image, copy the shareable link and send that to people, share on social media or your website. In both cases you can control whether others will have editing access or see the dashboard as view-only.

Data Studio sharing

What I really liked about Data Studio

It’s flexible and can connect to multiple different data sources.

It’s super easy to create charts. Without too much effort you can produce really professional-looking, slick dashboards.

It’s really quick to create reports.

There’s a huge level of customization, especially with the Google Analytics data connection.

Multiple pages allow you to create a hierarchy of dashboards of increasing levels of detail/complexity, to tell a story.

Best of all, the data range and filter range controls are very easy to implement but incredibly powerful in how they work.

And of course, like many Google products, it’s free!

Areas for improvement imho

It would be awesome to have object guidelines show up when moving objects around on the dashboard (e.g. photo editing software Pixelmator does this very well), to make it easier to line them up.

On a similar note, snapping to a grid to help line up objects would be helpful. Provided it could be toggled on/off of course!

More data connections, for example to SQL databases. This is coming soon…

I’d love to see more granular control over the chart details, for example formatting of the axes and working with data labels. The range of options is a great step in that direction and you can already make beautiful looking charts but this would take it to the next level.

I could not select multiple items by holding down the Ctrl (or Cmd) key and selecting, which struck me as odd. You can select multiple objects by dragging and holding the cursor across them however.

When I tried sharing, I was prompted to log into Google despite choosing the option to share with anyone. So, it seems that non-Google users can not view these dashboards at the moment.

When in Full Screen display mode, the page buttons are hidden and barely accessible, making it difficult to navigate through the dashboard.

Closing thoughts

If you’re invested in the Google ecosystem, work with data, especially Google Analytics, then I’d encourage you to give it a try. I’m sure Google will invest in this product and build out the features and data connection options. It’ll be interesting to see where it goes and how it competes with Microsoft’s PowerBI and other vendor offerings.

How to make a Histogram in Google Sheets and overlay a Normal Distribution Curve

Histogram and Normal Distribution image from Google Sheets
Histogram and Normal Distribution chart made in Google Sheets

In this tutorial you’ll learn how to make a histogram in Google Sheets with a normal distribution curve overlaid, as shown in the above image, using Google Sheets.

It’s a really useful visual technique for determining if your data is normally distributed, skewed or just all over the place.

What is a Histogram?

A histogram is a graphical representation of the distribution of a dataset.

In this example, I have 1,000 exam scores between 0 and 100, and I want to see what the distribution of those scores are. What’s the average score? Did more students score high or low? How clustered around the average are the student scores? Are the scores normally distributed or skewed?

What is a Normal Distribution Curve?

The normal distribution curve is a graphical representation of the normal distribution theorem stating that “…the averages of random variables independently drawn from independent distributions converge in distribution to the normal, that is, become normally distributed when the number of random variables is sufficiently large”.

Bit of a mouthful, but in essence, the data converges around the mean (average) with no skew to the left or right. It means we know the probability of how many values occurred close to the mean.

We expect 68% of values to fall within one standard deviation of the mean, and 95% to fall within two standard deviations. Values outside two standard deviations are considered outliers.

We expect our exam scores will be pretty close to the normal distribution, but let’s confirm that graphically (it’s difficult to see from the data alone!).

Let’s see how to make a Histogram in Google Sheets and how to overlay a Normal Distribution Curve, as shown in the first image above.

How to make a Histogram in Google Sheets

Step 1: Raw data

Copy the raw data scores from here into your own blank Google Sheet. It’s a list of 1,000 exam scores between 0 and 100, and we’re going to look at the distribution of those scores.

Step 2: Name that range

Create a named range from these raw data scores, called scores, to make our life easier. Highlight all the data in column A, i.e. cells A1:A1000, then click on the menu Data > Named ranges… and call the range scores:

named range

Step 3: Summary statistics

Set up a small summary table with the mean, median, mode and standard deviation of our population. The formulas are:

Mean:

=AVERAGE(scores)

Median:

=MEDIAN(scores)

Mode:

=MODE(scores)

Standard Deviation:

=STDEVP(scores)

summary table

Step 4: Create the frequency bins

Set up the frequency bins, from 0 through to 100 with intervals of 5. Put 0 into cell F2 and then you can use this formula to quickly fill out the remaining bins:

=F4 + 5

(it adds 5 to the cell above). Name this range bins.

Frequency bins

Step 5: Normal distribution calculation

Let’s set up the normal distribution curve values.

Google Sheets has a formula NORMDIST which calculates the value of the normal distribution function for a given value, mean and standard deviation. We calculated the mean and standard deviation in step 3, and we’ll use the bin values from step 4 in the formula.

In G2, put the formula:

=NORMDIST(F2,$D$1,$D$4,FALSE)

Drag it all the way down to G22 to fill the whole Normdist formula column:

normal distribution calculation column

Step 6: Normal distribution curve

Let’s see what the normal distribution curve looks like with this data.

Select the bins column and the Normdist column then Insert > Chart and select line chart, and make it smooth:

Smooth Normal distribution

You’ll have an output like this:

Normal distribution curve in Google Sheets
Normal distribution curve in Google Sheets

That’s a normal distribution curve, around our mean of 56.9. Great work!

We now need to calculate the distribution of the 1,000 exam scores for our histogram chart.

As we’re going to create a totally new chart with the histogram and normal curve overlaid (easier than modifying this one), you can put this normal distribution chart to one side now, or delete it.

Step 7: Frequency formula

Leave column H blank for now (we’ll fill this in shortly).

In column I, let’s use the FREQUENCY formula to assign our 1000 scores to the frequency bins. Type the following formula into cell I2 and press Ctrl + Shift + Enter (on a PC) or Cmd + Shift + Enter (on a Mac), to create the Array Formula. It’ll fill in the whole column and assign all the scores into the correct bins:

=ArrayFormula(FREQUENCY(scores,bins))

frequency formula

If you’re new to Array Formulas, check out this post: How do array formulas work in Google Sheets?

Step 8: Copy values

Copy this column of frequency values into the adjacent column J (we need this for our chart).

Pro tip: you can just copy I1:I2 into J1:J2, it’ll fill out the whole column with values.

Step 9: Scale the normal distribution curve

We need to scale our normal distribution curve so that it’ll show on the same scale as the histogram. Since we have 1,000 values in bins of 5, our scale factor is 5,000. Meaning, when I multiply the normal distribution values by 5,000, they’ll be comparable to the histogram values on the same axis. Also, they’ll sum to 1,000 matching the number of values in our population.

So in the blank column H, add the following formula and drag down to H22:

= G2 * 5000

Our completed data table now looks like:

final table

Step 10: Create the chart

This is where we see how to make a histogram in Google Sheets finally!

Note: the screenshots shared below show the old chart editor. The new chart editor opens in a side pane, but the steps and options are essentially the same.

Hold down Ctrl (PC) or Cmd (Mac) to highlight the bins data column, the Normal distribution and two histogram columns, but omit the Normdist formula column, as follows:

Highlight data

Then Insert > Chart, and select Combo chart:

Select combo chart

Select the option to use column F as labels:

select labels

In the Customization tab, remove the title and legend. Select the Smooth option:

select smooth option

Select the vertical axis. Delete the axis name. Set to have a range of 0 to 150, and set the major gridlines to 4.

Vertical axis

In the series section of the customization menu, choose the Normal Distribution series, and change from columns to line, so your chart looks like this:

Change to line

Next, choose the Histogram series and change the type from line to columns:

Change to columns

Select the Histogram 2 series and change the type from line to stepped area:

Stepped area

Then change the color to red, the line thickness to 1px and the opacity to 70%, to make our chart look like a histogram (this is why we needed two copies of the frequency column):

Histogram format options

Final tidy up: set the axes labels font size to 10, then click in the chart area to move and resize the it by dragging the edges outwards, so it fills out the whole of our chart canvas:

Move and resize

Voila! You’ve now learnt how to make a histogram in Google Sheets, overlaid with a normal distribution curve:

Final output

Want your own copy of this histogram chart?
Click here to access your copy of this template >>

Conclusion:

To conclude, we can see our exam score data is very close to the normal distribution. Hooray!

If we look closely, it’s skewed very, very slightly to the left, i.e. it has a longer tail on the left, more spread on the left. See how there is space between the red bars and the blue line on the left side, but the red bars overlap the blue curve on the right side. It’s subtle though.