Visitor Acquisition, Retention, and Attrition, Using BigQuery and a Simple Data Model

We notice there’s a general decline in retention over the two years. We also notice that the retention is higher at the beginning of the year, both years. Is it the result of better marketing efforts at the beginning of each year? Let’s look deeper and from a different perspective.

Higher Retention for January Visitors

With bounced sessions now removed from our data, the remaining non-bounce user segment is showing markedly higher retention rates for user who first visit in January, which could have arisen from two basic factors (assuming that user experience remained the same through the year):

• Retention rate for all traffic sources fell after the beginning of the year.
• We were driving a greater percentage of good (e.g., high-retention) traffic earlier in the year.

Let’s continue our exploratory data analysis and run a few more queries to shed some additional light.

Step 4: Another Perspective: Source/Medium

Since we are calculating retention and attrition based on the acquired visitors, it makes sense to look at it with respect to another dimension, say, the traffic source.This can tell us which channels bring visitors that we are more likely to stay. Conversely, it may point us to the channels that cause the biggest attrition.

The following query will count the acquired, retained and lost visitors by source and medium, excluding traffic sources that generated fewer than 100 visitors acquisitions during the time period selected. Note that 100 visitors is another number that varies greatly from one site to another.

Listing 4: Query to calculate acquired, retained and lost visitors by source and medium.

SELECT
  sourceMedium,
  COUNT(fullVisitorId) AS newVisitors,
  SUM(lostVisitor) AS lostVisitors,
  COUNT(fullVisitorId)-SUM(lostVisitor) AS retainedVisitors
FROM (
  SELECT
    fullVisitorId,
    firstMonth,
    sourceMedium,
    if ((secondMonth IS NULL AND thirdMonth IS NULL),1, 0) AS lostVisitor,
  FROM (
    SELECT
      fullVisitorId,
      firstMonth,
      sourceMedium,
      MAX(secondMonth) AS secondMonth,
      MAX(thirdMonth) AS thirdMonth
    FROM (
      SELECT
        f.fullVisitorId AS fullVisitorId,
        f.firstMonth AS firstMonth,
        f.sourceMedium AS sourceMedium,
        sv.firstMonth AS secondVisitMonth,
        s.secondMonth AS secondMonth,
        t.thirdMonth AS thirdMonth
      FROM (
        SELECT
          fullVisitorId,
          sourceMedium,
          calcMonth AS firstMonth
        FROM
          [XXX_VIEW_ID_YYY.All_Visitors]
        WHERE
          visitNumber = 1) AS f
      JOIN (
        SELECT
          fullVisitorId,
          calcMonth AS firstMonth
        FROM
          [XXX_VIEW_ID_YYY.All_Visitors]
        WHERE
          visitNumber = 2) AS sv
      ON
        f.fullVisitorId = sv.fullVisitorId
        AND f.firstMonth = sv.firstMonth
      LEFT JOIN (
        SELECT
          fullVisitorId,
          calcMonth-1 AS secondMonth
        FROM
          [XXX_VIEW_ID_YYY.All_Visitors]
        WHERE
          visitNumber <> 1) AS s
      ON
        f.fullVisitorId = s.fullVisitorId
        AND f.firstMonth = s.secondMonth
      LEFT JOIN (
        SELECT
          fullVisitorId,
          calcMonth-2 AS thirdMonth
        FROM
          [XXX_VIEW_ID_YYY.All_Visitors]
        WHERE
          visitNumber <> 1) AS t
      ON
        f.fullVisitorId = t.fullVisitorId
        AND f.firstMonth = t.thirdMonth)
    GROUP BY
      fullVisitorId,
      firstMonth,
      sourceMedium))
GROUP BY
  sourceMedium
HAVING
  newVisitors > 100

As done previously, let’s export the query result to a Google sheet, add two columns to count the percentage of lost and retained visitors, and sort by percentage retained. The result tells us which traffic sources are driving the greatest retention percentages.

The resulting table appears at the beginning of this post.

Now that we can see attrition/retention performance by traffic source/medium, let’s isolate the performance of that source/medium over time.

Step 5: Looking Deeper at One Source/Medium

We can look deeper by filtering by one traffic source/medium, say twitter/social, and then evaluate its month to month retention trend.

Listing 5: Querying retention month by month only for visitors acquired by our twitter/social campaigns

SELECT
  firstMonth,
  COUNT(fullVisitorId) AS newVisitors,
  SUM(lostVisitor) AS lostVisitors,
  COUNT(fullVisitorId)-SUM(lostVisitor) as retainedVisitors
FROM (
  SELECT
    fullVisitorId,
    firstMonth,
    if ((secondMonth IS NULL
        AND thirdMonth IS NULL), 1, 0) AS lostVisitor,
  FROM (
    SELECT
      fullVisitorId,
      firstMonth,
      MAX(secondMonth) AS secondMonth,
      MAX(thirdMonth) AS thirdMonth
    FROM (
      SELECT
        f.fullVisitorId AS fullVisitorId,
        f.firstMonth AS firstMonth,
       sv.firstMonth AS secondVisitMonth,
        s.secondMonth AS secondMonth,
        t.thirdMonth AS thirdMonth
      FROM (
        SELECT
          fullVisitorId,
          calcMonth AS firstMonth
        FROM
          [8839142.All_Visitors]
        WHERE
        sourceMedium = 'twitter/social' and
          visitNumber = 1) AS f
      JOIN (
        SELECT
          fullVisitorId,
          calcMonth AS firstMonth
        FROM
          [8839142.All_Visitors]
        WHERE
          visitNumber = 2) AS sv
      ON
        f.fullVisitorId = sv.fullVisitorId
        AND f.firstMonth = sv.firstMonth
      LEFT JOIN (
        SELECT
          fullVisitorId,
          calcMonth-1 AS secondMonth
        FROM
          [8839142.All_Visitors]
        WHERE
          visitNumber <> 1) AS s
      ON
        f.fullVisitorId = s.fullVisitorId
        AND f.firstMonth = s.secondMonth
      LEFT JOIN (
        SELECT
          fullVisitorId,
          calcMonth-2 AS thirdMonth
        FROM
          [8839142.All_Visitors]
        WHERE
          visitNumber <> 1) AS t
      ON
        f.fullVisitorId = t.fullVisitorId
        AND f.firstMonth = t.thirdMonth)
    GROUP BY
      fullVisitorId,
      firstMonth))
GROUP BY
  firstmonth
ORDER BY
  firstMonth

The query results indicate consistent performance over time for twitter/social traffic. The stronger overall retention performance (for all traffic) in January was likely due in part to a greater percentage of Twitter traffic in that month (rows 1 and 13).