Building an MVP with Google Sheets — no code required

I’m always looking for opportunities to not code. On a new project, we’ve been using Zapier to extract data from a source and store it in google sheets. Then we manually categorize the data. One of our challenges on this project is presenting the right amount of actionable data to the customer.

After hacking our way to to accumulate the data using zaps, I had planned to build (code) a dashboard. But the dashboard will change many times as we iterate. So, I went looking for alternatives. I discovered that google sheets has an amazing range of functions that when combined can be useful as an MVP (minimum viable product).

Here’s an example of a thin MVP built using Google Sheets.

Screen Shot 2015-01-28 at 4.18.02 PM

The example is classroom demographics tool, using this dataset. The example contains a dashboard with charts and tab for displaying all data. The charts were generated within google sheets on a single sheet. Both this sheet and an  additional sheet were published to the web using google’s Publish A Document feature.

Contained on the dashboard is the capability to “drill down” into datasets. In this demo, there is a chart displaying the ‘home states’ of the students. I link this chart to relevant datasets using google sheet’s query language.

Google allows you to query a spreadsheet as you would any database and return the result as html. For example, in ‘Student Home State’ chart, I create a link for each row, using the hyperlink function and a query as the link.

For example, to drill down and see the students whose home state is ‘NY’, I hyperlink the text ‘NY’ and link it to “https://docs.google.com/a/playpenlabs.com/spreadsheets/d/1PeK8vnm42-FM8fvhJWlZF2N8Y-XEUyL90timA_WG2J0/gviz/tq?tqx=out:html&tq=select+A,+B,+C,+D,+E,+F+where+D=%27NY%27&gid=0

Screen Shot 2015-01-28 at 4.36.51 PM

This link is generated using the link to your spreadsheet as a base. My link is https://docs.google.com/a/playpenlabs.com/spreadsheets/d/1PeK8vnm42-FM8fvhJWlZF2N8Y-XEUyL90timA_WG2J0/edit#gid=0. I remove the “/edit#gid=0″ and replace it with “/gviz/tq?tgx=out:html”. Which gives becomes “https://docs.google.com/a/playpenlabs.com/spreadsheets/d/1PeK8vnm42-FM8fvhJWlZF2N8Y-XEUyL90timA_WG2J0/gviz/tq?tqx=out:html

I then add a SELECT query to the url. If you are unfamiliar with SELECT queries go here for a basic overview. Be mindful that google allows a limited set of clauses and all clauses should be encoded. (google has a tool to help you encode the query) In this case, I want to show all columns to for when the column home state is equal to ‘NY’. My query looks like: SELECT A, B, C, D, E, F WHERE D=’NY’

After encoding, my query I add it onto my url. I also add back on gid which tells what sheer to use. My resulting url is ‘https://docs.google.com/a/playpenlabs.com/spreadsheets/d/1PeK8vnm42-FM8fvhJWlZF2N8Y-XEUyL90timA_WG2J0/gviz/tq?tqx=out:html&tq=select+A,+B,+C,+D,+E,+F+where+D=%27NY%27&gid=0

Another cool thing about this google sheet mvp is that authentication is already baked in. The published html is limited to the permissions set on the original spreadsheet.

This is a pretty rough hack and can be used in cases where visual design faux pas are forgivable because the value provided is greater.

Complex Queries + Basic Statistics in Rails

I inherited a rails codebase that was written about 5 years ago. The codebase is a web application for a nonprofit that does leadership training and assessments. Training participants perform a self-assessment and invite peers to evaluate their leadership. Each training participant receives a report that shows their self score in relation to peer scores, and other leaders who have taken the same assessment. The nonprofit’s database contains more than 80,000 rows of assessment scores. Not exactly “big data”, but not exactly small either.

Each assessment report contains roughly 20 – 30 assessment questions. Each question shows the self-score, peer scores, average peer score, and the quartile (compared against all previous assessments).

For each question, I looked up the peer evaluation scores for the participant:

def numeric_answers_for_q(question_id)
  Answer.joins(:evaluation, :question).where(:evaluations => {self_eval: false,
    participant_id: @participant.id,
    completed:true},
    :questions => {id: question_id, answer_type: 'numeric'}).
    where.not(numeric_response: 0).
    where.not(numeric_response: nil).
    pluck(:numeric_response)
end

In this case, I found all numeric answers, (there are also text answers), for completed peer evaluations for the given question and participant. Zero and nil answers are considered as not applicable, so we ignore these results.

Once I receive an the array of peer answers, I create a histogram with the array. The results of the histogram are later displayed.

def histogram_for_q(answers)
  histogram = Array.new(11) {|i| 0}
  answers.each do |a|
    histogram[a] += 1 unless a.nil? || a.zero?
  end
  return histogram
end

I also need the mean score.

def mean_score_for_q(answers)
  answers.sum.to_f/answers.length
end

If I only needed the mean score, I could’ve replace ‘pluck(:numeric_response)’ with ‘average(:numeric_response)’ in the numeric_answers_for_q method. I do this when I need the mean scores from previous assessments for comparison.

def self.peer_assessment_scores(question_id, participant_id)
    avgs_by_participant = Answer.joins(:evaluation, :question).where(:evaluations => {self_eval: false,
                                                     completed:true},
                                                     :questions => {answer_type: 'numeric', id: question_id}).
                                                     where.not(:evaluations => { participant_id: participant_id }).
                                                     where.not(numeric_response: 0).
                                                     where.not(numeric_response: nil).
                                                     group('evaluations.participant_id').
                                                     average(:numeric_response)
    avgs_by_participant.map {|k,v| v.to_f }
end

This last method I found the pretty helpful. Being able to find grouped averages based on complex conditions.

Code reviews are welcomed. Please post suggestions in the comments.

preview

Stop sending fake ‘personal’ emails during onboarding

I’ve been thinking recently about current onboarding techniques used by Saas products. Many of the products that I sign up for send an automated email after signup. I usually receive one of two types of emails. The first type of email is sent from ‘the company’, with ‘thank you for signing up’ content, and other helpful links. The second type of email is the automated email sent by ‘a person’, sometimes this appears to come from the ceo or account representative email address. This email is typically plain text and contains text to the effect of ‘I am here to help, reply to this email with any questions’.

It is the second type of email that annoys me the most. The recent hbr article, Customer Service needs to be either more or less robotic, helped me to articulate why this sort of email annoys me. It is pretty obvious that the ceo of the company or the account representative is not waiting by their email to send a personal welcome to every person who signups at any given time of the day. This doesn’t mean that the ceo or account representative does not want to personally welcome every person, its just not realistic. Imagine walking into walmart and the greeter is a life-sized cardboard printout of the ceo. The cardboard is playing a recorded script on loop, ‘Hi, Welcome to Walmart, let me know if there is anything that I can do.’ This would seem absurd, and far from authentic, since there really isn’t anything a cardboard printout could do. Well, these are my thoughts towards automated personal emails. My preference is for the robotic welcome email that contains helpful links to get me started.

Now, it is possible I’m the anomaly in this case, and customers respond greatly to these automated personal emails. If that’s the case keep on keeping on. If not, here’s my non-validated suggestion.

Send the automated welcome email from the company. In the email, place the ‘right amount’ of content to help the customer get started. Do an introduction within the email to the person who will be available to answer questions or feedback, include contact information. You can also let them know that they may receive an email shortly that person. Then if the ceo, personal representative, or whomever wants to welcome the customer, then have them personally write the welcome email. Just my two cents.

Image provided by Freepik

I’m in Tech And Had My Eggs Frozen On My 35th Birthday

Last Wednesday, October 8th, I turned 35. On Friday, October 10th, I underwent an egg retrieval process for the purposes of cryopreservation. Even as I write this post, nearly a week later, I still feel the pressure of swollen ovaries, and other annoying side effects from the process.

This week, the week after my egg retrieval procedure, one of the hottest topics in tech news, is that Apple and Facebook will cover the cost of egg freezing for employees. The articles written suggest that Apple and Facebook are allowing the benefit so that their female employees have the option to focus on their careers without the stress or worry of reproductive timelines.

Since the story broke, there has been a number of debates on whether this is a good thing or bad thing for women. Both side of the debates are helpful and have lead to further discussions on creating better workplaces for women. I believe Facebook and Apple are doing a great thing in expanding employee options by covering non-medically necessary treatments, in addition to covering medically necessary fertility treatments. Sure, there is some work to be done in ensuring employees also have flexible work options to care for a family while working. But this does not and should not take away that these benefits are a good thing. These discussion are great, but if we were really honest, these discussions are just the privileged debating benefits for the privileged. I believe many of us are missing the opportunity to have a more impactful discussion on reproductive health issues and the responsibility of both the government and insurance companies.

In the case of someone who is actively trying to have children now, and is unable to do so for medical reasons; what does it look like if that person does not work for Apple or Facebook? Well, for many women, they are left with either finding a way to pay for treatment out of their pockets, forego the option of having children, or adoption (which is another discussion and also filled with many complexities). This is because the state of California does not mandate insurance companies to offer fertility coverage such as cryopreservation, even if it is medically necessary. Therefore, many insurance companies operating in the state of California do not cover these treatments or they cover it very poorly (downright inadequate).

Me being personal:
I went through the egg retrieval process for medical reasons. The procedure was deemed medically necessary in order for myself and my husband to have children. We were one of the privileged ones and was able to find a way to pay for the treatment.  And this is what it costed us:

amex statement

In case you do not have a calculator, total costs this month were $17,100.21: $11,605 for the procedure and $5,495.21 for prescription drugs. Add that to the $30k, we’ve already spent this year in previous failed attempts. As I mentioned before, we consider ourselves privileged to be able to pay this. (it wasn’t easy financially, more like barely covering the month’s rent easy, but we found a way) However, my thoughts are constantly wondering about those who are not able to pay these fees. What about them?

Here’s my ask:
Let’s change the discussion. Let’s support every woman’s reproductive health right, whether at Apple, Facebook, or the corner grocery store. One way to do this is to ask your congress member to support affordable assisted reproductive care. The nonprofit, Resolve.org, makes it easy to send a letter in support to your congress member. And if you’re reading this post, it’s even easier, just click the link below to support reproductive rights. https://secure2.convio.net/res/site/Advocacy;jsessionid=81B399F8646FFB4652B96EE3299CE0C7.app263b?pagename=homepage&page=UserAction&id=499#.VEAzO9R4rWM

My swollen ovaries and my overworked amex card thanks you!

Passion Projects While Procastinating

There are about 20 items on my passion project list. Lately, I haven’t had much time for any passion projects. Most of the projects are time intensive, write a book, build silly game app, and so on. However, tonight, in avoidance of doing other work, I turned my attention to making a board game. I started by setting goals for the game. For example, what was the purpose of the game. I decided on it being a game for children that teaches them the basics of coding. The basics of coding being assignment variables, functions, conditionals and loops. For each subject, I came up with examples using real world objects. The examples for variables and conditionals were the hardest to discover. After this, I then brainstormed a theme for the game. I came up with a treasure hunt theme, in which each player uses pseudo code to move around the board in search of treasure. My draft board game looked like this:

IMG_1838.JPG

My husband and I played a couple of rounds, and discovered some gotchas that will need to be fixed in the game rules. But overall, it was fun, and I felt less guilty about my procrastination.

My next step is play a few rounds with friends. So, if you’re in the San Francisco area, and would love to test it out, let me know. In addition, if you know any teachers of middle school students who would be interested in having their students play the game, please let me know. Would love to see how students respond to the game.

****Post written from phone, excuse any typos

Everything is Awesome! A Day in the Life of this Entrepreneur

Wednesday, October 8th, 2014

3:30am My alarms goes off

Hurried out of bed. Poked my husband and said, “Let’s go! You don’t want to miss it.” We had discussed the plans for my birthday the night before. I was uber excited about the lunar eclipse, and we spoke in length about the best place to see the eclipse in full. Initially, we discussed camping on Mt. Diablo, but it wasn’t possible, since I had to be back in San Francisco for an 8:30a doctor appt. Finally, we decided, the best plan was to sneak up on our apartment’s rooftop and watch the eclipse from there. Then at 6:00a, walk to the top of Twin Peaks and have a morning picnic as we watched the sunrise and moonset simultaneously.

What ended up happening was: we opened the blinds, threw some pillows and blankets on the floor, and watched the eclipse from the warmth of our house, inside. By 5:00am, we had already drifted back to sleep, and knew that Twin Peaks was not happening.

Lunar Eclipse Seen From Our House

Lunar Eclipse Seen From Our House

6:00am – 7:00am  Husband’s alarm goes off every 10 minutes from him hitting the snooze. Seriously, he needs to stop that. 

7:10am Husband’s alarm goes off again and I finally wake up in a rage ready to throw his phone out the window.

I walk into the kitchen with a pissy mood, ready to calmly discuss with my husband about his fucking alarm. Oh, pancakes and freshly made bread made for me. My husband, “I had packed a bag to bring the camping stove, I was going to make you pancakes on Twin Peaks, but you looked too comfortable in bed, so I was going to bring it in to you.” Black girl blushing.

8:15am Crowded Bus ride to the doctor’s office.

8:45am – 9:30am Doctor Visit

Found out good news at today’s doctor visit. For the past two, weeks I’ve had a daily doctor’s appointment in the morning. Basically, the result of today’s doctor appointment releases me from having to go every day. Awesome sauce! Sidenote: Nothing serious here, just time consuming.

9:30am Bus ride back home for my 10am

10:00am-11:00am Pair programming

Troubleshooted a problem I was having with on one of my test specs for a client project. I used a service called Airpair in order to pair with another developer.

11:00am – 12:30p Lunch and prepare for 12:30p Customer Development Call

I had the chance to sneak in three personal calls. I caught up with an old friend from Philadelphia, and also spoke to my sister. The inlaws (from Denmark) called to wish me a Happy Birthday. I was successfully able to show off my new Danish skills, thanks to my Monday night Danish classes. All of these calls and another call (later in the day from another sister), were the highlights of my day. I also didn’t realize until much later that I never actually ate lunch.

12:30p – 1:15p Customer Development Call

Interviewed a potential customer to better understand how her company currently does customer support. We are working on a customer support tool at Playpen Labs, so customer interviews are crucial.

1:30p – 1:45p Budget and Scope Call with Client

1:30p Received voicemail from doctor. Change in meds, need to pick up new meds to start tonight. Specialty pharmacy is an hour drive away.

2:00p – 2:30p Catchup with Husband/Cofounder

My husband also had a customer development interview today. We quickly caught up and compared notes on our learnings from the calls. We also decided to strip out some of the features in our prototype, based on today’s learnings.

2:30p Picking up Zipcar with husband for drive to Pharmacist when I realized that I hadn’t eaten since 7am.

2:45p Picking up Zipcar with husband after grabbing a burger.

3:45p Arrive at pharmacy. Prescription order is not ready. Wait around until order is ready

5:00p Start drive back to San Francisco, traffic and all.

My sister called during the drive, which made the traffic manageable.

6:30p Arrive home after dropping off Zipcar

6:30p – 7:30p Procrastinated or Was Lazy (maybe both)

Sat on the couch and complained about being tired. Then debated with myself on whether to start some work now or start fresh tomorrow morning.

7:30p Dinner

8:00p Wrote a list of all the things I did not do today, and will have to do tomorrow and most likely over the weekend.

The list went something like this:

  • Prepare and send a client contract
  • Prepare and send a client proposal
  • CODE on client project
  • CODE Update prototype based on today’s learnings
  • CODE Catch up on ios class

After, I wrote the list, I decided that Everything Was Awesome! And I was going to write a blog post about it. Happy Birthday to Me!

Productivity Hack: Staying In Touch

for the tl;dr – 1)Create a mailchimp mailing list for those you want to stay in contact. 2)Once a month (or whichever frequency works for you) send an email with a personal touch and ask that people reply with their latest happenings. 3)Create an email address to easily auto-subscribe new contacts to the email list. 4)Create a zapier tasks that will add new subscribers to your mailchimp, when a new email is received by the created email address. 5)To add new contacts to your list, send them an email and bcc your created email address.

I’m absolutely miserable at staying in touch. There are so many people that I meet. And I want to know with all these people what they have been up to, what they are working on, any personal status changes, what can I congratulate them on, and/or what areas of their lives need support or encouragement. In the past years, I’ve defaulted to facebook to help me connect and keep my relationships with people that I meet. However, I currently have 1170 friends on facebook and with the fb algorithm, I usually only see the noisy people on my timeline. If I switch to the “most recent” view on fb, it still only shows status updates for the time that I am logged in. Combine that with the fact that a lot of people don’t post much on fb anymore (including myself — but I do lurk *hehe*), and its impossible to stay in touch. Oh, and I’ve never been the type of gal who picks up the phone (sorry).

Most people will argue that its impossible to stay in touch with everyone. Especially, as you get older, move around, and change jobs. I do agree, but I still would like to make some effort to maintain contact. Besides having a general interest in people that I care about, there is also a benefit to “networking” within your existing networks. There are many people who find their next job, opportunity, or mate from introductions from friends. And you never know who will connect you to that “next one” and vice versa.

I am a problem solver. My problem is I want to stay in better contact with friends, family, and associates. My first attempt at solving this problem, was to build a complex software application that keeps an eye on who I interact the most with on fb and suggest people with whom I don’t interact with often. But fb’s graph api isn’t well suited to do this. I also had to rethink a simpler solution.

Here’s the solution that I came up with: Once a month, I’ll send an short personal email out to family, friends, and associates, sharing my latest news, and invite them to reply to the email with their latest. Last month, I sent the first email. The email was sent in mass using mailchimp. Although, it was a mass email, my words were personal. I added a huge caption at the top of the email explaining why they were receiving the email, and it was ok if they wanted to unsubscribe to the email. I also asked friends to let me know whether they hated or loved my approach to staying in touch. Honestly, I was expecting most people to hate the email and unsubscribe with no response. It was quite the opposite. Most people replied and told me they thought it was a great idea. In addition, I learned a lot from the responses, and was able to reestablish contact with many friends. Some of my friends had recently moved or switched jobs. Some shared books that they were reading, and for a few, we made arrangements to meet up in person. I only heard from one person who responded and stated that he didn’t like the email because he thought it was impersonal. I was even thankful for his honest feedback. Overall, my first month of the “keep in touch” email was successful.

Later in the month, as I met more people at events, I found it a pain to log onto mailchimp to add new contacts to my email list. So, I created a Zapier task. Normally, when I meet someone at an event, I always send them a “Nice to meet you/Stay in touch” email. For the Zapier task, I created an email account specifically for this task. So, now when I send a “Nice to meet you” email, I bcc the created email account. The Zapier task is triggered once the account receives a new email, and then it automatically adds a new subscriber to my mailing list. And this is my staying in touch productivity hack.

How do you stay in contact with your network?