Welcome to Medallia Journey Analytics!

Medallia Journey Analytics provides an end-to-end Behavioral Analytics solution that tracks, enriches and stores your events. You can then use the Medallia Journey Analytics web interface to easily generate insights from these events.

Getting Started with Behavioral Analytics

Three easy steps will get you started generating user behavior insights on your own:

  1. Plan which events to track
  2. Implement Medallia Journey Analytics
  3. Build your first Dashboard

Within a couple of hours you’ll start getting events in the Dashboard and can query them in the Medallia Journey Analytics interface or database!

What Are Behavioral Analytics?

Behavioral analytics observe the user and the user’s behavior over time and show a sequence of events performed by users. This is also called a funnel of events – which is a sequence of specific events that occur in a specific order.

Medallia Journey Analytics is a complete behavioral analytics data analysis platform that connects all your event data from all your data points. It unifies data from a multitude of external and internal sources and inspects it as a single unit, thus facilitating time‑series behavior analysis that yields behavioral insights.

Medallia Journey Analytics provides behavioral analytics widgets and behavior query language (CQL) to make advanced behavioral analysis easy.

How Do Behavioral Analytics Differ from Regular Analytics?

Basic analytics (such as in KPI reports) differ from behavioral analytics in that regular analytics typically display quantitative reports that count data. For example:

  • Count 1 – How many people were leads?
  • Count 2 – How many people received sales calls?
  • Count 3 – How many people made a purchase?

Regular analytics display discrete (distinct) values, meaning that the total quantity of Counts 1, 2 and 3, regardless of their relationship to each other. This means that Count 1 shows the total number of people who were leads, Count 2 shows the total number of people to which home sales calls were made and Count 3 shows the total number of people who made a purchase, regardless of whether they were a lead and regardless of whether a sales call was made to them.

In a Behavioral Analytics report, Count 1 is the same as in the KPI report; however, Count 2 is a subset of Count 1, meaning that it represents the quantity of people who were leads and to whom sales calls were made, and Count 3 is a subset of Count 2, meaning that it represents the people who were leads, to whom sales calls were made and who made a purchase.

Print Friendly, PDF & Email

Standard SQL Dialect

Overview

The underlying Database behind Cooladata is Google BigQuery. Cooladata has added extensions and processing methods in order to improve BigQuery’s performance, run time as well as enables easier behavioral analysis with it’s CQL functions.

Currently, BigQuery supports two SQL dialects: standard SQL and legacy SQL. There are a few differences between the two dialects, including syntax, functions, data types and semantics. BigQuery standard SQL is compliant with the SQL 2011 standard and also includes extensions that support querying nested and repeated data.

Up to August 2018, Cooladata enabled running SQL scripts using Legacy SQL only. Cooladata now enables running freehand queries over Cooladata using Standard SQL, as part of a strategic plan to migrate Cooladata to operate solely in Standard SQL, encompassing all the advantages of the language, including performance enhancements, query optimizations and superior functionality.

Note! Standard SQL in Cooladata is still in beta. Please contact us with any issues or questions.

Using Standard SQL in Queries

Standard SQL is currently only supported in freehand queries, in the following features:

  • CQL report
  • Aggregation tables
  • R/Python Reports
  • Models
  • CQL Segments
  • CQL alert builders

Note! Using the CQL behavioral extensions (such as the Cohort, Funnel and Path functions) is not yet supported when using Standard Dialect.

To use Standard SQL, turn on the Standard SQL toggle in the top right hand side of the query editor:

 

 

Make sure you use Standard SQL syntax when writing the query. We’ve gathered a few syntax differences for you to start with. For more information see BigQuery documentation or contact support@cooladata.com:

Casting and Conversion Functions

In Legacy SQL, conversion and casting was done using <data type> (<expression>)

For example (Legacy):

SELECT float(1), float('87.35'), float('david') 
|
returns: 1.0, 87.35, null

In Standard SQL, conversion and casting is done by calling the CAST or SAFE_CAST functions.

For example (Standard):

SELECT CAST(1 AS FLOAT), CAST('87.35' AS FLOAT), CAST('david' as FLOAT)
|
returns: 1, 87.35, error

When using CAST, a query can fail if BigQuery is unable to perform the cast. For example, CAST('david' as FLOAT)returned an error.

When using SAFE_CAST, returns NULL instead of raising an error:

SELECT SAFE_CAST(1 AS FLOAT), SAFE_CAST('87.35' AS FLOAT), SAFE_CAST('david' as FLOAT)
|
returns: 1, 87.35, NULL

 

Date and Time Functions

All Cooladata date_range functions, such as last n days, current month, previous quarter, etc. are supported in Standard SQL as well.

The main difference is in the extraction and date functions:

EXTRACT

The EXTRACT functions returns the value corresponding to the specified date part of a date or timestamp expression.

In Legacy SQL, extracting a part of a date would work like so: part(timestamp/date expression) would return the month number of the timestamp.

For example (Legacy):

SELECT MONTH(event_time_ts) --when event_time_ts is: 2018-10-03 00:01
|
returns: 10

In Standard SQL, extracting a part of a date needs to use the EXTRACT function: EXTRACT(part FROM date_expression)

For example (Standard):

SELECT EXTRACT(MONTH FROM event_time_ts) --when event_time_ts is: 2018-10-03 00:01
|
returns: 10

DATE_ADD

The DATE_ADD function adds a specified time interval to a DATE or TIMESTAMP.

In Legacy SQL, DATE_ADD worked in the following syntax: DATE_ADD(timestamp or date expression,interval, interval_units)

For example (Legacy):

SELECT DATE_ADD(TIMESTAMP('2018-10-01 02:03'), 5, 'DAY')
|
returns: 2018-10-06 02:03

In Standard SQL, DATE_ADD only works with a date_expression (see TIMESTAMP_ADD for timestamp expression), using in the following syntax: DATE_DIFF(date_expression, date_expression, date_part)

For example (Standard):

SELECT DATE_ADD(DATE('2018-10-01'), INTERVAL 5 DAY)
|
returns:  2018-10-06 00:00

To add a time interval to a timestamp expression, use TIMESTAMP_ADD

For example (Standard):

SELECT TIMESTAMP_ADD(TIMESTAMP('2018-10-01 02:03:00'), INTERVAL 5 DAY)
|
returns: 2018-10-06 02:03

See the BigQuery reference for the supported interval units.

DATE_DIFF

The DATE_DIFF function returns the difference between two specified dates or timestamps.

In Legacy SQL, the function is called: DATEDIFF (no underscore) with the following syntax:  DATEDIFF(<timestamp1>,<timestamp2>) and returns the number of days between two TIMESTAMP data types. The result is positive if the first TIMESTAMP data type comes after the second TIMESTAMP data type, and otherwise the result is negative.

For example (Legacy):

SELECT DATEDIFF(TIMESTAMP('2012-10-02 05:23:48'), TIMESTAMP('2011-06-24 12:18:35'))
|
returns: 466

In Standard SQL, as with the DATE_ADD function, DATE_DIFF is only supported for DATE inputs. The function is used with the following syntax: DATE_DIFF(date_expression, date_expression, date_part). Returns the number of date_part boundaries between the two date_expressions. If the first date occurs before the second date, then the result is non-positive.

For example (Standard):

SELECT  DATE_DIFF(DATE('2012-10-02 05:23:48'), DATE('2011-06-24 12:18:35'), DAY)
|
returns:  466

To return the difference between 2 timestamp expressions use TIMESTAMP_DIFF. Returns the number of whole specified date_part intervals between two timestamps. The first timestamp_expression represents the later date; if the first timestamp_expression is earlier than the second timestamp_expression, the output is negative. Throws an error if the computation overflows the result type, such as if the difference in microseconds between the two timestamps would overflow an INT64 value.

For example (Standard):

SELECT TIMESTAMP_DIFF(TIMESTAMP('2012-10-02 05:23:48'), TIMESTAMP('2011-06-24 12:18:35'), DAY)
|
returns:  465

 

Count Functions

Both legacy SQL and standard SQL contain a COUNT function. However, each function behaves differently, depending on the SQL dialect you use.

In legacy SQL, COUNT(DISTINCT x) returns an approximate count. In standard SQL, it returns an exact count. For an approximate count of distinct values that runs faster and requires fewer resources, use APPROX_COUNT_DISTINCT.

NOT IN conditions and NULL Values

Legacy SQL does not comply with the SQL standard in its handling of NULL with NOT IN conditions, whereas standard SQL does.

UNNEST function

The UNNEST function takes an ARRAY and returns a table, with one row for each element in the ARRAY. You can also use UNNEST outside of the FROM clause with the IN operator.

For example:

SELECT * FROM UNNEST ([1, 2, 3]) as unnest_alias;

Returns:

Unnest_alias

1

2

3

For input ARRAYs of most element types, the output of UNNEST generally has one column. This single column has an optional alias, which you can use to refer to the column elsewhere in the query. ARRAYS with these element types return multiple STRUCT columns.

OFFSET clause

OFFSET specifies a non-negative skip row of type INT64, and only rows from that offset in the table will be considered.

This clause accept only literal or parameter values.

 

Aggregate Functions

An aggregate function is a function that performs a calculation on a set of values.

ARRAY_AGG

ARRAY_AGG function returns an ARRAY of column values, this function supports all types except ARRAY.

Syntex:

ARRAY_AGG([DISTINCT] column [{IGNORE|RESPECT} NULLS]

         [ORDER BY key [{ASC|DESC}] [, ... ]]  [LIMIT n]) [OVER (...)]

For example:

SELECT FORMAT("%T", ARRAY_AGG(DISTINCT x)) AS array_agg

FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x

Returns:  1, -2, 3, NULL]

STRING_AGG

STRING_AGG function returns a value (either STRING or BYTES) obtained by concatenating non-null values.

If a delimiter is specified, concatenated values are separated by that delimiter; otherwise, a comma is used as a delimiter.

Syntex:

STRING_AGG([DISTINCT] expression [, delimiter]  [ORDER BY key [{ASC|DESC}] [, ... ]] [LIMIT n]) [OVER (...)]

For example:

SELECT STRING_AGG(fruit, " & ") AS string_agg

FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit

Returns:  apple & pear & banana & pear

ARRAY_CONCAT_AGG

ARRAY_CONCAT_AGG function concatenates elements from an expression of type ARRAY, returning a single ARRAY as a result. This function ignores NULL input arrays, but respects the NULL elements in non-NULL input arrays.

Syntex:

ARRAY_CONCAT_AGG(expression  [ORDER BY key [{ASC|DESC}] [, ... ]]  [LIMIT n])

For example:

SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x)) AS array_concat_agg FROM (

 SELECT [NULL, 1, 2, 3, 4] AS x

 UNION ALL SELECT [5, 6])

Returns: [NULL, 1, 2, 3, 4, 5, 6]

Array Functions

ARRAY

The ARRAY function returns an ARRAY with one element for each row in a subquery.

For example:

SELECT ARRAY

 (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) AS new_array

Returns: [1, 2, 3]

ARRAY_CONCAT

ARRAY_CONCAT function concatenates one or more arrays with the same element type into a single array.

For example:

SELECT ARRAY_CONCAT([1, 2], [3, 4], [5, 6]) as count_to_six;

Returns:[1, 2, 3, 4, 5, 6]

ARRAY_TO_STRING

ARRAY_TO_STRING function returns a concatenation of the elements in array_expression as a STRING. The value for array_expression can either be an array of STRING or BYTES data types.

For example:

SELECT ARRAY_TO_STRING(["apples", "bananas", "pears", "grapes"] , '--')

Returns: apples--bananas--pears--grapes

ARRAY_LENGTH

ARRAY_LENGTH function returns the size of the array. Returns 0 for an empty array. Returns NULL if the array is NULL.

For example:

SELECT ARRAY_LENGTH(["apples", "bananas", "pears", "grapes"])

Returns:  4

ARRAY_REVERSE

ARRAY_REVERSE function returns the input ARRAY with elements in reverse order.

For example:

SELECT ARRAY_REVERSE(["apples", "bananas", "pears"])

Returns:  ["pears", "bananas", "apples"]

OFFSET and ORDINAL

Accesses an ARRAY element by position and returns the element. OFFSET means that the numbering starts at zero, ORDINAL means that the numbering starts at one.

For example:

SELECT list, list[OFFSET(1)] as offset, list[ORDINAL(1)] as ordinal

Returns:

| list                              | offset | ordinal |

| [apples, bananas, pears, grapes] | bananas | apples  |

SAFE_OFFSET and SAFE_ORDINAL

SAFE functions identical to OFFSET and ORDINAL, except returns NULL if the index is out of range.

For example:

SELECT list, list[OFFSET(3)] as offset, list[ORDINAL(3)] as ordinal

Returns:

| list                  | offset | ordinal |

| [coffee, tea, milk]   | NULL | milk |

SELECT * EXCEPT / REPLACE

A SELECT * EXCEPT statement specifies the names of one or more columns to exclude from the result. All matching column names are omitted from the output.

For example:

SELECT * EXCEPT (order_id) →  returns all the column except             ‘order_id’ column.

A SELECT * REPLACE statement specifies one or more expression AS identifier clauses. Each identifier must match a column name from the SELECT * statement. In the output column list, the column that matches the identifier in a REPLACE clause is replaced by the expression in that REPLACE clause.

For example:

SELECT * REPLACE ("widget" AS item_name) → the ‘item_name’ column returns "widget" value. 

Filtering selected tables using _TABLE_SUFFIX

To restrict the query so that it scans an arbitrary set of tables, use the _TABLE_SUFFIX pseudo column in the WHERE clause. The _TABLE_SUFFIX pseudo column contains the values matched by the table wildcard.

For example:

The followed query scans only the tables customer_0 and customer_4.

SELECT *

FROM `external.mySqlData.customer_*`

WHERE ( _TABLE_SUFFIX = '0'

   AND _TABLE_SUFFIX = '4' )

 

Using Expressions in Standard SQL

Since the Virtual Properties of type expression use SQL syntax when called in a query, the expression needs to match the query dialect type. To support Standard SQL dialect when using expressions, we have provided a way to enter the Standard SQL expression in the expression configuration.

To do so, in the expression configuration click on the “Advanced” section – there you can configure the Standard SQL expression. Click on “Copy legacy to standard expression” to copy the Legacy expression to the Standard one.

When using a Standard SQL query, the system will use the Standard SQL expression and if empty, it will try to use the Legacy one (in most functions the syntax remains the same between the two dialects).

 

Using Standard SQL in Query API

When querying Cooladata using API requests or any other way outside the UI (like when using our JDBC connection) – you can turn on standard SQL using one of the following methods:

  1. Using the SQL annotation: #standardSQL in the beginning of the query:
    #standardSQL
    SELECT  DATE_DIFF(DATE('2012-10-02 05:23:48'), DATE('2011-06-24 12:18:35'), DAY)
    |
    returns:  466
  2. Adding the standard parameter as part of the queryAPI request:
    https://app.cooladata.com/api/v2/projects/<project id>/cql?standard=true
Print Friendly, PDF & Email

Step 1 – Plan which events to track

Planning

Try to answer the following questions when planning which events to track:

  1. What are your objectives?
    Think about your business goals and strategies in order to determine the purpose of the data that you want to extract from the events sent to CoolaData. For example, optimizing customer retention, increasing conversions, expanding business leads, growing revenue or enhancing usability.
  2. Which data enables you to achieve these objectives?
    Think about the kind of information that could be sent from your website that would enable you to achieve these business objectives. You can track anything that anyone does in your website and anything that happens at any specific point in time.
  3. Write It Down:
    Each business objective is typically comprised of a series of events that lead up to a target event, such as a purchase. We recommend sending an event for each stage of a user’s journey (funnel) through your website. List the events and the data properties that could be sent to CoolaData at various stages of your users’ experience.

Events

Events represent the path which your users follow through your site.

Examples of typical paths could be:

  • eCommerce: search → view_product_details → add_to_cart → click_checkout → purchase.
  • Gaming: login → start_game → level_up → purchase_bonus_item.
  • Media&Publishing: view_homepage → view_article → click_video → video_completed.

 

Properties

Cooladata supports events with any number of properties, and automatically enriches them with additional properties that are often used.

 

Events by industry

We’ve created a list of recommended events and properties to implement for each industry. See the following links for your industry:

 

 

 

Print Friendly, PDF & Email

Step 2 – Implement Cooladata

Sign up to Cooladata

  1. Go to http://app.cooladata.com.
  2. Click Sign up.
  3. Fill in the form and click Sign up
  4. Wait until you receive an email with a registration link to the email you signed up with, and click the link to log in.

 

Create a Project

To create a new project:

  1. In the top right corner, click the Create your first project button.
  2. Enter the name of the project.
  3. Click CREATE.

With CoolaData, you can put all your data into a single project or you can divide your data up among multiple projects – as you see fit.

Why create multiple projects? 

You control which dashboards, reports and publications users see. However, Admin users can access all the data in any project to which they are allowed access. To control the data that each CoolaData Admin users can access you can create multiple CoolaData projects.

For example, in an ecommerce site, you might have different stores – each belonging to a different company. Therefore, you might only want each company’s Admin user to see the data of their own company. In this case, you should create a project for each company, so that each Admin user can only access their own data in dashboards, publications and using the CoolaData API.

 

Track Events

To start tracking events using the Cooladata trackers:

  1. After creating a project, if the page shown is not already displayed, select Project – Tracking.
  2. Choose the required OS and follow the on-screen instruction.

See more details and implementation options in the Sending Data section.

 

Monitor Incoming Events

CoolaData enables you to monitor incoming events so that you can verify proper integration with your application/website, regardless of the type of Tracker you use to send events to CoolaData.

As each event is received by CoolaData, it is classified as either valid or invalid and is displayed in the Live Events window within approximately five minutes. Valid events undergo additional processing by CoolaData and within an hour or two after being received, may be reclassified as invalid. In order to investigate the reason for invalid events, CoolaData provides a details option in the Live Events window and enables you to query its database for invalid events. See Handling Invalid Events for more information.

The Live Events page is used to monitor your incoming events to CoolaData. Open the page from the main menu Project – Live Events.

Events are shown in near realtime – from the time you send an event it may take up to a minute to appear on this page. The page shows the 500 most recently received events, sorted by the time they were received, with the most recent events appearing first. Click any column title to sort the list.

Each row represents an event. Click a row to see the raw JSON format it was sent in.

You can search the list or specific columns in the list from the top left search box.

The columns displayed in this list can be customized from the top right icon. The list of columns contains all available common properties in the project, as well as the following information:

  • raw_data: displays the original JSON sent (can also be seen by clicking the event).
  • Validity: an events can be valid – will be saved, invalid – will not be saved, or pending – a new event that will be saved if doesn’t conflict with other events in the project schema.
  • invalidComments: invalid events will specify the rejection reason in this column.
  • extraComments: pending events will specify the reason in this column.
Print Friendly, PDF & Email

Step 3 – Build your first dashboard

Create a new dashboard

  1. Open your workspace in the CoolaData Administrator console and select Dashboards 5-3.
  2. Click the Add + button. The following displays:
    GSSC 7
  3. Enter a name for the Dashboard and click Create. The new dashboard is displayed.
  4. To change the dashboard’s name, simply click the name and type over it.3-2

Add reports to the dashboard

  1. From the top right corner click the Add Reports button.
  2. Either choose Saved Reports to add an existing report, or New Report to create a new one. From here you can also add filters and descriptions to the sheet.
  3. To edit a report in a dashboard click the Edit button in the top right corner of the report. Note that you must have edit permission on the report to do this (only the report creator or project admin).
    3-9

 

Events will show up in the Dashboard within an hour or two of being received by CoolaData.

 

Share your Dashboard

The dashboard owner and editors can share a dashboard with anyone who has permission to the same projects. This gives them access to the dashboard and notifies them by email.

  1. In the dashboard, select Share GSSC 23 from the top right corner. The following panel opens:
    GSSC 21
  2. Copy the link and send it to anyone who already has permission to the dashboard to access it directly.
  3. In the Invite people field, select the people with whom to share this dashboard, and assign them View or Edit permissions. Only people who already have permission to the project can be invited to share your dashboard.
    • View: can see the dashboard and change the filter and date range for their own usage.
    • Edit: can make any change to the dashboard.
  4. Users who already have access to the dashboard are listed below – from here you can manage (change/remove) permission of users who already have access to the dashboard.

 

 

You can also

Add sheets

  1. Click on the Add + button to the right of the sheet tabs.
  2. Click on the down arrow to next to the sheet name to Rename, Duplicate or Delete it.

Set the dashboard date range

This timeframe applies to all reports in the dashboard unless the report is set to ignore external date range.

  1. Click the dates at the top right:  GSSC 18
  2. Select a timeframe for the Dashboard.

Set the dashboard filters

These filters apply to all reports in the dashboard unless the report is set to ignore external filters.
See more in Dashboard Filters.

  1. Click the Dashboard Filters 3-12 button at the top right.
  2. Set the desired filters and click apply.

Edit saved dashboards

You can open any dashboard you have permission on.
You can only edit/delete a dashboard if you are its owner, or if you were given editor permission on it.

  1. Open CoolaData and select Dashboards 5-3 – a list of the dashboards to which you have permission is displayed.
  2. Click any row to open the dashboard.
  3. To delete a dashboard click the row options and choose Delete, or from inside the dashboard open the dashboard option and choose Delete.
Print Friendly, PDF & Email