Go back to Blog

Growth & Marketing

Nupur Bhade Vilas on October 20th 2021

Meet Twilio Engage: the first growth automation platform designed for the digital era.

All Growth & Marketing articles

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.

The Fast Track to Data Driven.

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.

Find Insights Fast with Open Source SQL.

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 name column.

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.

Sometimes Simple Queries Are Best.

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.

Polish Up Your SQL Skills. Your 2015 Self Will Thank You.

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.

We’re excited to help you find insights in your Segment SQL data. You can sign up for Segment SQL here and for Mode here.

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.

All The King’s Databases

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.

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 users.payment_plan:

Note that we now need to fully qualify the tables in the FROM and JOIN clauses with their database names: segment and web_prod.

And our hard work pays off! Here we can see our iPhone MAUs sliced by payment plan:

How It Works

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 Segment database’s iphone_production schema is translated to the db_1234_iphone_production schema in Periscope’s data cache. And web_prod‘s (unspecified) public schema is translated to the db_1235_public schema. The rest of the query remains the same!

Start Exploring

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 friends@segment.com. We’d love to discuss.

We’re also offering a 30 day trial for Segment Redshift customers. To get started, swing on over to the Segment Redshift access page and then sign up for Periscope.

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 MerchThe 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.

Thanks again to Goodsie and Church of Merch for chatting with us! To learn more about our options for ecommerce, click here or sign up today.

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 & YorkHatcherySanta 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!

Thanks again to Joel from WooThemes and WooCommerce for taking the time to chat with us. To learn more about how to boost sales with ecommerce analytics, click here or sign up today.

Become a data expert.

Get the latest articles on all things data, product, and growth delivered straight to your inbox.