Analyzing Disparate Data with Segment Sources
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 (
, identifies.email email
, sum(amount) / 100 customer_value
join stripe.customers on
charges.customer_id = customers.id
join segment.identifies on
identifies.email = customers.email
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 (
over(partition by anonymous_id order by received_at asc)
segment.pages) as page_ordered
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 (
left join stripe_revenue on
first_page.anonymous_id = stripe_revenue.anonymous_id
In our final query we’ll take the average value of a customer by the first page they have visited.
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.
stripe_customers as (
, plans.name stripe_plan
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 (
tickets.id as ticket_id
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.
[zendesk_tickets.received_at:week] as week
, count(ticket_id) as volume_by_week
join segment.users segment on
stripe_customers.email = segment.email
join zendesk_tickets on
segment.id = zendesk_tickets.external_id
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.
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!