Artyom Keydunov

Amazon Athena

Building an Open Source Web Analytics Platform

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

Use Cases

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/)

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)

Amazon Athena

Building an Open Source Web Analytics Platform

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

Upgrade your data stack today