Tracking Daily Unique Visitors to Recently Published Blog Posts with Looker, Fivetran, Mixpanel, and BigQuery

If you work at a company that publishes a lot of content, it’s important to understand how well that content is performing. Not just in terms of page views and unique visitors, but whether it converts visitors into trials, etc.

At Help Scout we have a Looker dashboard to help us track all of these things and more. In this post, I’ll walk you through how we track the daily unique visitors to our recently published blog posts. For example, on May 2nd we published April’s Release Notes – how many people viewed that post on the day it was published? How about the day after? And how does that compare to our other recently published content?

Overview

Big picture, we fire a Viewed Page Mixpanel event on every marketing page. We then use Fivetran to get that event data into BigQuery, where we analyze it in Looker. You can read more about the setup here: Tracking What Pages Your Visitors View Prior to Signing Up Using Mixpanel, Fivetran, BigQuery, and Looker.

Querying for Recently Published Posts

With this data in hand, we need to figure out a way to determine what the recent blog posts were so that we can limit our analysis to them.

Here’s the query we use:

For Help Scout blog URLs (ie, URLs that begin with https://www.helpscout.net/blog/), we need to determine when the post was published. That’s the same as the first day it was viewed. However, because we launched Mixpanel page view tracking on April 3rd, this would make it look like every one of our posts was published on April 3rd or sometime after that. That’s why we limit the results to April 4th or later. Also, we want to limit it to posts that received at least a certain number of visitors that first day, otherwise the results will wind up including low traffic posts that were first viewed after April 4th.

This query gets us a list of those recently published posts:

Modeling the Data in Looker

Over in Looker, we’re going to create a derived table with these results so that we can determine whether a given Mixpanel event URL is a recently published blog post:

The reason we have the is_new_blog_content dimension here is because we’re going to LEFT JOIN all Mixpanel events on this derived table by the URL. Not all URLs will have a match in this table, so this dimension will let us limit the analysis to just events that were a recently published blog post.

Here’s how we model the relationship between our main Mixpanel events model and this derived table:

One other key piece of this is that we model how to calculate unique visitors in the main Mixpanel events view:

Creating the Chart in Looker

With these foundations in place, we can then create the chart we set out to.

We want to use that Is New Blog Content dimension to limit the results to recently published posts, then pivot the daily unique visitor count on the URL:

looker-recent-posts-explore.png

Then it’s just a matter of setting up the chart in Looker and voila, there we have it:

Screen Shot 2018-05-11 at 1.51.34 PM.png

Going forward, without anyone having to log into Google Analyics, we’ll be able to track the popularity of our new blog posts and track the trends over time.

By the way, that spike on April 19th is from our CEO’s Beacon 2.0 Preview: The User Interface post, all about Help Scout’s soon-to-be-released live chat & improved self service tool. If you’re interested in getting notified when it launches, you can sign up here.

Happy querying!

Analyzing a Conversion Funnel in BigQuery Using Fivetran Powered Mixpanel Data

mixpanel-funnels_fqmumg.png

In a recent post I outlined how to use Fivetran to sync Mixpanel data to BigQuery for analysis in Looker. Today we’ll walk through how to write a SQL query to analyze a funnel using the Mixpanel data in BigQuery.

For this analysis, we’re going to create a three-step funnel showing how many visitors who start on Help Scout’s pricing page click through to the sign up page and then sign up.

One Step

To begin, lets just look at visitors who viewed the pricing page:

You might wonder why we need the % at the end of the URL; that’s simply to make sure the results include pages with URL parameters such as https://www.helpscout.net/pricing/?utm_source=adwords.

Two Steps

Next, we’ll join these results on sign up page data, making sure that the sign up page views occurred after the pricing page views:

The reason we LEFT JOIN is because not all visitors will make it to the next step of the funnel and we want the data to reflect that.

Note that we join on both the distinct_id (to ensure each result is for a single visitor) and on the time the events occurred.

Three Steps

Extending this to the third and final step of the funnel, the Signed Up event, we get:

Determining the First Event Occurrence for Each Step

The query above will return every combination of pricing page views, sign up page views, and sign up events for each visitor. For our funnel though, we don’t care whether they loaded the pricing page or sign up page multiple times, we only care that they did it at all. So we modify the query to only return the first instance of each event for each visitor:

Measuring the Funnel

Finally, we count how many visitors made it to each step to get our funnel:

Voila!

From here, you can take it a step further and segment the results on a property in the first step of the funnel or add a funnel duration to limit how long visitors have to complete the funnel, etc.

If you have any questions about this, don’t hesitate to reach out.

Tracking What Pages Your Visitors View Prior to Signing Up Using Mixpanel, Fivetran, BigQuery, and Looker

setup.png

One of the things we’ve been analyzing at Help Scout recently is what paths individual companies take before signing up for a trial. For example, looking at a company that signed up last month, did they start their journey with Help Scout on our homepage? Or did they find us via our blog? Or one of our marketing landing pages? And once we know that, what other pages did they visit before signing up? Did that company wind up becoming a customer? How much did we make from them?

This post is about how we’ve wrangled the data using Mixpanel, Fivetran, BigQuery, and Looker to help us answer these questions.

Big picture, we’re tracking page view and sign up events in Mixpanel, syncing that data to BigQuery using Fivetran, then tying it to our internal company data in Looker for easy analysis.

Step 1: Tracking Page Views and Sign Ups in Mixpanel

If you’ve used an event-based analytics service, this step will be pretty straightforward. We load the Mixpanel script on every page, then fire a Viewed Page event:

And then if the company signs up for a trial, we fire a Signed Up event with that company’s id as a property:

These are the only two Mixpanel events we track. If we wanted to track actions in-app, we could also fire custom events for those, but we don’t at Help Scout because we tie this Mixpanel data together with our internal data about what companies have done in-app, eliminating the need for additional Mixpanel events.

Here’s what the Viewed Page event looks like in Mixpanel’s Live View:

Screen Shot 2018-04-06 at 1.42.07 PM.png

Also as we’ll see later, Mixpanel automatically tracks a lot of details about the visitor: things like the browser, country, URL, referrer, OS, etc, all of which we can use use in our analyses.

Step 2: Syncing Mixpanel data to BigQuery with Fivetran

Fivetran is this amazing service that specializes in helping you centralize all of your data in a data warehouse. For example, we have Fivetran connectors set up for MySQL (which we use internally at Help Scout), Salesforce, HubSpot, Google Sheets, and now Mixpanel:

Screen Shot 2018-04-06 at 1.46.42 PM.png

Screen Shot 2018-04-06 at 1.51.58 PM.png

Taking Mixpanel as an example, we provide Fivetran out Mixpanel API credentials, then Fivetran queries Mixpanel’s API periodically, cleans up the results, and throws it all in BigQuery:

Screen Shot 2018-04-06 at 1.50.21 PM.png

This lets us analyze our Mixpanel data just like we would other SQL data:

We can also access the custom event properties that we’re tracking for the Signed Up event using Standard SQL’s json_extract_scalar function:

Once we have the company id, it’s just a matter of querying for a specific company id to view their event history:

Step 3: Modeling the data in Looker

If you have access to a Business Intelligence tool like Looker, you can model this Mixpanel data and how to join it with your other data.

First, create a view to model the Mixpanel event data:

Then create a view with a derived table to model the relationship between a distinct_id in Mixpanel with a company id:

Finally, connect those two views and any others you want to be able to analyze together:

Step 4: Analyzing the data in Looker

Once you’ve modeled the data, you can analyze the event history for specific companies, track trends like page views and unique visitors, and more:

Screen Shot 2018-04-06 at 2.28.57 PM.png

In future posts I’ll walk through some of the other interesting analyses you can perform with this data.

If you have any questions about this setup, don’t hesitate to reach out.

Prioritization for Data Analysts

 

next-looker.png

For the past two years I’ve worked as a data scientist, first on the marketing team for WordPress.com and now on the growth team at Help Scout. In both of these roles I’ve been the sole data analyst on my team so I tend to have more work to do than I have time for. As a result, I spend a lot of time thinking about how to prioritize what’s on my plate. My goal with this post is to share some of what I’ve learned to help other data folks who are in similar roles.

The prioritization problem

Here’s a made up scenario:

Your manager asks you for help with a medium priority analysis that will take several days. Shortly after you begin working on it, a coworker pings you for help with an urgent request that will only take a few hours. A little while later, your boss’s boss asks you for help with a low priority one-day project.

You now have three requests from people with differing seniority each with a different duration and urgency – which do you work on next and why?

Ad hoc requests vs projects

At a high level, there are two types of data requests:

Ad hoc requests are questions that can be answered fairly quickly. They may take a few minutes or a few hours and the urgency can range from “drop what you’re doing” to “no rush at all”.

Projects take longer and usually have a higher impact on the business. For me, they range from a few days to (rarely) a few weeks.

While it may be tempting to only want to work on ad hoc requests or only on projects, the reality is that they’re both important and if you’re the only analyst, you’ll need to work them both into your schedule. For example, even if you’re working on a big project, you can’t simply ignore the urgent ad hoc requests that come up during the course of a week. Telling your CEO that you’ll get to his request in three weeks when you’re done with your project is not recommended.

Therefore my advice is to set aside about two hours each day to work on ad hoc data requests and spend the rest of your time working on your highest priority project. This will give you time to work on those longer, high impact initiatives but still give you time to work on the shorter requests as well.

I wouldn’t loop your manager into the prioritization of ad hoc data requests; he or she probably has better things to do than helping you decide “spend 30 minutes on this then spend an hour on that” etc etc. Use your judgment: work on whatever is the most urgent or will have the highest impact on the business. When in doubt, base it on the seniority of whoever is asking.

For projects, I definitely would recommend getting your manager’s help prioritizing what to work on. He or she help you decide where you can be the most impactful and when questions come up about why you’re not working on some other thing, it’s not just you who made that call.

I would only work on one project at a time. I’ve tried doing like 3 hours a day on one project, 3 hours a day on another, but all that’s going to do is make both take twice as long if not longer due to the frequent context switching.

When you meet with your manager, keep him or her up to date about where you’re at with your project. Often it’s hard to predict at the start how long a project will take. Something you thought might take a week will take a day. Something you thought would take three days will take two weeks. That’s just the nature of it. Keeping your lead informed.

Sometimes priorities will change. Your expected three-day project that is now in its second week may get put on the back burner in order to work on something else. It’s easy to get frustrated when this happens, but remember that you’re there to have the biggest impact on the business, not to finish projects just for the sake of finishing them. Sometimes what’s highest priority will change so be flexible.

When priorities change and you have to change projects midway, try to estimate what impact that will have on the completion of the original project. If you estimated you would complete your original project by the end of the week, but by switching projects it will now take an additional week, communicate that to the people waiting on the result of the original analysis.

Get clarity before beginning your analysis

Regardless of whether a request is ad hoc or a project, I’d recommending asking the requestor several questions:

  • How important is this?
  • Is there a deadline?
  • How accurate do you need the answer to be?
  • What type of end result are you looking for?

If you don’t ask about importance, you risk spending a lot of time on an analysis that is not that important to the person asking about it. It’s common for someone to ask a quick hey-I-wonder-about-this question, but they only care if you can find the answer quickly.

Similarly, ask whether it needs to be done by a specific date. If the answer is holding something up, it should get higher priority than something that doesn’t.

Asking about accuracy is something that took me a while to appreciate too. I’ve run into a lot of data requests that I can give a 95% accurate solution to fairly quickly, but that would take much longer to get to 100%. A lot of times, people will be fine with a 95% quick solution. Understanding when 95% is acceptable and when 100% is necessary can save you a lot of time.

Sometimes I also ask what type of end result the person is interested in. It often helps them and you clarify what the analysis is all about.

For longer projects, I’d also recommend periodically updating the person who requested it with your progress. As they see your progress, they might want to refine the request or may be happy with the results as-is, freeing you up to work on something else.

Staying organized

I’ve seen some data teams ask people to fill out a little template (usually in a Trello card) to help people ask good data questions. I prefer to discuss the details with the person, then create a Trello card for myself with the relevant information.

I would recommend keeping a list of all potential projects and ad hoc data requests. That is, don’t just try to keep it all in your head. I do this in Trello with four lists:

  • Multi-week Projects
  • Multi-day Projects
  • Multi-hour Projects
  • Quick Requests

Whenever someone asks a question, I get the details from them then create a new card and add it to the appropriate list.

You could also have a Completed list where you drag cards as you complete them to keep a record of it. I’ve also seen some teams have a list for each month or quarter (January 2018, February 2018, etc) and drag completed cards to the relevant list when it’s complete. I did that in the past, but tend to just archive the cards now to avoid cluttering up the Trello board.

Invest in a proper BI tool like Looker

One other big win has been switching from manual data analysis (using MySQL queries and R) to Looker, the premier Business Intelligence (BI) tool on the market today. I could go on for hours about how amazing Looker is, but at a high level it lets you:

  • Create dashboards that are automatically updated as the underlying data changes.
  • Use LookML, Looker’s modeling language, to teach Looker how our data fits together. With that in place, I rarely have to write queries by hand anymore. I can use Looker’s interface to quickly ask and answer questions.
  • This also means that other people at the company who may not have querying skills can answer their own questions without asking me every time.

I’d highly recommend checking out Looker if you still find yourself wrangling data a slow manual way.

If this post resonates with you, I’d love to connect

If you’re a data analyst, especially if you’re the only one at your company or part of a small team, I’d love to chat to learn more about what you’re working on, how you prioritize, etc. Drop me a note: matthew.h.mazur@gmail.com. Cheers!

Help Scout’s New Partnership with Fivetran

Today at Help Scout we’re excited to announce that we’re partnering with Fivetran, a fantastic service that makes it easy to centralize all of your data in a data warehouse. You can read my blog post about the partnership here: Performing Advanced Analytics on Your Help Scout Data with Fivetran.

The short version is that if you’re a Help Scout customer, you can sign up for Fivetran (for free!) and use it to get all of your mailbox data out of Help Scout and into a data warehouse like BigQuery. From there you can use SQL to analyze the data (on its own or combining it with your data from other services) or hook it up to a Business Intelligence tool for easy analysis and charting.

If this sounds interesting to you, check out the blog post and don’t hesitate to reach out if you have any questions about any of it: matt@helpscout.com.

HelpU, a new customer service education platform by Help Scout, is now live! 🚀

Screen Shot 2017-10-13 at 2.00.12 PM.png

My teammates at Help Scout have been hard at work on a new resource called HelpU to help you and your team learn how to deliver worldclass customer service. Whether you’re a member of a large support team (hi Automattic friends!) or handle support by yourself for a small side project (hi Microconf friends!), you’ll find a ton of practical guidance in HelpU to make you even better at wowing your customers.

You’ll be able to learn how to write effective knowledge base articles, how architecture your knowledge base, how to leverage data to make you and your customers more successful, how to save time with saved replieshow to foster a customer-focused company culture, and a whole lot more.

And even if you’re not involved in customer service at all, you should visit anyway just to marvel at its beautiful design 😍.

Check it out: HelpU: Customer Service and Education by Help Scout

A New Adventure

Just a note for those of you who follow me online that after almost four years at Automattic, I’m moving on to try something new.

Automattic is an amazing company and I’m incredibly grateful for having had the chance to work there with such a talented group of people.

As for what’s next: later this month I’ll be joining Help Scout to work as a data scientist on their growth team. I’m really excited by the opportunity and hope to share a lot of what I learn on this blog. More to follow!