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 user_id---via the 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 user_id anyway.

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.