Funnel analysis is best used in order to understand how many users progress through the different stages towards a goal decided upon by a company. In other words, the conversion of users between the different stages.

For example, let’s say that there are 100 users that clicked on a banner and were redirected to the company’s landing page. Only 20 users filled out the contact information on the landing page, meaning that the conversion rate for moving from clicking on the banner to filling the contact information on the landing page was 20%. This process continues with registration, deposits, purchases, transactions, etc.

Funnel analysis is relevant and important for any online company, specifically online gaming and e-commerce companies. Whether you want to investigate micro-conversions (tutorial completions, social activity, etc.) or macro-conversions, funnel analysis is important in that it lets you identify which stages of user activity do and do not contribute to attaining the desired goal.

CoolaData provides an intuitive and easy to use widget for this analysis. You can find the tutorial here.

CoolaData also provides the ability to use CQL in order to enrich the analysis and deepen it.

Getting Started – Basic Syntax

There’s a specific syntax in CQL that is used in order to analyze the funnel.

Below are the relevant parameters for the SELECT” clause.

    • STEP_ID: The step (event) number from first to last. For example for the first step, the STEP_ID will equal 1, for the second step the STEP_ID will equal 2, etc.

    • STEP_NAME: The name of the step (or event). For example, “Login” or “Registration”. You can use an “OR” function to select any of multiple events as a single step. For example:

      STARTS WITH "Play song" OR "Next song"
      FOLLOWED BY "Like" OR "Share"
    • USER_COUNT: The number of users that have done the relevant step.

CLUSTER FUNNEL BY Clause

SELECT [STEP_ID, STEP_NAME, USER_COUNT, STEP_DURATION]
FROM cooladata
WHERE 
CLUSTER FUNNEL BY {session | user | [x] days} [EACH]}
  STARTS WITH  
  FOLLOWED BY [NOT]  ...
  END CLUSTER

The “WHERE” clause has unique syntax used only in CQL:

  • CLUSTER and END CLUSTER: These will border the conditions that you will use.

  • FUNNEL BY X days: This will enable you choose the time frame by which the user will have completed all the steps in the funnel.

  • STARTS WITH “X”: This will be the first event that the user starts with.

  • FOLLOWED BY “X”: This will be the next event that the user did. This command can be used multiple times, depending on the number of ç have been done.

Step 1 – Basic Statement

Below you’ll be able to see the basic implementation of the CQL query that will analyze the funnel, with two steps, without breakdown (group by) and additional filters (where clauses):

SELECT STEP_ID, STEP_NAME, USER_COUNT 
FROM cooladata 
WHERE date_range(BETWEEN 2013-05-01 AND 2013-06-30) 
CLUSTER FUNNEL BY 7 days 
  STARTS WITH "Play song" 
  FOLLOWED BY "Next song" 
  END CLUSTER

 

This will be the output of of the CQL query above:

FUNNEL01

 Step 2 – Adding a Breakdown

Below you’ll be able to see exactly the same query as was presented in step 1, with one difference – the additional parameter “age_range”, which is marked in bold red. Adding this property to the query will break all the steps down to this property. Please note that there is no need to add the “GROUP BY” command that we’re used to in SQL.

Please see the example below:

SELECT STEP_ID, STEP_NAME, USER_COUNT, age_range 
FROM cooladata 
WHERE date_range(BETWEEN 2013-05-01 AND 2013-06-30) 
CLUSTER FUNNEL BY 7 days 
  STARTS WITH "Play song" 
  FOLLOWED BY "Next song" 
  END CLUSTER

This will be the output of of the CQL query above:

Funnel2

Step 3 – Adding a Filter

Below you’ll be able to see exactly the same query as was presented in step 2, with one difference – the additional filter “user_country”, which is marked in bold red. Please note that this filter is added to the “WHERE” clause just before the Funnel CQL unique syntax, starting with “CLUSTER” as well as being able to use regular SQL operators “AND” and “OR” as for your company’s specific needs. In this example we will receive data only for users from France, Great Britain, and Germany.

SELECT STEP_ID, STEP_NAME, USER_COUNT, age_range 
FROM cooladata 
WHERE date_range(BETWEEN 2013-05-01 AND 2013-06-30) AND (user_country IN ("FR", "GB", "GR")) 
CLUSTER FUNNEL BY 7 days 
  STARTS WITH "Play song" 
  FOLLOWED BY "Next song"
  END CLUSTER

This will be the output of of the CQL query above:

Funnel3

Step 4 – Adding Duration Between Steps

Below you’ll be able to see exactly the same query as was presented in step 3, with two differences – the additional field “step_duration” and a second “Next Song” step, both marked in bold and red.

“STEP_DURATION” is a field which shows the time between steps. As opposed to “FUNNEL BY X DAYS”, which will limit the time between the first and final steps, “STEP_DURATION” will show the time between the first and second steps, the second and third step, and so on for all steps.

Note that this field will show “0.00”, because the first step doesn’t have a time difference.

Please see the example below:

SELECT STEP_ID, STEP_NAME, USER_COUNT, age_range, STEP_DURATION
FROM cooladata 
WHERE date_range(BETWEEN 2013-05-01 AND 2013-06-30) AND (user_country IN ("FR", "GB", "GR")) 
CLUSTER FUNNEL BY 7 days 
  STARTS WITH "Play song" 
  FOLLOWED BY "Next song" 
  FOLLOWED BY "Next song"
  END CLUSTER

This will be the output of of the CQL query above:

Funnel4

Step 5 – Adding a Filter to the Funnel’s Steps

Below you’ll be able to see exactly the same query as was presented in step 4, with a few differences – the additional “WHERE” clauses after each event of the funnel, all marked in bold red.

These “WHERE” clauses are specific for each event in the funnel and therefore can be done for as many events in the funnel that we desire.

Please see the example below:

SELECT STEP_ID, STEP_NAME, USER_COUNT, age_range, STEP_DURATION
FROM cooladata 
WHERE date_range(BETWEEN 2013-05-01 AND 2013-06-30) AND (user_country IN ("FR", "GB", "GR")) 
CLUSTER FUNNEL BY 7 days 
  STARTS WITH "Play song"
  FOLLOWED BY "Next song" WHERE (song_category='Rock')
  FOLLOWED BY "Next song" WHERE (song_category='Rock' or song_category='Jazz')
  END CLUSTER

This will be the output of of the CQL query above:

Funnel5

Step 6 – Exporting Users which Met the Funnel Criteria

Below you’ll be able to see exactly the same query as was presented in step 4, with one difference – instead of the fields chosen before, we selected “user_id”, which is marked in bold red.

What this will do is return a list of users which have completed the funnel criteria.

Please see the example below:

SELECT user_id
FROM cooladata
WHERE date_range(BETWEEN 2013-05-01 AND 2013-06-30) AND (user_country IN ("FR", "GB", "GR"))
CLUSTER FUNNEL BY 7 days
STARTS WITH "Play song"
FOLLOWED BY "Next song"
FOLLOWED BY "Next song"
END CLUSTER”

This will be the output of of the CQL query above:

Funnel6

Keep in Mind:

  1. It is possible to add more properties to the “SELECT” clause and it will be displayed per user.

  2. When selecting “user_id” you will be able to choose certain steps which won’t be included in the funnel. This can be done by writing “NOT FOLLOWED BY X”.
    For example:

    CLUSTER FUNNEL BY 7 days 
      STARTS WITH "Play song" 
      FOLLOWED BY "Next song" 
      NOT FOLLOWED BY "Next song" 
      END CLUSTER

    This query will return users where their first event is “Play song”, their second event was “next song”, and their third event wasn’t “Next song”.

A Few Extra Tips

  1. The Query Time Frame: Please keep in mind that the selected time frame (“date_range”) is also affected by time interval (“CLUSTER FUNNEL BY 7 days”) selected between the first and last event. The time frame that you choose is only for the first event.
    For example, let’s say that the time frame you chose in between 1/1/2014 and 1/2/2014 and the time interval is 7 days. The results you will receive will be until the 8/2/2014 for first events that occurred on 1/2/2014.

  2. Number of Events: The maximum number of events supported in the funnel is five.

  3. When choosing a filter, if you select a session property, be aware that all the events will only refer to the particular session property selected. For example, if you choose to filter events created only in the Chrome browser then those will be the only events shown in the funnel. This means that if in the same sessions there are also sessions created in Internet Explorer or Firefox, they won’t be shown in the funnel.
Print Friendly, PDF & Email