Building a Domo dashboard for Write500

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 write500.net. 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:

write500-arch

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.

write500-marketing

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.

sendopened

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:

domodata

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.

2017-01-07-14_50_30-write500-domo

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.

2017-01-07-14_54_01-write500-domo

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.

calc1

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:

2017-01-07-15_19_06-write500-domo

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.

bm2.png

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:

2017-01-07-15_46_51-write500-_-open-rate-domo

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.

2017-01-07-16_59_10-write500-domo

 

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:

2017-01-07-17_00_34-write500-domo

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!

 

 

Conclusion

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.

3 thoughts on “Building a Domo dashboard for Write500

    1. Wogan Post author

      Thanks! I’ll be writing a lot more of these – when I start pulling in metrics from social networks, how to calculate impact, how to find leading factors, etc.

      Would love to read anything you publish on the subject, if you’re up for it 🙂

      Like

      Reply
  1. Pingback: It’s time to talk about charts | Wogan's Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s