AI, startup hacks, and engineering miracles from your friends at Faraday

Lifecycle mapping: uncovering rich, predictive data sources

Perry McDermott on


Customer audience with limited data

Customer lifecycle optimization: a 4-step process...

We recently talked about the role of data and artificial intelligence (AI) in the customer lifecycle optimization process.

To sum it up, because revenue is linked directly to the customer lifecycle, applying predictive data and AI yields powerful, fact-based predictions that help optimize our targeting, outreach, and overall spend at every stage.

This process is customer lifecycle optimization (CLO), and it involves four prescribed steps: lifecycle mapping, data discovery, predictive groundwork, and guided outreach.

Let’s dive into the first step: lifecycle mapping!

What is 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, which we’ll discuss more in a later article. For now, we'll focus on lifecycle mapping.


The uniform B2C customer lifecycle:

B2C customer lifecycle stages

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 organization’s customer journey. How are stages defined? Which attributes qualify individuals to be placed in those stages? What events trigger transitions between stages?

How to map your customer lifecycle...

Now that you’ve given some thought to your customer journey, it’s time to formalize your observations. Let’s map out your customer lifecycle to the uniform lifecycle we presented above.

Use the following table to help organize your findings:


Customer lifecycle mapping tool

Transition in — What events trigger someone to land in that stage? Did they submit a webform, sign up for a newsletter, or call in from a direct mailer?

Litmus test — Do individuals in this stage meet all the necessary criteria to belong in this stage? Are there any grey areas?

Transition out — What conditions trigger someone to transition out of this stage? You’ll notice that your “transition out” should overlap with the following stage’s “transition in.” This prevents leads and customer from slipping through the cracks.

What's next?

Your customer lifecycle mapping is something that everyone in your organization should agree with. If you run into confusion or disagreement, the process is working! Collaborate with your team until you can all agree on various stage attributes and transition triggers.

Now you’re ready to discover those rich, predictive data sources. We’ll explore the data discovery process in the next article, but if you’re not the waiting type, download our whitepaper, AI for the customer lifecycle: Making the most of your data!


Whitepaper — AI for the customer lifecycle

Optimize your customer lifecycle with artificial intelligence

Perry McDermott on

Customer audience with predictive data

AI-powered customer lifecycle marketing is the future. Why are so many companies missing the wave?

We all know we should be doing more with our data. If you’re a CEO, you’re hearing it from your board. If you’re a manager, you’re hearing it from your CEO. So why are so many companies having trouble operationalizing data-driven business processes?

Spoiler alert: it’s not due to a lack of data… corporate IT systems are better than ever at capturing data. Rather, as data volumes grow, it becomes increasingly difficult to access, analyze, and make meaningful predictions from all that data.

In an ideal world, we could analyze and make predictions using every single data point in our systems, but in reality, the “do-everything” approach to analytics is overkill. Focusing our analyses and predictions on specific business issues lets us extract immediate value from the right data and the right sources..

When it comes to B2C, our lowest-hanging fruit for a truly data-driven process is the customer lifecycle; where proven AI-powered predictive models can optimize marketing and customer outreach efforts at every stage. We call this customer lifecycle optimization.

What is customer lifecycle optimization?

Customer lifecycle optimization (CLO) is the practice of using data and AI to make predictions that measurably improve outcomes at each stage of the customer lifecycle.

As with other practices, CLO involves a series of prescribed steps to be done effectively.

The canonical CLO process comprises four steps: lifecycle mapping, data discovery, predictive groundwork, and purposeful outreach. We’ll dive deeper into each individual step in future posts, but keep in mind that this process is near-universal for B2C customer lifecycle optimization.

So what’s the point? Why does CLO matter?

Let's take a step back to answer that...

For nearly every B2C company, revenue is linked to the customer lifecycle. Lead generation, opportunity conversion, and customer retention outcomes will have a direct impact on revenues. So using data and AI to optimize these outcomes is the most logical way to systematically build AND defend revenue.

This probably isn’t news to you. The concept of customer lifecycle marketing has been around for a while, and many of you have tried to “use data” throughout the process. While that’s a smart decision, it’s probably safe to say that nearly all of you have been somewhat disappointed with the results.

Why is that?

Why is data-driven marketing so difficult to implement?

We’ve narrowed the answer down to two overarching factors: The Data Gap and Data Myopia...

The Data Gap: nearly all CEOs believe they have operationalized data-driven business, while nearly all employees feel the opposite. CEOs aren’t necessarily out-of-touch with the concept of data-driven business, and employees aren’t necessarily incapable of using analytical tools. The problem is that most enterprise analytics tools and BI systems take several days to deliver reports, use data with limited depth and breadth, and are therefore largely unused by employees.

Data Myopia: traditional enterprise analytics tools and BI systems suffer from “blind spots” in the underlying source data used to create reports. In other words, these systems report on data with limited depth and breadth, severely limiting the quality of analysis and prediction. For a deeper look into the importance of data depth and breadth, check out the the whitepaper linked at the end of this post.

Am I ready to use AI throughout my customer lifecycle?

Thanks to the uniformity of the B2C customer lifecycle, nearly every company can adopt and implement a truly data-driven approach to lead generation, opportunity conversion, and customer retention.

Check out AI for customer lifecycle: Making the most of your data to learn how to map out your customer lifecycle, discover relevant data sources, and setup the predictive groundwork necessary to guide your outreach.

Whitepaper — AI for the customer lifecycle

How to get U.S. Census data as CSV — censusapi2csv

Bill Morris on

This post is part of our data science series.

The U.S. Census and American Community Survey (ACS) are the crown jewels of open data (bother your Representative today to make sure they stay that way), but working with data from the Census API isn't always intuitive. Here's an example response to an API call for ACS per capita income data:

[["B19301_001E","state","county","tract","block group"],
["25611","50","007","000100","1"],
["36965","50","007","000100","2"],
["29063","50","007","000200","1"],
. . .

It's not a CSV, it's not exactly JSON, it's just . . . data. We tend to use CSVs as our basic building blocks, so we built a tool to nudge this response into a pure format. Here's how to use it:

Install

npm install censusapi2csv -g  

Usage

Let's grab a few things from the ACS API: total population (B01001) and per capita income (B19301), for every block group in Chittenden County, Vermont:

censusapi2csv -l 'block group' -f B01001,B19301 -s 50 -c 007  

. . . we can even pipe this into our favorite CSV-parsing tool, xsv:

censusapi2csv -l 'block group' -f B01001,B19301 -s 50 -c 007 | xsv table  

. . . and we get a formatted look at the data:

B01001_001E  B19301_001E  state  county  tract   block group  
3057         25611        50     007     000100  1  
1200         36965        50     007     000100  2  
1641         29063        50     007     000200  1  
1882         28104        50     007     000200  2  
699          61054        50     007     000200  3  
. . .

This is just a tiny step in the process of working with census data - and there are many alternative approaches - but we thought it was worth sharing.

Using context in redux-saga

Nick Husher on

At Faraday, we use redux-saga in our client-side webapp because it offers a robust and testable set of tools for managing complex and asynchronous effects within Redux apps. I recently upgraded our version of redux-saga from an embarrassingly old version to the latest, in part to take advantage of a new (but largely undocumented) feature: context.

The problem

I want to be able to write sagas without having to implicitly import any statically-declared singletons. The reason for that is simple: if I'm testing the saga that creates new users, I don't want to drag along the API layer or the router instance.

There are also a few modules in the codebase that only work in a browser and will fail loudly when I try to run them in mocha. If one of these browser-only modules ends up in an import chain, suddenly my tests start failing for bad reasons. At worst, I have to rethink my testing strategy to include a mock browser environment. I'm lazy and hate testing, and I think mocks are a gross awful code smell that should be avoided at all costs.

The solution

One of redux-saga's greatest features is that I can test a saga's behavior without actually executing that behavior, or even really knowing much about the details of how that behavior alters the world. It it should be possible to apply that to dependencies as well. And it is possible in redux-saga 0.15.0 and later using context.

Right now, there are very limited docs for context, but acts as shared value across all sagas that can be read with the getContext effect and written to by the setContext effect. It can also be set when the saga middleware is created by passing a context object as configuration.

Example

Let's say we're writing an app that fetches game inventory data from the server using some kind of authentication. We don't want tokens and authentication to bleed into all our sagas, so we wrap it up in a nice singleton API service:

class ApiService {  
  getInventory = () => {
    return fetch('/api/inventory', {
      headers: {
        Authorization: `Bearer ${this.token}`
      }
    }).then(res => res.json())
  }
}

const api = new ApiService()  
api.token = localStorage.token

export default api  

Without context, our saga would probably statically import the API singleton:

import { call } from 'redux-saga/effects'  
import api from './api' 

export function * fetchInventorySaga () {  
  const inventory = yield call(api.getInventory)
  // Do something with the inventory data...
}

All is well until we try to test it in nodejs/mocha:

import { fetchInventorySaga } from '../src/sagas/inventorySaga.js  
// ReferenceError: localStorage is not defined

There is no localStorage in the nodejs global context. We can either pull in a testing harness to change how import api from './api' is resolved, attempt to run the tests in a browser, or roll our own late-binding mechanism so that you don't need to import API and can pass the API instance in at runtime.

We need to solve the same problem for fetch, because that's also absent in nodejs.

Or, we could use context. Our saga changes only a little bit:

import { call, getContext } from 'redux-saga/effects'  
// No more API import:
// import api from './api' 

export function * fetchInventorySaga () {  
  // Get the api value out of the shared context:
  const api = yield getContext('api')

  const inventory = yield call(api.getInventory)
  // Do something with the inventory data...
}

We can now test the getContext effect just like we would any other redux-saga effect, and we can insert a mock value into fetchInventorySaga at test-time if we need to.

Setting up context in the main application is very straightforward. When you're creating your saga middleware:

import createSagaMiddleware from 'redux-saga'  
import api from './api'

const saga = createSagaMiddleware({  
  context: {
    api // our singleton API value
  }
})

Being able to late-bind singleton values like this has been enormously helpful writing robust tests in a complex codebase. I'll be steadily migrating the application code to use getContext more frequently, now that I have it as an option.

How to read CREATE TABLE SQL with pg_query

Seamus Abshere on

pg_query is a really cool Ruby library that uses Postgres's query parser to give you programmatic access to SQL queries.

One thing you can do with it is read a CREATE TABLE statement to map column names to column types:

sql = <<-SQL  
  CREATE TABLE example (
    my_text text,
    my_int int,
    my_float float
  )
SQL

require 'pg_query'  
types = PgQuery.parse(sql).tree[0].dig('RawStmt', 'stmt', 'CreateStmt', 'tableElts').map do |c|  
  c.fetch('ColumnDef')
end.inject({}) do |memo, c|  
  memo[c.fetch('colname')] = c.dig('typeName', 'TypeName', 'names').detect { |td| td.dig('String', 'str') != 'pg_catalog' }.dig('String', 'str')
  memo
end  

The output will be:

{
  "my_text"  => "text",
  "my_int"   => "int4",
  "my_float" => "float8"
}

Thanks to Lukas Fittl for this gem!

Geochunk: fast, intelligent splitting for piles of address data

Bill Morris on

aurora

This post is part of our practical cartography and data science series.

The problem: you want to split up a few million U.S. address records into equally-sized chunks that retain spatial hierarchy. You want to do this without anything other than a street address (geocoding is expensive!). Maybe you want to do this as part of a map/reduce process (we certainly do), maybe you want to do some sampling, who knows?

The solution: Muthaflippin' Geochunk

Anyone who's ever used U.S. ZIP codes as a way to subdivide datasets can tell you: 60608 (pop 79,607) is a totally different beast than 05851 (pop 525). They're not census tracts; it's not really appropriate to compare them statistically or thematically.

Our solution - largely the work of platform wizard and Rust enthusiast Eric Kidd - is to bake census data into a tool that does the splitting for you at a level that allows for easy comparison. More specifically:

It provides a deterministic mapping from zip codes to "geochunks" that you can count on remaining stable.

Check out the Jupyter notebook that explains the algorithm in detail, but it works like so:

Install

Install rust first if you don't have it:

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

. . . then geochunk, using the rust package manager:

cargo install geochunk  

. . . or install from one of the prepackaged binaries.

Use 1: Indexing

Build a table that assigns every U.S. zipcode to a geochunk that contains 250,000 people:

geochunk export zip2010 250000 > chunks_of_250k_people.csv  

Use 2: List processing

Alternately, let's try a pipeline example that uses geochunk csv: say you want to parallel-process every address in the state of Colorado, and you need equal-size but contiguous slices to do it.

wget -c https://s3.amazonaws.com/data.openaddresses.io/runs/283082/us/co/statewide.zip && unzip statewide.zip  
  • Pipe the full file through geochunk, into slices of about 250,000 people each:
cat us/co/statewide.csv | geochunk csv zip2010 250000 POSTCODE > statewide_chunks_150k.csv  

. . . and now you have 2 million addresses, chopped into ~8 equally-sized slices with rough contiguity:

denver

Geochunk works on this scale in 1.38s (Have you heard us evangelizing about Rust yet?), leaving you plenty of time for the real processing.

This tool is serious dogfood for us; it's baked into our ETL system, and we use it to try making a tiny dent in the Modifiable Areal Unit Problem. We hope you'll find it useful too.

How to generate UUID ranges for SQL queries

Seamus Abshere on

So you've got a table with a UUID primary key. How do you search for only 1/4, or 1/8, or 1/128 of it?

BETWEEN '00000000-0000-0000-0000-000000000000' AND '3fffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '40000000-0000-0000-0000-000000000000' AND '7fffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '80000000-0000-0000-0000-000000000000' AND 'bfffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN 'c0000000-0000-0000-0000-000000000000' AND 'ffffffff-ffff-ffff-ffff-ffffffffffff'  

And how do you generate those ranges? Here it is in Ruby, with n=4:

UUID_MAX = 2**128  
def to_uuid(int)  
  memo = int.to_s(16)
  memo = memo.rjust(32, '0')
  [ 8, 13, 18, 23 ].each do |p|
    memo = memo.insert(p, '-')
  end
  memo
end  
def sql_uuid_ranges(n)  
  chunk = UUID_MAX / n
  (n+1).times.map do |i|
    [chunk*i, chunk*i-1]
  end.each_cons(2).map do |bottom, top|
    a1, _ = bottom
    _, b2 = top
    "BETWEEN '#{to_uuid(a1)}' AND '#{to_uuid(b2)}'"
  end
end  
puts sql_uuid_ranges(4)  

Note that we have to do some fancy stuff with chunk*i-1 because BETWEEN is inclusive on both sides. Not that that will cause a collision any time before the heat death of the universe, but still.

Here's the same thing, with n=64:

BETWEEN '00000000-0000-0000-0000-000000000000' AND '03ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '04000000-0000-0000-0000-000000000000' AND '07ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '08000000-0000-0000-0000-000000000000' AND '0bffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '0c000000-0000-0000-0000-000000000000' AND '0fffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '10000000-0000-0000-0000-000000000000' AND '13ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '14000000-0000-0000-0000-000000000000' AND '17ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '18000000-0000-0000-0000-000000000000' AND '1bffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '1c000000-0000-0000-0000-000000000000' AND '1fffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '20000000-0000-0000-0000-000000000000' AND '23ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '24000000-0000-0000-0000-000000000000' AND '27ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '28000000-0000-0000-0000-000000000000' AND '2bffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '2c000000-0000-0000-0000-000000000000' AND '2fffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '30000000-0000-0000-0000-000000000000' AND '33ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '34000000-0000-0000-0000-000000000000' AND '37ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '38000000-0000-0000-0000-000000000000' AND '3bffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '3c000000-0000-0000-0000-000000000000' AND '3fffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '40000000-0000-0000-0000-000000000000' AND '43ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '44000000-0000-0000-0000-000000000000' AND '47ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '48000000-0000-0000-0000-000000000000' AND '4bffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '4c000000-0000-0000-0000-000000000000' AND '4fffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '50000000-0000-0000-0000-000000000000' AND '53ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '54000000-0000-0000-0000-000000000000' AND '57ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '58000000-0000-0000-0000-000000000000' AND '5bffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '5c000000-0000-0000-0000-000000000000' AND '5fffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '60000000-0000-0000-0000-000000000000' AND '63ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '64000000-0000-0000-0000-000000000000' AND '67ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '68000000-0000-0000-0000-000000000000' AND '6bffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '6c000000-0000-0000-0000-000000000000' AND '6fffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '70000000-0000-0000-0000-000000000000' AND '73ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '74000000-0000-0000-0000-000000000000' AND '77ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '78000000-0000-0000-0000-000000000000' AND '7bffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '7c000000-0000-0000-0000-000000000000' AND '7fffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '80000000-0000-0000-0000-000000000000' AND '83ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '84000000-0000-0000-0000-000000000000' AND '87ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '88000000-0000-0000-0000-000000000000' AND '8bffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '8c000000-0000-0000-0000-000000000000' AND '8fffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '90000000-0000-0000-0000-000000000000' AND '93ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '94000000-0000-0000-0000-000000000000' AND '97ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '98000000-0000-0000-0000-000000000000' AND '9bffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN '9c000000-0000-0000-0000-000000000000' AND '9fffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN 'a0000000-0000-0000-0000-000000000000' AND 'a3ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN 'a4000000-0000-0000-0000-000000000000' AND 'a7ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN 'a8000000-0000-0000-0000-000000000000' AND 'abffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN 'ac000000-0000-0000-0000-000000000000' AND 'afffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN 'b0000000-0000-0000-0000-000000000000' AND 'b3ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN 'b4000000-0000-0000-0000-000000000000' AND 'b7ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN 'b8000000-0000-0000-0000-000000000000' AND 'bbffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN 'bc000000-0000-0000-0000-000000000000' AND 'bfffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN 'c0000000-0000-0000-0000-000000000000' AND 'c3ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN 'c4000000-0000-0000-0000-000000000000' AND 'c7ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN 'c8000000-0000-0000-0000-000000000000' AND 'cbffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN 'cc000000-0000-0000-0000-000000000000' AND 'cfffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN 'd0000000-0000-0000-0000-000000000000' AND 'd3ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN 'd4000000-0000-0000-0000-000000000000' AND 'd7ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN 'd8000000-0000-0000-0000-000000000000' AND 'dbffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN 'dc000000-0000-0000-0000-000000000000' AND 'dfffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN 'e0000000-0000-0000-0000-000000000000' AND 'e3ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN 'e4000000-0000-0000-0000-000000000000' AND 'e7ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN 'e8000000-0000-0000-0000-000000000000' AND 'ebffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN 'ec000000-0000-0000-0000-000000000000' AND 'efffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN 'f0000000-0000-0000-0000-000000000000' AND 'f3ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN 'f4000000-0000-0000-0000-000000000000' AND 'f7ffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN 'f8000000-0000-0000-0000-000000000000' AND 'fbffffff-ffff-ffff-ffff-ffffffffffff'  
BETWEEN 'fc000000-0000-0000-0000-000000000000' AND 'ffffffff-ffff-ffff-ffff-ffffffffffff'  

You get the idea.

ActiveRecord ↔︎ Sequel raw sql cheat sheet

Seamus Abshere on

UPDATED with fixes from @jeremyevans

Going back and forth between ActiveRecord and Sequel can be very confusing... especially because the Sequel documentation assumes that you use "datasets" instead of raw sql.

What ActiveRecord Sequel
One value ActiveRecord::Base.connection.select_value(sql) DB[sql].get
One row ActiveRecord::Base.connection.select_row(sql) DB[sql].first
One column across many rows ActiveRecord::Base.connection.select_values(sql) DB[sql].select_map
Many rows as hashes ActiveRecord::Base.connection.select_all(sql) DB[sql].all
Quote value ActiveRecord::Base.connection.quote(value) DB.literal(value)
Quote identifier (e.g. table name, column name) ActiveRecord::Base.connection.quote_ident(ident) DB.quote_identifier(ident)

Please email us if you think of any other ones!

Customer Success, cookies, and diminishing returns: don't announce features all at once

Thomas Bryenton on

Chocolate chip cookies

Right this moment, how much would you pay for a cookie at a coffeeshop? Let's say the amount is $3.

picture_of_cookies

(I could really go for one, so my personal estimate might be higher. I mean, check out how good that looks.)

How much would you be open to paying the coffeeshop for a subsequent cookie? A third cookie? A fifth?

Probably it would be steadily less than $3. Why? Later cookies have lower value.

Well . . . for now at least. If you come back to the coffeeshop tomorrow, you could be ready for more!

Customer Success

A related principle applies in Customer Success: people love hearing when you ship a feature they wanted. But tell them about a third feature or a fifth? Their eyes will glaze right over.

(Speaking of glaze, if cookies aren't your thing, how about these glazed scones?)

scone_picture

Sure, sometimes an engineering team works in bursts. Progress can come all at once. Then, the temptation for Customer Success is to "catch up" by reporting things to clients exactly when they happen.

This doesn't mean that as a Customer Success team you can't be strategic about who to announce features to, and when.

So:

  • Notice each client's rhythm of hunger for new features
  • Keep a stash of "feature snacks" to give clients
  • Treat yourself to a literal or figurative cookie (or scone)

How to aggregate JSONB in PostgreSQL 9.5+

Seamus Abshere on

This is part of our series on PostgreSQL and things that are obvious once you see them. It's a 2017 update to our 2016 article How to merge JSON fields in Postgres Nice!

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):

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

Here's how you use it:

# 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 || that was introduced in Postgres 9.5.

It's just that simple!