# Customer Journey and Lifetime Value Analytics using Looker, Google BigQuery, Stitch and dbt

URL: https://rittmananalytics.com/blog/2019/05/21/customer-journey-and-lifetime-value-analytics-using-looker-google-bigquery-stitch-and-dbt
Description: One of the most common analytics use-cases I come across on client projects is to understand the “customer journey”; the series of stages prospects and customers typically go through from…
Author: Mark Rittman
Published: 2019-05-22T00:13:17+01:00
Modified: 2026-07-02T00:30:56+01:00

---

![Customer Journey and Lifetime Value Analytics using Looker, Google BigQuery, Stitch and dbt](https://cdn-images-1.medium.com/max/800/0*4E-2HBQReeVQhpJu)

One of the most common analytics use-cases I come across on client projects is to understand the [“customer journey”](https://en.wikipedia.org/wiki/Customer_experience#Digital_customer_journey); the series of stages prospects and customers typically go through from becoming aware of a company’s products and services through to consideration, conversion, up-sell and repurchase and eventually, to churn.

Customers typically interact with multiple digital and offline channels over their overall lifetime, and if we take the model of a consulting services business such as [Rittman Analytics](https://rittmananalytics.com/), a typical customer journey and use of those touch-points might look like the diagram below.

![](https://cdn-images-1.medium.com/max/800/0*4E-2HBQReeVQhpJu)

Each customer and prospect interaction with those touch-points sends signals about their buying intentions, product and service preferences and readiness to move through to the next stage in the purchase funnel.

![](https://cdn-images-1.medium.com/max/800/0*NEGQX2ckdD0lkrKy)

Creating visualization such as these in Looker typically involves two steps of data transformation either through a set of Looker (persistent) derived tables, or as we’ve done for our own operational analytics platform, through two [dbt](https://www.getdbt.com/) (Data Build Tool) models sequenced together as a transformation graph.

To start, we select a common set of columns from each digital touchpoint channel as brought in by our project data pipeline tool, [Stitch](https://www.stitchdata.com/); Hubspot for inbound and outbound marketing communctions together with sales activity and closes/loses; Harvest for billable and non-billable client days; Segment for website visits and so on. For each source we turn each type of customer activity they record into a type of event, details of the event and a monetary value (revenue, cost etc) that we can then use to measure the overall lifetime value of each client later on.

![](https://cdn-images-1.medium.com/max/800/0*_Imn4FqJZCWRa4LG)

Note the expressions at the start of the model definition that define a number of recency measures (days since last billable day, last contact day and so on) along with another set that are used for defining customer cohorts and bucketing activity into months and weeks since those first engagement date.

{{  
    config(  
        materialized='table',  
        partition\_by='DATE(event\_ts)'  
    )  
}}  
SELECT  
    \*,  
    {{ dbt\_utils.datediff('last\_billable\_day\_ts', current\_timestamp(), 'day')}} AS days\_since\_last\_billable\_day,  
    {{ dbt\_utils.datediff('last\_incoming\_email\_ts', current\_timestamp(), 'day')}} AS days\_since\_last\_incoming\_email,  
    {{ dbt\_utils.datediff('last\_outgoing\_email\_ts', current\_timestamp(), 'day')}} AS days\_since\_last\_outgoing\_email,  
    {{ dbt\_utils.datediff('last\_site\_visit\_day\_ts', current\_timestamp(), 'month')}} AS months\_since\_last\_site\_visit\_day,  
    {{ dbt\_utils.datediff('last\_site\_visit\_day\_ts', current\_timestamp(), 'week')}} AS weeks\_since\_last\_site\_visit\_day  
FROM  
  (SELECT  
      \*,  
      ROW\_NUMBER() OVER (PARTITION BY customer\_id ORDER BY event\_ts) AS event\_seq,  
      MIN(CASE WHEN event\_type = 'Billable Day' THEN event\_ts END)  
          {{ customer\_window\_over('customer\_id', 'event\_ts', 'ASC') }} AS first\_billable\_day\_ts,  
      MAX(CASE WHEN event\_type = 'Billable Day' THEN event\_ts END)  
          {{ customer\_window\_over('customer\_id', 'event\_ts', 'ASC') }} AS last\_billable\_day\_ts,  
      MIN(CASE WHEN event\_type = 'Client Invoiced' THEN event\_ts END)  
          {{ customer\_window\_over('customer\_id', 'event\_ts', 'ASC') }} AS first\_invoice\_day\_ts,  
      MAX(CASE WHEN event\_type = 'Client Invoiced' THEN event\_ts END)  
          {{ customer\_window\_over('customer\_id', 'event\_ts', 'ASC') }} AS last\_invoice\_day\_ts,  
      MAX(CASE WHEN event\_type = 'Site Visited' THEN event\_ts END)  
          {{ customer\_window\_over('customer\_id', 'event\_ts', 'ASC') }} AS last\_site\_visit\_day\_ts,  
      MAX(CASE WHEN event\_type = 'Incoming Email' THEN event\_ts END)  
          {{ customer\_window\_over('customer\_id', 'event\_ts', 'ASC') }} AS last\_incoming\_email\_ts,  
      MAX(CASE WHEN event\_type = 'Outgoing Email' THEN event\_ts END)  
          {{ customer\_window\_over('customer\_id', 'event\_ts', 'ASC') }} AS last\_outgoing\_email\_ts,  
      MIN(event\_ts)  
          {{ customer\_window\_over('customer\_id', 'event\_ts', 'ASC') }} AS first\_contact\_ts,  
      DATE\_DIFF(date(event\_ts),MIN(CASE WHEN event\_type = 'Billable Day' THEN date(event\_ts) END)          {{ customer\_window\_over('customer\_id', 'event\_ts', 'ASC') }},MONTH) AS months\_since\_first\_billable\_day,  
      DATE\_DIFF(date(event\_ts),MIN(CASE WHEN event\_type = 'Billable Day' THEN date(event\_ts) END)  
          {{ customer\_window\_over('customer\_id', 'event\_ts', 'ASC') }},WEEK) AS weeks\_since\_first\_billable\_day,  
      DATE\_DIFF(date(event\_ts),MIN(CASE WHEN event\_type like '%Email%' THEN date(event\_ts) END)  
          {{ customer\_window\_over('customer\_id', 'event\_ts', 'ASC') }},MONTH) AS months\_since\_first\_contact\_day,  
      DATE\_DIFF(date(event\_ts),MIN(CASE WHEN event\_type like '%Email%' THEN date(event\_ts) END)  
          {{ customer\_window\_over('customer\_id', 'event\_ts', 'ASC') }},WEEK) AS weeks\_since\_first\_contact\_day,  
      MAX(CASE WHEN event\_type = 'Billable Day' THEN true ELSE false END)  
          {{ customer\_window\_over('customer\_id', 'event\_ts', 'ASC') }} AS billable\_client,  
      MAX(CASE WHEN event\_type LIKE '%Sales%' THEN true ELSE false END)  
          {{ customer\_window\_over('customer\_id', 'event\_ts', 'ASC') }} AS sales\_prospect,  
      MAX(CASE WHEN event\_type LIKE '%Site Visited%' THEN true ELSE false END)  
          {{ customer\_window\_over('customer\_id', 'event\_ts', 'ASC') }} AS site\_visitor,  
      MAX(CASE WHEN event\_details LIKE '%Blog%' THEN true ELSE false END)  
          {{ customer\_window\_over('customer\_id', 'event\_ts', 'ASC') }} AS blog\_reader,  
      MAX(CASE WHEN event\_type LIKE '%Podcast%' THEN true ELSE false END)  
          {{ customer\_window\_over('customer\_id', 'event\_ts', 'ASC') }} AS podcast\_reader  
  FROM  
  -- sales opportunity stages  
      (SELECT  
            deals.lastmodifieddate AS event\_ts,  
          customer\_master.customer\_id AS customer\_id,  
          customer\_master.customer\_name AS customer\_name,  
          deals.dealname AS event\_details,  
          deals.dealstage AS event\_type,  
          AVG(deals.amount) AS event\_value  
      FROM  
          {{ ref('customer\_master') }} AS customer\_master  
      LEFT JOIN  
          {{ ref('deals') }} AS deals  
          ON customer\_master.hubspot\_company\_id = deals.associatedcompanyids  
      LEFT JOIN  
          {{ ref('hubspot\_owners') }} AS owners  
          ON deals.hubspot\_owner\_id = CAST(owners.ownerid AS STRING)  
      WHERE  
          deals.lastmodifieddate IS NOT null  
      {{ dbt\_utils.group\_by(n=5) }}  
      UNION ALL  
  -- consulting days  
      SELECT  
            time\_entries.spent\_date AS event\_ts,  
            customer\_master.customer\_id AS customer\_id,  
          customer\_master.customer\_name AS customer\_name,  
          projects.name AS event\_details,  
          CASE WHEN time\_entries.billable THEN 'Billable Day' ELSE 'Non-Billable Day' END AS event\_type,  
          time\_entries.hours \* time\_entries.billable\_rate AS event\_value  
      FROM  
          {{ ref('customer\_master') }} AS customer\_master  
      LEFT JOIN  
          {{ ref('harvest\_projects') }} AS projects  
          ON customer\_master.harvest\_customer\_id = projects.client\_id  
      LEFT JOIN  
          {{ ref('harvest\_time\_entries') }} AS time\_entries  
          ON time\_entries.project\_id = projects.id  
      WHERE  
          time\_entries.spent\_date IS NOT null  
      {{ dbt\_utils.group\_by(n=6) }}  
  UNION ALL  
  -- incoming and outgoing emails  
      SELECT  
            communications.communication\_timestamp AS event\_ts,  
            customer\_master.customer\_id AS customer\_id,  
          customer\_master.customer\_name AS customer\_name,  
          communications.communications\_subject AS event\_details,  
          CASE WHEN communications.communication\_type = 'INCOMING\_EMAIL' THEN 'Incoming Email'  
               WHEN communications.communication\_type = 'EMAIL' THEN 'Outgoing Email'  
               ELSE communications.communications\_subject  
          END AS event\_type,  
          1 AS event\_value  
      FROM  
          {{ ref('customer\_master') }} AS customer\_master  
      LEFT JOIN  
          {{ ref('communications') }} AS communications  
          ON customer\_master.hubspot\_company\_id = communications.hubspot\_company\_id  
      WHERE  
          communications.communication\_timestamp IS NOT null  
      {{ dbt\_utils.group\_by(n=5) }}  
  UNION ALL  
  -- sales opportunity stages  
      SELECT  
            invoices.issue\_date AS event\_ts,  
            customer\_master.customer\_id AS customer\_id,  
          customer\_master.customer\_name AS customer\_name,  
          invoices.subject AS event\_details,  
          'Client Invoiced' AS event\_type,  
          SUM(invoices.amount) AS event\_value  
      FROM  
          {{ ref('customer\_master') }} AS customer\_master  
      LEFT JOIN  
          {{ ref('harvest\_invoices') }} AS invoices  
          ON customer\_master.harvest\_customer\_id = invoices.client\_id  
      WHERE  
          invoices.issue\_date IS NOT null  
      {{ dbt\_utils.group\_by(n=5) }}  
  UNION ALL  
      SELECT  
         invoices.issue\_date AS event\_ts,  
         customer\_master.customer\_id AS customer\_id,  
         customer\_master.customer\_name AS customer\_name,  
         invoice\_line\_items.description AS event\_details,  
         'Client Credited' AS event\_type,  
          COALESCE(SUM(invoice\_line\_items.amount ), 0) AS event\_value  
      FROM  
          {{ ref('customer\_master') }} AS customer\_master  
      LEFT JOIN  
          {{ ref('harvest\_invoices') }} AS invoices  
          ON customer\_master.harvest\_customer\_id = invoices.client\_id  
      LEFT JOIN  
          {{ ref('harvest\_invoice\_line\_items') }} AS invoice\_line\_items  
          ON invoices.id = invoice\_line\_items.invoice\_id  
      {{ dbt\_utils.group\_by(n=5) }}  
      HAVING  
         (COALESCE(SUM(invoice\_line\_items.amount ), 0) < 0)  
  UNION ALL  
      SELECT   
              pageviews.timestamp AS event\_ts,  
              customer\_master.customer\_id  AS customer\_id,  
              customer\_master.customer\_name AS customer\_name,  
              pageviews.page\_subcategory AS event\_details,  
              'Site Visited' AS event\_type,  
              sum(1) as event\_value  
      FROM   
          {{ ref('customer\_master') }}  AS customer\_master  
     LEFT JOIN   
          {{ ref('pageviews') }} AS pageviews   
          ON customer\_master.customer\_name = pageviews.network  
      {{ dbt\_utils.group\_by(n=5) }}  
  UNION ALL  
      SELECT  
          \*  
      FROM  
          (SELECT  
              invoices.paid\_at AS event\_ts,  
          customer\_master.customer\_id AS customer\_id,  
              customer\_master.customer\_name AS customer\_name,  
              invoices.subject AS event\_details,  
              CASE WHEN invoices.paid\_at <\= invoices.due\_date THEN 'Client Paid' ELSE 'Client Paid Late' END AS event\_type,  
              SUM(invoices.amount) AS event\_value  
          FROM  
              {{ ref('customer\_master') }} AS customer\_master  
          LEFT JOIN {{ ref('harvest\_invoices') }}  AS invoices  
              ON customer\_master.harvest\_customer\_id = invoices.client\_id  
          WHERE  
            invoices.paid\_at IS NOT null  
          {{ dbt\_utils.group\_by(n=5) }}  
          )  
      )  
  WHERE  
      customer\_name NOT IN ('Rittman Analytics', 'MJR Analytics')  
  )

Deploying this model and then querying the resulting table, filtering on just a single customer and ordering rows by timestamp, you can see the history of all interactions by that customer with our sales and delivery channels.

![](https://cdn-images-1.medium.com/max/800/0*cR7SmHEjK3aryKmB)

You can also plot all of these activity types into a combined area and bar chart, for example, to show billable days initially growing from zero to a level that we maintain over several months, and with events such as client being invoiced, incoming and outgoing communications and the client actually paying in-full at the end.

![](https://cdn-images-1.medium.com/max/800/0*C8qUql3ghozG0u0N)

We can also do things such as restrict the event type reported on to billable days, and then use Looker’s dimension group and timespans feature to split the last set of weeks into days and week numbers to then show client utilisation over that period.

![](https://cdn-images-1.medium.com/max/800/0*3Co1garyusvs1oT6)

Of course we can also take those monetary values assigned to events and use them to calculate the overall revenue contribution for this customer, factoring in costs incurred around preparing sales proposals, answering support questions and investing non-billable days in getting a project over the line.

![](https://cdn-images-1.medium.com/max/800/0*9vPEX014iu3F2-lq)

Our second dbt model takes this event data and pivots each customer’s events into a series of separate columns, one for the first event in sequence, another for the second, another for the third and so on.

![](https://cdn-images-1.medium.com/max/800/0*QTF-JvMRZPutEaRw)

Again, code for this dbt model is available in our [project git repo](https://github.com/rittmananalytics/ra_dw_dbt).

{{  
    config(  
        materialized='table'  
    )  
}}  
WITH event\_type\_seq\_final AS (  
    SELECT  
        customer\_id,  
        user\_session\_id AS event\_type\_seq,  
        event\_type,  
        event\_ts,  
        is\_new\_session  
    FROM  
        (SELECT  
            customer\_id,  
            event\_type,  
            last\_event,  
            event\_ts,  
            is\_new\_session,  
            SUM(is\_new\_session) OVER (ORDER BY customer\_id ASC, event\_ts ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS global\_session\_id,  
            SUM(is\_new\_session) {{ customer\_window\_over('customer\_id', 'event\_ts', 'ASC') }} AS user\_session\_id  
        FROM  
            (SELECT  
                \*,  
                CASE WHEN event\_type != LAG(event\_type, 1) OVER (PARTITION BY customer\_id ORDER BY event\_ts ASC) OR last\_event IS NULL THEN 1  
                ELSE 0  
                END AS is\_new\_session  
            FROM  
                (SELECT  
                    customer\_id,  
                    customer\_name,  
                    CASE WHEN event\_type LIKE '%Email%' THEN 'Presales'  
                    WHEN event\_type LIKE '%Bill%' THEN 'Delivery' ELSE event\_type END AS event\_type,  
                    event\_ts,  
                    LAG(CASE WHEN event\_type LIKE '%Email%' THEN 'Presales' WHEN event\_type LIKE '%Bill%' THEN 'Delivery' ELSE event\_type END, 1) OVER (PARTITION BY customer\_id ORDER BY event\_ts ASC) AS last\_event  
                FROM  
                    {{ ref('customer\_events') }}  
                ORDER BY  
                    customer\_id ASC,  
                    event\_ts ASC,  
                    event\_type ASC  
                ) last  
             ORDER BY  
                customer\_id ASC,  
                event\_ts ASC,  
                event\_type ASC  
           ) final  
        ORDER BY  
            customer\_id ASC,  
            is\_new\_session DESC,  
            event\_ts ASC  
        )  
    WHERE  
        is\_new\_session = 1  
    {{ dbt\_utils.group\_by(n=5) }}  
    ORDER BY  
        customer\_id,  
        user\_session\_id,  
        event\_ts  
)  
SELECT  
    customer\_id,  
    MAX(event\_type\_1) AS event\_type\_1,  
    MAX(event\_type\_2) AS event\_type\_2,  
    MAX(event\_type\_3) AS event\_type\_3,  
    MAX(event\_type\_4) AS event\_type\_4,  
    MAX(event\_type\_5) AS event\_type\_5,  
    MAX(event\_type\_6) AS event\_type\_6,  
    MAX(event\_type\_7) AS event\_type\_7,  
    MAX(event\_type\_8) AS event\_type\_8,  
    MAX(event\_type\_9) AS event\_type\_9,  
    MAX(event\_type\_10) AS event\_type\_10  
FROM  
    (SELECT  
        customer\_id,  
        CASE WHEN event\_type\_seq = 1 THEN event\_type END AS event\_type\_1,  
        CASE WHEN event\_type\_seq = 2 THEN event\_type END AS event\_type\_2,  
        CASE WHEN event\_type\_seq = 3 THEN event\_type END AS event\_type\_3,  
        CASE WHEN event\_type\_seq = 4 THEN event\_type END AS event\_type\_4,  
        CASE WHEN event\_type\_seq = 5 THEN event\_type END AS event\_type\_5,  
        CASE WHEN event\_type\_seq = 6 THEN event\_type END AS event\_type\_6,  
        CASE WHEN event\_type\_seq = 7 THEN event\_type END AS event\_type\_7,  
        CASE WHEN event\_type\_seq = 8 THEN event\_type END AS event\_type\_8,  
        CASE WHEN event\_type\_seq = 9 THEN event\_type END AS event\_type\_9,  
        CASE WHEN event\_type\_seq = 10 THEN event\_type END AS event\_type\_10  
    FROM  
        event\_type\_seq\_final  
    )  
{{ dbt\_utils.group\_by(n=1) }}

Pivoting each customer’s events in this way makes it possible then to visualize the first n events for a given set of customers using Looker’s [Sankey custom visualization](https://looker.com/platform/blocks/viz/sankey), as shown in the final screenshot below.

![](https://cdn-images-1.medium.com/max/800/0*Yi5OQ5p8OEAIkFpt)

More details on our own internal use of Looker, Stitch, dbt and Google BigQuery to create a [modern operational analytics platform](https://rittmananalytics.com/blog/2019/5/7/how-rittman-analytics) can be found in this earlier blog post, and if you’re interested in how we might help you get started with Looker, check out our [Services page](https://rittmananalytics.com/services-index#looker).