In today’s fast-paced market, tapping into real-time data is essential for staying competitive. With Twilio Segment, you can simplify data collection across multiple platforms and centralize everything in a single data warehouse — unlocking powerful segment analytics on your segment data. From tracking app page views to monitoring revenue-impacting events, you’ll have the insights you need right when you need them.
In this article, we’ll walk you through how to run SQL queries against your Segment pipeline by setting up SingleStore as a destination and using SingleStore’s SQL Editor. You’ll learn how to create a SingleStore account, configure the destination in Segment, and execute sample analytic SQL queries to pull out actionable metrics.
Whether you’re a small startup or a large enterprise, you’ll come away with technology know-how and detailed, hassle-free steps to seamlessly analyze your Twilio Segment data in SingleStore.
Prerequisites
A Twilio Segment account with at least one source already configured and sending analytics data
Familiarity with Segment integrations and how to track events from different sources
A test app that sends data to your Segment project
Basic SQL skills to query your data warehouse for insights
Step 1: Set up a Free Shared Tier SingleStore workspace
In the left-hand menu, click Create New > Deployment.
In the Create Workspace form, select Starter and follow the on‑screen instructions to complete the form.
Click Create Starter Workspace.
Once the workspace is created, click Connect > SQL IDE.
Copy the Host, Username, Password and Database parameters.
Step 2: Add SingleStore as a destination in Segment
Log in to your Segment workspace and go to Connections > Sources.
Select the desired source, then click Add Destination.
Find and select SingleStore from the Catalog, then click Add Destination again.
In the Setup tab, enter a destination name and click Create Destination.
In the Settings tab, configure your SingleStore connection:
Host, Port (default: 443), Username, Password
Database Name and Table Name (default: segment_data)
Click Save Changes.
In the Mappings tab:
Click New Mapping > Send Data
Map your source events to destination fields
Click Next, name the mapping, and Save
Return to the Settings tab and toggle Enable Destination to activate the pipeline.
Once complete, your SingleStore destination is live and ready to ingest event streams for SQL-based analysis.
Step 3: Confirm data is reaching SingleStore
Select <your_deployment> Connect > SQL Editor.
Run a simple SQL snippet to count the number of ingested events in your default table (segment_data):
1SELECT COUNT(*) FROM segment_data;
4. Retrieve the 10 most recent events to verify the timing and details of each record:
1SELECT * FROM segment_data ORDER BY timestamp DESC LIMIT 10;2
5. (Optional) From a server or third-party client, run the same queries to confirm your organization can access these metrics for dashboards, analytics tools or reporting within your development workflow.
With these checks, you’ll know your analytics data is flowing smoothly into SingleStore and ready for SQL-based exploration. Once you see your events in the SQL Editor, you can confidently build reports and dashboards to extract business value.
Understanding Segment’s data model
The following table outlines the schema of the default segment_data table used to store Segment analytics data:
Column | Type | Description |
messageId | text | A globally unique UUID assigned by Segment to each message (used for deduplication and tracing). |
timestamp | datetime(6) | The ISO 8601 timestamp when the event occurred or was received by Segment (with microsecond precision). |
type | text | The message category is one of track, identify, page, screen, group or alias, indicating how the data should be handled. |
event | text | The name of the event being tracked (only populated for track calls, e.g. "Order Completed"). |
name | text | The name associated with a page, screen or group calls (e.g. the page title or group name). |
properties | JSON | A free-form object containing any custom attributes sent with a track, page or screen call (e.g. revenue, product details). |
userId | text | A stable, persistent identifier for a known user (set via identify or included on track calls). |
anonymousId | text | A temporary identifier is automatically generated for anonymous visitors before calling identify. |
groupId | text | The identifier for an account or group (populated on group calls to associate users with an organization). |
traits | JSON | A JSON object of user traits sent in an identity call (e.g. email, name, signup date). |
context | JSON | Metadata about the environment and integrations: browser, device, OS, IP address, library versions and destination settings. |
This structure gives you full flexibility to implement SQL queries that surface the value of your data.
Writing actionable SQL queries on Segment data warehouse
Once your Segment data is flowing into SingleStore, the next step is making it work for you. With SQL, you can go far beyond basic analytics tools, crafting custom reports, charts and insights tailored to your project, company or industry.
In this section, we'll demonstrate how to write practical queries using a web eCommerce app as an example. The app uses the Segment client to track events like "Product Liked", "Product Disliked" and "Product Bought", each enriched with properties like "productId", "productName" and "productPrice". These events are triggered from both client-side and server-side, and stored in the segment_data table in SingleStore.
Using this setup, you’ll learn how to:
Build funnel analysis queries to understand where users drop off or convert within the same session
Segment customers into retention cohorts based on engagement or purchases
Estimate lifetime value (LTV) to identify high-value segments and influence communications strategies
Each use case will show how to add segment analytics in a structured, hassle-free way using only SQL, no extra code and no new packages — just raw query power on clean, defined data. This approach also ensures minimal dependency on third-party integrations, making your analytics more resilient and easier to support across teams and environments.
Launch AI-driven features. Without hitting limits.
- Free to start.
- Start building in minutes.
- Run transactions, analytics & AI.
Funnel analysis
Understanding how users move through your funnel is a core application of segment analytics. In this example, we'll focus on a simple but powerful flow: how many users who triggered the "Product Liked" event went on to complete a "Product Bought" event, a clear measure of purchase intent conversion.
This type of analysis is particularly useful for eCommerce apps that track user engagement using the analytics client. By sending data from your website or other platforms through Segment to SingleStore, you get a complete view of how product interest translates into revenue.
Here’s what we’re solving:
Count the number of unique users who liked at least one product
Count how many of those same users later bought a product
Calculate the conversion rate between the two events
The following example query gets these insights:
1WITH events AS (2 SELECT3 COALESCE(userId, anonymousId) AS user_key,4 MIN(5 CASE 6 WHEN type = 'track' 7 AND event = 'Product Liked' 8 THEN timestamp9 END10 ) AS like_time,11 MIN(12 CASE 13 WHEN type = 'track' 14 AND event = 'Product Bought' 15 THEN timestamp16 END17 ) AS purchase_time18 FROM segment_data19 GROUP BY user_key20)21SELECT22 SUM(like_time IS NOT NULL) AS users_liked,23 SUM(purchase_time IS NOT NULL AND purchase_time > like_time) AS users_bought,24 ROUND(users_bought / NULLIF(users_liked, 0) * 100, 2) AS conversion_rate25FROM events;
This is a great starting point to add segment analytics that map directly to customer behavior. With minimal setup and no additional code or packages, you reduce the risk of inconsistencies or analytics-related bug fixes later in your pipeline.
Retention cohorts
Retention is one of the most telling metrics in Segment analytics, revealing how effectively your app or website keeps new customers coming back. In this example, we’ll calculate what percentage of users who made their first purchase (the Product Bought event) return to make a second purchase within 30 days of that initial transaction.
This streamlined analysis lets you quickly gauge:
Product stickiness. How compelling your offering is to first-time buyers
Customer loyalty. The share of users who engage again
Early growth signals. Whether your onboarding, messaging or promotions successfully drive repeat purchases.
Here’s what we’re solving:
Identify each user’s very first purchase date.
Determine whether they made another purchase within the next 30 days.
Produce a clear summary of total new buyers, number retained at 30 days and the 30-day retention rate for fast, ongoing monitoring.
The SQL query below leverages the segment_data table to capture each user’s first purchase timestamp, then checks for a subsequent “Product Bought” event in the 30-day window that follows.
1WITH first_purchase AS (2 SELECT3 COALESCE(userId, anonymousId) AS user_key,4 MIN(5 CASE6 WHEN type = 'track'7 AND event = 'Product Bought'8 THEN timestamp9 END10 ) AS first_purchase_time11 FROM segment_data12 GROUP BY user_key13),14second_purchase AS (15 SELECT16 fp.user_key,17 MIN(sp.timestamp) AS second_purchase_time18 FROM first_purchase AS fp19 JOIN segment_data AS sp ON COALESCE(sp.userId, sp.anonymousId) = fp.user_key20 AND sp.type = 'track'21 AND sp.event = 'Product Bought'22 AND sp.timestamp > fp.first_purchase_time23 AND sp.timestamp <= fp.first_purchase_time + INTERVAL 30 DAY24 GROUP BY fp.user_key25)26SELECT 27 COUNT(*) AS total_buyers,28 SUM(second_purchase_time IS NOT NULL) AS retained_buyers,29 ROUND(retained_buyers / NULLIF(total_buyers, 0) * 100, 2)30FROM first_purchase AS fp31LEFT JOIN second_purchase AS sp32USING (user_key);
By layering this kind of retention logic on top of your existing setup, you effectively add segment analytics without needing extra code or third-party tools, just precise SQL and your existing API-driven event structure.
Lifetime value (LTV)
Estimating customer Lifetime Value (LTV) is critical for optimizing acquisition strategies, personalizing engagement and prioritizing high-value segments. With segment analytics and SQL, you can quickly calculate how much revenue each user has generated over time directly from the "Product Bought" events captured in your Segment integrations.
In this example, we’ll focus on:
Summing the total "productPrice" per user from "Product Bought" events
Counting the number of purchases per user
Optionally, identifying top spenders or segments by cohort or geography using properties from the event or context
This type of query enables your company to track and compare different customer groups, ideal for tailoring communications strategies, setting advertising budgets or designing loyalty programs.
1WITH all_purchases AS (2 SELECT3 COALESCE(userId, anonymousId) AS user_key,4 properties::$productPrice AS product_price5 FROM segment_data6 WHERE type = 'track'7 AND event = 'Product Bought'8)9SELECT 10 user_key,11 SUM(product_price) AS revenue,12 COUNT(*) AS purchases,13 ROUND(revenue / NULLIF(purchases, 0), 2) AS avg_order_value14FROM all_purchases15GROUP BY user_key16ORDER BY revenue DESC;
Note: To ensure accurate results, make sure the “productPrice” property is consistently defined and captured across all platforms via the Segment API.
LTV analysis remains one of the easiest ways to add segment analytics that drive measurable business impact.
Best practices for SQL analytics on Segment data
As your organization scales its use of analytics on top of Segment data, it’s important to maintain consistency, accuracy and performance in how you write and manage SQL. Below are three best practices we recommend to ensure your analytics remain reliable and actionable over time.
1. Normalize key properties early
When using Segment in your app, it’s common to track custom events with dynamic properties. While this flexibility is powerful, it can introduce fragmentation in your analytics if the same property (e.g. “productPrice”) appears in multiple shapes or formats.
Note: Before building out complex dashboards or reports, run basic profiling queries to identify inconsistencies. Where possible, standardize field names and data types either in the code (using the Segment library or middleware) or in SQL views that clean and normalize the raw data.
2. Define reusable views for common queries
If you find yourself repeating the same logic across dashboards, like filtering out bot traffic or calculating first_purchase_date, consider encapsulating this logic in SQL views. It will make your queries cleaner and reduce the chance of discrepancies over time.
This also helps newer team members understand your analytics layer without diving into every raw node of your event stream. For a growing company, consistency here often saves hours of debugging and analytics-related support down the line.
3. Plan migration from Free Shared Tier to Standard Tier early
While the SingleStore Free Shared Tier is excellent for initial testing, prototyping and validating your Segment integration, it comes with limited resources and concurrency. As your project grows and your queries become more complex, you may notice performance bottlenecks or delays in data ingestion.
To fully leverage SingleStore's real-time processing power, consider migrating to the Standard Tier. It offers dedicated compute, higher availability and horizontal scalability, ensuring analytics on your Segment data run reliably at any scale. This is especially important when your company starts generating high event volumes or supporting multiple teams working on the same analytics stack.
Conclusion
Analyzing Twilio Segment data with SingleStore offers a hassle-free way to move from raw event collection to high-impact insights. Whether you're tracking customer behaviour in an eCommerce app or building real-time dashboards across teams, this workflow gives your analytics function the flexibility and scale it needs. By combining powerful SQL with a unified data model, you can execute everything from funnel analysis to LTV calculations, all without leaving your SQL Editor.
And as your support needs grow, SingleStore is built to scale with you, whether you’re running quick tests or powering mission-critical queries across your entire organization. For advanced use cases, enterprise deployments or technical support, both Segment and SingleStore offer the tools and guidance to keep your analytics infrastructure running smoothly.
