Chris Meier
Chris joined Periscope Data to spread his love of all things data. When he isn't sharing the hottest tips for making dashboards magical, you'll find him outside droning and taking photos.

Analyzing Disparate Data with Segment Sources

April 6, 2016

Collecting data across all the ways your customers interact with your product makes valuable analysis possible. The challenge with performing the analysis is finding a way to query data from multiple sources without a messy and complicated process.

Our partner Segment’s new Sources offering allows you to seamlessly plug in data from hubs like Stripe, Salesforce, and Zendesk into your data warehouse. You can even join it with the behavioral data you can collect with Segment already. With all of this data in a single location, getting insights in Periscope is even easier!

Finding Landing Page Value with Segment Sources

Analyzing data from different touchpoints in the customer journey lets you answer important questions about your business, like which landing pages drive the most conversions.

Determining whether our blog pages drive more conversions than our product pages, for example, allows us to decide which section of our site we should improve first.

We’ll use behavioral analytics and Stripe data loaded through Segment Sources to determine which section of the site is most valuable.

When a new Source is added to Segment, a new schema is also created that allows you to link your Sources through your Segment data.

Identifying Customer Value

In our first query we want to get the monthly value of each customer. We can do this by querying the amount attribute from the Stripe charges table. We’ll then get the Segment identifier, anonymous_id, for the Stripe customer by joining the Stripe customer email to the Segment email.

with stripe_revenue as (
    select
      identifies.anonymous_id
      , identifies.email email
      , sum(amount) / 100 customer_value
    from
      stripe.charges
      join stripe.customers on
        charges.customer_id = customers.id
      join segment.identifies on
        identifies.email = customers.email
    group by
      1
      , 2
  )

What Did the Customer See First

Next we’ll want to find the first page a customer has ever visited. To do this we will look through the pages table in our Segment data. We can use a window function to count the row number for each user’s visits when ordered by the date they visited the site.

This will give us a give us an integer value, rn, that will increase by one for every page visited. We can query that result to only get the first page visited by a user, or where rn is equal to 1.

, first_page as (
    select
      *
    from
      ( select
      anonymous_id
      , received_at
      , name
      , row_number()
        over(partition by anonymous_id order by received_at asc)
        as rn
    from
      segment.pages) as page_ordered
    where
      rn = 1
  )

Finding the Most Valuable Pages

Our next step is putting the two sources together! Since we have tied the anonymous_id to the customer’s monthly value and also have identified the customer’s first page visited using the anonymous_id, we can use that to join the two results together.

, pages_with_revenue as (
    select
      name
      , customer_value
    from
      first_page
      left join stripe_revenue on
        first_page.anonymous_id = stripe_revenue.anonymous_id
  )
end

In our final query we’ll take the average value of a customer by the first page they have visited.

select
  name
  , avg(customer_value)
from
  pages_with_revenue
group by
  1
order by
  2 asc

We can quickly throw this into a bar chart to easily visualize the values.

It looks like our blog section drives more conversions, so we’ll focus our work there to start!

Investigating Value of Chat Volume

For our next act of Source-ry we will investigate whether customers on our lower priced payment plans are taking up more support resources than those on higher plans.

During a recent discussion on where the support team spends their time, a manager brought up that her team was seeing more tickets from lower priced plans. We decided to look into this to see if the data supported what the manager was seeing. To get this data, we started by querying the customer’s Stripe data, then their Zendesk data, and joining it all together using Segment.

Querying Customer Data

The relevant data from Stripe is the customer email along with the plan they are are on, so we can write a simple query to get both.

  with
    stripe_customers as (
      select
        customers.email
        , plans.name stripe_plan
      from
        stripe.customers
        join stripe.subscriptions subs on
          customers.id = subs.customer_id
        join stripe.plans on
          subs.plan_id = plans.id
    )

Gathering Zendesk Tickets

Pulling our Zendesk data is next and this is quickly done by looking at our tickets table. It is important that we also select the external_id from the users table as you’ll see in our next step.

   , zendesk_tickets as (
      select
        tickets.id as ticket_id
        , tickets.received_at
        , users.external_id
      from
        zendesk.tickets
        join zendesk.users on
          tickets.requester_id = users.id

    )

Joining it With Segment

With our Zendesk tickets and Stripe customer data we are now ready to join the two together and rank our plans by number of tickets. Through the Segment users table we can easily join the two, and count the ticket volume by week and Stripe plan.

  select
    [zendesk_tickets.received_at:week] as week
    , stripe_plan
    , count(ticket_id) as volume_by_week
  from
    stripe_customers
    join segment.users segment on
      stripe_customers.email = segment.email
    join zendesk_tickets on
      segment.id = zendesk_tickets.external_id
  group by
    1
    , 2

With our data joined and aggregated, we can chart the volume by week and segment it by the associated plan.

It turns out our support manager was right: our lowest tiers, Free and Team, were responsible for most of the support ticket volume.

Tying together your data sources clearly opens the door for more holistic analysis. With Stripe, Salesforce and Zendesk accessible through Segment Sources you can perform similar analysis to fully understand the value at each stage of the customer lifecycle.

Good luck!

Using Segment Sources and want to analyze your data? Our solutions team is ready to help get you started with dashboards like the one below. Just sign up for Periscope and we’ll get you set up!

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?