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 is now 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 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 revenue 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.
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 Sisense for Cloud Data Teams and we’ll get you set up!