Krystian Fras

Use Cases

Multi-Tenant Analytics with Auth0 and Cube

Sometimes, existing commercial or out-of-the-box open-source tools like Grafana don’t fit requirements for Nginx log analytics. Whether it is pricing, privacy, or customization issues, it is always good to know how to build such a system internally. 

In the following tutorial, I’ll show you how to build your own [Nginx](https://www.nginx.com/) log analytics with [Fluentd](https://www.fluentd.org/), [Kinesis Data Firehose](https://aws.amazon.com/kinesis/data-firehose/), [Glue](https://aws.amazon.com/glue/), [Athena](https://aws.amazon.com/athena/), and [Cube.js](https://cube.dev). This stack also makes it easy to add data from other sources, such as Snowplow events, into the same S3 bucket and merge results in Athena. I’ll walk you through the whole pipeline from data collection to the visualization.

[Here is the live demo of the final dashboard](https://statsbotco.github.io/cubejs/nginx-analytics-dashboard/).

Here’s a sample architecture of the application we’re going to build:
![Architecture](https://media.graphcms.com/output=format:jpg/ACbpDPQRKCakOMHIUVXu)

## Collecting Nginx Logs

By default, Nginx writes logs in a plain text format like this:

```bash
4/9/2019 12:58:17 PM1.1.1.1 - - [09/Apr/2019:09:58:17 +0000] "GET /sign-up HTTP/2.0" 200 9168 "https://example.com/sign-in" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.86 Safari/537.36" "-"
4/9/2019 12:58:17 PM1.1.1.1 - - [09/Apr/2019:09:58:17 +0000] "GET /sign-in HTTP/2.0" 200 9168 "https://example.com/sign-up" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.86 Safari/537.36" "-"
```

Although we can parse that, it would be much easier to change Nginx configuration to use the JSON format. 

```conf
log_format json_combined escape=json '{ "created_at": "$msec", '
			'"remote_addr": "$remote_addr", '
			'"remote_user": "$remote_user", '
			'"request": "$request", '
			'"status": $status, '
			'"bytes_sent": $bytes_sent, '
			'"request_length": $request_length, '
			'"request_time": $request_time, '
			'"http_referrer": "$http_referer", '
			'"http_x_forwarded_for": "$http_x_forwarded_for", '
			'"http_user_agent": "$http_user_agent" }';

access_log  /var/log/nginx/access.log  json_combined;
```

### Create an S3 Bucket
Create a new S3 bucket for the log files. Note that logs should be placed in the root of the bucket and no other data can be stored in that bucket. Also, consider using the same region as your Athena because Athena is not available in some regions.

### Define a Table Schema via the Athena Console

Open your Athena console and select the database you'd be using. To create a table, you can use AWS UI or just run create statement in the console.

```sql
CREATE EXTERNAL TABLE `kinesis_logs_nginx`(
  `created_at` double, 
  `remote_addr` string, 
  `remote_user` string, 
  `request` string, 
  `status` int, 
  `bytes_sent` int, 
  `request_length` int, 
  `request_time` double, 
  `http_referrer` string, 
  `http_x_forwarded_for` string, 
  `http_user_agent` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
  's3://<YOUR-S3-BUCKET>'
TBLPROPERTIES ('has_encrypted_data'='false');
```

### Create a Kinesis Firehose Stream

Open up the Kinesis Firehose console and click "Create delivery stream." Enter a name for the stream and set delivery to `direct PUT`.

![Create delivery stream](https://media.graphcms.com/output=format:jpg/KtIhwoRVmR1qUGrSoFAg)

Click next. Select `Record format conversion` to `Enabled`, `Output format` to `Apache ORC`, and select the database you created as the schema source.

![Set record format](https://media.graphcms.com/output=format:jpg/DupZUDNgTa6UgOc9Vvai)

Select S3 as a storage format. Select the S3 bucket you created for log files and leave `S3 Prefix` empty.

![S3 settings](https://media.graphcms.com/output=format:jpg/JI3GLcGNQCKi09bD1BKL)

You can change other options depending on your load, but we'll continue with the default ones. S3 compression is not available since the ORC format includes compression.
Now you've created the Kinesis Firehose stream!

### Fluentd

There are many different ways to install and use [Fluentd](https://www.fluentd.org/). We'll stick with the Docker version.

First, create a fluent.conf file and add a new Fluentd source to collect logs:

```xml
<source>
  @type forward
  port 24224
  bind 0.0.0.0
</source>
```

Now we can run a Fluentd server with this configuration. Here's a basic configuration; you can check out other options on [Docker Hub](https://hub.docker.com/r/fluent/fluentd/).

```bash
$ docker run \
  -d \
  -p 24224:24224 \
  -p 24224:24224/udp \
  -v /data:/fluentd/log \
  -v <PATH-TO-FLUENT-CONF>:/fluentd/etc fluentd \
  -c /fluentd/etc/fluent.conf
  fluent/fluentd:stable
```

This configuration uses the `/fluentd/log` path for cache files. You can run Fluentd in Docker without mounting the data directory, but in the case of a restart, you can lose all cached logs. Also, you can change the default `24224` port to any other unused port. Now, as we have a running Fluentd server, we can stream Nginx logs to it.

Since we run Nginx in Docker, we can run it with the built-in docker Fluentd log driver:

```bash
$ docker run \
--log-driver=fluentd \
--log-opt fluentd-address=<FLUENTD-SERVER-ADDRESS>\
--log-opt tag=\"{{.Name}}\" \
-v /some/content:/usr/share/nginx/html:ro \
-d \
nginx
```

We'll use the [Amazon Kinesis Output Plugin](https://github.com/awslabs/aws-fluent-plugin-kinesis). It can store logs captured from Nginx as ORC files.

Next, select the Nginx log using tag prefix match and parse JSON:

```xml
<filter YOUR-NGINX-TAG.*>
  @type parser
  key_name log
  emit_invalid_record_to_error false
  <parse>
    @type json
  </parse>
</filter>
```

We’re using the [kinesis_firehose](https://github.com/awslabs/aws-fluent-plugin-kinesis) output plugin to send parsed logs to Kinesis Firehose:

```xml
<match YOUR-NGINX-TAG.*>
	@type kinesis_firehose
	region region
	delivery_stream_name <YOUR-KINESIS-STREAM-NAME>
	aws_key_id <YOUR-AWS-KEY-ID>
	aws_sec_key <YOUR_AWS-SEC_KEY>
</match>
```

## Athena

Now you can query Nginx logs in Athena with SQL. Let's find some recent errors:

```sql
SELECT * FROM "db_name"."table_name" WHERE status > 499 ORDER BY created_at DESC limit 10;
```

### Full Scan for Each Request

Now we have logs parsed and delivered to S3 in the ORC format, which is compressed and efficient to query. Also, Kinesis Firehose partitions logs by date and hour, but querying and filtering them requires Athena to scan all files. This is a problem, because the more logs you store, the more data gets scanned per request. It is slow and also pricey, because Athena pricing depends on scanned data volume.

To solve this, we'll use AWS Glue Crawler, which gathers partition data from S3 and writes it to the Glue Metastore. Once data is partitioned, Athena will only scan data in selected partitions. It makes querying much more efficient in terms of time and cost.

### Setting an Amazon Glue Crawler

Amazon Glue Crawler can scan the data in the bucket and create a partitioned table for that data. 

Create a Glue Crawler and add the bucket you use to store logs from Kinesis. You can add multiple buckets to be scanned on each run, and the crawler will create separate tables for each bucket. Make sure to schedule this crawler to run periodically depending on your needs. We usually run a single crawler for all buckets every hour. Also, it's easier to use a separate database for all crawled tables.

### Partitioned Tables

Open your Athena console and choose the database you selected in the crawler configuration. After the first run of the crawler, you should see a table named the same as the S3 bucket where all log files are located. You can query results for some hours and filter the data by partitions.

```sql
SELECT * FROM "default"."part_demo_kinesis_bucket"
WHERE(
  partition_0 = '2019' AND
  partition_1 = '04' AND
  partition_2 = '08' AND
  partition_3 = '06'
  );
```

This query will select all events that came from 6 AM to 7 AM on April 8, 2019. 

Did that help? Let's run the same request without a partition filter.

![Query without partition filter](https://media.graphcms.com/output=format:jpg/dfCkPCTiSI6PDqZCssZ4)

You can see that the query took 3.59 seconds to run and processed 244.34 megabytes of data. Let's use partition helpers:

![Query with partition filter](https://media.graphcms.com/output=format:jpg/gqN32aeQUWKgz3wnD9pi)

This query is a bit faster and, more importantly, took only 1.23 megabytes of scanned data. On larger datasets, the difference grows dramatically.

## Building a Dashboard with Cube.js

We will use Cube.js to query Athena and build a dashboard. It has plenty of powerful features, but the main reasons we would want to use it, in this case, are automatic handling of partitions and pre-aggregations.

Cube.js can dynamically generate SQL, taking care of partitioning. It uses [data schema](https://cube.dev/docs/reference/frontend/cubejs-client-core), which is written in Javascript, to generate SQL. We can put instructions on how to filter partitions in the data schema. 

Let’s create a new Cube.js app with a serverless deployment option. Since we’re using Athena, serverless is the easiest way to deploy. If you prefer other deployment environments, such as Docker or Heroku, you can check the documentation on deployment options [here](https://cube.dev/docs/deployment).

```bash
$ npm install -g cubejs-cli
$ cubejs create nginx-log-analytics -t serverless -d athena
```

Cube.js uses environment variables for database credentials. On new app creation, the CLI generates the `.env` file with placeholders in the project directory. Fill it with your [Athena credentials](https://cube.dev/docs/connecting-to-the-database#notes-aws-athena).

Now, we can create a [data schema](https://cube.dev/docs/guides/recipes/code-reusability/schema-generation) file, where we’ll define how Cube.js should query the Athena logs table, as well as measures and dimensions for our dashboard. If you are just getting started with Cube.js, I recommend checking [this](https://cube.dev/blog/cubejs-open-source-dashboard-framework-ultimate-guide/) or [that](https://cube.dev/docs/product/data-modeling/overview) tutorial to learn more about data schemas and how Cube.js generates SQL.

In the `schema` folder, create the file `Logs.js` with the following content:

```javascript
const partitionFilter = (from, to) => `
    date(from_iso8601_timestamp(${from})) <= date_parse(partition_0 || partition_1 || partition_2, '%Y%m%d') AND
    date(from_iso8601_timestamp(${to})) >= date_parse(partition_0 || partition_1 || partition_2, '%Y%m%d')
    `

cube(`Logs`, {
  sql: `
  select * from part_demo_kinesis_bucket
  WHERE ${FILTER_PARAMS.Logs.createdAt.filter(partitionFilter)}
  `,

  measures: {
    count: {
      type: `count`,
    },

    errorCount: {
      type: `count`,
      filters: [
        { sql: `${CUBE.isError} = 'Yes'` }
      ]
    },

    errorRate: {
      type: `number`,
      sql: `100.0 * ${errorCount} / ${count}`,
      format: `percent`
    }
  },

  dimensions: {
    status: {
      sql: `status`,
      type: `number`
    },

    isError: {
      type: `string`,
      case: {
        when: [{
          sql: `${CUBE}.status >= 400`, label: `Yes`
        }],
        else: { label: `No` }
      }
    },

    createdAt: {
      sql: `from_unixtime(created_at)`,
      type: `time`
    }
  }
});
```

In the top level `sql` expression for the `Logs` cube, we are using the [FILTER_PARAMS](https://cube.dev/docs/cube#context-variables-filter-params) feature of Cube.js to dynamically generate SQL based on the passed filter parameters.

We also define measures and dimensions we’ll use in our dashboard. One last thing to do before building a frontend is to set up pre-aggregations. The Cube.js pre-aggregation engine builds a layer of aggregated data in your database during the runtime and keeps it up-to-date. It can significantly speed up the performance and also in the case of Athena reduce billing as well. [This guide](https://cube.dev/blog/high-performance-data-analytics-with-cubejs-pre-aggregations/) covers using pre-aggregations in more details.

To add it to our schema, add the following block to the end of the cube definition.

```javascript
preAggregations: {
  main: {
    type: `rollup`,
    measureReferences: [count, errorCount],
    dimensionReferences: [isError, status],
    timeDimensionReference: createdAt,
    granularity: `day`,
    partitionGranularity: `month`,
    refreshKey: {
      sql: FILTER_PARAMS.Logs.createdAt.filter((from, to) => 
        `select
           CASE WHEN from_iso8601_timestamp(${to}) + interval '3' day > now()
           THEN date_trunc('hour', now()) END`
      )
    }
  }
}
```

We’re pre-aggregating all the measures and dimensions we’ll use and also making this pre-aggregation to be partitioned by month. [Partitioning pre-aggregations](https://cube.dev/docs/pre-aggregations#rollup-time-partitioning) can dramatically increase background refresh time.

Now, we are ready to build our frontend dashboard.

Cube.js provides [REST API](https://cube.dev/docs/rest-api), a [Javascript client](hhttps://cube.dev/docs/reference/frontend/cubejs-client-core), and bindings for popular frameworks, such as React and Vue. The clients are visualization agnostic and take care of API calls and data formatting, letting developers use any visualization library.

The Cube.js server accepts a query in a [JSON format](https://cube.dev/docs/query-format) with measures and dimensions. It then generates and executes SQL against Athena, and sends the result back. For example, to load the count of requests with an error over time by day, we can use the following request:

```json
{
  "measures": ["Logs.errorCount"],
  "timeDimensions": [
    {
      "dimension": "Logs.createdAt",
      "dateRange": ["2019-01-01", "2019-01-07"],
      "granularity": "day"
    }
  ]
}
```

You can install the Cube.js Javascript Client and React binding with NPM.

```bash
$ npm i --save @cubejs-client/core @cubejs-client/react
```

Then import the `cubejs` and `QueryRenderer` components, and use them to fetch the data. In the example below, we use Recharts to visualize data.

```javascript
import React from 'react';
import { LineChart, Line, XAxis, YAxis } from 'recharts';
import cubejs from '@cubejs-client/core';
import { QueryRenderer } from '@cubejs-client/react';

const cubejsApi = cubejs(
  'YOUR-CUBEJS-API-TOKEN',
  { apiUrl: 'http://localhost:4000/cubejs-api/v1' },
);

export default () => {
  return (
    <QueryRenderer
      query={{
        measures: ['Logs.errorCount'],
        timeDimensions: [{
            dimension: 'Logs.createdAt',
            dateRange: ['2019-01-01', '2019-01-07'],
            granularity: 'day'
        }]
      }}
      cubejsApi={cubejsApi}
      render={({ resultSet }) => {
        if (!resultSet) {
          return 'Loading...';
        }

        return (
          <LineChart data={resultSet.rawData()}>
            <XAxis dataKey="Logs.createdAt"/>
            <YAxis/>
            <Line type="monotone" dataKey="Logs.errorCount" stroke="#8884d8"/>
          </LineChart>
        );
      }}
    />
  )
}
```

[This tutorial](https://cube.dev/blog/cubejs-open-source-dashboard-framework-ultimate-guide/) goes into great detail on how to build dashboards with Cube.js.

You can find a CodeSandbox with a demo dashboard built with React and Cube.js below.

<iframe src="https://codesandbox.io/embed/l5qyrzlw07?fontsize=14" css="width:100%; height:500px; border:0; border-radius: 4px; overflow:hidden;" sandbox="allow-modals allow-forms allow-popups allow-scripts allow-same-origin"></iframe>

Timur Minulin

Nginx Log Analytics with AWS Athena and Cube.js

_This is the second part of a tutorial series on building an analytical web application with [Cube.js](https://github.com/cube-js/cube.js). You can find [the first part here](https://statsbot.co/blog/building-an-open-source-mixpanel-alternative-1/). It expects the reader to be familiar with Javascript, Node.js, React, and have basic knowledge of SQL. [The final source code is available here](https://github.com/cube-js/cube.js/tree/master/examples/event-analytics) and [the live demo is here](https://d1ygcqhosay4lt.cloudfront.net/). The example app is serverless and running on AWS Lambda. It displays data about its own usage._

In this part, we are going to add Funnel Analysis to our application. Funnel Analysis, alongside with Retention Analysis, is vital to analyze behavior across the customer journey.  A funnel is a series of events that a user goes through within the app, such as completing an onboarding flow. A user is considered converted through a step in the funnel if she performs the event in the specified order. Calculating how many unique users made each event could show you a conversion rate between each step. It helps you to localize a problem down to a certain stage.

Since our application tracks its own usage, we'll build funnels to show how well users navigate through the funnels usage. Quite meta, right?

Here’s how it looks. You [check the live demo here](https://d1ygcqhosay4lt.cloudfront.net/funnels).

![](https://media.graphcms.com/yN9dB53UTkOFUwIsjwLT)

## Building SQL for Funnels

Just a quick recap of part I—we are collecting data with the Snowplow tracker, storing it in S3, and querying with Athena and Cube.js. Athena is built on Presto, and supports standard SQL. So to build a funnel, we need to write a SQL code. Real-world funnel SQL could be quite complex and slow from a performance perspective. Since we are using Cube.js to organize data schema and generate SQL, we can solve both of these problems.

Cube.js allows the building of packages, which are a collection of reusable data schemas. Some of them are specific for datasets, such as the [Stripe Package](https://github.com/cube-js/stripe-schema). Others provide helpful macros for common data transformations. And one of them we're going to use—the Funnels package.

_If you are new to Cube.js Data Schema, I strongly recommend you check [this](https://cube.dev/docs/guides/recipes/code-reusability/schema-generation) or [that](https://statsbot.co/blog/building-an-open-source-mixpanel-alternative-1/) tutorial first and then come back to learn about the funnels package._

The best way to organize funnels is to create a separate cube for each funnel. We'll use `eventFunnel` from the Funnel package. All we need to do is to pass an object with the required properties to the `eventFunnel` function. 
[Check the Funnels package documentation for detailed information about its configuration.](https://cube.dev/docs/funnels)

Here is how this config could look.
_In production applications, you're most likely going to generate Cubes.js schema dynamically. [You can read more about how to do it here.](https://cube.dev/docs/schema-generation)_

```javascript
import Funnels from "Funnels";

import { eventsSQl, PAGE_VIEW_EVENT, CUSTOM_EVENT } from "./Events.js";

cube("FunnelsUsageFunnel", {
  extends: Funnels.eventFunnel({
    userId: {
      sql: `user_id`
    },
    time: {
      sql: `time`
    },
    steps: [
      {
        name: `viewAnyPage`,
        eventsView: {
          sql: `select * from (${eventsSQl}) WHERE event = '${PAGE_VIEW_EVENT}`
        }
      },
      {
        name: `viewFunnelsPage`,
        eventsView: {
          sql: `select * from (${eventsSQl}) WHERE event = '${PAGE_VIEW_EVENT} AND page_title = 'Funnels'`
        },
        timeToConvert: "30 day"
      },
      {
        name: `funnelSelected`,
        eventsView: {
          sql: `select * from (${eventsSQl}) WHERE event = '${CUSTOM_EVENT} AND se_category = 'Funnels' AND se_action = 'Funnel Selected'`
        },
        timeToConvert: "30 day"
      }
    ]
  })
});

```

The above, 3-step funnel, describes the user flow from viewing any page, such as the home page, to going to Funnels and then eventually selecting a funnel from the dropdown. We're setting `timeToConvert` to 30 days for the 2nd and 3rd steps. This means we give a user a 30 day window to let her complete the target action to make it to the funnel.

In our example app, we generate these configs dynamically. [You can check the code on Github here.](https://github.com/cube-js/cube.js/blob/master/examples/event-analytics/backend/schema/Funnels.js)

## Materialize Funnels SQL with Pre-Aggregations

As I mentioned before, there is a built-in way in Cube.js to accelerate queries’ performance. 
Cube.js can materialize query results in a table. It keeps them up to date and queries them instead of raw data. Pre-Aggregations can be quite complex, including multi-stage and dependency management. But for our case, the simplest `originalSql` pre-aggregation should be enough. It materializes the base SQL for the cube.

[Learn more about pre-aggregations here.](https://cube.dev/docs/pre-aggregations)

```javascript
import Funnels from 'Funnels';
import { eventsSQl, PAGE_VIEW_EVENT, CUSTOM_EVENT } from './Events.js';

cube('FunnelsUsageFunnel', {
  extends: Funnels.eventFunnel({ ... }),
  preAggregations: {
    main: {
      type: `originalSql`
    }
  }
});
```
## Visualize

There are a lot of way to visualize a funnel. Cube.js is visualization-agnostic, so pick one that works for you and fits well into your app design. In our example app, we use a bar chart from the Recharts library.

The Funnels package generates a cube with `conversions` and `conversionsPercent` measures, and `steps` and `time` dimensions. To build a bar chart funnel, we need to query the `conversions` measure grouped by the `step` dimension. The `time` dimension should be used in the filter to allow users to select a specific date range of the funnel.

Here is the code (we are using React and the Cube.js React Client):

```jsx
import React from "react";
import cubejs from "@cubejs-client/core";
import { QueryRenderer } from "@cubejs-client/react";
import CircularProgress from "@material-ui/core/CircularProgress";
import { BarChart, Bar, XAxis, YAxis, CartesianGrid, Tooltip } from "recharts";

const cubejsApi = cubejs(
  "YOUR-API-KEI",
  { apiUrl: "http://localhost:4000/cubejs-api/v1" }
);

const Funnel = ({ dateRange, funnelId }) => (
  <QueryRenderer
    cubejsApi={cubejsApi}
    query={{
      measures: [`${funnelId}.conversions`],
      dimensions: [`${funnelId}.step`],
      filters: [
        {
          dimension: `${funnelId}.time`,
          operator: `inDateRange`,
          values: dateRange
        }
      ]
    }}
    render={({ resultSet, error }) => {
      if (resultSet) {
        return (
          <BarChart
            width={600}
            height={300}
            margin={{ top: 20 }}
            data={resultSet.chartPivot()}
          >
            <CartesianGrid strokeDasharray="3 3" />
            <XAxis dataKey="x" minTickGap={20} />
            <YAxis />
            <Tooltip />
            <Bar dataKey={`${funnelId}.conversions`} fill="#7A77FF"/>
          </BarChart>
        );
      }

      return "Loading...";
    }}
  />
);

export default Funnel;
```

If you run this code in CodeSandbox, you should see something like this.

<iframe src="https://codesandbox.io/embed/nw87w1nnjm?fontsize=14" css="width:100%; height:500px; border:0; border-radius: 4px; overflow:hidden;" sandbox="allow-modals allow-forms allow-popups allow-scripts allow-same-origin"></iframe>

The above example is connected to the Cube.js backend from our event analytics app. 

In the next part, we’ll walk through how to build a dashboard and dynamic query builder, like one in Mixpanel or Amplitude. Part 4 will cover the Retention Analysis. In the final part, we will discuss how to deploy the whole application in the serverless mode to AWS Lambda.

[You can check out the full source code of the application here.](https://github.com/cube-js/cube.js/tree/master/examples/event-analytics)

[And the live demo is available here.](https://d1ygcqhosay4lt.cloudfront.net/)

Artyom Keydunov

Building an Open Source Mixpanel Alternative. Part 2: Conversion Funnels

_This is the first part of a tutorial series on building an analytical web application with [Cube.js](https://github.com/cube-js/cube.js). It expects the reader to be familiar with Javascript, Node.js, React, and have basic knowledge of SQL. [The final source code is available here](https://github.com/cube-js/cube.js/tree/master/examples/event-analytics) and [the live demo is here](https://d1ygcqhosay4lt.cloudfront.net/). The example app is serverless and running on AWS Lambda. It displays data about its own usage._

![](https://media.graphcms.com/8iQ3g6FUTDqvC73peT6q)

There is a category of analytics tools like Mixpanel or Amplitude, which are good at working with events data. They are ideal for measuring product or engagement metrics, such as activation funnels or retention. They are also very useful for measuring A/B tests. 

Although all these tools do a job, they are proprietary and cloud-based. That could be a problem when privacy is a concern. Or if one wants to customize how funnels or retention work under the hood. While traditional BI tools, like Tableau or Power BI, could potentially be used to run the same analysis, they can not offer the same level of user experience. The problem is that they are designed to be general business intelligence tools, and not specific for funnels, retention, A/B tests, etc.

With recent advancements in frontend development, it became possible to rapidly develop complex user interfaces. Things which took a week to build five years ago could be built in an afternoon nowadays. On the backend and infrastructure side, cloud-based MPP databases, such as BigQuery and Athena, are dramatically changing the landscape. The ELT approach, when data is transformed inside the database, is getting more and more popular, replacing traditional ETL. Serverless architecture makes it possible to easily deploy and scale applications.

All of these made it possible to build internal alternatives to established services like Mixpanel, Amplitude, or Kissmetrics. In this series of tutorials, we’re going to build a full-featured open-source event analytics system.

It will include the following features:

- Data collection;
- Dashboarding;
- Ad hoc analysis with query builder;
- Funnel analysis;
- Retention analysis;
- Serverless deployment;
- A/B tests;
- Real-time events monitoring;

The diagram below shows the architecture of our application:

![](https://media.graphcms.com/Q37GPFyT72clApTI6gDg)

In the first part of our tutorial, we’ll focus more on how to collect and store data. And briefly cover how to make a simple chart based on this data. The following parts focus more on querying data and building various analytics reporting features.

## Collecting Events
We’re going to use Snowplow Cloudfront Collector and Javascript Tracker. We need to upload a tracking pixel to Amazon CloudFront CDN. The Snowplow Tracker sends data to the collector by making a GET request for the pixel and passing data as a query string parameter. The CloudFront Collector uses CloudFront logging to record the request (including the query string) to an S3 bucket.

Next, we need to install Javascript Tracker. [Here is the full guide](https://github.com/snowplow/snowplow/wiki/javascript-tracker-setup).

But, in short, it is similar to Google Analytics’s tracking code or Mixpanel’s, so we need to just embed it into our HTML page.

```javascript
<script type="text/javascript">      
  ;(function(p,l,o,w,i,n,g){if(!p[i]){p.GlobalSnowplowNamespace=p.GlobalSnowplowNamespace||[];
   p.GlobalSnowplowNamespace.push(i);p[i]=function(){(p[i].q=p[i].q||[]).push(arguments)
   };p[i].q=p[i].q||[];n=l.createElement(o);g=l.getElementsByTagName(o)[0];n.async=1;
   n.src=w;g.parentNode.insertBefore(n,g)}} .  (window,document,"script","//d1fc8wv8zag5ca.cloudfront.net/2.10.2/sp.js","snowplow"));

  window.snowplow('newTracker', 'cf', '<YOUR_CLOUDFRONT_DISTRIBUTION_URL>’, { post: false });
</script>
```
[Here you can find how it is embedded into our example application.](https://github.com/cube-js/cube.js/blob/master/examples/event-analytics/frontend/public/index.html#L26-L33)

Once we have our data, which is CloudFront logs, in the S3 bucket, we can query it with Athena. All we need to do is create a table for CloudFront logs.

Copy and paste the following DDL statement into the Athena console. Modify the LOCATION for the S3 bucket that stores your logs.

```sql
CREATE EXTERNAL TABLE IF NOT EXISTS default.cloudfront_logs (
  `date` DATE,
  time STRING,
  location STRING,
  bytes BIGINT,
  requestip STRING,
  method STRING,
  host STRING,
  uri STRING,
  status INT,
  referrer STRING,
  useragent STRING,
  querystring STRING,
  cookie STRING,
  resulttype STRING,
  requestid STRING,
  hostheader STRING,
  requestprotocol STRING,
  requestbytes BIGINT,
  timetaken FLOAT,
  xforwardedfor STRING,
  sslprotocol STRING,
  sslcipher STRING,
  responseresulttype STRING,
  httpversion STRING,
  filestatus STRING,
  encryptedfields INT
)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
LOCATION 's3://CloudFront_bucket_name/AWSLogs/Account_ID/'
TBLPROPERTIES ( 'skip.header.line.count'='2' )
```
Now we are ready to connect Cube.js to Athena and start building our first dashboard.

## Building Our First Chart

First, install Cube.js CLI. It is used for various Cube.js workflows.

```bash
$ npm install -g cubejs-cli
```

Next, сreate a new Cube.js service by running the following command. Note, we are specifying Athena as a database here (-d athena) and template as serveless (-t serverless). Cube.js supports different configurations, but for this tutorial, we will use the serverless one.

```bash
$ cubejs create event-analytics-backend -d athena -t serverless
```

Once run, the `create` command will create a new project directory that contains the scaffolding for your new Cube.js project. This includes all the files necessary to spin up the Cube.js backend, example frontend code for displaying the results of Cube.js queries in a React app, and some example schema files to highlight the format of the Cube.js Data Schema layer.

The `.env` file in this project directory contains placeholders for the relevant database credentials.  For Athena, you'll need to specify the AWS access and secret keys with the [access necessary to run Athena queries](https://docs.aws.amazon.com/athena/latest/ug/access.html), and the target AWS region and [S3 output location](https://docs.aws.amazon.com/athena/latest/ug/querying.html) where query results are stored.

```bash
CUBEJS_DB_TYPE=athena
CUBEJS_AWS_KEY=<YOUR ATHENA AWS KEY HERE>
CUBEJS_AWS_SECRET=<YOUR ATHENA SECRET KEY HERE>
CUBEJS_AWS_REGION=<AWS REGION STRING, e.g. us-east-1>
# You can find the Athena S3 Output location here: https://docs.aws.amazon.com/athena/latest/ug/querying.html
CUBEJS_AWS_S3_OUTPUT_LOCATION=<S3 OUTPUT LOCATION>
```
Now, let’s create a basic Cube.js Schema for our events model. Cube.js uses Data Schema to generate and execute SQL; [you can read more about it here.](https://statsbot.co/docs/getting-started-cubejs)

Create a `schema/Events.js` file with the following content.

```javascript
const regexp = (key) => `&${key}=([^&]+)`;
const parameters = {
  event: regexp('e'),
  event_id: regexp('eid'),
  page_title: regexp('page')
}

cube(`Events`, {
  sql:
    `SELECT
      from_iso8601_timestamp(to_iso8601(date) || 'T' || "time") as time,
      ${Object.keys(parameters).map((key) => ( `url_decode(url_decode(regexp_extract(querystring, '${parameters[key]}', 1))) as ${key}` )).join(", ")}
    FROM cloudfront_logs
    WHERE length(querystring) > 1
    `,

  measures: {
    pageView: {
      type: `count`,
      filters: [
        { sql: `${CUBE}.event = 'pv'` }
      ]
    },
  },

  dimensions: {
    pageTitle: {
      sql: `page_title`,
      type: `string`
    }
  }
});
```
In the schema file, we create an Events cube. It is going to contain all the information about our events. In the base SQL statement, we’re extracting values from the query string sent by the tracker by using the `regexp` function. Cube.js is good at running transformations such this and it could also materialize some of them for performance optimization. We’ll talk about it in the next parts of our tutorial.

With this schema in place, we can run our dev server and build the first chart. 

Spin up the development server by running the following command.

```bash
$ npm dev
```

Visit [http://localhost:4000](http://localhost:4000), it should open a CodeSandbox with an example. Change the `renderChart` function and the `query` variable to the following.

```jsx
const renderChart = resultSet => (
  <Chart height={400} data={resultSet.chartPivot()} forceFit>
    <Coord type="theta" radius={0.75} />
    <Axis name="Events.pageView" />
    <Legend position="right" name="category" />
    <Tooltip showTitle={false} />
    <Geom type="intervalStack" position="Events.pageView" color="x" />
  </Chart>
);

const query = {
  measures: ["Events.pageView"],
  dimensions: ["Events.pageTitle"]
};
```

Now, you should be able to see the pie chart, depending on what data you have in your S3.

![](https://media.graphcms.com/MPZmJlTBQKeDk4260AEg)

In the next part, we’ll walk through how to build Conversion Funnels. In the third part we will make a dashboard and dynamic query builder, like one in Mixpanel or Amplitude. Part 3 will cover Retention Analysis. In the final part, we will discuss how to deploy the whole application in the serverless mode to AWS Lambda.

[You can check out the full source code of the application here.](https://github.com/cube-js/cube.js/tree/master/examples/event-analytics)

[And the live demo is available here.](https://d1ygcqhosay4lt.cloudfront.net)

Building an Open Source Mixpanel Alternative. Part 1: Collecting and Displaying Events

In this tutorial, we’ll explore how to add analytical features, such as reporting and dashboarding to an existing e-commerce web application with [Cube.js](https://github.com/statsbotco/cube.js). We’re going to use [Saleor](https://getsaleor.com/) as our e-commerce platform. It is powered by a GraphQL server running on top of Python 3 and a Django 2 framework. Both the storefront and the admin dashboard are React applications written in TypeScript and powered by Apollo GraphQL. We’ll add dashboarding and reporting capabilities to the admin dashboard with the Cube.js.

## Installing Saleor

Saleor server and the admin dashboard are located in a single repo , and the storefront is in the separate repo. For the purpose of the tutorial, we need only the server and the admin dashboard. It comes with very good documentation and detailed installation guide, which you can find here. Please follow the instructions on installation and them come back here.

Saleor server and the admin dashboard are located in a single repo [here](https://github.com/mirumee/saleor). It comes with very good documentation and detailed installation guide, which you can find [here](https://docs.saleor.io/docs/3.x/overview/introduction). Please follow the installation instructions and them come back here.

Once, it is installed, let’s populate our store with example products and orders, as well as the admin account. Run the following command in your terminal.

```bash
$ python manage.py populatedb --createsuperuser
```
Next, start the local server.

```bash
$ python manage.py runserver
```

Open [http://localhost:8000](http://localhost:8000) in your browser and it will show you your storefront. Since in previous step we loaded some example data, it will show you some categories and products inside. In this tutorial we’re going to work with the admin dashboard, which could be found here [http://localhost:8000/dashboard/next](http://localhost:8000/dashboard/next). Notice, `next` in the url - Saleor is updating the admin UI and we’re going to work the latest one. To access admin dashboard enter `admin@example.com` for email and `admin` for password.

This how it should look like after you log in.

![](https://media.graphcms.com/WBiIMVPTYsk53s1E5w3A)

## Installing Cube.js

Now, as we have Saleor up and running we can install Cube.js to start adding analytical features to our e-commerce dashboard. The Cube.js backend is a Node app, which could be run as a separate microservice or in a serverless mode. Let’s create our Cube.js backend instance.

Run the following commands in your terminal

```bash
$ npm install -g cubejs-cli
$ cubejs create saleor-analytics -d postgres
```

The above commands should create a new Cube.js application. Since we are using Postgres with Saleor we set it as a database type (-d). Cube.js use ‘dotenv’ to manage credentials, so we need to update .env file with our database's credentials.

```bash
# Update database credentials in the .env file

CUBEJS_DB_TYPE=postgres
CUBEJS_DB_NAME=saleor
CUBEJS_DB_USER=saleor
```
The next step is to tell Cube.js how to convert raw data in the database into meaningful analytics insights. This process is sometimes called data modeling. It is a way to translate business level definitions, such as customer lifetime value into SQL. In Cube.js it’s called schema and schema files are located in ‘schema’ folder. 

Cube.js CLI generates an example schema file - `schema/Orders.js`. Change it’s content to the following.

```javascript
cube(`Orders`, {
  sql: `select * from order_order`,

  measures: {
    count: {
      type: `count` 
    }
  },

  dimensions: {
    created: {
      type: `time`,
      sql: `created` 
    }
  }
});
```

Cube.js uses schema to generate a SQL code, which will run in your database and return a result back. A schema is also used to manage pre-aggregations and cache, you can learn more about it here. 

That is all we need to start our first analytics server. In the project folder run:

``` bash
$ npm run dev
```

It will spin up the development server at [http://localhost:4000](http://localhost:4000). If you open it in the browser it’ll show the codesandbox with an example on how to use an API with React.

Now, as we have Cube.js server up and running the next step is to add analytics section to our Saleor admin dashboard.

## Add Analytics Section to Saleor Dashboard

We’re going to add analytics section to to the Saleor dashboard. The admin dashboard is a React app written in Typescript, which is located in `saleor/static/dashboard-next` folder.  Within this folder create a new folder - `analytics`. it is going to contain all our code for analytics sections. First, we’ll add an analytics page, which is going to be a container for all of our charts.

```tsx
// Create analytics/index.tsx with the following content

import * as React from "react";

import DashboardPage from "./views/DashboardPage";

const Component = () => <DashboardPage />;

export default Component;

// Create analytics/views/DashboardPage.tsx with the following content
import * as React from "react";

import Container from "../../components/Container";
import PageHeader from "../../components/PageHeader";
import i18n from "../../i18n";

const DashboardPage = () => (
  <Container width="md">
    <PageHeader title={i18n.t("Analytics")} />
  </Container>
);

DashboardPage.displayName = "DashboardPage";
export default DashboardPage;
```

```tsx
// Add analytics route to index.tsx

import AnalyticsPage from "./analytics";

// Add analytics SectionRoute just after Home

<SectionRoute path="/analytics" component={AnalyticsPage} />
```

That should be enough to render our new blank analytics page at [http://localhost:8000/dashboard/next/analytics](http://localhost:8000/dashboard/next/analytics). You can try to open it in your browser. Next, let’s add Analytics item to the left menu. 

First, will add new icon component and then edit AppRoot.tsx to add menu item.

```tsx
// Create icons/Analytics.tsx with the following content

import createSvgIcon from "@material-ui/icons/utils/createSvgIcon";
import * as React from "react";

export const Analytics = createSvgIcon(
  <>
      <g id="Group" transform="translate(-188.000000, -237.000000)">
        <path d="M204,240 L208,240 L208,256 L204,256 Z M198,246 L202,246 L202,256 L198,256 Z M192,250 L196,250 L196,256 L192,256 Z M192,250" />
      </g>
  </>
);
Analytics.displayName = "Analytics";
export default Analytics;
```

```tsx
// Import icon and add new element to menuStructure array in the AppRoot.tsx

import Analytics from "./icons/Analytics";

// ...

const menuStructure: IMenuItem[] = [
  // ...
  {
     ariaLabel: "analytics",
     icon: <Analytics />,
     label: i18n.t("Analytics", { context: "Menu label" }),
     url: "/analytics"
  }
]
```

That’s it! Open admin dashboard in your browser and you should see the new menu item. Now, let’s add some charts to our Analytics Page.

![](https://media.graphcms.com/wRdN1FCnRaiECchciHAS)

## Create First Char

Let’s create a ChartCard React Component in `analytics/components/ChartCard.tsx`.  It will receive a query as an input and will encapsulate getting data from Cube.js API and then rendering the visualization. 

We need to install Cube.js javascript client with React wrapper and Bizcharts for visualizations. Bizcharts is easy to use visualization library for React apps based on G2. It comes with pretty nice default styles. Run the following commands in the Saleor root directory:

```bash
$ npm install @cubejs-client/core @cubejs-client/react bizcharts moment numeral --save
```

Our Cube.js server is running on [http://localhost:4000](http://localhost:4000) and API endpoint is located at [http://localhost:4000/cubejs-api/v1](http://localhost:4000/cubejs-api/v1), so we need to specify it in the apiUrl parameter when initializing Cube.js client instance. It also expects an API key, which you can copy from Cube.js server console output when you start it.

![](https://media.graphcms.com/eMpLqJUnTnetsgNmNvxD)

```tsx
// Create analytics/components/ChartCard.tsx with the following content

import * as cubejs from '@cubejs-client/core';
import { QueryRenderer } from '@cubejs-client/react';
import Card from "@material-ui/core/Card";
import { Axis, Chart, Geom, Tooltip } from 'bizcharts';
import * as moment from 'moment';
import * as React from "react";

import CardTitle from "../../../components/CardTitle";
import Skeleton from "../../../components/Skeleton";

const CHART_HEIGHT = 400;
const API_KEY = 'YOUR-CUBE-JS-API-KEY';
const API_URL = 'http://localhost:4000/cubejs-api/v1';
const cubejsApi = cubejs(API_KEY, { apiUrl: API_URL });

const renderChart = (resultSet) => (
  <Chart
    scale={{ category: { tickCount: 8 } }}
    height={CHART_HEIGHT}
    data={resultSet.chartPivot()}
    forceFit
  >
      <Axis name="category" label={{ formatter: val => moment(val).format("MMM DD") }} />
      {resultSet.seriesNames().map(s => (<Axis name={s.key} />))}
      <Tooltip crosshairs={{type : 'y'}} />
      {resultSet.seriesNames().map(s => (<Geom type="line" position={`category*${s.key}`} size={2} />))}
  </Chart>
);

const ChartCard = ({ title, query }) => (
  <Card>
    <CardTitle title={title} />
    <QueryRenderer
      query={query}
      cubejsApi={cubejsApi}
      render={ ({ resultSet }) => {
        if (!resultSet) {
          return (
            <div style={{ padding: "10px" }}>
              <Skeleton />
            </div>
          )
        }

        return renderChart(resultSet);
      }}
    />
  </Card>
);

ChartCard.displayName = "ChartCard";
export default ChartCard;
```

Now, let’s add our first chart in DashboardPage using ChartCard component.

```tsx
// Replace the content of analytics/views/DashboardPage.tsx with the following

import * as React from "react";

import CardSpacer from "../../components/CardSpacer";
import Container from "../../components/Container";
import PageHeader from "../../components/PageHeader";
import i18n from "../../i18n";

import ChartCard from "./../components/ChartCard";

const DashboardPage = () => (
  <Container width="md">
    <PageHeader title={i18n.t("Analytics")} />
    <CardSpacer />
    <ChartCard
      title={i18n.t("Orders Last 30 Days")}
      query={{
        measures: [ "Orders.count" ],
        timeDimensions: [{
          dateRange: ['2019-01-01', '2019-01-31'],
          dimension: 'Orders.created',
          granularity: 'day'
        }]
      }}
    />
  </Container>
);

DashboardPage.displayName = "DashboardPage";
export default DashboardPage;
```

That will render our first chart at [http://localhost:8000/dashboard/next/analytics](http://localhost:8000/dashboard/next/analytics).

## Add More Charts

First, let’s add support for other visualizations, such as a pie chart. The query to Cube.js will remain the same, we just need to support the pie chart in the `renderChart` methods. Also, we’ll add support for currency formatting using numeraljs. 

```tsx
// Replace the content of analytics/components/ChartCard.tsx with the following

import * as cubejs from '@cubejs-client/core';
import { QueryRenderer } from '@cubejs-client/react';
import Card from "@material-ui/core/Card";
import { Axis, Chart, Coord, Geom, Legend, Tooltip } from 'bizcharts';
import * as moment from 'moment';
import * as numeral from 'numeral';
import * as React from "react";

import CardTitle from "../../../components/CardTitle";
import Skeleton from "../../../components/Skeleton";

const CHART_HEIGHT = 400;
const API_KEY = 'YOUR-CUBE-JS-API-KEY';
const API_URL = 'http://localhost:4000/cubejs-api/v1';
const cubejsApi = cubejs(API_KEY, { apiUrl: API_URL });

const formatters = {
  currency: (val) => numeral(val).format('$0,0'),
  date: (val) => moment(val).format("MMM DD"),
  undefined: (val) => val
}
const renderLine = (resultSet) => (
  <Chart
    scale={{ category: { tickCount: 8 } }}
    height={CHART_HEIGHT}
    data={resultSet.chartPivot()}
    forceFit
  >
      <Axis name="category" label={{ formatter: formatters.date }} />
      {resultSet.seriesNames().map(s => (<Axis name={s.key} label={{ formatter: formatters[resultSet.loadResponse.annotation.measures[s.key].format] }} />))}
      <Tooltip crosshairs={{type : 'y'}} />
      {resultSet.seriesNames().map(s => (<Geom type="line" position={`category*${s.key}`} size={2} />))}
  </Chart>
);

const renderPie = (resultSet) => (
  <Chart height={CHART_HEIGHT} data={resultSet.chartPivot()} forceFit>
    <Coord type="theta" radius={0.75} />
    {resultSet.seriesNames().map(s => (<Axis name={s.key} />))}
    <Legend position="right" name="category" />
    <Tooltip showTitle={false} />
    {resultSet.seriesNames().map(s => (<Geom type="intervalStack" position={s.key} color="x" />))}
  </Chart>
);

const renderChart = (resultSet, visualizationType) => (
  {
    'line': renderLine,
    'pie': renderPie
  }[visualizationType](resultSet)
);

const ChartCard = ({ title, query, visualizationType }) => (
  <Card>
    <CardTitle title={title} />
    <QueryRenderer
      query={query}
      cubejsApi={cubejsApi}
      render={ ({ resultSet }) => {
        if (!resultSet) {
          return (
            <div style={{ padding: "10px" }}>
              <Skeleton />
            </div>
          )
        }

        return renderChart(resultSet, visualizationType);
      }}
    />
  </Card>
);

ChartCard.displayName = "ChartCard";
export default ChartCard;
```

Now, let’s define more measures and dimensions in the Cube.js schema. Open your Cube.js Service project and edit the Orders.js file in the schema folder.

```javascript
// Replace the content of schema/Orders.js with the following:

cube(`Orders`, {
  sql: `select * from order_order`,

  measures: {
    count: {
      type: `count`
    },

    totalNet: {
      sql: `total_net`,
      type: `sum`,
      format: `currency`
    },

    averageValue: {
      sql: `total_net`,
      type: `avg`,
      format: `currency`
    }
  },

  dimensions: {
    created: {
      sql: `created`,
      type: `time`
    },

    status: {
      sql: `status`,
      type: `string`
    }
  }
});
```

We have added two new measures — total sales and average sales. And one new dimension — status.

Now, let’s define all new queries in the DashboardPage component. We will display the line charts for total orders, total sales, and average order value (per day), and pie chart for the breakdown of order count by status. The default date range is set to last 30 days.

```tsx
// Replace the content of analytics/views/DashboardPage.tsx with the following
import * as moment from 'moment';
import * as React from "react";

import CardSpacer from "../../components/CardSpacer";
import Container from "../../components/Container";
import PageHeader from "../../components/PageHeader";
import i18n from "../../i18n";

import ChartCard from "./../components/ChartCard";

const cardContainerStyles = {
  display: "grid",
  gridColumnGap: "24px",
  gridTemplateColumns: "1fr 1fr",
  rowGap: "24px"
}

const timeDimensions = [{
  dateRange: [
    moment().subtract(30, 'days').format("YYYY-MM-DD"),
    moment().format("YYYY-MM-DD")
  ],
  dimension: 'Orders.created',
  granularity: 'day'
}];
const queries = [
  {
    query: {
      measures: ["Orders.count"],
      timeDimensions
    },
    title: i18n.t("Total Orders"),
    visualizationType: 'line',
  },
  {
    query: {
      measures: ["Orders.totalNet"],
      timeDimensions
    },
    title: i18n.t("Total Sales"),
    visualizationType: 'line'
  },
  {
    query: {
      measures: ["Orders.averageValue"],
      timeDimensions
    },
    title: i18n.t("Average Order Value"),
    visualizationType: 'line'
  },
  {
    query: {
      dimensions: ["Orders.status"],
      measures: ["Orders.count"]
    },
    title: i18n.t("Orders by Status"),
    visualizationType: 'pie'
  }
];

const DashboardPage = () => (
  <Container width="md">
    <PageHeader title={i18n.t("Analytics")} />
    <CardSpacer />
    <div style={cardContainerStyles}>
      {queries.map((query, index) => (
        <ChartCard key={index} {...query}  />
      ))}
    </div>
  </Container>
);

DashboardPage.displayName = "DashboardPage";
export default DashboardPage;
```
This will give us a fully working dashboard with four basic metrics. In the next tutorial, we’ll show you how to add dashboard filters and a query builder to let users build custom reports.

Use Cases

Multi-Tenant Analytics with Auth0 and Cube

Nginx Log Analytics with AWS Athena and Cube.js

Get Cube updates to your inbox

Building an Open Source Mixpanel Alternative. Part 2: Conversion Funnels

Building an Open Source Mixpanel Alternative. Part 1: Collecting and Displaying Events

Adding an E-Commerce Admin Dashboard to Saleor

Upgrade your data stack today