For years people have griped about the cost of buying and selling homes through real estate agents. The 6% commission paid to real estate agents has remained surprisingly resilient amidst a broad reduction in other financial transaction fees (think stock trade fees). As a homeowner or buyer, depending on your market, the only way to avoid paying agent commissions is through a for-sale-by-owner process. As of 2018, only [11% of American](https://www.nar.realtor/research-and-statistics/quick-real-estate-statistics) chose to sell their home this away and often the result is a lower overall sales price.

This is where our client [Homie](https://www.homie.com/) comes in. Homie’s mission is to make homeownership easy, affordable, and accessible to all. Its technology platform streamlines the home buying and selling processes to reduce fees and commissions, while buyers and sellers work with a real estate agent. To date, they estimate they’ve saved their clients over \$61MM.

Homie came to Faraday when they were in the process of expanding beyond their current markets of Utah and Arizona into Nevada. They were looking to leverage the Faraday platform to support a few key initiatives.

## Predicting optimal locations for expansion

Moving into a new market is always challenging for a company, largely due to the lack of on-the-ground experience and local brand recognition. Simultaneously, data collected from one market is not necessarily representative of the new market. To overcome these obstacles, Homie uses Faraday's location and market intelligence solutions capabilities to guide their expansion across the mountain west. Specifically, Faraday lets Homie predict which zip codes in their target markets contain significant populations of high-propensity customers (homebuyers and sellers), giving the team a better understanding of where they should prioritize their acquisition efforts.

## Reaching likely buyers and sellers, directly and cost-effectively

Working with their Faraday customer success manager, the Homie team built geo-normalized models that accounted for market variances across key attributes, such as home value and household income. Then, they deployed custom lookalike audiences to Facebook, building up additional training data in their new markets. The results from this work were positive, with Faraday-generated audiences doubling conversion rates and reducing acquisition costs by more than 33%.

_Learn more about [likely buyers in Facebook](https://faraday.ai/predict/pipelines/likely_buyers_in_facebook) with Faraday_

## Check them out!

We are excited about the work [Homie](https://www.homie.com/) is doing to give its customers more transparency and control over the home buying and selling process and while getting all the support they need from a whole team of licensed agents, attorneys, and experts.

How Homie uses Faraday to guide its market expansion strategy and reach likely customers, directly and cost-effectively.

How Homie is using location and market intelligence solutions to expand and lower costs

Over the last decade, fintech firms have democratized broad swaths of the financial services landscape, whether they be the likes of Robinhood and stock trading to Prosper and personal loans or Lemonade and homeowner insurance. Our client [DiversyFund](https://diversyfund.com/) is part of the movement to lower barriers to participation, providing everyday investors access to high-value private market assets, like real estate, by removing net worth and high minimum investments requirements. Its online platform makes investing easy, and its educational content helps to inform first-time investors.

DiversyFund’s Growth REIT was qualified by the SEC in late 2018, and by June 2019 they had established their platform and investment criteria to make a concerted push into the retail investor market. Shortly thereafter, DiversyFund began its engagement with Faraday with the intent of using our platform for a few key initiatives.

## Develop a better understanding of their customer base

Being fairly new to market, DiversyFund didn’t have a great sense of who their customers were or how they differed from one another. Faraday enhanced their data and applied machine learning techniques to surface a range of insights about who their customers are and what makes them unique.

DiversyFund’s insight work with Faraday uncovered a fascinating finding: two clearly distinct customer types — aspirational investors and experienced investors. These two groups had unique demographic and financial profiles and used DiversyFund in different ways.

## Predict investment propensity to optimize acquisition spend

DiversyFund wanted to acquire valuable customers in a cost-effective manner by aligning acquisition costs with investment likelihood. Building off of their customer insights, DiversyFund created two predictive models to target and attract the two types of customers. To ensure they reached them with relevant content, they curated their ad creative and set CPA goals that aligned with each group. By creating individual modeling strategies, and in turn digital audiences, DiversyFund achieved a strong ROAS for each group.

_Learn more about [optimitized customer acquisition](https://faraday.ai/solutions/customer_acquisition) with Faraday._

## Refine and scale their personalization strategy

Serving the broad retail investing market means playing to a highly diverse customer base that requires varied messaging and content.

When leads convert into customers, DiversyFund uses email campaigns to engage with and educate them. Given the varied level of investment acumen across their customer base, DiversyFund needed to find a way to segment their customers to assure the right content reached the right person.

To do this, DiversyFund used the Faraday platform to develop brand-specific personas that let them reach a new level of scalability in their personalization efforts. These personas are assigned at the moment of signup and allow the brand to segment their customers by sophistication level, without requiring a questionnaire or observation of investment activity.
With this deeper level of customer insight, DiversyFund has created marketing drips for each persona group and seen a clear uptick in email engagement and deliverability rates.

## Check them out!

We love [DiversyFund's](https://diversyfund.com/) work and are excited for more people to take advantage of their services. In an age of increasing economic inequality, DiversyFund is using technology and regulatory advancements to open up new avenues of wealth generation for everyday Americans.

How DiversyFund uses AI to better understand their customers and deliver personalized investment content.

How DiversyFund uses data science to democratize real estate investment

Acquiring customers who are enthusiastic about your brand and will support you in the long run is vital to establishing the scalability and longevity of your business. As the first step of building out a loyal customer base, [customer acquisition](https://faraday.ai/solutions/customer_acquisition) is a high priority for marketing teams.

This article explores four ways you can leverage data science to extend your acquisition spend further to build a loyal customer base.

## Targeting predicted high-LTV customers

The fact that it costs more to acquire new customers than it does to retain existing ones is no secret, which makes finding high-LTV prospects paramount to ensuring a successful, cost-effective acquisition strategy that will fuel future revenue.

Knowing your target audience is a key piece of acquiring valuable customers from the get-go. What makes your best existing customers unique? What draws them to your brand and/or products? Defining and identifying high-LTV customers is the first step in predicting who out there will become a valuable customer.

Predictive analytics play a crucial role in identifying traits and patterns in your customer data that are indicative of high-value customers. Of course, the depth and breadth of your customer data affects how accurate these predictions will be. Introducing third-party data is a great way to gain deeper insights, fuel your predictions, and generate higher-performing lookalike audiences.

## Reaching likely buyers through multiple channels

Acquisition campaigns are most effective when orchestrated across multiple channels. [Reaching likely buyers](https://faraday.ai/predict/category/likely_buyers) with consistent messaging through paid social, direct mail, and other targeted channels helps reinforce your brand in their minds and gives them more opportunities to engage.

To reach the same consumers across online and offline channels, you can't rely on anonymized audiences. Instead, you'll need a secure way of building known-identity audiences bolstered by third-party data.

## Using known-identity attribution to optimize your channel mix

A diversified marketing mix allows you to reach varying demographics across different online and offline channels. But the sheer scale of marketing across numerous platforms doesn’t guarantee a high conversion rate or substantial ROI, so knowing where your money is best spent should be a high priority.

Equip your team with the capabilities to closely monitor campaign performance across each channel so you can identify what is working and what isn’t. It’s worth noting that this can be a difficult task with cross-channel campaigns, as several digital channels provide anonymized or aggregated results.

There are several attribution models out there, but we've found that known-identity attribution can provide you with a better understanding of who you should be targeting on each channel. Acquisition spend is precious, and having the ability to — and being excited about — shifting gears to improve your strategy is crucial. Marketing strategies should not be fixed; they should evolve as your customer base grows and changes.

## Delivering customized offers and discounts

Some consumers need a bigger push than others to convert — this is where promotions and discounts come in. But to protect ROI on your campaigns, it's important to predict which customers are will stay engaged after that initial purchase. Delivering a 15% discount to all consumer might help drive conversions and reduce your average cost-per-acquisition, but if they don't come back, you might find yourself in a tough spot down the road.

Similarly, not all consumers need a discount to nudge them along the path to conversion. Using [predictive analytics](https://faraday.ai/features/predictions) to determine who should receive a discount and how big that discount should be can help you beat cost benchmarks and increase the overall return on your acquisition spend.

How consumer marketers are using machine learning to optimize customer acquisition and advertising efforts.

Creative ways to optimize your ad spend with machine learning

The use of third-party data isn't new, but it's becoming a necessity as businesses shift to a customer-centric approach. Marketers everywhere are realizing that first-party data is only the tip of the iceberg when it comes to understanding their customers.

The rise in digital technologies and interactions has increased the need for bringing outside data into their companies. While it may be as simple as understanding your existing customers' household and financial standing, with third-party data you can also gain deep insights into your leads on a known-identity basis.

Overall, leveraging third-party and first-party data adds breadth and depth to your customer insights, informing better branding and messaging decisions, personalization efforts across channels, and taking much of the guesswork out of targeted and location-based marketing campaigns.

## Customer identity resolution

The capability to bring together first-party data from various sources and third-party data to compile a unified picture of each customer is increasingly valuable. Spending dedicated to identity data assets [grew by more than 50% in 2018](https://www.iab.com/wp-content/uploads/2018/12/IAB_The-State-of-Data-2018_2018-12-05_Final.pdf?ref=faraday-blog), to a total of $846MM.

Personally identifiable information (PII) is crucial to connecting the dots across multiple touchpoints (online and offline) to form a holistic picture of your customers and prospects so you can reach them with content that will resonate with them through the channels that matter.

## Using third-party data for personalization

In a time when [almost all consumers](https://www.bondcap.com/report/it19/?ref=faraday-blog#view/148) prefer ads and products that are relevant to them, knowing how and when to serve your target market is crucial to effectively engaging your customers. According to [Forbes](https://www.forbes.com/sites/insights-treasuredata/2019/05/01/the-path-to-personalization/?ref=faraday-blog&sh=5bd84dc7a76b) 46% of marketing executives are not where they want to be in terms of delivering personalization, and 57% say that they hope to advance their personalization initiatives over the next year.

Enriching your first-party data with third-party information on your customers' financial, household, transactional, or lifestyle categories gives you a clearer idea of what your customers look like in real life.

To take it a step further, enriched customer data helps machine learning algorithms build better models. Cluster models, for example, sort individuals into distinct groups based on common attributes. These groups, or personas, can affirm your intuitions about what your customer base looks like, but may also surprise you by revealing significant groups you hadn't known about or previously considered important.

## Using third-party data for ad optimization

If you're using predictive lead scoring capabilities, the accuracy and precision of those scores improve with the quality and quantity of third-party data that you introduce. This not only benefits your leads' experiences, as they'll receive content that better aligns with their interests and where they are in their customer journeys, but it will save you time and money when you're deciding how to prioritize your outreach.

A popular way of leveraging third-party data with predictive modeling for digital marketing is Facebook lookalike audiences. But since the platform shut down advertisers' access to this data in April of 2018, gaining deep insights from Facebook has been a struggle for many businesses that are trying to better understand their audiences and target the right consumers.

If you enrich your first-party data with third-party attributes, you can gain insights on a granular level that you aren't getting with Facebook and other marketing channels. You can then segment known-identity audiences based on certain attributes — or develop in-depth personas for segmentation — and push the appropriate ad creative to those individual segments with the goal of increased, personalized engagement. This goes for not only new leads you're nurturing, but also for the customers you're actively engaging.

Another advantage of leveraging third-party data is the ability to match site's captured emails to real people. This allows you to bucket leads into certain personas for more personalized nurture campaigns that will pull them through their customer journeys with you. Your leads are more likely to receive messaging that aligns with where they are in their individual journeys, and it will save your team time and money when you decide how to prioritize your lead outreach.

## Using third-party data for location-based marketing efforts

Geographic data has obvious benefits, but incorporating additional third-party in geographic analyses improves the quality of the insights. Knowing where your ideal customers live is one piece of the geomarketing puzzle, but gaining an understanding of how your audiences will best interact with your ads offline is achieved by knowing more than just their geographic location.

Attributes like household information, transportation preferences, and lifestyle data helps you prioritize where you place ads, as well as where you might establish new brick-and-mortar presences. A subway ad or billboard placed in your customers' neighborhoods can be a great way to advertise your e-commerce business for those who don't live near a storefront, but for customers who are in your brick-and-mortar trade zones, perhaps a direct mailer advertising an in-store sale would better drive business.

From a creative perspective, audiences can vary widely depending on their physical locations, so the messaging and images chosen for your campaigns should be curated for specific audiences in particular geographies. Providing relevant content increases your chances of audience engagement, so capitalizing on the insights provided by third-party data is a necessity.

This is the case with SEM advertising, which segments audiences by geographic location. With the insights gained from third-party data, you can optimize your SEM spend by targeting areas where higher-value customers reside, spending less on areas that aren't as likely to bring in revenue.

## Using third-party data responsibly

With the rise of regulations like GDPR, privacy and validity of data are prominent concerns when using third-party data and PII. However, there are ways to ensure that your customers' and prospects' privacy is protected. You want to be sure your data comes from a verified source, isn't dangerously invasive, and is leveraged to benefit the customers you're targeting.

Social scraping is a common tactic, and it can provide large categories of data on behavioral tendencies, consumer habits, and some personal information, though this data may not always be entirely accurate. At Faraday we avoid social scraping in favor of canonical sources of information from established third-party data vendors. Vendors like Epsilon verify the accuracy of the high-quality data we license and leverage for our partners.

Faraday's Director of Data Science, William Morris, notes that we sidestep many consumer privacy issues by dealing with datasets that categorize data, such as consumer purchases, by type of product and frequency, rather than the exact stores where those purchases are made. This leads to a significant lift in machine learning models' predictive capabilities without being invasive from a privacy standpoint. Additionally, each of our clients' customer data is siloed using industry-standard practices to protect the privacy of each clients' customers.

Faraday also actively works to build models that are agnostic to a person's membership to protected classes. We exclude potentially harmful categories like ethnicity, religion, or primary language data, as these can disenfranchise certain groups of the consumer population. As an additional security measure, we employ a suite of tools for post-model checking to ensure that attributes that could indicate a person's race or class don't make the models biased or adversely impact a population.

Taking into consideration these types of ethical practices is important when exploring your own use of third-party data.

Using third-party data responsibly benefits brands and consumers alike. Learn how brand marketers are doing it right.

Using third-party data in marketing responsibly 

For nearly every B2C company, revenue is linked to the customer lifecycle. Customer acquisition, engagement, and retention initiatives directly or indirectly impact revenue growth and sustainability by either reducing customer acquisition costs, increasing customer lifetime value (LTV), or ideally, both.

Effective marketing strategies revolve around the customer lifecycle; understanding key lifecycle stages, identifying events that are likely to trigger transitions between stages, and finding ways to optimize interactions across the lifecycle.

Naturally, the more you know about your customers and how to influence them to transition into a desired stage, the more efficiently you can grow and sustain revenue with the resources at your disposal.

Simply put, that's what customer lifecycle optimization is all about: leveraging rich customer data and predictive analytics techniques to generate insights and make predictions that measurably improve outcomes at each stage of the customer lifecycle.

## How customer lifecycle optimization works

Customer lifecycle optimization (CLO) is a practice. As with other practices, CLO involves a series of prescribed steps to be done effectively. While specific lifecycle stage names, data sources, and analytics techniques will vary depending on your industry, company, and objectives, the canonical CLO process comprises four steps: lifecycle mapping; data discovery; predictive groundwork; and implementation and action.

### Customer lifecycle mapping

The first step in any CLO initiative is always identifying and defining key lifecycle stages and transitions between stages. Mapping these stages and transitions to a uniform customer lifecycle is crucial to uncovering rich, predictive data.

The uniform B2C customer lifecycle stages are:

1. Lead generation
2. Lead conversion
3. Retention and expansion
4. Reactivation

While terminology will change from business to business, we found that this formulation is rich enough to capture important boundaries, yet simple enough to avoid stages with ambiguous transitions.

Think about your customer lifecycle. How are stages defined? Which attributes qualify individuals to be placed in those stages? What events trigger transitions between stages?

### Customer data discovery

At each stage, prospects, leads, and customers will complete certain events that will individually or collectively trigger a transition in or out of that stage. Individuals will also have different attributes, which help determine whether or not they belong in any given stage.

Go through each cell of your customer lifecycle map and think about which events or attributes in your data could be used to trigger a transition or pass the litmus test. Then, think about where you can find that data. Is it in your ESP, CRM, or a custom data warehouse?

### Customer prediction groundwork

Once you've formalized your lifecycle and its representation in data, you can start recognizing patterns and eventually predict outcomes. Imagine loading up your converted leads (customers in "retention and expansion") alongside your stale leads that never converted. What differences can you find?

This comparison analysis is especially effective when you've added depth and breadth to your existing data.

As you experiment with this type of pattern recognition, you'll quickly realize that it's the kind of thing that computers do very well. That's where machine learning comes in handy.

### Predictive engagement

Whether you want to increase conversion rates from lead generation campaigns or reduce churn from existing customers, properly targeted outreach is essential to engaging and motivating the right leads and customers to take a desired action.

Outbound communication is the strongest and most versatile intervention at an organization's disposal to compel progress and therefore expand revenue. This includes individually targeted digital advertising, a form of direct outreach.

Having identified a stage transition you'd like to motivate with an outreach intervention, the question becomes, "Who do I reach?" Regardless of the desired transition, the general technique is called audience expansion, also known as "lookalike" audiences.

To apply the audience expansion technique, we must always identify the audience and a set of candidates with similar characteristics and attributes.

With these groups defined, the next step is to apply your predictive groundwork. This could involve using patterns you identified in your data to look for similar opportunities among your candidates, or in more advanced cases, using artificial intelligence to build a predictive model trained to discriminate between likely and unlikely transitioners.

Finally, you will be left with a well-defined group of candidates likely to transition into a desired lifecycle stage when reached with relevant content.

## Putting it all together

To summarize the CLO practice, it's useful to recall the original motivation: leveraging rich data and statistical predictions to optimize revenue-building initiatives throughout the customer lifecycle. This means motivating transitions from one stage to the next.

Customer lifecycle optimization is the practice of leveraging data to maximize revenue-generating customer behaviors throughout the lifecycle. Learn how it works.

What is customer lifecycle optimization and how does it work?

**UPDATED FOR 2017** Now with easy subquery support and a more sensible argument order!

We adapted [this excellent piece by Dmitri Fontaine](http://tapoueh.org/blog/2014/02/21-PostgreSQL-histogram) and turned it into a function.

It can be invoked like this:

```
SELECT * FROM histogram($table_name_or_subquery, $column_name);
```

. . . to give sweet results like this, in a check of the distribution of 2016 political contributions in Vermont:

```
fec=# SELECT * FROM histogram('(SELECT * FROM small_donors_vt LIMIT 50000)', 'transaction_amt');

 bucket |   range   | freq |       bar
--------+-----------+------+-----------------
      1 | [0,9]     | 2744 | ******
      2 | [10,19]   | 5630 | *************
      3 | [20,29]   | 6383 | ***************
      4 | [30,39]   | 1290 | ***
      5 | [40,49]   |  369 | *
      6 | [50,59]   | 3541 | ********
      7 | [60,69]   |  174 |
      8 | [70,79]   |  313 | *
      9 | [80,89]   |  171 |
     10 | [90,99]   |   65 |
     11 | [100,109] | 2363 | ******
     12 | [110,119] |   51 |
     13 | [120,129] |  115 |
     14 | [130,139] |   32 |
     15 | [140,146] |   11 |
     16 | [150,159] |  187 |
     17 | [160,169] |   24 |
     18 | [170,177] |   33 |
     19 | [180,189] |   19 |
     20 | [191,199] |   24 |
     21 | [200,200] |  795 | **

```

Use it yourself by adding this to your postgres setup:

```
CREATE OR REPLACE FUNCTION histogram(table_name_or_subquery text, column_name text)
RETURNS TABLE(bucket int, "range" numrange, freq bigint, bar text)
AS $func$
BEGIN
RETURN QUERY EXECUTE format('
  WITH
  source AS (
    SELECT * FROM %s
  ),
  min_max AS (
    SELECT min(%s) AS min, max(%s) AS max FROM source
  ),
  histogram AS (
    SELECT
      width_bucket(%s, min_max.min, min_max.max, 20) AS bucket,
      numrange(min(%s)::numeric, max(%s)::numeric, ''[]'') AS "range",
      count(%s) AS freq
    FROM source, min_max
    WHERE %s IS NOT NULL
    GROUP BY bucket
    ORDER BY bucket
  )
  SELECT
    bucket,
    "range",
    freq::bigint,
    repeat(''*'', (freq::float / (max(freq) over() + 1) * 15)::int) AS bar
  FROM histogram',
  table_name_or_subquery,
  column_name,
  column_name,
  column_name,
  column_name,
  column_name,
  column_name,
  column_name
  );
END
$func$ LANGUAGE plpgsql;
```

Note:

1. You don't need to use a subquery - you can also just provide a table name as the first argument.
1. Subqueries are useful for sampling. For example, `(SELECT * FROM bigtable LIMIT 500 ORDER BY RANDOM()`. Don't forget parentheses!
1. You can call this as `SELECT histogram()` or `SELECT * FROM histogram()`. The latter form is much more legible!

Happy querying!

Learn how to create in-table histograms with PostgreSQL.

How to do histograms in PostgreSQL

Like many subscription services, meal delivery brand [Marley Spoon](https://marleyspoon.com/) faced the challenge of retaining customers. High churn rates made it difficult to cover acquisition costs and scale revenue, so they needed a way to identify risky customers. To improve their retention capabilities, Marley Spoon approached Faraday to assess the predictive accuracy between traditional segmentation and AI-driven churn modeling techniques.

## Traditional segmentation vs machine learning

With traditional methods, brands analyze trends among customer segments and take action to [prevent those with high churn rates](https://faraday.ai/predict/category/churn_scores) from unsubscribing. This is a relatively reactionary measure. In contrast, leveraging machine learning models to [score leads](https://faraday.ai/solutions/lead_management) and customers for their propensity to churn allows brands to segment customers based on churn risk and properly engage high-risk customers before they unsubscribe.

## Analysis and results

Because we’re most interested in the models’ ability to accurately predict churn, we evaluate performance based on how many churned customers were included in the top 10% and top 20% of predicted churners. Here are the results:

**Churn rate comparison:**

- 19% churn rate for likely churners identified with Faraday churn predictions
- 12% churn rate for likely churners identified through traditional segmentation

**Churn detection lift:**

- 70% lift over baseline with Faraday churn predictions
- 15% lift over baseline with traditional segmentation

To sum it up, Faraday's machine learning [predictions](https://faraday.ai/features/predictions) detected churn 5X better than the traditional technique.

Learn how the meal delivery subscription brand was able to identify likely churners 5X better with machine learning predictions.

How Marley Spoon predicted churn 5X better with machine learning

**Update** _We used to call this `jsonb_collect`, but then we realized it was very similar to `json_object_agg(name, value)`... except that there is no version of that function that just takes an expression. So, copying `jsonb_agg(expression)`, we give you..._

How do you aggregate (aka merge aka combine aka collect) JSONB fields in Postgres 9.5+? You define a new aggregate `jsonb_object_agg(expression)`:

```sql
CREATE AGGREGATE jsonb_object_agg(jsonb) (
  SFUNC = 'jsonb_concat',
  STYPE = jsonb,
  INITCOND = '{}'
);
```

Here's how you use it:

```console
# select * from greetings;
         data
-----------------------
 {"es":"Saludos"}
 {"en":"Hello"}
 {"ru":"Здравствуйте"}
(3 rows)

# select jsonb_object_agg(data) from greetings;
                        jsonb_object_agg
-------------------------------------------------------------
 { "es" : "Saludos", "en" : "Hello", "ru" : "Здравствуйте" }
(1 row)
```

If you're curious about the aggregate we just added, note that `jsonb_concat(jsonb, jsonb)` is the [function backing `||`](https://github.com/postgres/postgres/blob/REL9_5_STABLE/src/backend/utils/adt/jsonfuncs.c#L3349) that was [introduced in Postgres 9.5](https://www.postgresql.org/docs/9.5/static/release-9-5.html).

It's just that simple!

Learn how to aggregate (aka merge aka combine aka collect) JSONB fields in Postgres 9.5+.

How to aggregate JSONB in PostgreSQL 9.5+

At Faraday, we've long used [csvkit](https://csvkit.readthedocs.io/en/latest/) to understand, transform, and beat senseless our many streams of data. However, even this inimitable swiss army knife can be improved on - we've switched to [xsv](https://github.com/BurntSushi/xsv).

xsv is a fast CSV-parsing toolkit written in [Rust](https://www.rust-lang.org/en-US/) that mostly matches the functionality of csvkit (including the clutch ability to pipe between modules), with a few extras tacked on (like smart sampling). Did I mention it's fast? In a standup comparison, I ran the "stats" module of XSV against "csvstat" from csvkit, on a 30k-line, 400-column CSV file:

- Python-based csvkit chews through it in a respectable-and-now-expected 4m16s.

- **xsv takes 1.8 seconds.** I don't even have time for a sip of my coffee.

The difference between csvkit and xsv is partly defined by scale; both tools are plenty fast on smaller datasets. But once you get into 10MB-and-upward range, xsv's processing speed pulls away exponentially.

If you've been using csvkit forever (like me), or if you want to be able to transform and analyze CSVs without loading them into a DB, give xsv a shot:

### Install Rust

```bash
curl https://sh.rustup.rs -sSf | sh
```

. . . which also gives you the rust package manager `cargo`, which lets you:

### Install xsv

```bash
cargo install xsv
```

Then be sure your PATH is configured correctly:

```bash
export PATH=~/.cargo/bin:$PATH
```

. . . and try it out on a demo CSV with 10k rows, some messy strings, and multiple data types:

```bash
curl https://gist.githubusercontent.com/wboykinm/044e2af62fc0c7f77e17f6ccd55b8fb0/raw/fca391e6c03a06a7be770fefca6c47a9acdd2305/mock_data.csv \
| xsv stats \
| xsv table
```

_(`xsv table` formats the data so it's readable in the console):_

```bash
field           type     sum                 min                  max                  min_length  max_length  mean                stddev
id              Integer  5005000             1                    1000                 1           4           500.49999999999994  288.6749902572106
first_name      Unicode                      Aaron                Willie               3           11
last_name       Unicode                      Adams                Young                3           10
email           Unicode                      aadamsp5@senate.gov  wwrightd8@upenn.edu  12          34
gender          Unicode                      Female               Male                 4           6
ip_address      Unicode                      0.111.40.87          99.50.37.244         9           15
value           Unicode                      $1007.98             $999.37              0           8
company         Unicode                      Abata                Zoovu                0           13
lat             Float    243963.82509999987  -47.75034            69.70287             0           9           24.42080331331331   24.98767816017553
lon             Float    443214.19009999954  -179.12198           170.29993            0           10          44.36578479479489   71.16647723898215
messed_up_data  Unicode                      !@#$%^&*()           𠜎𠜱𠝹𠱓𠱸𠲖𠳏       0           393
version         Unicode                      0.1.1                9.99                 3           14
```

Happy parsing!

A data science hack to process CSVs faster using XSV.

How we made our CSV processing 142x faster

The Rust compiler just saved me from a nasty threading bug. I was working on an open source development tool for Docker apps with lots of microservices, and I decided to parallelize the routine that transformed `docker-compose.yml` files. This was mostly an excuse to check out the awesome [rayon][] library, but it turned into a great example of what real-world Rust development is like.

[rayon]: https://github.com/nikomatsakis/rayon

The original routine looked something like this:

```rust
/// Process our pods, flattening and transforming them using our
/// plugins, and output them to the specified directory.
fn output_helper(&self, op: Operation, export_dir: &Path) -> Result<()> {
    // Output each pod.
    for pod in &self.pods {
        // Don't export pods which aren't enabled.
        if !pod.enabled_in(&self.current_target) {
            continue;
        }

        // Figure out where to put our pod.
        // ...

        // Combine overrides, make it standalone, tweak as needed, and
        // output.
        let mut file = try!(pod.merged_file(&self.current_target));
        try!(file.make_standalone(&self.pods_dir()));
        let ctx = plugins::Context::new(self, pod);
        try!(self.plugins().transform(op, &ctx, &mut file));
        try!(file.write_to_path(out_path));
    }
    Ok(())
}
```

To convert this to a parallel loop, I started by changing:

```rust
for pod in &self.pods {
```

To:

```rust
self.pods.par_iter().map(|pod| -> Result<()> {
```

Here, `Result<()>` means "this closure might return an error, or it might return an empty tuple, basically `void`." (For more information on Rust error handling, check out [the Rust book][errors].) But it couldn't be that easy, could it?

[errors]: https://doc.rust-lang.org/book/ch09-00-error-handling.html

Nope. The Rust compiler showed me the following error message. It's long, but I'll translate:

```
error[E0277]: the trait bound `plugins::PluginTransform + 'static: std::marker::Sync` is not satisfied
   --> src/project.rs:397:30
    |
397 |         self.pods.par_iter().map(|pod| -> Result<()> {
    |                              ^^^ trait `plugins::PluginTransform + 'static: std::marker::Sync` not satisfied
    |
    = note: `plugins::PluginTransform + 'static` cannot be shared between threads safely
    = note: required because it appears within the type `Box<plugins::PluginTransform + 'static>`
    = note: required because of the requirements on the impl of `std::marker::Sync` for `std::ptr::Unique<Box<plugins::PluginTransform + 'static>>`
    = note: required because it appears within the type `alloc::raw_vec::RawVec<Box<plugins::PluginTransform + 'static>>`
    = note: required because it appears within the type `std::vec::Vec<Box<plugins::PluginTransform + 'static>>`
    = note: required because it appears within the type `plugins::Manager`
    = note: required because it appears within the type `std::option::Option<plugins::Manager>`
    = note: required because it appears within the type `project::Project`
    = note: required because it appears within the type `&project::Project`
    = note: required because it appears within the type `&&project::Project`
    = note: required because it appears within the type `[closure@src/project.rs:397:34: 424:10 op:&plugins::Operation, self:&&project::Project, export_dir:&&std::path::Path]`
```

The key bit to take away here is that "`Sync` is not satisfied" for the trait `plugins::PluginTransform`. In Rust, `Sync` is a special trait that tells the compiler that it's safe to share an object between two threads. That whole long list of "notes" afterwards looks intimidating, but it's really just telling me that my `PluginTransform` objects live inside my `plugins::Manager` type, which in turn lives inside my `Project`. The Rust compiler is verbose, but it tries to be helpful.

A bit of investigation reveals that `PluginTransform` inherits from the `Plugin` trait:

```rust
pub trait PluginTransform: Plugin {
```

So we can fix this problem by changing the declaration of `Plugin` from:

```rust
pub trait Plugin {
```

To:

```rust
pub trait Plugin: Sync {
```

This says, "All types implementing `Plugin` must also allow me to access them from multiple threads." With that fixed, I re-run `cargo test` and get a similar message:

```
error[E0277]: the trait bound `plugins::transform::vault::GenerateToken + 'static: std::marker::Sync` is not satisfied
   --> src/plugins/transform/vault.rs:230:6
    |
230 | impl plugins::Plugin for Plugin {
    |      ^^^^^^^^^^^^^^^ trait `plugins::transform::vault::GenerateToken + 'static: std::marker::Sync` not satisfied
    |
```

This time, I change:

```rust
trait GenerateToken: Debug {
```

To:

```rust
trait GenerateToken: Debug + Sync {
```

This says, "All types which implement `GenerateToken` must also implement `Debug` (so I can print them), as well as `Sync` (so I can share them). We're making progress!

Re-running `cargo test`, however, reveals the actual bug, and it would have been a nightmare to debug:

```
error[E0277]: the trait bound `std::rc::Rc<std::cell::RefCell<std::vec::Vec<(std::string::String, std::vec::Vec<std::string::String>, vault::client::VaultDuration)>>>: std::marker::Sync` is not satisfied
   --> src/plugins/transform/vault.rs:124:6
    |
124 | impl GenerateToken for MockVault {
    |      ^^^^^^^^^^^^^ trait `std::rc::Rc<std::cell::RefCell<std::vec::Vec<(std::string::String, std::vec::Vec<std::string::String>, vault::client::VaultDuration)>>>: std::marker::Sync` not satisfied
    |
```

Uh-oh. In our test harness, we have a type `MockVault`, which contains an `Rc<RefCell<_>>`:

```rust
type MockVaultCalls = Rc<RefCell<Vec<(String, Vec<String>, VaultDuration)>>>;

/// A fake interface to vault for testing purposes.
#[derive(Debug)]
#[cfg(test)]
struct MockVault {
    /// The tokens we were asked to generate.  We store these in a RefCell
    /// so that we can have "interior" mutability, because we don't want
    /// `generate_token` to be `&mut self` in the general case.
    calls: MockVaultCalls,
}

```

We use the `MockVault` to simulate a connection to Hashicorp's [Vault][], a secure central storage for passwords and other secrets, which issues time-limited credentials. And when we test our Vault code, we use the `calls` member here to record all the requests that we would have made to Vault.

[Vault]: https://www.vaultproject.io/

The type `Rc<RefCell<_>>` is a hack. The `GenerateToken` API assumes that our token-generator is a read-only object. This is good, because we want to access it from multiple threads! But in the test code, we need to create some "interior" mutable state. Basically, we ask Rust to replace compile-time borrow checks with run-time borrow checks. (For more details, see [the Rust book][mutability].) But `Rc<RefCell<_>>` is a lightweight mechanism designed for single-threaded code.

[mutability]: https://doc.rust-lang.org/beta/book/mutability.html

The fix is to replace `Rc<RefCell<_>>` with `Arc<RwLock<_>>`, which is fully thread-safe:

```rust
type MockVaultCalls = Arc<RwLock<Vec<(String, Vec<String>, VaultDuration)>>>;
```

Once we make this change, Rust reminds us to change all the code that accesses `calls`, too:

```
error: no method named `borrow_mut` found for type `std::sync::Arc<std::sync::RwLock<std::vec::Vec<(std::string::String, std::vec::Vec<std::string::String>, vault::client::VaultDuration)>>>` in the current scope
   --> src/plugins/transform/vault.rs:132:20
    |
132 |         self.calls.borrow_mut().push((display_name.to_owned(), policies, ttl));
    |                    ^^^^^^^^^^
    |
...
error: no method named `borrow` found for type `std::sync::Arc<std::sync::RwLock<std::vec::Vec<(std::string::String, std::vec::Vec<std::string::String>, vault::client::VaultDuration)>>>` in the current scope
   --> src/plugins/transform/vault.rs:368:23
    |
368 |     let calls = calls.borrow();
    |                       ^^^^^^
    |
```

Phew. If we hadn't caught that `Rc<RefCell<_>>`, our test suites might have randomly segfaulted a couple of times a week, and it would have taken us weeks to track it down. I don't even think our current test suites _could_ trigger this bug, but I bet a future version would have been able to, leaving a nasty surprise for us someday. The Rust compiler dug down through layers of data structures and found the one bit that wasn't thread safe.

## Wrapping it up

Rust also complains about that fact that we're calling `continue` from inside a closure:

```
error[E0267]: `continue` inside of a closure
   --> src/project.rs:402:17
    |
402 |                 continue;
    |                 ^^^^^^^^ cannot break inside of a closure
```

We can fix this by moving this code:

```rust
// Don't export pods which aren't enabled.
if !pod.enabled_in(&self.current_target) {
    continue;
}
```

...into a `filter` call on our parallel iterator:

```rust
self.pods.par_iter()
    // Don't export pods which aren't enabled.
    .filter(|pod| pod.enabled_in(&self.current_target))
    // Process each pod in parallel.
    .map(|pod| -> Result<()> {
```

The final problem is that we now have a parallel computation that returns a bunch of `Result<()>` objects, and we need to boil them down to a single `Result<()>` like we had before. If we have a bunch of error messages, I'm happy to just pick one. No need to overwhelm the user.

I spoke to Josh Stone, and he helped me come up with the following:

```rust
    .map(|pod| -> Result<()> {
        // ...
    }
    // If more than one parallel branch fails, just return one error.
    .reduce_with(|result1, result2| result1.and(result2))
    .unwrap_or(Ok(()))
```

This says, "Given any two results named `result1` and `result2`, take `result1` if it's an error, otherwise take `result2`." It's basically a short-circuit `&&` operator, but for `Result` values instead of booleans. I've filed an issue suggesting that `rayon` should provide those last two lines as a built-in function.

[issue113]: https://github.com/nikomatsakis/rayon/issues/113

And yes, that's a genuine parallel [map-reduce][] in Rust!

[map-reduce]: http://research.google.com/archive/mapreduce.html

So here's our final loop, using work-stealing parallelism:

```rust
fn output_helper(&self, op: Operation, export_dir: &Path) -> Result<()> {
    // Output each pod.  This isn't especially slow (except maybe the
    // Vault plugin), but parallelizing things is easy.
    self.pods.par_iter()

        // Don't export pods which aren't enabled.
        .filter(|pod| pod.enabled_in(&self.current_target))

        // Process each pod in parallel.
        .map(|pod| -> Result<()> {
            // Figure out where to put our pod.
            // ...

            // Combine overrides, make it standalone, tweak as needed, and
            // output.
            let mut file = try!(pod.merged_file(&self.current_target));
            try!(file.make_standalone(&self.pods_dir()));
            let ctx = plugins::Context::new(self, pod);
            try!(self.plugins().transform(op, &ctx, &mut file));
            try!(file.write_to_path(out_path));
            Ok(())
        })

        // If more than one parallel branch fails, just return one error.
        .reduce_with(|result1, result2| result1.and(result2))
        .unwrap_or(Ok(()))
}
```

This was pretty painless! I have a lot more things to parallelize, of course. But this is what day-to-day Rust development is like: I have to do a bit of extra work to satisfy the compiler (which mostly becomes a reflex). But in turn, the compiler ferrets out all kinds of subtle concurrency errors and generally watches my back. It's an interesting tradeoff, and overall I like it.

A detailed example of how to parallelize a loop with Rust and rayon.

All posts