WITH prep AS ( SELECT a.company, a.domain_userid__c, a.convertedopportunityid, b.name AS accountname, c.name AS servicecontract, CASE WHEN e.name IN ('Managed Service - Batch', 'Snowplow Insights - Batch','Managed Service - Real-Time', 'Snowplow Insights - Real-Time','Snowplow Insights - Enterprise','Support') AND d.poc__c IS NOT TRUE THEN e.name ELSE null END AS recurring_contract, d.poc__c FROM salesforce.sf_lead AS a LEFT JOIN salesforce.sf_account AS b ON a.convertedaccountid = b.id LEFT JOIN salesforce.sf_servicecontract AS c ON c.accountid = b.id LEFT JOIN salesforce.sf_contractlineitem AS d ON c.id = d.servicecontractid LEFT JOIN salesforce.sf_pricebookentry AS e ON d.pricebookentryid = e.id WHERE a.isdeleted IS NOT TRUE AND b.isdeleted IS NOT TRUE AND c.isdeleted IS NOT TRUE AND d.isdeleted IS NOT TRUE AND e.isdeleted IS NOT TRUE AND a.name NOT IN ('Christophe Bogaert','Yali Sassoon', 'Test Test', 'rg fg','C C', 'test', 'Test','snowplow', 'test case','Z Y','fds dsf') GROUP BY 1,2,3,4,5,6,7 ) SELECT --TO_CHAR(DATE_TRUNC('month', page_view_start),'YYYY-MM-DD') AS "month::filter", page_url, COUNT(*) AS page_views, COUNT(DISTINCT user_snowplow_domain_id) unique_users, COUNT(DISTINCT CASE WHEN session_index = 1 THEN user_snowplow_domain_id END) AS new_users, COUNT(DISTINCT CASE WHEN page_view_index = 1 THEN user_snowplow_domain_id END) AS new_users_first_page, COUNT(DISTINCT CASE WHEN domain_userid__c IS NOT NULL THEN user_snowplow_domain_id END) AS new_lead, COUNT(DISTINCT CASE WHEN recurring_contract is not null THEN user_snowplow_domain_id END) AS mrr FROM derived.page_views AS a LEFT JOIN prep AS b ON a.user_snowplow_domain_id = b.domain_userid__c WHERE referer_url NOT IN ('webto.salesforce.com/servlet/servlet.WebToLead','webto.salesforce.com/','www.salesforce.com/servlet/servlet.WebToLead','www.salesforce.com/') GROUP BY 1 ORDER BY 7 DESC
Being able to query across all of this data lets me see which pages are doing the most work for us bringing in new customers. (It’s also important to look at data like this to identify sections of our website that are either performing below expectations or are surprisingly effective at converting visitors.) This table validates that our blog is generating some leads, but the question I want to answer is how effective is that content- is sixth place good for our blog? Or can it do better?
Looking at the URL conversion rates, we can learn a lot about how the blog is performing compared to other parts of our website. Of the 108,845 pageviews on the home page, 862 leads were generated (about .79%) and of those leads, 31 became customers (about 3.59%). On our blog, however: the 16,244 pageviews on our blog generated 203 leads (1.25%), and of those leads 15 became customers, amounting to 7.38% of the leads. Looking at a different metric,
NEW_USERS_FIRST_PAGE, we can see of the page views, how many were new visitors to our site and what their landing page was. The 862 leads generated by the homepage were 1.37% of the 63,044 new, first time users; compare that with the 39% of our blog’s 519 new, first time users and we have evidence to suggest that our content is effective at driving interest and sign ups to our paid product.
Understanding the relationship between content and lead generation is multi-faceted and complex with many different methodologies for joining these two together. How you connect this data becomes a component of your attribution model (we’ll look at attribution modeling more in a future post). With a working version of an attribution model, we can use that to compare the number of leads that are attributed to a blog post we’re generating on a weekly basis with the overall total number of leads to get an idea of our blog’s impact, called lead influence percentage.
This graph shows that as our volume of leads increases, the blog influence percentage decreases, a conclusion that seems counterintuitive to the conclusion about our blog’s conversion rate. Our next step is to closely analyze our leads from the past two months, as well as going forward, to understand the impact our content has, identify the positive effects, and eventually scale them.
When I first started working for Snowplow, we came up with a hypothesis: if our blog provides education, resources, and thought leadership around digital analytics, event-data modeling, and the business benefits of a sophisticated data stack and culture, then our content will drive traffic and leads. If we’re successful along these vectors, we expect to see the several metrics move including percentage of traffic from external sources and social referrals, increases in newsletter subscriptions, and increases in overall site traffic over a longer time horizon.
One way we benchmark if this is to determine how evergreen our content is, or how well it retains its value. A post on basic analyses any product analyst can use provides immediate value, and will still be relevant to readers looking to learn more about product analytics six months after publishing. Contrast this with a technical release post which sees almost no readership after the next release, or a post following a major event company event or conference which decreases exponentially.
This is our viewership on a blog post after a company event. As expected, it shows a big spike in traffic after publication followed by a sharp decrease. Over time, it’s received a modest amount of views, but with most of them coming from the initial post date, it’s unlikely we’ll see traffic to this post increase much going forward. Evergreen content covers topics that will frequently be top of mind or are essential to digital analytics professions, attracting new readers over time. The blog post mentioned on product analytics graphs much closer to what we want our blogs to look like:
The viewership on our product analytics piece suggests we’re moving in the right direction, but there are still a lot of unanswered questions. With these performance benchmarks gathered over the previous months, we can compare to the next three months and have a much better understanding of what content is the most useful and appreciated by our audience.
Make sure you subscribe to our newsletter if you want to learn more about how you can use Snowplow data for your marketing analytics and let us know in the comments below if you think our blog is providing a resource.