Custom SQL Report

Limits

Any SQL report can return at most 100.000 results. If you don't add a LIMIT ... as the last part of your SQL query — or LIMIT is more than 100.000 — we automatically add LIMIT 100000 to the end of your SQL query.

SELECT * FROM users LIMIT 100000

Users and Events tables

There are two tables: users and events. The users table contains all static user properties, such as first visit, referring traffic source, etc and are prefixed with u_col_. The events table contains a log of all actions that users made, such as a purchase, pageview, etc and all properties are prefixed with e_col_.

To insert user or event column names, use the Add SQL menu option.

Examples:

SELECT e_col_country FROM events WHERE id = 'abcd_1234'

SELECT u_col_initial_country FROM users WHERE id = 'user_xyz'

Joining Users and Events

Example to count all download events, with joined user properties, if user country is US:

SELECT COUNT(events.id) FROM events LEFT JOIN users ON events.user_id = users.id WHERE events.name = 'download' and users.country = 'US'

Automatic chart mapping

SQL results can be automatically be mapped to a chart by specific column names, using the .. AS .. keyword. This can then be used to run for more user friendly displaying, instead of just a table, and to be able to run plugins on the data.

To make a chart, we need a minimum of one value and a timestamp. A value can be specified by giving a numerical column the name linevalX or barvalX, where X is the value number starting with 1. By adding a whitespace and then a custom display name, that will be used as display name. Each value can be part of a specific date range, segment. This can be specific be adding the a linevalX and comma, and then and or both the keywords segX and/or dateX. Each of those can also have a space and their custom segment/date range display name.

To indicate time, return a column with timestamp type, as as name time_day, where day could also be hour, week, month or total.

As last, an additional groupX column name can be specified to indicate a grouping. Again a whitespace and custom display name can be used here.

Example — Line chart mapped query

SELECT max(date_trunc('day', FROM_UNIXTIME(events.created_at))) as "time_day event creation date", COUNT(events.id) as "lineval1 Value 1" FROM events GROUP BY date_trunc('day', FROM_UNIXTIME(events.created_at))

Example — Another line chart mapped query

SELECT max(date_trunc('week', FROM_UNIXTIME(events.created_at))) as "time_week event creation week", COUNT(events.id) as "lineval1 Value 1,seg1 Segment A, date1 Custom date range", date_format(FROM_UNIXTIME(events.created_at), '%W') AS "group1 Day of week" FROM events GROUP BY date_trunc('week', FROM_UNIXTIME(events.created_at)), date_format(FROM_UNIXTIME(events.created_at), '%W')

Example — Bar chart mapped query

SELECT max(date_trunc('month', FROM_UNIXTIME(events.created_at))) as "time_month event creation month", COUNT(events.id) as "barval1 Value 1", COUNT(events.id)*2 as "barval2 Value 1 Double", COUNT(events.id)*3 as "barval3 Value 1 Triple" FROM events GROUP BY date_trunc('month', FROM_UNIXTIME(events.created_at))

Example — Comparing two totals for two different date ranges, to e.g. be displayed as score metric on dashboard:

SELECT true as "time_total total", COUNT(*) as "lineval1 Value-A, date1 date-range-1", COUNT(*)*2 as "lineval1 Value-A, date2 date-range-2" FROM events

Example — List of users

SELECT users.id AS "group1 user_id", users.u_col_initial_country AS "group2 country", rand() AS "lineval1" FROM users GROUP BY users.id, users.u_col_initial_country

Example — List of events

SELECT events.id AS "group1 event_id", events.e_col_country AS "group2 country", rand() AS "lineval1" FROM events GROUP BY events.id, events.e_col_country