How to use Segment SQL with the Mode Playbook
Working with Segment SQL? Mode Playbook is designed to work seamlessly with Segment SQL. To get started, connect Segment SQL to Mode by following a few simple directions---anyone can do it all from their browser. Once you're connected, the Playbook Overview provides step-by-step instructions for using Playbook reports with your data. The notes below outline how your data is structured within Segment SQL, and provides a few recommendations on how to adapt that data to use it with Mode Playbook.
Segment's users table
Segment SQL doesn't provide an exact users table. Segment's 'identifies' table, however, can offer a good substitute. You can use the identifies table to define your users table as follows:
SELECT user_id, MIN('timestamp') AS activated_at FROM my_project.identifies GROUP BY 1
This will find all distinct values of
user_id in the identifies table, and set their activation date as the first time they were identified by Segment.
If you're tracking a specific event for when users sign up---for example,
complete_signup---you can also use this event to define your users table:
SELECT user_id, MIN('timestamp') AS activated_at FROM my_project.complete_signup GROUP BY 1
Segment's events table
Segment SQL records each event that you track in a separate table. If you only care about one event, then you can use just that event table as your events table:
SELECT user_id, event AS event_name, 'timestamp' AS occurred_at FROM my_project.my_event
If you want to include multiple events in your events table, you can UNION multiple tables together.
UNION functions append the results of two SQL queries together. This allows you to combine multiple events into a single table. Note that you can use the event name in the Segment table, or you can define it in the query:
SELECT user_id, event AS event_name, 'timestamp' AS occurred_at FROM my_project.my_event_1 UNION ALL SELECT user_id 'event_2' AS event_name, 'timestamp' AS occurred_at FROM my_project.my_event_2
In some cases, you might want to have generic events in your events table---for example, every page view. In this case, you can use Segment's pages table. Note that this table excludes mobile app views; to include app views, combine this with Segment's screens table.
SELECT user_id, 'page_view' AS event_name, 'timestamp' AS occurred_at FROM my_project.pages UNION ALL SELECT user_id, 'screen_view' AS event_name, 'timestamp' AS occurred_at FROM my_project.screens
Finally, if you'd like to include specific events along with page or screen events, you can
UNION all of these table together as well.
Using the Playbook with Redshift
Every Playbook report includes a link to a how-to guide. You can find links to each report's guide in the report's description and at the top of the report query. If you're using Segment SQL, you should follow the notes that describe how to make the report compatible with a Redshift database.
What about people who aren't logged in?
Segment tracks anonymous users---users who aren't logged in and have no
anonymous_id field. This field is roughly analogous to a tracking token in a browser. If you want to adapt reports to include logged-out users, use the
anonymous_id field instead of the
user_id field. Importantly, while
anonymous_ids are recorded with pages events, they typically aren't recorded for events that require users to be logged in; in these cases, it's best to use
Troubleshooting common issues
Schema "some name" doesn't exist
Some Segment columns include periods. When referencing these columns, put the column name in double-quotes (e.g.,
SELECT pages."properties.path" FROM my_project.pages). If you don't put use quotes, SQL reads what's before the period as a schema reference rather than as part of the column name.
If you have any other questions about using Playbook with Segment, please contact our success team.