Report object
Constructing a report
note
the events, per, dateRanges and other construction options can only be used once in the constructor. E.g. setting later reportObject.events = ['other_event'] will not work; we need to use the setters like reportObject.setEvents(..) (overwrite) reportObject.addEvents(..) (adds). All other props have overwritting setters: reportObject.setTimeUnit(..), reportObject.setDateRanges(..)
- Also note that methods can be chained: new Report().setEvents().setTimeUnit();
Value indexes
!! TODO for each report, explain what value becomes what index. Simply use a few example events for funnel, retention, etc and then show the values array.
Variables recipes
!! TODO Explain how to use the variables (also substitute ones with the variable.helperfunc) based on actual templates made so far. See all usage patterns.
Custom SQL
!!! TODO also how do date ranges work?
Note that all SQL should use single quotes, except for column names: SELECT date_trunc('week', ...) FROM ... AS "lineval1"
For some cases working with the Report objects might not be possible, because it simply cannot generate the kind of data you want. In that case you can use a fully custom SQL query, which can be used as an input to any report plugin.
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 'ucol'. The events table contains a log of all actions that users made, such as a purchase, pageview, etc and all properties are prefixed with 'ecol'. An example:
By joining the users table with the events table, we can filter certain events depending on their user properties:
Before you can use SQL results can be automatically be mapped to a chart by specific column names, using the ${this.code('.. 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 ${this.code('linevalX')} or ${this.code('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 ${this.code('segX')} and/or ${this.code('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 ${this.code('time_day')}, where day could also be ${this.code('hour')}, ${this.code('week')}, ${this.code('month')} or ${this.code('total')}.
As last, an additional ${this.code('groupX')} column name can be specified to indicate a grouping. Again a whitespace and custom display name can be used here.
See examples 1, 2 and 3.
Examples 1 and 2 return the users data format which can be used with report plugins that support "List of users" we need a group by "user_id" as minimum, and name that group by as such, and a value. E.g. for A/B testing, we should then take some "value" column as conversion either 0/1, see example 4.
To return in the events data format which can be used with report plugins that support "List of events" we need a group by "event_id" as minimum, and name that group by as such, and a value. Also make sure to use only the events table. See example 5.
// Example 1: 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 2: 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 3: 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 4: 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 5: 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 6: 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
Mapping of SQL columns
TODO lineval1, seg1, etc
Date ranges
- 'now', 'relative' or absolute
To execute a custom SQL query with dynamic date ranges, either create a bookmarked report or a new custom SQL report object as shown in the example.
If you want to use a custom fixed date range, then avoid hard-coding it in the SQL query itself (this will then make the Insight Archive not get the historical result for that query), but use a {type: "CUSTOM", ..} like in the 2nd example.
The markers used in the SQL query are the same ones that can be used when creating a new 'Custom Report'.
Compare to previous:
Default relative ranges:
// Available date ranges: ["TODAY", "YESTERDAY", "LAST_7_DAYS", "LAST_WEEK_SUN_SAT", "LAST_WEEK_MON_SUN", "LAST_WEEK_MON_FRI", "LAST_14_DAYS", "THIS_MONTH", "LAST_30_DAYS", "LAST_90_DAYS", "LAST_180_DAYS", "LAST_MONTH", "LAST_3_MONTHS", "LAST_6_MONTHS"] const report = new Report({ reportType: "user-graph", timeUnit: "day", dateRanges: ["LAST_30_DAYS"] });
const results = await ReportData.get(report);
Custom relative range:
Recipes - Report specific
User report
Calculate with custom SQL (from moment template)
Calculate retention
Calculate session
Event report
Funnel report
Retention report
Session report
Recipes - Reports generally
Setting "Per"
Using a raw object for "timeUnit", we can change "per". !! TODO But better is to add a "per" and "setPer" which can be simple user/event property to API