Nupur Bhade Vilas on October 20th 2021
Derek Steer on November 19th 2014
We welcome Derek Steer, CEO of Mode, to the Segment blog! Mode is a SQL integration partner and has created these open source queries so you can start learning ASAP. The best part? Their playbooks are tailor-made for the Segment SQL schema.
You’re planning ahead to 2015. “Be more data-driven” is on your company to-do list.
You want to give your team access to better information–and help them make more informed decisions. Questions like “how many invites did we see yesterday?” and “how are customers using the invite feature?” are popping up right and left. Maybe you’re hearing things like “what marketing channels lead to the highest lifetime value customers?” when six months ago this type of question sounded simpler, like “how many daily sign ups are we getting?”
It turns out a lot of these insights are hiding in the raw event data that your product is already generating, quietly in the background.
Thanks to Segment, you can track these analytics events and flip on Amazon Redshift to start digging into these questions.
As we started working with our own Segment data—and helping some customers beta testing the product—we found that many of us had similar questions about how people use our products.
To put all these questions in one place, we started writing SQL queries that could be tailored to anyone’s Segment data schema. These open source queries—we call them the Mode Playbook—can help you find Retention and User Behavior insights in your data from the moment you connect your database to Mode.
Let’s look at an imaginary messaging app as an example.
Your Product Managers are talking about how to increase sent messages but no one seems to have a specific understanding of what users do before they send a message. With the User Path report, you can help them visualize the paths the users take before sending a message.
This report, like every other Playbook report, uses a common table expression. Think of a common table expression like a temporary table that a subsequent query can reference. Modify the common table expression to reference your schema and run it. The next step is to share it with coworkers and start talking about what the data means for upcoming decisions.
The magic of this common table expression and the standardized Segment event stream is that this report will work whether you’re analyzing messages sent, photos shared, orders placed—anything. It can also work with mobile apps: Replace the
pages table with the
screens table, and the
properties.title column with the
If you’d like to use more explicit events other than page or screen views, you can combine Segment events together into a single table using the
UNION function in Redshift. As long as the events table defined here has a
user_id column, and
event_name column, and an
occurred_at column, the rest of the report just works.
You can also explore other aspects of retention and user behavior in the seven other Playbooks.
You don’t always need a fancy interactive chart to answer someone’s question—and dashboards often lead to requests for underlying data. So, we advocate starting with simple queries that get to the heart of your questions. Let’s say the Product Manager in the example above is curious how message volume changed since a feature launch. It just takes a quick query to find out:
Create a simple line chart and share the report out with stakeholders. They’ll be able to refresh the results any time.
We developed the Playbook to get you and your company on the fast track to being more data-driven, inspiring deeper exploration of your data with SQL. It’s easy to get started with SQL, especially if you’re familiar with Excel. We created a free, interactive tutorial called SQL School, so you can dive right into learning SQL or brush up your skills.
To learn more about how we seamlessly integrate with Segment SQL and have helped customers like Munchery get up-and-running quickly, mosey on over to Mode.
Harry Glaser on November 17th 2014
We welcome Harry Glaser, CEO of our SQL partner Periscope, to the Segment blog! Harry’s talking cross-database joins on the heels of our Amazon Redshiftlaunch. If you want to analyze behavioral data across platforms, and Excel won’t cut it, here are some tips to level-up your analytics game.
It began with the best of intentions: You launched your first web app for your customers, backed by a database full of transactional data to analyze. In time you added a read replica, and replaced Excel with an more-advanced visualization tool to go with it.
Now you’re launching your first mobile app. You want SQL access to the underlying data store, but building a server to receive pings is much too difficult. So you make great use of a fabulous event-tracking to SQL solution.
But now your data is in two places. What if you want to know whether your iOS users are big spenders? You’d need to slice monthly iOS users in your mobile app database by payment plan information in your web app database. Luckily, there is a solution: cross-database joins.
You need to connect your transactional, web, and mobile in one table. To start, Let’s count our iPhone MAUs (monthy active users), with Segment’s SQL schema as an example.
We’re counting a user as active in a given month if they’ve started a session in that month. This query gives us a graph like this:
Now, we just need to bring our payment plans into the chart. This is where the magic happens. We’ll join in the
users table on our web database, and slice the query by
Note that we now need to fully qualify the tables in the
JOIN clauses with their database names:
And our hard work pays off! Here we can see our iPhone MAUs sliced by payment plan:
Cross-database joins, and in the case of Periscope – our query speeds, are enabled by our Postgres-based data cache. Each customer’s data is stored in the same database, with one schema per (database, schema) pair. This architecture allows us to run exactly the query you wrote, with some simple rewrites to make it valid.
Here’s the rewritten query:
In this example, your
iphone_production schema is translated to the
db_1234_iphone_production schema in Periscope’s data cache. And
public schema is translated to the
db_1235_public schema. The rest of the query remains the same!
We hope you enjoyed this lesson in cross-data base joins. If you have any thoughts or questions, reach out to us on Twitter @PeriscopeData or hit up firstname.lastname@example.org. We’d love to discuss.
Diana Smith on September 5th 2014
Last week, we announced new support for popular ecommerce platforms including Goodsie, a stylish store builder with neat customers like Church of Merch, The Boomcase, and Strolby. We sat down with Jonathan Marcus, founder and CEO of Goodsie, and Jeremy Mora, founder and CEO of Church of Merch, to learn more about how merchants are using Segment on the Goodsie platform.
Segment: Let’s start with the basics. What is Goodsie?
Jonathan: Goodsie provides modern ecommerce software for small and medium size retailers. Our real-time design system is drag-and-drop, modular and can be easily customized by people of all skill levels. We enable anyone to build an Internet Retailer Top 500 level ecommerce presence without the technical overhead associated with other ecommerce platforms. We also have a multi-store white label ecommerce platform for companies like Church of Merch who host multiple stores for different customers.
Segment: And that brings us to Church of Merch. Could you tell us about your business?
Jeremy: We provide merchandise services (product production, fulfillment, ecommerce and tour support) to many of the world’s leading EDM artists like Dada Life, Zedd, Krewella and lots of other popular musicians such as Beck and Phoenix. We use Goodsie as a platform to build all of our stores.
Segment: That’s awesome! We’ve got a lot of fans of those bands around here, so we’re amped y’all are using Segment. Jonathan, how does Segment work on the Goodsie platform?
Jonathan: Segment provides our customers with access to a vast array of the best-in-class third-party analytics and marketing services like Olark (live chat), Optimizely (A/B testing), Mixpanel (real-time analytics), and 100+ others. And, they do this without requiring any technical integration, which tightly aligns with our code-free product positioning.
Segment: That’s a good point! Our ecommerce plugins make Segment available to store owners with zero code for the first time. Jeremy, what are the benefits of code-free integration in your opinion?
Jeremy: We are currently using Segment to connect with CrazyEgg for heat-mapping and Olark for live chat. Soon, we would like to add Mixpanel for real-time analytics and Optimizely for A/B testing. Segment allows us to turn all of these services on instantly and without any technical work! Having on-demand, technical free access to all of these best-in-class services allows us to become more sophisticated with analytics, optimization and marketing, which translates into more sales and a smarter process for the musicians we work with.
Diana Smith on September 2nd 2014
Last week we announced new ecommerce plugins for online merchants to integrate analytics and marketing tools with zero code. Here’s a conversation with our platform partner at WooCommerce, Joel Bronkowski. He’s given us the inside scoop on WooCommerce, the Segment plugin, and why WooThemes follows the model build, measure, learn. Read on to learn more!
Segment: Tell us a little bit about WooCommerce.
Joel: WooCommerce is the world’s most popular ecommerce platform. Powered by WordPress and built by WooThemes, the goal of WooCommerce is to allow you to sell anything online - beautifully. You can integrate with payment processors, easily manage shipping methods and inventory, set up flexible tax rules, and view detailed store reports all from your WordPress dashboard.
Segment: What are a few of your favorite customer stores?
Joel: I’m glad you asked this! Trolling through WooCommerce sites and seeing all the amazing ways our platform is being used is one of my favorite things to do. Some of my favorites are Porter & York, Hatchery, Santa Monica Pier and Pedego.
Segment: Why would these kinds of stores want to use Segment’s WooCommerce plugin?
Joel: Segment enables WooCommerce store owners to easily pick which customer data or triggers they want to track, all through an easy and automated process. Via the WooCommerce Segment extension by Extensionworks, customers are then able use Segment to send that data to advertising, analytics and email tools.
Segment: What can your customers do with the right analytics and marketing tools?
Joel: Within the ecommerce space understanding your customer behavior and audience is extremely key. Analytics and marketing tools can help merchants learn about their user base behavior and then take action based on that data. Let’s take WooThemes for an example.
The concept of build, measure and learn was somewhat new when I came to WooThemes, but it has since become an integral part of what we do. As much as possible we try to understand our customer needs and behavior, and then base business decisions on this information. Data drives most of our decisions at WooThemes, and in the instances where we take chances, we always try to learn from the data following any experiment.
Segment: The idea of build, measure, learn seems like a great strategy for WooThemes. In your personal role, do you use any metrics to measure your success?
Joel: My role is mainly focused on forming new relationships with companies we have a WooCommerce integration with or companies we are interested in working with. Sometimes it’s hard to measure the value of a relationship, but it always feels good to see new products hitting the market or to see a special offer go out to our customers. I’d like to think that a large part of my role is making WooCommerce and WooThemes look more attractive by working with only the best ecommerce and WordPress services. We are always looking to provide new valuable offerings and tools to our users. It’s always a huge win when we can create partnerships where the customer wins (by saving money), the service partner wins by acquiring new users, and we win by generating revenue.
Segment: Sounds like a winning trifacta. We’re glad we made the cut!