Welcome to the Mode Playbook! The Playbook is a repository of open source analysis that can be adapted to work with your data. Each Playbook is built on top of fake data designed to look like data that a typical web software company might have. The guide below outlines in detail how to change each query to fit to your data and how to customize a Playbook to your exact needs.

Getting Started

Playbook reports are built on top of a common schema of a users table and an events tables. Neither of these concepts require precise definition or precise table names. Users can be a table of people, accounts, visitors, or any other type of identity; events can be a table of page views, purchases, logins, or a combination of many different actions.

The users table should have one row per user (or customer or account) and typically has two columns. The first column should be a user_id, which is a unique identifier for that user. The second column should be an activated_at timestamp. This represents the first time that user signed up, their first purchase, or any other initializing event.

The events table should have one row per event. Events can be logins, purchases, clicks, screen views, or any other action taken by users. This table typically has three columns. The first column would be a user_id, which matches to the user_id field in the first table. The second column should be the event_name, which is used to identify which action a user took (in cases when the events table only represents on action, the event_name column is unnecessary). The final column should an occurred_at timestamp, which represents when this action occurred.

While your database may have explicit user and events tables, it's not necessary to have these exact tables: you only need to have data that allows you to generate tables with this information. For example, suppose you have only one table of customers purchases, and that table only includes a customer email address and a purchase table. You can generate a users table with the following query:

SELECT email AS user_id,
       MIN(purchase_date) AS activated_at
  FROM customer_purchases
 GROUP BY 1

You can generate an events table in a similar fashion:

SELECT email AS user_id,
       'purchase' AS event_name,
       purchase_date AS occurred_at
  FROM customer_purchases

Using Playbook Reports

Once you've defined a users table and events table, duplicate the report that you'd like to use. This will take you to the Query Editor, where you can directly edit the report's SQL. Before making changes, remember to change the data sources for all queries from the Mode Public Warehouse to your private connected database (learn more about which databases Mode can connect to). Note that all reports run against private data are only visible to people with access to that data.

From the Query Editor, add your users and events tables to each report you'd like to use. To make it easy for you to add these tables to your report, reports define these tables at the top of each query using common table expressions. Common table expressions work similarly to sub-queries, except they're defined at the top of queries rather than in-line. This makes it possible for you to format your tables to fit the requirements discussed above without having to manipulate most of the query.

Note that some versions of SQL (most notably MySQL) don't support common table expressions. If you're using one of these versions of SQL, you can replace the users and events tables references in the query with sub-queries that matches the users and events common table expressions.

Once you've defined these tables, make any other changes to the query that are necessary. Most reports don't require any additional changes, but those that do have include exact details on what to change in their report guide (URLs for guides are available at the top of each query).

Finally, some SQL syntax is specific to the database it's run against. All of the examples were written against Mode's PostgreSQL database. If you'd like to use these reports against a different type of database, each help doc includes tips for adapting the query to your database.

Customizing or Improving Playbook Reports

While these reports should work with minimal adjustments, you might want to make additional changes. You might want to set different time windows, filter on different metrics, or use parts of a query to solve a completely different problem. If you'd like to make changes, you can directly edit all of the SQL in the query, as well as the presentation code that creates the visualizations.

We also realize that, like most open source software, our work may have bugs, inefficiencies, or other areas for improvement. If you'd like to make any changes to the published version of reports---or contribute new versions of other reports and analyses---Playbook queries and presentations are available on Github. We'd love to hear from you.