Building Analytics

A key part of any build is figuring out what colleagues want to track and how to effectively manage that data. In this case, there is a lot of stuff to track. I recently finished building a hefty analytics components to the app and wanted to share my approach and some things that drove me crazy.

First, I had to really think through which analytics were important. The initial ask was for about 30 different types of analytics, but then, when adding in timeframes, and additional category components, we were talking about close to 400 queries… not super useful for an early launch. Additionally, through my experience with Neighborsations, I could look at these requests and know which ones were most important for raising funds, for identifying user paths, and which ones wouldn’t really be useful until we had a larger critical mass using the application. I also made sure to ask my team what the most important success factors were to them (ie- if this number isn’t what we want it to be, then the business is not successful and we need to make some hard choices, quickly… that’s actually a vital part to determining core metrics and something I learned from Steve Wendel who’s awesome at pushing you on those hard questions.)

The easiest way to do the queries was through writing DB queries, putting them into a model with methods and then creating a view. The app is an ember-appkit-rails app which kinda mushes the rails api and ember together but I decided to keep this simple and just run everything outside of the ember piece and just keep it as typical rails.

There were also a couple of options for running the number… we could have done a rake task or set up a chronjob. Right now, the approach is just to have the business folks hit that page whenever they want new numbers (with the understanding that they shouldn’t hit it too often because it kicks off a bunch of queries that hit the database).

To seed the data, I was originally leaning towards factory girl but decided to start with just creating the data I needed in the tests. What I didn’t realize is that the app automatically pulls in fixture data, so everytime I had a to eq it would fail because the number would be incorrect. That taught me… after spending probably too much time trying to figure out how to not have the test pull in the fixtures, I realized I should just embrace them, learn how to set up the fixtures for my tests correctly and use them to generate the data I needed.

Then I created a model with the class AdminAnalytics. Each analytic was a separate method. I think if I wanted to go back and continue to refactor further, then I could easily break the model into separate classes. For example, instead of having one overarching AdminAnalytics class, it would probably make more sense to have a Users class, a Posts class, and Ingredients class, etc.

Then it was down to the queries. I didn’t have much SQL experience and some of these queries were pretty complicated so it helped me to first write out all of the steps that I was looking for before translating it into an actual query. Once I did that, I could take those queries and use ActiveRecord to give me the rails magic that made the queries a little easier. For example, when joining tables in queries, you don’t have to note the join table… you can just note the two tables and activerecord will figure out the relationship between the two tables on its own. Then, because a lot of the queries involved profile type names or time parameters, I refactored by making those things arguments on the method that could be put in place in the view.

Then I created a controller which was literally one line and was able to create a view that just called the instance variable @analytics and the method with whatever arguments I needed. Bam! Analytics.

Now, once this was all done, we actually decided to pull the analytics out of the app. Instead of bundling these things together, we made it a separate app entirely that talks to the primary app in order to get the numbers needed. When we pulled it out, we used sequel which made it easier to pull those queries in (although still more difficult than just doing it right in the app). The nice part about the app was being able to use ActiveRecord but it also made the analytic piece dependent on a bunch of different models. For example, to get the total number of users, you just do User.count or to get the total number of posts, Post.count. You’re already depending on two different models here, User and Post. So, part of pulling this piece into a separate app was so that we were no longer relying on multiple models in order to get these numbers.

The other thing I really wanted to do was set up a simple dashboard using Dashing. I’d seen people whip these dashboards up in no time flat, so I figured I’d take a stab at it… boy was I mistaken. I think my journey to a dark place started with the decision to use dashing-rails instead of dashing. See, I figured, if I used dashing then I would have to create the connections to the database in order to get the information for the queries I was running. If I used dashing-rails, then the dashboard would be a part of the app and this part would be easier. (If you’re thinking about the paragraph above and thinking, wait a second, you pulled the whole thing into it’s own app anyway in the end, yes. I realize this and boy is hindsight 20/20). Dashing-Rails involves a bit more setup. You’ve gotta set up concurrency, you have to use a database that has lots of threads like puma, etc. The issues started there and just didn’t stop. First, I had an issue with puma and so I upp’ed the number of threads possible and that seemed to fix it. Then, there was a database connection issue. The DB connection would time out almost immediately. We got this working as well, but it still times out. After a certain number of rounds, the thing just kicks the bucket. Then, I had an issue where all my dashboard widget boxes would show up but nothing would show up in them. Sometimes, if I commented things out and then reimplemented them one at a time, they would work again. And there was no rhyme or reason about when the dashboard would start up and kick off, versus when it wouldn’t.

So, here’s the really annoying part. I FINALLY got the issues fixed (with the help of lots of pairing with a few different, very patient people) and pushed the dashboard to production, where everything promptly broke and nothing rendered correctly. Then we got it rendering correctly, but the queries still aren’t running correctly. All the while, I’m kicking myself because I was the one who said, “oh, and I’ll build this awesome dashboard to go along with the simple analytics view. It’ll be fun and shouldn’t take too long.” I think the dashboard was mostly a lesson in when to give up. I should have scrapped it after the first week and a half, but I was a little too stubborn and a little too determined to get it done.

So, that’s how I built the analytics. For my final thoughts, I think the moral of this story is to always keep it simple. Start small, get that shipped and continue to add and improve.

More Bot Bits

The final component of bot functionality was setting up the database. This was really the thing that I wanted to gain some experience with and by setting up a database not in a rails application, I was able to understand some of the components a bit better. For my purposes, this database would be used to keep track of points. Often times, people will award other people points based on funny or interesting things they post. They’ll also subtract points for things but in the past, we’ve had no way to keep track of this. This bot added the following functionality: adding and subtracting points, telling a user what their score is when they ask, and printing out the leaderboard when asked.

First, Chris and I chose a database. We decided to go with Sequel because it looked pretty interesting and simple to set up. I set up the connection first that created the database but then moved that into a separate file. This way I have the option to run the create table script only when I want to create a new database table. Then I set the database connection as a global constant like this DB = Sequel.connect('sqlite://bot.rb') to get things going and set out to write the related methods.

The first method looks to see if a nickname is already in the database or if we need to add it and acts on that.

def nick_id(nick) 
  set = score.where(:nick => nick) 

  if set.empty?
    nil 
  else
    set.first[:id] 
  end
end

So, we set the variable set which find the list of every score that has the nickname nick (nick being whatever the person’s nickname is). Then, if it doesn’t find anything, then it will return nil. Otherwise, the methods grabs the id of the first matching nick it finds with that name and returns it.

The second method inserts or updates the score.

def insert_or_update_score(nick, points)
  nick.downcase!
  if nick_id(nick)
    row = score.where(:id => nick_id(nick)).first
    updated_points = row[:points] + points      

    score.where(:id => nick_id(nick)).update(:points => updated_points)
  else
    score.insert(:nick => nick, :points => points) 
  end
end

So, first I need to make sure all the nicks are being evaluated in the same way. I discovered this when testing and found that two different nicks would be saved in the database based on if they were capitalized or not. Then, this method needs to fetch a record, then increment the number and then save it back. If there is no nick, then it creates the new nick in the database and gives it points.

Finally, I wanted to be able to lookup the score and if someone has no points then the method throws an exception.

def lookup_score(nick)
  score.filter(:nick => nick).first[:points] 
rescue 
  nil
end

I added a rescue so if there are any exceptions, the rescue catches the exception and returns nil. Chris warned me that when looking at future projects, the rescue option can be dangerous because it can hide serious errors but for our purposes, using rescue is perfectly acceptable.

THEN WE REFACTORED!
Setting the database connection as a global constant is not a good idea and having all of these methods in the main file just make it messy and unorganized. First, I changed the database connection to an instance variable by putting it into a singleton class and initializing it.

require 'singleton'

class DB
  include Singleton
  
  def initialize(file = 'sqlite://bot.db')
    @db = Sequel.connect(file)  #setup the DB connection
  end

This meant that I also had to change all of the DB[:score] instances in the code because it was now @db. Then, instead of just putting @db in each place, I extracted it into a method that does it for us.

def score
  @db[:score]
end

And that is how I set up the bot’s database.

Onto Session 2!

I fear I won’t be able to process everything and get it in a post before I start forgetting what my notes mean! Expect rapid postings in the near future.

Onto Session 2!

TL;DR: databases.

This session was about the Zombie apocolyse and data structures with Casey Rosenthal. I knew walking into this one that it would be a little over my head but after a post session debrief with Conrad (my guide) that helped frame database structures in general, everything clicked! I’m also just now starting to really get into database structures and understand more about how they work, how to work with them, and what are the ways you can structure different data types within databases so this ended up being a great session for me to be in. Also, it was about a zombie apocalypse so it had to be good. Let’s start from the basics. There are two types of databases, SQL databases and NoSQL databases. NoSQL databases are essentially anything that isn’t a SQL database. NoSQL databases can also be known as distributed key value databases. Examples of these databases include MongoDB and Riak. SQL databases include MySQL, SQLite, etc. There are a handful of differences but the main difference is that SQL databases are based in table modeling. SQL databases can be a bit more flexible if you have similar types of information coming in all the time, but they can also be harder to scale. SQL databases are also good as long as everything is on one machine because they are safe, have lots of features and have many other good aspects but features these same features are hard to implement once you are running on many machines. Why do you need to move to many machines instead of having just one machine? Because you can’t read the information quickly enough when there is a lot of it (essentially, the queries start taking too long and you need to split the information onto more than one machine). Finally, SQL databases enforce the structure. If you use a SQL database, all of the tables in the database require the same information. In a NoSQL database, this isn’t the case. NoSQL databases are designed to they can start as one thing but easily scale to become larger and hold different types of information. And because there isn’t the same table structure, it can be easier to store lots of different types of information. So, when using a NoSQL database, you don’t have to use commands like rake db:migrate because there are no data migrations (yeah, that kinda blew my mind and it was also where the whole rest of the talk suddenly clicked into place for me). AND there’s no schema file! A NoSQL database has tables but they aren’t specific so they’re called connections instead of tables. The downside, however, is that you can’t tell exactly what’s in the database unless you pull all the records

So, back to the actual talk…when thinking about what type of database to use, it’s important to think about how the information will need to be presented in the end. This was an idea that continued to surface throughout the talk.

Some important terms I learned when thinking about NoSQL databases (and databases in general) were high availability, strong consistency, and partition tolerance. High availability means you can connect to any part of the system and you can both read and write all the data. So, for example, if you have 3 different silos that each keep information, the databases are kept in sync with one another so that you can access all the same data from any of those silos. In other words, if a server crashes, the database still works (Casey has a great slide that shows this, so I definitely recommend looking up the talk on Confreaks when it’s up). Strong consistency means that parts of the database will remain in sync with one another. Finally, partition tolerance means that if the cable comes out of the wall, the database will remain running.

When you have a distributed system, there are a few data modeling options. You can do a document based inverted index or a term based inverted index. The document based inverted index means that you can write the information efficiently but that you’ll have an inefficient read process. The term based inverted index is the opposite. You have an efficient read but inefficient write system. So again, it comes down to really thinking about the data you have and what will be more important to your business processes.  Will you be reading a lot of data or writing a lot of data? The

He also talked about Highly Available systems (HA) of geohashes and one other type of HA system that I can’t remember. Lastly, I learned about some interesting components that are Riak-specific. Riak creates data siblings, which is also not possible in SQL but is possible in key value databases. Basically, when information is written into the system to the same key (so to the same data entry) from two different places (so two people are updating the same person’s file at the same time) the system will save both entries and connect them. In other systems, they just take the most recent timestamp as the most recent data but here, siblings are created and the two entries are both saved, become siblings and then the user is notified the next time that file is accessed being told “hey, you’ve got two entries here. Which one is correct?”

The rest of the talk went into some more detail on this information, how to locate zombies via zip code and other interesting components of the database but, for me, this is what I gleaned from the talk. For more examples, information, and the source code, you can check out zombies.samples.basho.com.