This document outlines how to use Mode Playbook with your data. The three sections below outline what data is required, how to format reports to fit that data, and how to edit reports to make them compatible with different types of databases.

Required data

The Playbook reports are built on top of a common schema of people and events. Most reports use only these two tables, one for people and one for events.

The people table can represent users, customers, accounts, or any other type of identity. This table is expected to have one row per user (or customer or account), and typically has two columns. One 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. Finally, this table should have one row per user.

The second table is an events table. This table should include on row per event. Events can be logins, purchases, clicks, screen views, or any other action taken by users. This table should have three columns. One column should be a user_id, which matches to the user_id in the first table; one column should be the event_name, which is how you identify which action a user took; and one column should be called occurred_at, and is a timestamp of when this event took place.

Some reports only require an events table, while others might require additional information. These additional details are noted in the guide for each report.

Formatting reports to your data

Playbook reports use common table expressions to help you format your data to each query. Common table expressions allow you define tables at the top of your query rather than in-line as sub-queries. This makes it possible for you to format your tables to fit the requirements discussed above without having to manipulate most of the query.

To use common table expressions, write the query that defines your users and events table in the "users"; and "events"; expressions. These queries can simply select columns from a single table, or they can be complex queries that join and aggregate tables. Once you use these common table expressions to match your data to the format described above, the rest of the query should work without you needing to make any changes.

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.

If you're using a Segment SQL database, there are some additional nuances to keep in mind when writing your common table expressions. Check out this article to learn more. Notes for using Playbook Reports with Segment SQL.

Database notes

All of the Playbook reports are built on top of Mode's public PostgreSQL database. Different versions of SQL---such as MySQL, Redshift, and others---use slightly different syntax or offer different sets of functions. This section describes all of the syntax or functions that appear in Playbook queries that is specific to PostgreSQL, and offers suggestions on how to adapt it to other versions of SQL.

Current timestamp function

These are used to define the current timestamp.

• PostgreSQL: NOW()
• MySQL: NOW()
• Redshift: SYSDATE

Adding or subtracting a fixed time period to a date

These functions allow you to add or fixed time intervals to a date.

• PostgreSQL: date_field - INTERVAL '1 DAY', date_field + INTERVAL '1 DAY'
• MySQL: DATE_SUB(date_field, INTERVAL 1 DAY), DATE_ADD(date_field, INTERVAL 1 DAY)
• Redshift: date_field - INTERVAL '1 DAY', date_field + INTERVAL '1 DAY'

In some cases, when adding a date to Redshift, this syntax is necessary:

• Redshift: DATEADD(interval,number_of_periods,date_field)

The interval can be month, week, day, or other time periods; the number of periods is the number of intervals you want to add.

Generate a incremental series

It's often useful to create a single-column table with values that increment by one (e.g., a table with 100 rows numbered from 1 to 100). These functions create that table. In most cases, these tables are then joined to another table.

• PostgreSQL: SELECT s.a AS counter FROM generate_series(first_value,last_value) AS s(a)
• MySQL: SELECT s.a AS counter FROM generate_series(0,10) AS s(a)
• Redshift: In Redshift, the PostgreSQL function works, but can't be combined with other data. Therefore, it's often easiest to generate this table by selecting a limited number of rows from an existing table, and counting the row numbers of that table. For example: SELECT ROW_NUMBER() OVER (ORDER BY occurred_at) + (first_value - 1) AS counter FROM (SELECT * FROM a_table LIMIT last_value - first_value + 1)

Window functions

Window functions allow you to calculate aggregate values from many rows without aggregating the rows together.

• PostgreSQL: Learn more.
• MySQL: Learn more.
• Redshift: Learn more.

Difference between two dates in days

These functions describe how to find the difference in days between two dates.

• PostgreSQL: DATE_PART('day',date_2 - date_1)
• MySQL: DATEDIFF(date_1,date_2)
• Redshift: DATEDIFF('day',date_1,date_2)

Difference between two dates in weeks

These functions describe how to find the difference in weeks between two dates.

• PostgreSQL: TRUNC(DATE_PART('day',date_2 - date_1)/7)
• MySQL: TIMESTAMPDIFF(week,date_1,date_2)
• Redshift: FLOOR(DATEDIFF('day',date_1,date_2)/7)

Difference between two dates in months

These functions describe how to find the difference in months between two dates. Because PostgreSQL has no function for calculating differences in months directly, the calculation is a bit longer.

• PostgreSQL: (EXTRACT('year' FROM date_2) - EXTRACT('year' FROM date_1)) * 12 + (EXTRACT('month' FROM date_2) - EXTRACT('month' FROM date_1)) - CASE WHEN (CEILING(DATE_PART('day',date_2) - DATE_PART('day',date_1))) < 0 THEN 1 ELSE 0 END
• MySQL: TIMESTAMPDIFF(month,date_1,date_2)
• Redshift: FLOOR(MONTHS_BETWEEN(date_2,date_1))

Difference between two dates in minutes or seconds

These functions describe how to find the number of seconds or minutes between two timestamps. These values return the number of seconds; to return the number of minutes, simply divide by 60.

• PostgreSQL: EXTRACT('EPOCH' FROM date_1 - date_2)
• MySQL: TIME_TO_SEC(TIMEDIFF(date_1,date_2))
• Redshift: EXTRACT('EPOCH' FROM date_1 - date_2)

Day of week functions

These functions convert a timestamp into the day of the week the timestamp occurred. For PostgreSQL and Redshift, day values are returned as integers from 0 to 6, where Sunday equals 0. MySQL returns integers from 1 to 7, where Sunday equals 1.

• PostgreSQL: EXTRACT('DOW' FROM date_field)
• MySQL: DAYOFWEEK(date_field)
• Redshift: EXTRACT('DOW' FROM date_field)

DATE_TRUNC

These functions truncate timestamps into a day, week, or month value. They're typically used for grouping all the actions from a single day or week together.

• PostgreSQL: DATE_TRUNC('day',date_field), DATE_TRUNC('week',date_field), DATE_TRUNC('month',date_field)
• MySQL: To truncate a timestamp to day, use DATE(date_field). To truncate a timestamp to a week, use YEARWEEK(date_field). To truncate to a month, use CONCAT(YEAR(date_field),MONTH(date_field)).
• Redshift: DATE_TRUNC('day',date_field), DATE_TRUNC('week',date_field), DATE_TRUNC('month',date_field)

To learn more, check out this blog post on DATE_TRUNC.