In my last blogpost (was it really more than two months ago—I plead CEO-overload!), we looked at how SQL and data integration are essential to the development of a truly useful customer profile. At the end, I promised to step through the process of nurturing relationships, where we guide prospects and customers through each stage, sharing and collecting information in a step-wise cadence. So here goes—and note that I’m using the vocabulary and categorizations from Salesforce, one of the main customer relationship management apps on the market:

  1. First, a set of information is collected from an interested party—also known as a lead—and further information is sent to match the needs of that lead.
  2. After that, the lead is qualified as a prospect, and the sales rep conducts further qualification discussions to move that prospect to the next stage of the pipeline.
  3. At this point, enough information is known on the needs of the prospect to determine if an opportunity for a sale exists. If yes, the sales rep takes the final qualification step by negotiating the terms of a deal.
  4. When (and if) a deal is struck, that opportunity becomes a customer.

What we can see in this nurturing process, as in most business processes or complex transactions, is that the whole operation is built around a series of steps, or a business workflow. At each step, specific information is gathered and you move to the next steps only when the information requirement of the current step is fulfilled, as we see below:


What I am describing here is obvious at the business level—or “conceptual level” in the parlance of the data-modeling world. However, when it comes to the details of low-level implementation at the data structure or database level, things are not so cleanly delineated and as a result, currently deployed solutions are far from optimal. So let’s revisit this pattern as it applies to the integration of a user profile at the level of SQL.

Digging Deeper Into the Process of Building a User Profile

Let’s suppose that we have four sets of information—AKA tables in SQL—about a user with relationships that can be direct (such as how the blue table with the person icon connects with the green or orange table in the diagram below) or indirect (like the blue and violet tables). For the sake of simplicity, we’ll say that these 4 four tables are hosted within the same database system (in real life, you might very well have this information spread across several different data silos, adding a ton more complexity to the task of building a user profile).

To get a complete profile of a customer, you would glue together those tables by joining them via a garden variety “inner join based on equality,” which is the most frequently used style of join.

Inner Join Based On Equality

And at this stage, most implementers would be happy—but are we really delivering the profile that was defined in the specification? Not even close. What we’re getting is a list aggregated in a table—basically a hodgepodge of all paths linking the initial info about the person (the blue icon) to the rest of the profile information (orange, green, violet).

What we really need is a complete profile segmented by the different steps of our workflow, one that’s organized by the different contexts of our nurturing process. In this specific data organization, we can easily distinguish who is still in the lead stage, and who is a prospect, opportunity, or customer. This graph—or hierarchy—gives us a complete, contextual picture of our pipeline, narrowing from a very large lead base to a smaller subset of customers, as we see below.

Contextual Picture

Fulfilling this requirement for clarity and context enables us to imagine an architecture that would deliver a better solution to our quest for a complete profile. Don’t worry—we’ll dig deep into this architecture in my next post.

Data Architecture