How to extract a contact list from BambooHR and import it into Google Contacts

BambooHR is an HR SaaS used to manage employees.  It is typically used as a system of record for employee details.  Unfortunately, BambooHR doesn't make it easy for your digital address book to use the contact information it contains.  Two ways the product could be improved to help out its users in this respect are:

• Offer CardDAV service.  This is by far the best option.  This would enable you to connect iOS or OS X directly to BambooHR leaving Bamboo in charge of employee data and minimal messing about.
• Export Contacts a friendly format:
• Google Contact's CSV format (which implies very specific column requirements, naming and order)
• Apple's VCF (contact card) format

As these options aren't possible we'll fake our way into the second option by using BambooHR's ad-hoc reporting tool to create a CSV file we'll eventually import (with changes) to Google Contacts.

1. Create a bamboohr report that includes the employees you want along with the fields you want.  Download it in CSV format.

Here is an example of some of fields I selected using BambooHR's custom reporting tool:

2. Enter Google Contacts.  Create a single contact that has sample data in all the fields you want have available for your contacts.   It is possible to create custom fields for data that doesn't directly map from BambooHR to Contacts using Custom fields in Contacts.  For example, I created a custom Contacts field for employee start date:

I used other custom fields for line manager and office location.

Now export a CSV version of the one contact record you've created.

3. Open the Contact CSV file in OS X Numbers (Excel, at least on OS X, doesn't work with UTF-8 encoding and trashes special characters).  You'll note many unused column headers.  Their inclusion, names and order are important - don't change any of them.  Google Contacts import is really picky when it imports data and generally won't work if you don't have the exact columns it expects.

Some of the columns will have static values.  For instance I ended up with a "Relation 1 - Type" column that I had to repeat "Line Manager" for all rows.

4. Open BambooHR CSV file in OS X numbers.  Remove the one test record.  Drag the columns from the BambooHR CSV file to the Google Contacts CSV file making sure you preserve column names and order.

Note: You MUST preserve column inclusion, names and order for importing to work.  Most of the problems and unexpected results I had with this process was because I hadn't done so.

If you've exported any dates like employee start date from BambooHR you'll need to change them from the default MM/DD/YYYY to YYYY-MM-DD which is what Google Contacts import requires.

5. Export the results from Numbers as a new CSV file.  At this point you should have a new CSV file that is a merge of Google Contacts columns and all contact data from BambooHR.  Many of the columns will be empty.

6. Before you import anything, make sure you take a backup of any existing contacts you have.  You may also to decide to delete the contacts that you already have in place before you import anything.  Google Contacts provides a merge function which works pretty well but I've found its better to backup/delete my current contact set and start with an empty upload area.  In particular we might hand off a mobile phone number from a previous employee to a new one meaning that two employees will have the same number if I don't clear out the old ones.  If you make changes to the original contacts or add notes to them you'll be stuck - you'll need to rely on a merge.

At this point you have a backup (if you want one) and you've decided whether you're starting with a clean slate or will merge.

7. Import your new CSV contacts list into Google Contacts.

Run Google Contacts merge if you need to.

8. Mark the newly imported set to be included in “My Contacts” (by default it isn’t).

9. Assuming you've connected your Google Contacts to OS X and iOS Contacts apps it may take a few minutes for them to appear.

And that's it.  Depending on how often you add/change/delete employees in BambooHR you'll have to repeat this every few months to make all the new changes available.  You can help your colleagues who would benefit from your fancy new contact list by exporting and sharing VCF (for OS X, iOS) or Google CSV format files from Google Contacts so others can import them.

---

Postnote: BambooHR has updated some of their default report column header names to match Google's Contacts header naming convention.  If they would also allow for user-defined column names and an optional static value for these custom columns in their ad-hoc reporting tool that would be really useful as well as you could basically build the CSV file in exactly the format needed by Google Contacts import.

Payday Loans, E-Commerce... and Internet Gambling?

TL;DR: In the future will an e-commerce user be able to take an immediate payday loan to buy something?  If so, could they do the same to deposit on with an Internet gambling service?

Some weeks ago there was a segment on John Oliver's Last Week Tonight (US-based comedy/news TV show) on Payday Loans.  I didn't know anything about them so other than the information from the show itself so I did a little reading (Wikipedia - Payday Loan).  In essence the loans are:

• Short-term (e.g., 30 days), but easy to roll into another short term loan
• High risk - loan is unsecured
• Extremely high interest (no joke, from 350% to over 3000% APR equivalent)
• Not necessarily linked to a payday event
• Somewhat like a pawnbroker, although pawnbroker loans are secured against goods left at the shop
• Somewhat like a credit card but with a much lower bar to loan money and much higher interest rates
• Sometimes not subject to usury laws (unreasonable interest rates).  These laws vary by State in the US and in some cases there seems to be ways to bypass them (e.g., partnering with a Native American tribe).  I've no idea what the legality of them are in countries other than the US
• Reliant on the consumer having previous payroll and employment records available for review by the lender
• Moving online

Those last two points are the most interesting.  Available data must be increasing rapidly due to rapidly increasing tracking of digital consumers (Big Data anyone?) and we all know what happens when online is a viable replacement for retail.

Instead of judging the fairness or morality of short-term lending practices, I'm instead going to apply a little Internet business logic to see how payday loans might intersect with e-commerce and Internet gambling.  And while I use use specific company names to provide a few illustrative examples, I have no insights or inside information on what these companies are actually up to.

Internet e-commerce payments are dominated by a few big players like Paypal or Stripe.  They charge 2.9% of price plus a small fee per transaction.  The fees are typically paid by the company and built into product pricing.  These companies provide an interface to various customer financial instruments, including credit cards.

In markets where it is legal to gamble online, I can use a credit card to deposit gambling funds through services like Datacash or Neteller.  Services like these have effectively been available since Internet gambling started.  There are many different service charging structures depending on what type of financial instrument the customer uses.  For credit cards they're similar to Paypal and Stripe fees although some services can charge 10% or more.

Now take a look at a company like Zest Finance.  They use a Big Data approach to mash lots of customer data together from a variety of sources to create a much more realistic and near-real-time view to enable more accurate underwriting.  Using an increasingly large pool of rich and near-real-time customer data amplifies the value of their service.  And while readily available and commoditized knowledge of applicant profiles based on digital usage and other people-data services may be strongest in the US, no doubt this trend will move on to other countries as well, including countries where Internet gambling is legal.

Now take a look at zestcash and spotloan.  They are online versions of a payday loan retail store.  Both enable you to apply for a short-term loan online and receive the money within a day.  Zest Finance "powers" the underwriting side of zestcash and spotloan.  Both zestcash and spotloan list interest rates of around 380% APR on their respective websites.

It is Internet business-as-usual to disintermediate land-based operations so nothing surprising about moving payday loan retail shops online.  Additionally, the margin gap for retail payday loan businesses and credit cards is enormous and there-in lies a big opportunity for more efficiently determined risks, interest rates and taking a slice of the margin differences.

Now it gets interesting.  The former President of Zest Finance has moved on to a company called 2checkout as their new CEO.  2checkout is a second tier payments company similar to Paypal and Stripe.  2checkout was effectively purchased by Chicago Growth Partners (“CGP”) and Trident Capital.  I don't know if there are common investors or not with Zest (Flybridge, GRP, Lightspeed, Matrix) and 2checkout.  If there is an investor connection in addition to the leadership one, the assertion I'm going to make becomes even more realistic.

And what is that assertion?  That there is a long tail of online users that want to buy something but don't have enough money in their bank account or available from a credit card at that moment to make the purchase.  As a result a sale opportunity is lost that could otherwise be made if an immediate payday loan was available to them.  The only missing link is immediate under-writing calculations and integration of a short-term loan service into the payment user journey.  As Big Data techniques move from batch to near real time analysis, such rapid calculations should become realistic for companies like Zest Finance.  And if Zest ties up with a payments provider like 2checkout, that will address the user journey integration requirement.

If those two things happen, a company like 2checkout can offer a new and differentiating payment instrument to their b2b customers - giving their end users an immediate loan to make a purchase that otherwise wouldn't be made.  2checkout service users will just receive their settlements as usual, completely insulated by whatever mechanics enable the 2checkout payment.

Now taking the same logic and applying it to Internet gambling, a generalized payments service that included short-term loans as a payment instrument could provide such a service to Internet gambling services as well.  And ultimately the justification could be that such services are no different than credit card use.  Credit card companies could be accused of being overly "discriminatory" to whom they issue credit cards and "inefficient" in their underwriting assessment.

As an investor, you may be excited by what you've read here.  Moving a land-based operation (payday loan shops) online, an automated long-tail segmentation of fat margins through Big Data techniques, and enabling a new customer demographic to spend money online sits square in the middle of many digital business models.  Additionally, the funds transfer segment of Internet businesses has been a historically rich gold mine making it even more compelling.

However, with respect to Internet gambling, while betting on credit does go on, there can be negative repercussions when it's taken to excess.  Reputable gambling companies generally avoid policies leading to credit card chargebacks and they would prefer to not to be part of a "gambling debt" story in the news where their brand is highlighted.  Punters betting within their means as a budgeted form of entertainment should be the preferred customer for reputable gambling companies.

Regardless of whatever judgment you have about this, my view is that this capability is or will shortly be technically possible in markets where there is a high degree of readily available and near-real-time data on applicants.  There are only a few ways to prevent or slow this from happening:

1. Ban the practice at a regulatory level and actively enforce such a ban.  You're seeing this approach in some states in the US with respect to payday loan practices.
2. Reduce or restrict availability of the financial and Internet profiling data that enables near-real time underwriting and risk calculations.  Given the on-going privacy erosion trend, I'd guess this is an unlikely tactic.

What do you think?  Ridiculous idea or will we see short-term loans in e-commerce payments or even Internet gambling account top-ups in a few years?

(Again, please don't assume that any of the companies listed here are pursuing the strategy I outline above!  They're just used as examples of something that could be done and I am in no way implying that this is their intent.)

Wordpress and slow http (wp_remote_get, ...)

Just a quickie:  I had a serious performance problems using Wordpress's wp_remote_get().  It was very slow, 5+ seconds to return values that should take well under 1 second.  Using curl and wget at the command line on the same machine using the same URL was sub 1 second.

Long story short, php5-curl wasn't installed on the Wordpress server.  Installed it, restarted Apache, and Wordpress's wp_remote_get() speed became as fast as the command line equivalents.

I don't know what Wordpress was using to hit the remote URL before I installed php5-curl, but it was incredibly slow.

Replacing Big SaaS - How to cut the Google, Apple, Dropbox, Microsoft, ... cords

With a Prism and Snowden inspired kick in the backside I finally got around to establishing some autonomy from the Big Boys with respect to email, contacts, calendar, network storage/sync and other common personal use SaaSs.  No rocket science here, just a consolidation of lots of "which one is best for me" research, "follow the tutorial" efforts and Google and log file problems/solutions to explain how to install, configure and maintain the types of services you get "for free" from Google, Apple, Dropbox and the rest.

This article is an overview of how to accomplish replacing the important Big SaaS, it is not a detailed step-by-step with every command listed.  I reference a number of other web pages and tutorials to help with the harder parts.

Overview

Here is a basic overview of the substitutions:

Service	Before	After
Hosting and OS	Google, Apple, Microsoft, Yahoo, ...	Digital Ocean "Droplets" Linux
Email	Google, Apple, Microsoft, Yahoo, ...	postfix, dovecot
Contacts	Google, Apple	davical
Calendar	Google, Apple	davical
Network storage and sync	Dropbox, Copy, Google Drive	ownCloud

The aspirational criteria I had for the substitutions were:

• Open source
• Supported with apt-get or similar installer with an up-to-date stable version available
• At least some recent community activity and support
• Positive reviews, particularly as versus their popular commercial alternatives
• Free or close to it
• Targeted solutions, not one package that is providing many services (e.g., MS Exchange vs Postfix)

It's also important to keep in mind that these solutions generally won't be as good as their popular commercial alternatives where armies of developers and systems administrators support them and taking advantage of big economies of scale and underpricing.  To take this path you're going to forfeit convenience, better usability, rock solid systems and uptime, macro level security, and "free" pricing for greater privacy and control.

Lastly, there are many more areas that could be substituted and I've not done or written these up yet - I note at least some of them at the bottom of article.

What's Required From You

You have to be able to do the following to get this working:

• Basic Unix shell commands and configuration file editing
• Willingness to read various tutorials and how-tos and be able to google for the rest
• Willingness to pay $5 per month for hosting and another $1 per month for backups
• Accept having a total data footprint of 15GB or less (or be willing to pay for more storage)
• A basic understanding of SSL certificates is useful

1. Create an SSH key

Follow Digital Ocean's tutorial to create your own key.

2. Have a domain name ready to use

There are many companies that offer domain registration.

3. Hosting

Set up an account with Digital Ocean (digitalocean.com).  Their basic IaaS virtual server ("Droplet") is cheap, plenty performant for our uses here and their management and provisioning interface is pleasantly usable.

Buy the cheapest cheapest droplet at $5 per month (1 CPU, 512MB RAM, 20GB Disk, 1TB transfer).  This will provide plenty of horsepower and space for the average user.

You might select "Amsterdam" as your region if you thought that might provide a safer environment for your data as opposed to hosting that is based in the USA (Digital Ocean's other sites are in New York and San Francisco).

Select OS "Ubuntu 12.04 x64".  You could probably safely use the newer versions, I've just not moved up to them yet.

Install the SSH certificate you created in step 1.

Enable "VirtIO" if you want.  Whatever it is.

After your new virtual server is created, activate automatic backups for it.  They may only be taken about once per week but they're a bargain at $1 per month.

Set up your new domain name to point to your new droplet IP address.  Digital Ocean's DNS interface is easier than godaddy's.  Configure your domain to use Digital Ocean's DNS.

NOTE: The only thing I don't like about Digital Ocean for hosting is there is no apparent way to cost effectively scale just disk size.  I'd like to keep the memory and CPU of the smallest instance but then easily scale up disk space.  Replacing network storage and big IMAP email archives will exceed the 20GB limit for "power" users.  There are plenty of other providers and some allow a low-performance-high-disk-space specification.  However, among the usual suspects like Amazon and Rackspace along with a number of others I found googling around, I didn't find any in the same price range as Digital Ocean.  Maybe Digital Ocean will add the feature of cost effectively adding disk space only in the future.

4. Basics

Verify you can log in as root using ssh and the ssh certificate you created.

Restrict root login to only allow certificate based logins.

Create a new user that you'll use to do most work from here forward.

Enable new user for sudo use.

Install zsh (or your preferred shell if its not already present) and make it your default shell.  Update your login shell preferences.

Create/deploy another ssh certificate for the new user you've created.

Install ntp.

Install iptables as your firewall.  Digital Ocean has a good tutorial. 

5. Supporting applications

Before we get to the applications we want, we have to install their supporting applications.

Install postgres - used by davical

Install MySQL - used by ownCloud

Install Apache and PHP - used by almost everything

Install phppgadmin - used to administer the Postgres / davical database

Install phpmyadmin - used to administer the MySQL / ownCloud database

6. Create a free SSL Certificate and install it

The certificate will be used by a number of services we install.

Use this tutorial at arstechnica to create a free Class 1 SSL certificate with startssl.com.

Tips:

• startssl.com creates an S/MIME and authentication certificate and automatically installs in your browser.  You might want to save the authentication certificate someplace secure.
• Certificate only good for one year - just remember you need to renew it each year (all your services dependant on a valid SSL cert will stop working when cert expires)

7. Email

Note: I don't typically use webmail, so I didn't bother installing a webmail service.

Install postfix - see Digital Ocean tutorial

Install dovecot - also see Digital Ocean tutorial, my user comments on dovecot

Update DNS MX record.

Adjust iptables firewall settings - see Digital Ocean tutorial

Tips:

• I found "apt-get install mail-stack-delivery" did the heavy lifting for me here.
• Make sure you un/comment out exactly what you want in /etc/postfix/master.cf
• Increased value of mail_max_userip_connections from 10 to 30 in /etc/dovecot/conf.d/01-mail-stack-delivery.conf due to an IMAP error limit popping up in OS X mail.
• Digital Ocean has subsequently created a tutorial for iRedMail - looks easier to set up and includes a webmail interface

Note: not added in spam filtering yet.

8. Contacts and Calendar

Install davical.

I looked at and discounted the following:

• calendarserver - depends on extended file attributes; apt-get exists but doesn't appear to be maintained
• radicale - no backoffice, feels too barebones
• baikai - No apt-get; synology's choice for their sync app
• ownCloud - ownCloud already looks bloated

9. Network storage and sync

Install ownCloud.

The goal here is secure and pervasively available files.  Like Dropbox and the paid version of BoxCryptor - both of which are closed source and therefore non-starters with my stated criteria.

You can create an encrypted filesystem on your main OS, ideally once that can be used by several OSs and place the system in ownCloud network synced storage.  When choosing an filesystem, it's important that the encrypted filesystem is in separate files or some type of chunks, not one big blob (like truecrypt) as big blobs don't sync well when you have concurrent clients syncing.  Ideally you want a filesystem that encrypts file names, content, and inode structures separately in small efficient pieces.  While interesting, I'm seeing enough limitations and sync problems with OS X's encrypted sparse bundle approach that I don't recommend it (use EncFS if you can; else use BoxCryptor even though its closed source).

iOS and Android Support

The above approach is fully supported by iOS and Android devices using standard protocols:

• Managing email via Secure IMAP
• Sending mail via Secure SMTP
• Calendar via calDav over https
• Contacts via cardDav over https
• Network storage and sync via ownCloud iOS/Android apps; runs over over https

This probably goes without saying, but assume you'll lose your device at some point.  Think about what is on the device and how easy it is to access it.  Do you use a PIN with a self-destruct after so many incorrect entries?  Do you have logins and passwords in Contacts or Notes files?

Maintenance Notes

You will have to renew your startssl.com security certificate each year.

Spin up the occasional backup on another droplet to verify backups and the restore process works.

Security Notes

Nothing is 100% secure.  The approach I've presented here has two big problems:

• Hoards of security specialists at the big companies will collectively know more about security than you or I ever will.  Security exploits of fairly new and not widely used applications like ownCloud and davical are possible.  You're therefore effectively trading off having thousands of staff at the big SaaS providers or the government having access to your data vs relying on common sense security basics to stay safe.  In this case, we've done the basics:
• We're running the iptables firewall with only the bare minimum of ports open
• All coms over SSL
• We're not storing the actual data on the server in an encrypted format.  Ideally we'd use an encrypted filesystem on the server so that the hosting provider couldn't snoop disk data.  Of course, decrypting "on the fly" as applications access the encrypted disk is also a risk, but without using your own secured physical server you are stuck with that problem.

I've not yet installed openvpn.  Could switch access to potentially vulnerable apps like Davical's backoffice, phpmyadmin, phppgadmin to VPN only access.  I did add in .htaccess/.htpasswd files across the backoffices for slightly better security.

Lastly, this is pretty obvious, but use long passwords with lots of variation between passwords and a mix of letters (upper/lower), numbers, and symbols.

Conclusion 

Google, Apple, Dropbox and others provide a great no/low cost option for services like email, personal information management and network storage.  Signing up for an account with Google is a lot easier and cheaper than the approach outlined above.  You get most of these services "for free".  So if the thought of Google, Apple, Dropbox and others reading your emails and documents and enabling governments to do likewise doesn't bother you at all, then by all means use their free services.

However, if you think you have a right to personal information privacy without business and governments having the ability to read it then you might want to consider implementation of the approach in this tutorial.

What have I missed and what has worked well for you?