Building a Domo dashboard for Write500

This post is more than a year old. The information, claims or views in this post may be out of date.

This post is part of a series of posts I’m doing about Domo (the Business Cloud) and how I’m using the free tier to analyze metrics around Click here for the full listing.

There’s a surprising amount of things to keep an eye on when it comes to a project like Write500.

Right now, only the free tier of Write500 exists – users get sent to a subscribe page, they opt-in, and receive daily writing prompts over email. It won’t always be so simple: I’ve got plans for a Plus (paid) version, and mobile apps to extend Write500’s reach.

So even this simple, free version, in terms of the architecture, already looks like this:


Every box in there has something that needs to be monitored in some way. Some of the monitoring is historical (looking back at performance over time), some of it is by exception (being alerted when something goes wrong).

That’s already a lot of potential metrics, from a lot of different sources. Individually, they aren’t particularly hard to get – it’s deciding which ones to get, how often, and what you’ll do with the information, that matters.

And that’s the product side of the equation. Not only am I building the product, I’m the one-person marketing team behind it.


Each of those boxes, too, can have a whole universe of metrics in them. Social in particular, which has a penchant for inventing crazy new metrics every few months.

Where to start?

If it seems overwhelming – it is. There’s a lot of information out there, not all of it useful. It’s way too easy to get hung up on the wrong metrics, and miss out on the ones that matter.

So the best place to start, in my experience, is not with the data. I’ve seen too many BI projects start out with what the team could get from their databases, and in the end, they delivered dashboards that looked identical to what they were already getting from their data in the first place.

What we’re after here is actionable, meaningful insight. So without further ado:

1. The Question

A much better place to start, is the question.

If I could answer one question about your business, what would it be?

For Write500 I have a lot of questions, but in this post we’ll deal with the one that’s most important to me:

Are my subscribers getting value from Write500?

I built Write500 because I wanted to be nudged to write 500 words per day. So far, as the user of my own product, I’ve managed to achieve that – but my experience doesn’t matter nearly as much as the experience of my subscribers. If they’re not getting the value they need, then I’m either aiming at the wrong audience, or there’s a problem with my product.

Now, that question could be answered anecdotally. I could point to a few places I’ve seen bloggers start their challenge, and take that as a sign that users are getting value. I’ve seen a lot of businesses do this – make gut judgements based on limited information.

I have learned, however, not to trust my gut. Let’s quantify this instead.

Getting metrics about my subscribers is easy – I can pull the subscribers table and chart the growth. Getting metrics about the value is a little bit harder – right now, I’m not equipped for that.

Ideally, the value I want subscribers to get out of my product is producing 500 words per day – and the only way to track that metric is to have them plug their 500 words into a system where I can measure their wordcount. That’s a feature planned for the next version of Write500, but right now, I don’t have that data.

The best I can do, with the information I have, is determine whether or not users are opening the emails I’m sending on a regular basis. That’s not the best metric for whether or not they’re getting value from Write500, but I’ll operate on the assumption that consuming the prompts is at least partially valuable.

Here, already, one of the biggest problems in BI becomes visible: The gap between what you need to know about your business, and the data you have on hand to answer it. So, so many BI vendors and consultants sell dashboards that chart what you have, but don’t give a second thought to what you need.

2. The Data

So based on my question, what data can I acquire to answer it?

Write500 has a bespoke tracking pixel. Every prompt that gets sent is uniquely identifiable, and includes embedded tracking that fires (email client willing) when the email is opened. This data is sent straight back to my server, instead of going through a third-party analytics tool.


The raw data, as you can see in the screenshot, includes the GUID for each send, and the server-side timestamp for when the email was opened. I also have the timestamp for when the email was sent. With this data, I can answer two sub-questions:

  1. Are subscribers opening the emails they’re receiving?
  2. Do subscribers open the email on the same day they receive it?

Together, the answers to those questions would at least indicate whether or not the emails themselves are being utilized (and whether my subscribers are getting value). So looking at the data I have available, this is what I can get to help answer those questions:

Per subscriber: (dimension)

  • The amount of prompts they have been sent (metric)
  • The amount of prompts they have opened (metric)
  • The open rate (calculated)

Per day: (dimension)

  • The total amount of prompts sent (metric)
  • The amount opened within 24 hours (metric)
  • The amount opened within 48 hours (metric)
  • The amount opened within 72 hours (metric)
  • The amount opened after 72 hours (metric)
  • The amount not opened (metric)

Dimensions and Metrics

I should explain what these are before continuing, and I’m doing this in the context of Domo’s capabilities. Within any tabular data set, you’ll have two types of fields:

  • Numbers
  • Everything that’s not numbers

Metrics are numbers you can measure, and Dimensions are the categories you group/slice/breakdown those metrics in. So:

  • New Subscribers per day
  • Recurring Visitors by mobile device
  • Purchases over $50 by persona

In the samples above, Day is a dimension (because it’s not a number). But Subscriber is also a dimension, even though it’s technically a numeric ID – except that it’s a number that makes no sense to measure. This is the exception to the rule.

It can’t be summed, averaged, min’d or max’d and present any meaningful result: What’s the point of knowing the average subscriber ID in your database? The count of subscribers is more useful, and in that case, the count becomes the metric.

3. The Acquisition

So now we know what question we’re answering, and the data that we’re going to use to answer it. This is where we start getting into the implementation.

The first step, obviously, is to acquire the data. In a previous post, I dealt with this issue for Write500, eventually settling on a SFTP upload for my subscribers dataset. Since then, I’ve added a better stats system to the app.

So for Write500 specifically, I now have this happening:

Write500 Data - New (1).png

I have a series of Artisan commands, each of which generates a single CSV file. Those commands are scheduled using the cron system on Forge, and when they run, the results are uploaded to a dedicated S3 bucket. Domo, in turn, pulls that information into its Vault:


Now that I have the data coming in, it’s time to build some charts!

4. Visualization

Visualization is a problem domain all its own, and has way too much science behind it to cram into a 3000-word blog post. However, we’re doing visualization for BI, which is very different to visualization for analysts, engineers and scientists.

BI visualization has to meet several fundamental criteria (at least, the type of BI I do):

  • Self-contained – the chart should explain what it is without requiring an appendix. Any chart that has a “How to read this chart” next to it, is a bad chart.
  • Key call-out – Whatever the purpose of the chart is, it should be obvious at a glance, preferably from across the room.
  • Honest – Too many “analysts” will fiddle with colors, min/max axes dimensions, sorting and scale to try to massage the truth behind the numbers. Don’t.
  • Actionable Next Step – In charts that are meant to drive decision-making, make the decision obvious.

In my experience, this tends to mean the following:

  • You’re going to re-use a lot of the same 3-4 chart types (line, bar, area, donut)
  • You’re going to create lots of smaller charts, instead of consolidated/complex ones
  • Colors are really important
  • Short titles are really important

I will eventually build the charts that answer my key question, but let’s start with some simple ones.

First, we start with a blank canvas.

Domo includes the ability to group charts into Collections, which make it simpler to manage complicated dashboards. In this case, all the charts that pertain to my subscribers will go in one collection – other collections will include marketing and product data.

Note: All the charts here are built on real data, as of today.

0. Warming up

Easiest thing to chart – one metric vs a date.


Here’s an example of what I think is a good chart:

  • The title and subtitle make it clear what I’m looking at,
  • The summary number makes the current status clear,
  • The bar chart shows the growth nicely, and
  • Using Domo’s linear regression Projection feature, I can see that I should end today with over 100 new subscribers

So overall – I’m getting new subscribers, and it’s visually obvious from the chart that the velocity is increasing.

But now let’s answer our first sub-question:

Are subscribers opening the emails they’re receiving?

Now we get to define a proprietary metric! I’m going to create a metric called “Engaged”:

Engaged: A user that has no more than 1 unopened prompt email

The logic behind this: Users receive one email per day, and there will be a gap between receiving an email and opening it. However, for as long as that gap exists, the email will show as “1 unopened” in my data, which is not fair – the user just hasn’t had a chance to open the most recent email yet.

“2 unopened” means they’ve neglected at least one email, so I’ll consider them less engaged. Yes, this is a high standard.

How do we calculate this metric? Here’s the Beast Mode formula I’m using, directly in the card.


This gets us the total users that have between 0 and 1 unopened emails, as a proportion of the total number of users in the data.

That can be represented in a single large number card, like so:


That’s a pretty impressive result! Almost 70% of my subscribers are opening their emails on a regular basis, and it could be even higher: remember that I cannot track opens from email clients that block the call to my tracking pixel.

I also want to get a sense of the proportions, though – so we’ll take that summary metric and use it as a summary number on a pie chart. I’ll group my subscribers like so, using another Beast Mode.


Honestly, these sorts of CASE statements represent 90% of what I use Beast Mode for, in Domo – to create custom categories.

We’ll apply that formula to the data, and spin up a pie chart. Here’s what we get:

2017-01-07 15_24_03-Write500 - Domo.png

So of all my subscribers, 60 of them are neglecting their prompts completely. This is also useful information – I know who those subscribers are, so I can segment them out and try different email subject lines, to see if I can maybe get them more engaged and bolster the overall rate.

There’s one more chart we can create – we can compare our engagement rate to an industry open rate. Using this source data, I’ll take Hobbies as my benchmark.

2017-01-07 15_28_21-Write500 - Domo.png

Not bad at all! But then, that’s to be expected – my emails are not marketing, they’re more transactional in nature, and transactional emails have much higher open rates.

So let’s go back to our question:

Are subscribers opening the emails they’re receiving?

Resoundingly, yes: Close to 70% of my subscribers are opening their emails.

On to the next question!

Do subscribers open the email on the same day they receive it?

The answer to our previous question can cover this one too – if Unopened is between zero and one, then yes – most open the same day they receive it. However, that’s an aggregate view, and I want to see some more specific on how long it takes subscribers to engage with their emails.

So for this, we’re looking at the time difference between the email being sent, and opened. I’ve already done the heavy lifting of categorizing the data inside my data-providing Artisan commands, so the data I’m visualizing is already close to useful:


Our last charts looked at Subscriber Engagement, which is a higher-level metric than the actual individual emails being opened or not.  For these charts, we’re taking a different angle, looking at the actual emails. We’ll get similar rates, but not identical – that’s to be expected.



Of all the emails that do get opened, practically all of them get opened on the same day (within 24 hours of being sent). And it looks like, overall, about 40% of the emails sent by the system have not been opened yet. Or, at least, they haven’t fired the tracking pixel yet.

The thing about tracking email open rates in particular, though – naturally, it’s not a trendable metric.

For example: You can send 100 emails on Monday, then calculate the open rate every day of the week, and get a different (higher) number every day.

Why? Because subscribers take time to open their emails (if at all). So looking at that chart above, right now, 40% of the emails I’ve sent are unopened. Those emails could have been sent today, or a week ago.

Right now, I care about whether or not subscribers open on the same day – and according to this chart, nearly all opened emails are opened on the same day. Emails opened at +1 Day and up are completely dwarfed. This is what happens when we remove Unopened emails from the chart:


So that’s my question answered!

5. Taking Action

I’ve got my questions, they’re being answered by data, and now I need to think about the next step in this: What actions can I take, based on this data, to drive my business goals?

Right now, my goals are two-fold:

  1. Drive more subscribers to Write500
  2. Improve the email open rate

For (1), what I really need to look at is my subscriber acquisition machine – the marketing I’m doing, which is outside the scope of this post. I do plan on writing another one dealing specifically with marketing metrics though.

For (2), I now have a tight group of subscribers that are either not opening their emails, or not opening them on a daily basis – meaning they’re not writing every day. I can segment these users out and try a few things to remedy that.

I could also simply reach out to the subscribers that haven’t opened any emails yet. There might be a good reason why those emails aren’t being opened:

  • maybe they’re getting caught in spam,
  • maybe the user hasn’t been at their mailbox in the last week, or
  • maybe they no longer want to receive them, but haven’t bothered opting out.

My action would be to find out what it is, and remedy it where possible.

6. (Finally) Measuring Results

The final piece of the puzzle, and the one question that every BI dashboard should answer:

Are our actions having the intended effect?

Since the insights and actions are data-driven, the results should be too. For every action that you take to impact your metrics, you need to think about how you’re going to measure the results in a quantifiable way.

Let’s take the Open Rate chart above. When I start taking action to improve it, I expect to see that, over time, the overall percentage of Unopened emails goes down.

Since it’s an email open rate though, I can only get snapshots at regular intervals. The data generator I wrote does just that – it gathers point-in-time statistics, and appends them to the dataset. Right now, it’s running once per day, and it’s grouping the sends by the date they went out.

So putting that into a 100% Stacked Bar Chart, I get this:

2017-01-07 17_02_06-Write500 - Domo.png

Every day, I will get another bar on this chart, and the chart itself will be completely recalculated (if emails sent on previous days are opened, which we know from the data above it will be).

However, over time, that red portion should get smaller. If I wanted to create very precise tracking on this, I’d create a recursive dataflow to record the Unopened rate in a dedicated dataset – but for right now, I think I have enough BI going on!




I hope this post has been informative!

I’m planning on doing a deeper dive post into Write500’s metrics at the end of the month – that post will deal more with my expectations vs the reality, the charts I ended up using on a daily basis, what decisions I took, and how I measured those results.

2017-01-07 17_17_53-Write500 _ IG_ write500 - Domo.png
The dashboard I’m sticking with for now.

If you want to be notified when I write more of this stuff, check the Subscribe widget in my sidebar, top right. Or you can follow me on Twitter.

Pulling arbitrary data into Domo

This post is more than a year old. The information, claims or views in this post may be out of date.

This post is part of a series of posts I’m doing about Domo (the Business Cloud) and how I’m using the free tier to analyze metrics around Click here for the full listing.

There are almost as many ways to integrate data, as there are types of data, and source systems that hold data. There’s basically no way to cover them all in the space of one blog post.

In terms of Domo, though, all data is tabular – Domo doesn’t deal with binaries, log streams, delimited text files or complex JSON documents. Just plain old CSVs, which makes sense if you’re doing BI for business – most of the things that matter to decision-makers can be represented in table form.

So long as you can get your data in CSV format, there are a good couple of options to get it into Domo. In this case, I want to pull anonymous user data from write500 – just enough to chart user growth.

I’m also working within the limitations of what the free tier of Domo can do. The Enterprise tier is capable of more advanced acquisition, including basic JSON and CSV API endpoints.

In this example, I do have the benefit of being both the developer of the product, and the business user consuming the dashboards – so I can write any solution I need. Based on what the free tier is capable of, I have the following options:


That’s a lot of options – and at some point, I’ve used every one.

From top to bottom:

1. Cloud Storage
I could write a script on the server to package the data in a format that can be uploaded to cloud storage – so a zipped file pushed to Box, OneDrive or Dropbox for Business. Domo then has cloud connectors that will let me pull that down as usable data.

I could expose the data through a simple JSON API, and use Google Sheets as an intermediary. It’s possible to use the built-in scripting language and some scheduling to populate a Google Sheet, which is then trivial to import into Domo. It’s not very scalable though.

3. Export to CSV
I could write a script to dump out my required data as a CSV file, then pull that over SFTP. This is easier to set up, but still requires some scripting work. I can then pull the resulting data with the CSV/SFTP connector.

4. Direct Database Access
I could use the MySQL connector to hit the write500 database directly. I’d have to open firewall ports, add users, and do a bunch of other setup first. I’m not in favor of doing that much work right now, though.

So we’ll go with 3 – I’ll write a script to produce the exact CSV file I need, then set up the SFTP connector to fetch it.

Deciding what to fetch

There’s something of an art to deciding what metrics to actually pull – you first need to decide what’s actually useful to you, in terms of answering your business questions. In this example, I know I simply need a dataset that looks like this:

User ID Date Registered Is Subscribed
1 2017-01-01 05:30:00 1

How you generate that CSV is up to you – in my case, I wrote a basic Bash script to do that:


Ok, so “basic” might be the wrong word to use – but all that’s really doing is generating a CSV file, with the right header line and correctly-formatted data.

That script is set up to run every 30 minutes, and it will keep a fresh copy of users.csv in a dedicated user’s home directory.

Now to import that! Domo has a set of connectors for working with files:

1. That (+) icon is everywhere.
2. We’re looking for this one.

On the next screen, typical SFTP setup stuff – host, username and password. If you run a tightly-secured system, you might also need to whitelist an IP range so that Domo can reach your server.

3. Just needs a valid SSH (/SFTP) user.
4. Voila! Our file.
5. I want frequent updates!
6. Name, Describe, and Save


So what we just did there – we set up the SFTP connector to fetch that CSV file once every hour. Every time it does that, it will overwrite the dataset we have with new data – that’s what the Replace method means, in the Scheduling tab.

Finally, named and described. It’s helpful to prefix all your datasets with a short project name – makes it easier to find later.

In no time at all, we’ve got our data!

Sweet, sweet data.

From here, the next step is to visualize it. That’s a whole topic all on its own (so I won’t go into detail here), but here’s a dead-simple line chart built from that data, to show the trending over time:

54 opted-in users from a total of 80 or so

So that’s a basic rundown of a CSV connector, end-to-end. This example does lean towards the “more complex” side of data acquisition when it comes to Domo. Luckily, most of the high-value stuff exists in systems that Domo already has great connectors for.

If you want to be notified when I write more of this stuff, check the Subscribe widget in my sidebar, top right. Or you can follow me on Twitter.

Using Domo – First Steps

This post is more than a year old. The information, claims or views in this post may be out of date.

This post is part of a series of posts I’m doing about Domo (the Business Cloud) and how I’m using the free tier to analyze metrics around Click here for the full listing.

I’m a stats nerd. I love numbers, graphs, and digging into them to find meaning. So, when the opportunity came up at my employer (Acceleration) to partner with Domo and deliver cloud-based BI to customers, it was a natural fit. I’ve spent a bunch of time in both the product and the ecosystem, and have recently started using it to run analytics for my own projects.

A few months ago, Domo made free instances available – anyone can now sign up and get a feature-limited account. I did exactly that, and I’m hoping to write a few posts about how to use various parts of it to do really cool stuff.

What is Domo?

Domo is a cloud-based BI tool that’s aimed at enterprises. Their pricing reflects that, with the entry-level accounts sitting at $175/user/month. Which is not cheap. I haven’t checked comprehensively (there are lots of older-school solutions like SAS and Tableau out there) but it would not surprise me to learn that Domo is the highest-priced BI tool on the market.

The one thing that Domo does really well, as compared to other solutions, is data acquisition. The cloud is a very messy place – some vendors have APIs, some don’t and every API is different. It’s a far cry from the relatively straightforward world of ODBC, where vendors all adhered to the same standard for moving data around.

For some enterprises, the one-click nature of getting data in is the single biggest selling point. The transformation and charting and reporting, social features and alerts and the app are all nice – but the fact that a non-technical user can plug in a few usernames and passwords and get a dashboard, that’s the winning stuff.

Domo makes it very straightforward to access common sources (popular cloud tools, social networks, cloud productivity, etc), and equally straightforward to push data in via a REST-based API. I even built a PHP library for that (shameless plug alert).

So for today, I’m gonna get started by getting a Domo instance up and running, and pulling in Google Analytics data to power a simple dashboard.

This is by far the easiest thing I’ve ever done in this tool – the cool stuff comes later!

Getting Started

The signup form is here, and having just tried it, I got my instance invite about a minute later. Of course, the first thing you should do is company setup – just to put your name and face on it!

Handy little app launcher – everything’s here
Company Settings > Company Overview – just the basics!

There’s also a profile setup, but I’m skipping that here – I’m the only person using this instance for now.

Next up, I’ll use the Domo Appstore to deploy a pre-built Google Analytics dashboard. The Free tier of Domo includes 4 apps, so this is made very straightforward:

1. Launcher > Appstore
2. Pretty hard to miss!
3. Yes please, we’ll try it!
4. Naming it after the product I’m tracking

From there, Domo builds out a page with the name from step 4, and fills it with a bunch of charts all powered by sample data. This gives you a good idea of what sort of insights you’re going to get – but I’ll skip straight to the relevant part:

5. On the top right, Connect Your Data
6. Connect accounts! Future installs will skip this step.
7. Domo helpfully uses oAuth for this part
8. My account is now linked, and can be selected for future app installs (or new datasets).
9. Pick the view to get data for – just one for now.
10. And away we go!

And there we go. I’ve clicked my way to a dashboard.

11. Charts! Really quite simple.

It takes a few minutes to do the initial pull (longer if there’s a lot of data), but eventually it’ll all populate. The datasets that power that dashboard will automatically refresh once per day. The next best thing to do is get the mobile app set up, so you can reach those dashboards on the go:

For now, I’m going to be putting a dashboard together to track the growth of – it’s a good sample to use, seeing as it’s a source I have full control of, and I am actually going to try identifying trends I can capitalize on to drive user growth.

If you want to be notified when I write those upcoming posts, use that little Subscribe widget, top right of the sidebar.