Report object

Constructing a report

new Report({
title: 'Report XYZ',
description: 'Description for the report',
reportType: 'event-graph',
timeUnit: 'day', // hour, day, week, month, total // Or use report.setTimeUnit; both this and 'timeUnit' support rawObj (can be used to change the 'per' also for example)
dateRanges: [
// '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',
'LAST_30_DAYS',
{
type: 'TODAY',
compareToPrevious: true
},
{
from: new Date(new Date().getTime() - (7 * 24 * 60 * 60 * 1000)).toString(),
to: new Date().toString(),
compareToPrevious: false
},
{
from: moment().subtract(7, 'd').toString(),
to: moment().toString(),
compareToPrevious: true
}
],
events: ['backend_paid', 'visit_landing_page'], // Short-cut. Also addEvents(['event1', 'event2']). Can use setCustomItems(rawItems) or {customItems: rawObj}
// Note: the following all need raw objects
groupBy: null, // Or use reportObject.setGroupBy
segmentBy: null, // Or use reportObject.setSegmentBy
userFilter: null, // Or use reportObject.setUserFilter
report: null // Optional: This is the actual inner report object (retention, funnel, user, event, session, sql). Can be set to raw report object as found in reportObject.report or when doing <Debug obj={report}> on non-standalone . Better to user simple helper like 'events'.
});
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:

SELECT e_col_country FROM events WHERE id = 'abcd_1234'\nSELECT u_col_initial_country FROM users WHERE id = 'user_xyz'

By joining the users table with the events table, we can filter certain events depending on their user properties:

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'

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

const reportObject = new Report({
"sql": "SELECT max(date_trunc('day', FROM_UNIXTIME(events.created_at))) as \"time_day event creation date\", COUNT(events.id) as \"lineval1 Value 1\", COUNT(events.id)*2 as \"lineval2 Value 2\", COUNT(events.id)*3 as \"lineval3 Value 3\" FROM events GROUP BY date_trunc('day', FROM_UNIXTIME(events.created_at))"
});

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'.

Example 1
// 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"]
// Or a custom date range that can include today for example, by using the "now" object like in "reportCustomRange".
const report = new Report({sql: "SELECT COUNT(*) FROM users WHERE FROM_UNIXTIME(users.created_at) >= {{dr,1_from}} AND FROM_UNIXTIME(users.created_at) <= {{dr,1_to}}", dateRanges: ["LAST_30_DAYS"]});
const reportCustomRange = new Report({sql: \`SELECT COUNT(*) FROM users WHERE FROM_UNIXTIME(users.created_at) >= TIMESTAMP '${moment(now).subtract(7, 'days').format('YYYY-MM-DD')}' AND FROM_UNIXTIME(users.created_at) <= TIMESTAMP '${moment(now).format('YYYY-MM-DD')}'\`});
const results = await ReportData.get(report);
return {results};
Example 2
const report = new Report({sql: "SELECT COUNT(*) FROM users WHERE FROM_UNIXTIME(users.created_at) >= {{dr,1_from}} AND FROM_UNIXTIME(users.created_at) <= {{dr,1_to}}", dateRanges: [{type: "CUSTOM", from: moment().subtract(365, 'days'), to: moment().subtract(7, 'days')}]});
const results = await ReportData.get(report);
return {results};

Compare to previous:

const report = new Report({
reportType: "user-graph",
timeUnit: "day",
dateRanges: [{type: "LAST_90_DAYS", compareToPrevious: true}]
});
const results = await ReportData.get(report);
return {results};

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:

const report = new Report({
reportType: "user-graph",
timeUnit: "day",
dateRanges: [{type: "CUSTOM", from: moment().subtract(365, 'days'), to: moment().subtract(7, 'days')}]
});
const results = await ReportData.get(report);
return {results};

Recipes - Report specific

User report

Calculate with custom SQL (from moment template)

const calculateHasEvent = {
"calculate": [{
"aggregate_function": "avg",
"filter": {
"conjunctions": [],
"queries": [
{
"event": conversionEvent,
"index": 0,
"operator": {
"type": "boolean",
"value": "IS NULL"
},
"property": {
"event": conversionEvent,
"human": "Has this event",
"type": "boolean"
},
"queryType": "event",
"steps": [
{
"id": 0,
"next": "SELECT_OPERATOR",
"show": false,
"title": "Has this event",
"valid": true
},
{
"id": 1,
"next": "SELECT_VALUE",
"show": false,
"title": null,
"valid": true
},
{
"id": 2,
"next": null,
"show": false,
"title": null,
"valid": true
}
],
"valid": true,
"value": null,
"valueType": "hasThis"
}
]
},
"property": {
"name": `${count.event} ${count.type == "eventProperty" ? count.propertyHuman : ''} - Users without conversion`,
"resultType": "number",
"sql": sql,
"sqlValidation": {
"fromCriteriaMarkerUsed": true,
"markerCount": 3,
"momentMarkerUsed": true,
"momentSafe": true,
"resultValueMarkerUsed": true,
"validSQL": true
},
"type": "sql"
},
"type": "user-defined",
"valid": true
}],
"color": "yellow",
"name": "",
"operators": []
};
report.addItem(calculateHasEvent);

Calculate retention

// Retention 1st week
report.addItem({
"calculate": [{
"aggregate_function": "avg",
"filter": {
"conjunctions": [],
"queries": []
},
"property": {
"chosen_option": "came_back_and_had_retention",
"count_first_only": false,
"ignore_first_period": "day",
"ignore_first_period_till": "1",
"name": "1-week retention",
"period_count": 2,
"period_size": "year",
"return_events": [],
"start_events": [],
"type": "retention"
},
"type": "user-defined",
"valid": true
}],
"color": "violet",
"name": "",
"operators": []
});
// Retention 2nd week
report.addItem({
"calculate": [{
"aggregate_function": "avg",
"filter": {
"conjunctions": [
],
"queries": [
]
},
"property": {
"chosen_option": "came_back_and_had_retention",
"count_first_only": false,
"ignore_first_period": "day",
"ignore_first_period_till": "8",
"name": "2-week retention",
"period_count": 2,
"period_size": "year",
"return_events": [
],
"start_events": [
],
"type": "retention"
},
"type": "user-defined",
"valid": true
}],
"color": "violet",
"name": "",
"operators": [
]
});

Calculate session

// Avg. session count
report.addItem({
"calculate": [{
"aggregate_function": "avg",
"filter": {
"conjunctions": [
],
"queries": [
]
},
"property": {
"count_per": "none",
"name": "session_count",
"new_session": {
"timeUnit": "minutes",
"value": 30
},
"only_first": false,
"option": "count",
"session_contains": [
],
"type": "session"
},
"type": "user-defined",
"valid": true
}],
"color": "blue",
"name": "",
"operators": [
]
});

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

Group by

const availableCampaignProperties = await Utils.filterPropertiesWithData(Utils.getUserProperties().filter(userProperty => userProperty.category.match(/campaign/i)), "LAST_180_DAYS");
availableCampaignProperties.forEach(campaignProperty => {
report.addGroupBy(campaignProperty);
});

Segments

const segmentA = Utils.createUserFilter(segment, campaignClusters, {skipSegmentFilter: true, skipUserFilter: true});
report.addSegmentBy({userFilter: userFilter, name: "Segment A"});