David Ganzhorn
Ganz joined Periscope Data from Google, where he ran dozens of A/B tests on the search results page, generating billions in new revenue. At Periscope Data he hacks on all levels of the stack.

The High-Performance SQL Blog

Analyzing Our Long Term Blog Metrics With SQL

April 15, 2015

Our Long Term Blog Performance

At Periscope, our blog is a key channel for bringing in leads, getting our name out in front of data analysts, and positioning ourselves as experts on SQL.

We had a good idea of the short term performance of our posts, but wanted to better understand their long term performance. So we thought through some simple metrics, wrote our SQL, and created the charts.

Below we jump into a couple of the metrics and queries that we use to determine long term post performance.

Total Short Term vs. Long Term Traffic

One indicator of how well our posts are doing is the amount of traffic that comes in after we stopped each post’s initial promotion. The chart we want to see is traffic from the initial promotion period compared against traffic that arrived after.

We’ll define short term traffic as the first thirty days of traffic and long term traffic as all subsequent traffic.

Across all posts, we see a shift to a majority of long term traffic. Our old posts are still being read!

Let’s dig into the corresponding SQL. We start with our two tables, posts and pageviews. The posts table stores each post’s publication date:

The pageviews table contains each post and the users, identified by cookies, who viewed that blog post on a given date:

We want to measure more than pageviews, so we define a visitors table. We’ll attribute a new reader to the first post they read.

To get the first pageview for each user we’ll use a window function to number each user’s pageviews in order, and select only their first visit. We cover this technique and alternatives in 4 Ways to Join Only The First Row in SQL.

create table visitors as
  select * from (
    select 
      *
      row_number() over (partition by cookie order by created_at)
    from pageviews) ordered_pageviews
  where row_number = 1

Combining the visitors and posts table, we create the above chart by splitting visitors into short term and long term buckets based on when they read the post.

select 
  date_trunc('week', visitors.created_at) as week,
  case when (visitors.created_at - posts.created_at) 
             < interval '30 days' 
       then 'Short Term' 
       else 'Long Term' end as period,
  count(1) as visitor_count
from visitors 
join posts using (post)
group by 1,2

This yields:

Do Individual Posts Keep Growing?

We now know that the combined long term traffic for all our blog posts is rising. We don’t know, though, if this is because every post continues to do well after the first 30 days or if there are a couple dominant posts that mask the other posts’ performance.

Let’s check out the long term traffic on a per post basis. We’ll also filter out the short term traffic to make the long term trends clearer.

The posts are aging well and drawing more traffic over time! The two highest performing posts in the long term were Use Subqueries to Count Distinct 50X Faster and 4 Ways to Join Only The First Row in SQL.

This chart’s SQL is similar to the previous query. Instead of grouping by short and long term time periods, we’ll group by title. We’ll also exclude any short term data with the where clause.

select
  date_trunc('week', visitors.created_at) as week,
  posts.title,
  count(1) as readers
from visitors 
join posts using (post)
where 
  (visitors.created_at - posts.created_at) >= interval '30 days'
group by 1,2

Which gives us:

Does Long Term Traffic Drive More Conversions?

Increasing traffic is great, but that traffic doesn’t help much if it doesn’t translate into more conversions. For this analysis we define a conversion as signing up for a trial account.

We can plot a chart similar to the chart above that compares short term and long term traffic, but further restrict the data to visitors that also sign up.

We received 60% more signups from long term traffic than short term traffic!

The query for this chart is the same as our first query with two small changes: we join to our signups table via pageview cookies, and count signups instead of pageviews. We distinct on cookie because some very excited readers signup multiple times, which we don’t want to count as multiple conversions.

select
  date_trunc('week', visitors.created_at) as week
  case when (visitors.created_at - posts.created_at) 
             <= interval '30 days' 
       then 'Short Term' 
       else 'Long Term' end as period,
  count(distinct signups.cookie) as signups
from visitors 
join posts using (post)
join signups using (cookie)
group by 1,2

The results look like:

Conversion Rate of Long Term Traffic

For our last metric, we wanted to see how our traffic and conversion rate varies per post between the short and long term. To illustrate this, we made the bubble chart below where bubble size is the total number of signups.

Lots of big blue bubbles! All of our highest converting posts are in their long-term phase.

One datapoint that stands out is the How To Calculate Cohort Retention in SQL post, which drove 5 conversions at a 1.0% conversion rate in the first thirty days, and 38 conversions at a 3.1% signup rate since.

The SQL for this bubble chart needs to return an x-axis (signup_rate), a y-axis (visits), a bubble size (signups), and a series name (period).

select 
  count(distinct signups.cookie) / count(1)::float as signup_rate,
  count(1) as visits,
  count(distinct beta_signups.id) as signups,
  case when (visitors.created_at - posts.created_at) 
           < interval '30 days' 
     then 'Short Term' 
     else 'Long Term' end as period,
from visitors 
join posts using (post)
join signups using (cookie)
group by 2,3

The output is:

Go Forth and Measure!

We hope you enjoyed these examples of how performance differs between the short and long term for our posts. We also hope that you try these metrics on your own data and find as many surprises as we did.

If you’d like to create charts like these for your own data, try Periscope.


Want to discuss this article? Join the Periscope Data Community!
Tags: 
Haven't tried Periscope Data yet?
Start a trial and we’ll send you one of our famous coffee mugs.
Read More
Haven’t tried Periscope Data yet?

Subscribe to our Newsletter