Dev

Calculating in the Database and Revealing Tendencies

Aug 11, 2014

This last week, I focused primarily on the Cushion backend. In working locally on the app, I realized that it was becoming sluggish—especially on pages with a lot of content. I’ve been building this app with progress in mind, so I haven’t been as considerate as I should be. I query the database far too often and calculate the results in Ruby when it could all be done in the database. Because of this, performance issues are apparent.

For example, a single request to retrieve a list of projects uses a single query for the projects, but then three additional queries for each project to grab the sum of its invoices. With my current personal data, this resulted in nearly 150 queries just to pull down my projects! Not good.

I knew this would happen when I wrote the code because, at the time, I just needed it to work. I’m being a responsible dev and using test-driven development for the backend, so in order for the tests to pass, they just need the correct result. Now that I’m further along, however, I also need the code to perform well. Luckily, I can go back and fine-tune the code, knowing that my tests are in place to double-check that it still works.

After getting up to speed with PostgreSQL, I realized just how much I could do in the database rather than in Ruby—apparently, much more than I imagined. I started by focusing on those invoices sums—paid income, unpaid income, and relative estimated income. The first two are straightforward—just paid invoices and unpaid invoices—but the last one is trickier.

Every project has an estimated income, which is a ballpark amount that this project could potentially bring in. It’s not a guarantee that you’ll reach that amount, nor is it a guarantee that it will only bring in that amount. It’s just a figure to give you an idea. With this figure, however, we can do some interesting things, like seeing how close or far off you were.

If we take the estimated income for a project and subtract the sum of its invoices, we can see the actual income relative to the estimated amount. As you’re working on the project, this can be useful for seeing how much is potentially remaining, if it’s capped-off at that amount. Or, if you’re working hourly without a cap, you can see how much more you’ve made than originally anticipated. Hopefully, you fall in the latter end of this example.

2014-08-11-client-budget

I wrapped up projects and moved up the chain to clients. For clients, I have these sums of income as well, but on a wider scale, combining the sums of each client’s projects. This is useful in seeing which clients bring in the most (in case you need to spread out your income streams) and which clients owe you the most (revealing a red flag going forward).

We also have a lot of data to play with in regards to scheduling. Since every project has both estimated and actual start dates and end dates, I’ve been able reveal projects that start late or drag on too long. This is useful enough on the project level, but if we take a step back and group the projects by client, we can learn a lot about client tendencies.

2014-08-11-client-schedule

Now, instead of sums, we can calculate averages. The three main columns represent the average duration, delay and drag of clients. With them, we can expose a lot. If a client consistently feeds you only small scope projects, you can know with confidence whether you can fit them in an open slot in your schedule. On the cautionary side, we can use average delay and drag to question whether you should still work with this client or just account for the delay and drag when planning the next gig with them.

2014-08-11-tooltips

I also improved the schedule tooltip to provide written details of what you’re looking at rather than forcing you to do the math—you can now point to a specific segment of a bar and see what it represents. For clients and projects, these segments include the delay, drag, duration and estimated duration.

Under invoices, using the dates assigned to each (issued, due, and paid), the segments represent how early an invoice was paid, how late an invoice was paid (more realistic), how long an invoice took to get paid, and how long until an invoice is due.

With these new tooltips in place, I feel like I’m going in a really good direction—taking useful data and making it easily accessible. Being able to see the data in table form is easy, but I’d rather provide the ability to interact with data in context.

Share this on Twitter or Facebook

Archive

  1. Our First Company Lunch
    Story
  2. How to embed Vue.js & Vuex inside an AngularJS app... wait what?
    Dev
  3. Funding Cushion
    Story
  4. Hiring a Team of Freelancers
    Story
  5. Taking a Real Break From Work
    Story
  6. Slack as a Notification Center
    Dev
  7. Document Your Features
    Story
  8. 300
    Story
  9. Vacations
    Design
  10. Offering Discounts
    Design
  11. Waves of Traffic
    Story
  12. Less Blogging, More Journaling
    Story
  13. Retention Through Useful Features
    Design
  14. The Onboarding Checklist
    Design
  15. Spreading the Word
    Story
  16. From Beta to Launch - The Subdomain
    Dev
  17. From Beta to Launch - Sign up
    Design
  18. From Beta to Launch - Messaging
    Design
  19. Launch
    Story
  20. Authenticating with 3rd Party Services
    Dev
  21. Intro to Integrations
    Design
  22. Inspiration vs Imitation
    Story
  23. The Emotional Rollercoaster
    Story
  24. Designing Project Blocks
    Design
  25. Everything in Increments
    Story
  26. Deleting Your Account
    Design
  27. Designing the Subscription Page
    Design
  28. Rewriting the Timeline
    Dev
  29. Restructuring the Individual Project Page
    Design
  30. Project Blocks
    Story
  31. Redesigning the Homepage
    Design
  32. Multiple Timelines
    Design
  33. Archiving and Estimate Differences
    Design
  34. Multiple Financial Goals
    Design
  35. Zooming in on the Timeline
    Design
  36. Currency
    Dev
  37. Preferences, Accounts, and a Typeface Change
    Design
  38. Sending Out the First Email
    Story
  39. Currency Inputs, Notifications, and Invoice Nets
    Design
  40. Dots and Lines
    Design
  41. Calculating in the Database and Revealing Tendencies
    Dev
  42. Improved Form UX
    Design
  43. Cushion is Online
    Story
  44. Schedule Timeline Patterns
    Design
  45. A Slimmer Schedule Timeline
    Design
  46. The Schedule Timeline
    Design
  47. Plugging in Real Data for the First Time
    Design
  48. Transitions and Project Lists
    Design
  49. Death to Modals
    Design
  50. The Individual Project Page
    Design
  51. Estimated Incomes and Talks with Other Freelancers
    Story
  52. Statuses to Lists and the Paid Beta
    Story
  53. The Timeline
    Story
  54. Invoice Terminology
    Dev
  55. Modal Forms
    Dev
  56. Wiring the Backend to the Frontend
    Dev
  57. Balancing Design and Dev
    Story
  58. Timecop, Monocle, and Vagrant
    Dev
  59. Going with Ruby and Sinatra
    Dev
  60. Ditching local-first and trying out Node.js
    Dev
  61. Switching to AngularJS
    Dev
  62. Building the Table with Vue.js
    Dev
  63. Clients, Projects, and Invoices
    Dev
  64. Introduction
    Story

Ask a Freelancer

A podcast series where experienced freelancers answer questions about freelancing.

Listen to the Podcast

Talking Shop

An interview series where we talk to freelancers about important topics in the freelance world.

Read the Interviews

Running Costs

Take a close look at the costs that go into running a web app and why we use specific services.

View the Costs

How It’s Made

Follow along with the journal for insight into the overall experience of building an app.

Read the Journal