Exploring your Heroku Rails app’s database using SQLPro for Postgres

In the past when I’ve wanted to explore production data for a Heroku-hosted Ruby on Rails app, I’ve primarily used heroku console and rake tasks. Each method has limitations though: heroku console makes easy to answer simple questions about your data, but makes it difficult to perform complicated analyses that take more than a few lines of code. Rake tasks let you perform complex analyses, but make it difficult to explore data because each time you tweak your task to do something new, you need to commit, push to production, run the task, and wait for it to execute. Neither option makes it easy to quickly explore the data.

Wouldn’t it be nice if you could quickly query your database and explore the results?

Fortunately there is a way using a combination of Heroku’s pg:pull feature and a Mac app called SQLPro for Postgres. Here’s how it works:

Step 1: Pull your production data into a local Postgres database

Heroku makes this fairly easy using the pg:pull command:

$ heroku pg:pull HEROKU_POSTGRESQL_MAGENTA mylocaldb --app sushi

Where mylocaldb is the name of a local Postgres database, sushi is the name of your Heroku app, and HEROKU_POSTGRESQL_MAGENT is the name of your database which you can obtain by running:

$ heroku pg:info -a sushi

If your local Postgres instance requires a user name and password, you can provide them via the command line as well:

$ PGUSER=postgres PGPASSWORD=password heroku pg:pull HEROKU_POSTGRESQL_MAGENTA mylocaldb --app sushi

In order for this command to work, mylocaldb can’t exist when you run this command. To delete it beforehand, you can run:

$ dropdb mylocaldb

For my own workflow combine them and use a Bash alias to make it easier to run:

alias prdb="dropdb preceden_production_copy; PGUSER=postgres PGPASSWORD=password heroku pg:pullHEROKU_POSTGRESQL_MAGENTA preceden_production_copy --app sushi"

Then I can just run prdb (my short hand for “Preceden Database”) from the command line to drop the old copy and grab the latest production data:

$ prdb
heroku-cli: Pulling postgresql-infinite-32999 ---> preceden_production_copy
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
...

Step 2: Explore the data using SQLPro for Postgres

SQLPro for Postgres is a fantastic Mac app for exploring Postgres databases. You can also query the data other ways but for quickly exploring, querying, and exporting the data, SQLPro for Postgres is hard to beat.

Here’s what the UI looks like along with an example query to display the first 10 people to sign up:

sqlpro-for-postgres.jpg

In future posts we’ll see how to query Postgres with R to analyze the data and gain insights about how people use our products.

If you’re interested in learning more, sign up for my new Data Science for Product Analytics newsletter to get notified when there are new posts.

Update: check out the follow up post, How to Schedule Cloning your Heroku Postgres Database Locally.

Visualizing Your SaaS App’s Monthly Active Users Broken Down by Signup Cohort

This week at Automattic I’ve been helping with a tool that will allow us to visualize the number of active WordPress.com users each month broken down by when those users signed up for an account. I think this type of chart and what you can learn from it are incredibly valuable so I wanted to show you all how to quickly create one for your own service.

Here’s an example of what this type of chart looks like courtesy of Buffer’s Joel Gascoigne:

What I really like about it is that for each month you can see how many active users there are and when those users signed up for an account. This not only gives you a sense how long ago your active users signed up, but also of your service’s ability to retain users over time.

If you’d like to create a similar chart to visualize your SaaS app’s active users, I put together a small R script on Github that will help you do just that.

The only thing that you need to provide the script is a CSV file that contains user IDs and dates that those users performed an action in your app.

For example, the test data set that comes with it contains user IDs and actions performed by users of one of my apps (Preceden, a web-based timeline maker) for the first year that the site existed (as determined by the automatically set created_at and updated_at values on the Ruby on Rails Active Record objects that each user is associated with):

2   2010-03-28
2   2010-04-09
2   2010-04-10
2   2010-05-16
3   2010-01-31
3   2014-05-07
3   2014-09-30
3   2015-04-11
4   2010-01-31
4   2010-10-06
...

In this example user IDs 2 and 3 each performed actions on four dates and user ID 4 performed actions on 2 dates. The script will analyze that data to figure out which cohort the user belongs to based on the earliest date the user performed an action and count that user toward the active users for each subsequent month that he or she performed an action:

monthly

As you can see there was a huge spike at the beginning of the year when Preceden launched on HackerNews and was subsequently covered on other tech sites, but by December only a fraction of those users were still active. On that note, I encourage you to strive to build a service like Buffer that delivers long term value so your chart doesn’t wind up looking like this one :).

If you have any questions or need help customizing the script in any way, please don’t hesitate to drop me a note.

Thanks Joel Martinez and Rob Felty for providing feedback on the code.

Redesigning Preceden’s Color Picker

Preceden, the web-based timeline maker that I launched in January 2010, has been slowly growing over the years and while I don’t spend that much time on it these days, I still try to put in a few hours each month to make it better and keep fresh on the code.

One feature that has always bugged me but that I’ve been procrastinating about fixing is the color picker for choosing the color of an event. Here’s what the interface has looked like for almost all of Preceden’s existence:

When I originally implemented this, my novice thought process went something like this:

  • Users should be able to choose any color they want
  • Building a flexible color picker is hard, so I’ll use an open source one

After a bit of research, I found one, modified it to get rid of some of the unnecessary fields, and added a recent colors section.

Perfect, right?

Well, it worked for the most part but if there’s one important thing I’ve learned building websites and web applications over the last few years is that the simpler you can make your interface, the better. Being able to choose any color sounds good, but in practice users have found this color picker confusing and hard to use. How do you choose normal green from the starting point above? Are you supposed to type something in that field on the right? Are are you supposed to know what hex color codes correspond to what colors?

If you’re building an application for designers having this level of customizability might be a good thing, but Preceden is used by a lot of students, teachers, and other people 99% of whom probably don’t know the difference between #FF0000 and #00FF00.

The straw that broke the camel’s back was this email that I received from a user, Caline, last Friday night:

Can you tell me why I might be having so much problems with the colour selector in Preceden timeline? I cannot get the slider or the moveable dot to work at all. To make colours I have been trial and error changing numbers of colours to make new colours, very frustrating. At least is there a list of colour numbers you can send me to enter?

Despite my best efforts to ensure the color picker worked across all browsers, issues always pop up every few months that make me want to find another profession. Because the color picker wasn’t working for her, Caline had resorted to typing in random numbers to generate different colors.

I responded to her email, apologized, asked her go to SupportDetails.com, download the PDF report, and send it to me so I could figure out what browser she was using in the hope that it would help me track down the cause of the bug. I also Googled “html color codes” and sent her a link to the top result, html-color-codes.info. I was about to move on and wait for her response to troubleshoot the issue when it hit me: this is crazy. The color picker has been a major pain for me and for Preceden’s users for years. It was time to fix it.

Using the colorful table on html-color-codes.info as a starting point, I set out to redesign the color picker from the ground up. Here is the result:

preceden_color_picker_new_525_v2

A few highlights:

  • Users no longer have an option to enter a hex value. If you can’t find a color on that palette that suits you, Preceden probably isn’t the right product for you.
  • The recently used colors are now actually sorted by when you last used it (the old version showed colors that were in use, but wasn’t sorted). It also shows 25 max instead of 10.
  • For new events, the random color that it chooses (from a predefined list that I created using the colors on this palette that I think look the best) is guaranteed not to be one of the colors you recently used. This is important because most users want color variety and without any checks in place, the color that it assigned to a new event might have been the same color that was used for the last event, making your timeline look not quite as good as it could.
  • The simplicity makes it much easier to ensure it works across all browsers.
  • Because it’s a bit wide, if positioning it like the screenshot above makes it fall off screen, it’s automatically repositioned so that it falls on the screen (important for students who typically use Preceden in computer rooms with small monitors).

All of these should contribute towards a vastly better experience for one of Preceden’s most frequently used features. And it took about 3 hours. Why didn’t I do this sooner?

I wrote Caline and let her know about the new color picker. A few hours later she replied:

Hey this works great now, love the new colours palette. Thank you!!! :)

This makes my work so much easier. Thanks!!

My question now is whether this can be simplified even more. Users might not even need this quantity of colors to choose from. What do you think? Any suggestions?

Marketing Mornings

At last year’s Micrconf Mike Taber, one of the event organizers, gave a talk on how setting up systems and processes can benefit your organization. One of his recommendations was to establish Marketing Mondays where you set aside one day a week to work entirely on marketing efforts.

The idea that you should have to set aside time for something as essential to your business as marketing might seem laughable, but it is a big problem for a lot of startups including my own. Given the choice between building something new and writing a blog post, for example, I’ll almost always start up TextMate and start coding. It’s not that I don’t think marketing is important, but that I love coding and I have this misguided belief that if I build a great product it will  market itself through other people sharing it.

If you build a good product, a fraction of your visitors will share it and if you’re lucky your startup will even grow organically through word of mouth. The reality, however, is that most startups will either grow too slowly or not at all if you’re relying solely on word of mouth. You absolutely need to find other ways of educating people about the existence of your product.

And while I know this is true, I still don’t always do it. For most of January I was working on the new Domain Name Trends tool for Lean Domain Search. From start to finish it took about a month to complete. Each day I woke up I had a choice of what to work on and I always chose to work on developing this new product at the expense of everything else. How much marketing did I do during this time? Take a look at Lean Domain Search’s news section:

lds_news_sidebar

I had one update on December 18th, right before I started working on the trend analysis tool. My next blog post was not until January 28th, the day I launched the tool. I did not do any major marketing activities during that time.

Would one interesting, well-written blog post per week have delayed the trend analysis tool launch by that much? Maybe a few days. But here’s the more important question: what’s my primary objective? It’s to grow Lean Domain Search (and my other product, Preceden). Building this new trend analysis tool was a major initiative and long term it may be a major factor in Lean Domain Search’s long term growth, but maybe not. I should have spent more time marketing while I was working on this new feature in order to keep a steady stream of new visitors coming to the site. Marketing is not something you do only when you need a break from coding.

With that in mind, I’m going to try out something new: instead of Marketing Mondays like Mike suggested, I’m going to practice Marketing Mornings. Every day from around 9am to noon I am going to work on marketing initiatives instead of product-building initiatives. This will include writing blog posts and tutorials, making videos, going over analytics data, analyzing conversion funnels, improving SEO, and more.

It’s hard to say for sure what the outcome will be or whether I’ll be able to maintain it long term, but I’ll probably be marketing about 5 times as much as I have done in the past so it will be interesting to see what happens. I’ll post updates here periodically on what marketing things I’ve done and what the results wind up being so that hopefully others can benefit from my lessons learned as well.

Wish me luck. :)

Emphasizing My Web App’s Free Trial Improved Its Sign Up Rate By 25%

Preceden, a web-based timeline maker that I run, is free to try but costs $39 once to upgrade to a pro account, which let’s you add an unlimited number of events to your timelines.

Conveying this limitation to visitors has always been a bit tricky. The more I emphasize the cost, the lower the conversion rate is. However, you don’t want to hide the cost completely, as that’s both dishonest and would lead to a lot of people signing up that have no intention of ever paying for it.

In the past, the Pricing and Signup page looked like this:

You’ve got your sign up form on the left with the pricing details on the right hand side of the page. To me, it’s clear that you can sign up, try it for free, and upgrade if you want, but my perspective is so biased because I built the service. What’s really important is whether or not it is clear to folks visiting the page.

On a whim, I decided to test a variation of this page with one simple modification. Instead of “Sign Up“, the header would say “Sign Up Now For Free“:

After a few weeks of testing, the results are in and they are nothing short of spectacular:

Per ABingo: “The best alternative you have is: [sign_up_now_for_free], which had 505 conversions from 1365 participants (37.00%). The other alternative was [sign_up], which had 385 conversions from 1305 participants (29.50%). This difference is 99.9% likely to be statistically significant, which means you can be extremely confident that it is the result of your alternatives actually mattering, rather than being due to random chance.”

In other words, 25% more people signed up when the headline read “Sign Up Now For Free” vs just “Sign Up”.

I’ve been running a lot of A/B tests on both Preceden and Lean Domain Search, but most do not result in a statistically significant change. This small change resulted in the second largest improvement to Preceden’s conversion funnel ever (first was some homepage variations I tested last year). Not bad.

Two final thoughts:

  1. Adding the word “Now” to the second headline may have impacted the results. A better variation to test would have been “Sign Up For Free” so that the impact of “Now” and “For Free” was not combined into a single metric.
  2. The pricing details have not changed, so why does this make such a difference? People weren’t reading the pricing information, right? But what if they still aren’t? What if more people are signing up now because they think the service is free? A better conversion event would be how many actually upgraded to a paid account, but because that number is relatively low (~4% of people who sign up) it would have taken a longer to get statistically significant results.

Preceden A/B Test Results: Apparently Folks Don’t Like Gradients

I’ve been running an A/B test on Preceden, my web-based timeline maker, for the last week to test the impact of gradient bars on the sign up rate.

Half the people saw solid color bars:

And half the people had a slight gradient fade added to them:

I measured the number of unique visitors who saw a timeline and considered it a conversion when the person signed up for an account. Note that the participant figures include anyone who viewed a timeline including existing users, people viewing someone else’s timeline, etc (ie, not just new users visiting the homepage). Because I’m just comparing the relative results, it doesn’t matter that the numbers include existing users, etc.

Users with non-CSS3 compliant browsers were also included in both test groups, but their browsers only rendered the fall-back solid color version. Since these people were evenly distributed between both test groups, it should not have an impact on the relative results.

I expected the gradient timelines to blow the solid colors out of the water, but the opposite was true (no = solids, yes = gradients):

The results per A/Bingo:

The best alternative you have is: [no], which had 102 conversions from 4166 participants (2.45%). The other alternative was [yes], which had 71 conversions from 4229 participants (1.68%). This difference is 99% likely to be statistically significant, which means you can be very confident that it is the result of your alternatives actually mattering, rather than being due to random chance. However, this statistical test can’t measure how likely the currently observed magnitude of the difference is to be accurate or not. It only says “better”, not “better by so much”.

Why did solid colors outperform gradients when (at least to me) gradients look much better?

My guess is that on average, the gradients look gaudy. People want simple timelines. Gradients are not simple.

If that is indeed the reason, it reaffirms what most of my previous A/B tests have taught me. Simple wins.

But I don’t know. Maybe I missed something. I’d love to hear your thoughts in the comments.

Why I’m Building Simple Tools to Help Market My Web App

I’ve been making huge pushes lately on Preceden, a web-based timeline maker that I launched just over two years ago.

Today I’m trying something new as part of Preceden’s overall marketing strategy and I’d like to share my thought process because I think other web developers might benefit from it too.

Background

As I noted above, Preceden is a web app let’s you create simple, multilayered timelines.

For example, here’s a screenshot showing the timeline of the events leading up to the crash of the Costa Cordia (you play around with the actual timeline here: Costa Concordia Timeline.)

Preceden is popular with students, teachers, researches, and genealogy buffs, to name a few groups.

Marketing 1.0

I love building tools, but I don’t love marketing them. By marketing I mean things other than building that contribute to getting new people to your site.

Marketing, however, is a huge huge HUGE piece of the puzzle and if you neglect it you’re going to be missing out on a tremendous amount of value. As Rob Walling notes in Start Small Stay Small: A Developers Guide to Launching a Startup, “Market comes first, marketing second, aesthetics third, and functionality a distant fourth.” In terms of how I’ve worked in the past, I almost reverse it: function first, aesthetics second, market third, and marketing a distance fourth. And I’ve learned the hard way how wrong I am.

Preceden has grown steadily with little effort on my part thanks to two things:

1) Word of mouth. People like it. They tell each other. On blogs, in class, etc.

2) Content generation. When people create timelines on Preceden, they can keep them private or, like the Costa Concordia example above, share them. The shared timelines get indexed by Google and over time the number of Google queries that Preceden ranks for has steadily grown:

So I started thinking about it: how else can I get more people to the site?

Marketing 2.0

There’s definitely a lot of room for improvement with respect to making Preceden easier to share (word of mouth) and SEO (content generation), but today I’m trying something new out that I think will be a big win.

Here’s the idea: Build free time-related tools on Preceden that attract the type of people who might convert into paying Preceden users.

A picture is worth a thousand words.

Google Adwords Tool tells me that upwards of 800K people search for date to date calculator per month:

The top result for date to date calculator is from DateAndTime.com:

The DateAndTime.com date calculator is garbage:

In my eyes, the things it does poorly are:

  • Different fields for the month/day/year
  • It asks me whether I want to include the end date in the calculation (why not just show me both results?)
  • If you want to include the time in the calculation, you have to go to another calculator that is even more complicated than this one
  • When you submit the data, it takes you to a different page with the results (why no Ajax?):

I spent today building a better version of this.

Here’s the result:

You can play with it here: Date to Date Calculator.

You can use the date picker to pick a date or enter one manually (optionally including a time) and it will Ajaximagically show you the result:

Notice:

  • The page title and the H1 are “Date to Date Calculator” (go go SEO)
  • The start and end dates support a wide variety of formats (and I didn’t have to do any extra work for this because I had already written the necessary modules for Preceden itself)
  • The results are fetched via Ajax and rendered below the inputs
  • Below the calculator are a Facebook like button (which points to Preceden.com) and FAQs to answer folk’s questions
  • And if you enter a date as the end date, it shows you the results including and not including that date:

Basically, I built a better mousetrap. And I hope people find it and like it better than the existing tools and some of them convert to paid Preceden users.

And there’s a lot more tools I can add to Preceden’s new Calendar Calculators page down the road too: Add and subtract from a date/time, countdown timer, etc.

How much of a difference will these tools make to my bottom line? Subscribe to this blog or follow me on Twitter @mhmazur to find out :)

One final note: As software developers, I think a lot of us tend to get caught up on solving complicated technical problems. Lean Designs, an HTML5-based web design tool that I’ve also been working on, is just one example. While these pursuits may be intellectually rewarding, let’s not forget that there are thousands of simple, real world problems that we could eliminate if we only applied ourselves to them. More than 800,000 people search for date to date calculators each month. Think about that. It’s mind-blowing.

Update: As Simon points out below, the 800K number is for broad match. The number of people who search exactly for “date to date calculator” is closer to 2,900. I decided to change the page from “Date To Date Calculator” to “Date Duration Calculator” which I think is more meaningful.