Skip to content

Metabase: An Easy Query Builder For Your RDBMS

If you have a relational database as the primary datastore for your application, and you want to easily allow non technical folks to build reports against data in that database, I highly recommend evaluating metabase. This open source query builder is dead simple to install (takes about one minute on a free heroku dyno). You can create individual users, and they all get access to an easy to use interface so they can look at data in your tables. All the nomenclature is non technical, nary a select, group by or from clause to be seen. It’s written in java, but you don’t have to know java to run it.

We haven’t used this for long, so I can’t talk much about the warts, but I was referred to this by someone else who had great success in using it in their organization. The only downside that I’ve seen so far is that joins are not supported, so you end up having to create views if your database is normalized. More on that decision.

Things I wish I knew about Stripe

Caterpillar
Striped, but not charging your credit card

So, at The Food Corridor, we’ve been using Stripe happily since we launched in June of 2016.  As a developer, I’d used Stripe before in a couple of different ways, but this has definitely been my most sustained use of the payment service.  (If you don’t know what Stripe is, it is an API that makes charging customers as easy as an API call.  More here.)

I wanted to outline some of the things I’ve learned from months of using Stripe.

  • Stripe supports pulling money directly from bank accounts, via ACH, but it really isn’t the same ACH as your bank lets you do.  This is because Stripe isn’t a bank.  The biggest thing to be aware of here is that Stripe ACH takes 7 days to arrive in your bank account.  Another issue is that you have to do verification.  They have two ways of doing verification–micro deposits and Plaid.  Plaid is instant, but only supports major banks, which was a non starter for us (updated 9/8: Plaid supports around 1000 banks now).  The code for micro deposits is straightforward, but be prepared for some customer support issues.  Stripe deposits two amounts and withdraws just one amount, which was confusing for some of our users.  It also takes a couple of days, so if your users are hot to spend money, Stripe ACH may not be a fit.  The win?  Definitely cheaper.  (And I didn’t find any other service that would support both credit card and ACH transactions that was developer friendly.)
  • Don’t forget to set up your webhooks out of the gate.  Stripe mentions this, but I glossed over it in the early days, and missed some events that were important.  (The most relevant is that ACH is asynchronous, so when an ACH transfer fails, it is reported via webhook.  If bank account verification doesn’t work, you’ll get a different kind of webhook.  Review the docs and set up webhooks for all the ACH events.)  If you don’t have time for a full featured webhook processing implementation, Zapier can just send the webhook data to your email. This can be a great stopgap solution.  Or you can use stripe_event.
  • Per support, if a webhook post fails (because your app is down, for example), they are retried once an hour for 72 hours.
  • Speaking of stopgap solutions, the Stripe Dashboard is fantastic for manual processes.  Just because you can automate everything via an API, doesn’t mean you should.  There can be some complicated edge cases with payment processing, especially around refunds, but they can easily be handled with a google doc of instructions and the Stripe Dashboard.  I have found only one use case that the API can handle that the dashboard cannot (a partial refund of an ACH transaction).
  • I have found Stripe support to be excellent, quick and knowledgeable.
  • Occasionally customer charges will be declined because of bank fraud triggers.  Expect to occasionally ask your customers to call their bank.  (I think this has happend about once every third month).
  • Disputes are a total pain, because the process is opaque and slow (expect a resolution in about two months and know you are not in possession of the payment during that time).
  • Make sure to capture the payment id anytime you charge a card or run ACH.  It will make future automation a lot easier.
  • Monthly plans are complicated, so if you can lean on Stripe for management, even if you are doing manual plan management (applying coupons, adding, or removing users from plans via the dashboard), do that.
  • The first payment you charge takes 7 days to move from stripe to your​ bank account.  This is for fraud protection.  Payments thereafter typically take 2 days (but it depends on your country and industry).

And here are some special tips if you are using Stripe Connect (their marketplace product).

  • Read the docs!
  • Remember that first payment timeline?  It applies to every one of the connected accounts.  Think about charging your own credit card as soon as you connect an account to help with customer cash flow.
  • Consider whether you want to use managed vs standalone accounts.  Managed accounts are a lot more work but allow you to have a seamless UX that you control.  Standalone accounts, which we use, are far quicker to setup.  I think this depends on the number of sellers you have in your marketplace.
  • You also want to think about whether to place the charges on the platform account or on the connected accounts.  A major factor there is who bears the Stripe fees, the platform or the sellers.  We charged on the platform account because we wanted all our data in one place.  If you are selling plans, you can’t charge on the platform and use Stripe plans.
  • If you are charging on the platform account, and are using standalone accounts (where the sellers have to set up a stripe account) your sellers won’t see charge descriptions unless you manually copy the description over.  The code looks like:

# this will let the sellers know what invoice the charge was for
transfer_id = charge.transfer
transfer = Stripe::Transfer.retrieve(transfer_id, expand: ['destination_
payment'])
payment_id = transfer.destination_payment
payment = Stripe::Charge.retrieve(payment_id, {stripe_account: destinati
on_account_id})
payment.description = description
payment.save

Happy charging!

Presenting on Stripe tomorrow

Excited to say that I’ll be presenting on my company’s use of Stripe at Boulder.rb tomorrow.  Working title of my talk: “Do you like Money?”.

What I’ll cover:  an overview of the service, real code, testing, operational practices, and gotchas.  This will be based on my experience at The Food Corridor, where we’ve processed over $250k of transactions through Stripe.

Hope to see you there!

Handling Bookings with Shopify

calendar photo
Photo by Michael McCarty

I am doing some research for a possible client engagement.  The client has invested in their Shopify storefront, and are extremely reluctant to move away from it.  Updated 8/27: the client is MM Local Foods, and the issue is with their Harvest Share.

However, a significant subset of orders placed through this system have ‘pickups’ associated with the order–these are events where the customer picks up the product.  There are multiple locations and multiple dates, and the customer can switch from one pickup to another at no cost.

Shopify, to put it mildly, doesn’t handle this use case well–it is about at easy to modify an order in Shopify as it is to read that Aztec calendar to your left.

Edit Order is the only Shopify app that I could find which will let anyone modify orders, and it has significant limitations:

  • only staff with access to /admin can modify orders.
  • it doesn’t actually modify an order.  Rather, it deletes an order and replaces it with a modified order (order #204 becomes order #204A).  This means you have to re-run the payment process.

I also looked at BookThatApp, which specifically handles ‘bookings’ within the Shopify framework, for services like tours or piano lessons.  Unfortunately, this service doesn’t let the customer reschedule the booking themselves (I asked BookThatApp support to be sure).

So, I spent a fair bit of time wandering the internet, looking for scheduling and booking SaaS apps that:

  1. had an API that could be integrated with Shopify
  2. would handle events at specific dates, times and places rather than letting the customer pick freely from from a weekly availability calendar (such as for piano lessons or massage appointments)
  3. let the customer modify their pickup without calling customer service
  4. looked professional
  5. wasn’t too expensive
  6. wasn’t too cumbersome to manage from the business side

The bad news is I didn’t find any software that fulfilled all requirements, even after hours of searching and signing up for about ten different applications (at least they all had free trials!).

I was astonished–was I not searching the right keywords?  Is this such a niche need?  A lot of scheduling software failed on criteria #2.  Eventbrite failed criteria #6 and #3.  Most booking software failed #5 and #3–which makes sense as they are aimed at tour companies who don’t want customers changing their tour dates without talking to someone.  I looked at some class scheduling software, but couldn’t figure out how to make it work.

The good news?  I found one solution that does almost everything above–it does fail criteria #1, but I think it is the best of the worst.  The other alternative is to write custom code, and that always worries me.

Why does writing custom code worry me?  One word: maintenance.  Especially for a small, non software focused business, maintenance of custom software is costly.  Instead, it is better to conform your business processes to the SaaS application which best fits them, and let someone else shoulder the burden of maintenance.  This is not always the case–sometimes needs are so precise and static that custom software is the right answer.  But my default is always to look for other solutions.

I’ve outlined some of the pros and cons to the customer and am waiting to hear back on how to move forward.

And if you know of any solutions that might be better options for this customer, I’d love to hear of them.

The limits of Google forms

paperwork photo
Photo by Harald Groven

I spent a fair amount of time in the spring working on Google forms. If you have Google apps for your domain and are doing any kind of data entry at all, you should use Google forms. With this, you can create a web and mobile friendly data entry form easily, with validation, for free.

And by you, I mean ‘you, the non technical user’–a big win.  I’m a big big fan of anything that removes developers from the loop because they are too often the bottleneck.  Of course, as you’ll see below, Google forms is not entirely the land of milk and honey.

Any data entered typically goes into a Google spreadsheet for easy batch processing. You can limit use of the form to your Google apps domain, too.

Here are some ways that I’ve seen Google forms used to eliminate manual re-keying of data:

  • Event RSVPs
  • Contact forms
  • Order forms for collateral
  • Transaction reporting
  • Timesheet or vacation tracking

Google forms are extremely flexible, but do have some significant limitations (which is why other form building companies like Wufoo haven’t had all the air sucked out of them).

Some limitations and issues are:

  • If you post to a Google spreadsheet, the typical use case, you are bound by spreadsheet limits: 400k rows, 256 columns.
  • You can’t host the form on your domain. The best solution to this issue I found was to do a URL redirect of a domain name. For example, from someform.example.com to the Google form URL. This lets you replace the form while still sending people to the same URL.
  • You can’t have a form email an arbitrary address on submission without custom code.
  • Multi page forms are possible, but clunky.
  • Validation is limited, though using regular expressions gives you a fair bit of power (but then takes form creation/maintenance out of the realm of the non developer).
  • UI customization is limited. A Google form will always look like a Google form (short of serious server side gymnastics). It will always have the ‘powered by Google forms’ link, the same crappy ‘response received’ page, and the same horrible handling of closed forms (an unmodifiable message from Google, with no way to customize it).
  • Option lists are static (though if you use formRanger, you can alleviate this issue).
  • File upload is not supported. Seriously. Even though Google wants you to use Google Drive.

As alluded to above, you can use Google Apps Script to alleviate some of the issues with Google forms. However, doing so pushes the maintenance of that form into developer land (or at least ‘power user’ land).

Even with all the warts, Google forms is a powerful tool. And did I mention it is free? If you or anyone in your business is currently doing manual data re-entry, and the limits above haven’t scared you away, I’d take a long hard serious look at Google forms.

Building an automated postcard mailing system with Lob and Zapier

Courtesy of smoothfluid
Courtesy of smoothfluid

I was looking at automated paper mailing systems recently (and listed what I found), and was especially impressed with Lob, especially the ease of its API.

Among other printing services, Lob will let you mail a postcard with a custom PDF on both sides, or a custom PDF on one side and a text message on the other, anywhere in the USA for $0.94.  (Sorry, not sure about international postcards) The company for which I work sends out tens of thousands of postcards every quarter. The vendor which we use charges them a similar fee (less, but in the same ballpark) but there’s a manual process to deliver the collateral and no API. So an on-demand, one by one post card sending system is very interesting to me.

Note that I haven’t received the Lob postcard which I sent myself, so I can’t speak to quality. Yet.

The Lob API is a bit weird, because the request is form encoded rather than a JSON payload.  It also uses basic auth, but only the username, not the password. But the API seems to have all the pieces you’d need to generate all kinds of postcards–reminder postcards, direct mail postcards, photo postcards, etc.

After testing out the service via the web interface and cURL examples, I thought that it’d be fun to build a Zapier zap. In particular, being able to send a postcard for an entry in a Google spreadsheet seemed like a useful use case. Plus, Zapier is awesome, and I’d wanted to test out their integration environment for myself.

So, I built a Zapier integration for Lob, using the Zapier developer docs in combination with the Lob developer docs. It was actually easy. The most complicated step was translating the Zapier action data, which is a one or two dimensional array of typed data, into the Lob data format, which wanted a couple of text fields and two address arrays. Zapier has a scripting environment that let me modify data from APIs pre and post send, and even had an example about form encoded APIs. Zapier’s JavaScript scripting development environment was full featured, including syntax and error highlighting. It had no real debugging available, but I could use the venerable debug-by-log-statement method fairly easily.

Where could I take this next? Everywhere people use postcards in real life. The postcards depend on PDF files (see a sample), so if you are generating a custom postcard for each interaction things become more complex, but there are a few APIs (based on a 30 second google search, here and here) available for dynamic PDF generation. There are also limits on API call throughput, if I stuck to the Zapier integration–I could send at most 300 postcards a day, unless I managed multiple spreadsheets.

I see reminders of high value events (dentist, house maintenance, etc), contests and marketing as key opportunities for this type of service. And if I had a product where direct mail was a key component, using Lob directly would be worth serious consideration.

Regarding the Zap, I believe I cannot make this Zap available to anyone else. Since I’m not a representative of Lob, I couldn’t commit to maintaining this Zap, and Zapier doesn’t want to have any of their customers depending on an integration that could disappear or be unsupported at any time–a fair position.

If the Zapier or Lob folks want to take this integration and run with it, I’d be happy to share my code–just leave a comment. If anyone else is interested in being able to generate Lob postcards from a Google spreadsheet (or any other compatible API) via Zapier integration, let me know and I’ll see what I can do.