Data Visualization FAQs

How do I write a Custom SQL widget?

  1. In the dashboard, click Add Widget
  2. In the new widget, scroll down and select SQL Mode in the Widget Builder
  3. Enter in your SQL query into the field

The table name is the machine name of the Reporting Table you will be using.

The columns in the SELECT statement are the machine names of the Event and User Fields you will be using. Please note that if your Custom SQL query returns NULL or empty string ('') values, it will not be displayed correctly. For more information, see the article on Custom SQL.

Custom SQL Examples

Using variables and running totals

One of the features of using custom SQL when creating widgets is the ability to use variables. The creation and use of variables follows the same structure as that of mySQL. A mySQL Variable is a virtual field that is created in the query and does not exist in the table. A variable is denoted using the @ symbol with a name to accompany (i.e. @x or @y)

Variables can be used for setting a constant number for use in formulas. A variable works by using the @ symbol and assigning it a name:

SELECT( @y:=0 ) v

The variable here is named y and referenced as @y. We initially set the variable to 0, so any references to @y at this point would result in 0. If we want to update the variable to add a + 1 for each row, we could use the following formula:

@y:=@y+1 as 'variable'

This will result in a column where @y increments by 1 with each row:

Date variable
2014-05-01 1
2014-05-02 2
2014-05-03 3

You can also use variables in mySQL to create a running sum, reference a value on a given date or action, or count rows in the table. For example, to create a running total the following statement can be used:

  @x:= b.om_total_revenue + @x
(SELECT @x:=0) a,
FROM table

Using functions

Functions can be used for building widgets. Supported functions include the type: control, numeric, date, time, text search, and cast. Here is an example of supported functions:

  IFNULL(<expr>,0) AS field1,
  COUNT(DISTINCT om_uid) AS field2,
  SUM(IF(<expr>,<true>,<false>)) AS field3

Joining reporting tables

Joins can be used to join two reporting tables on a shared column of data, such as om_activity_date. Here is a basic example / template of a join clause:

  FROM a ) a
  FROM b) b

When joining two different tables the filters may need to point to different field/table names. To tell the filters to use a specific table name we can employ the $WHERE and $HAVING aliases.

SELECT sm.`om_activity_date`, sm.`om_new_users`, cm.`om_new_users` AS `cm_new_users`
FROM (SELECT date(a.activity_date) AS `om_activity_date`, SUM(a.`new_users`) AS `om_new_users` 
    FROM `om_day_gmet_eng` a
    WHERE (a.`project_name` = 'My Game') AND $WHERE(om_day_gmet_eng, a) 
    GROUP BY date(a.activity_date)) AS `sm`
    (SELECT `om_activity_date` AS `om_activity_date`, SUM(IF(om_retained_days = 0, om_users, 0)) AS `om_new_users` 
    FROM `md_mnt1_bb7e` 
    GROUP BY `om_activity_date`) AS `cm` ON sm.`om_activity_date` = cm.`om_activity_date`
ORDER BY sm.`om_activity_date` ASC

How do I use Filters with a Custom SQL Widget?

You can use the filters on the side bar by passing in the following variables:

WHERE $WHERE(<machine_name>,<table alias>)
HAVING $HAVING(<machine_name>,<table alias>)

If due to sub-queries or joins the table name does not match the machine-name of the reporting table, then it can be specifically changed using the alternative syntax. Multiple $WHERE and $HAVING are possible.

To find a table's machine name, navigate to Data > Reporting Tables

A select statement with variables might look like this:

    count(distinct game) as games
        SUM(users) AS users
    FROM inner_table #assume machine name is "inner_table_mn"
    GROUP BY 1,2
) AS outer_table
WHERE $WHERE(inner_table_mn,outer_table)
HAVING $HAVING(inner_table_mn,outer_table)

Note: The columns in the select statement must match the machine names of the fields being used in the filter. For example, Activity Date is om_activity_date and the column cannot be aliased as anything else.

When should I use Conditions in a Widget versus Filters on a Dashboard?

And important distinction is between Widget Conditions and Dashboard Filters. The similarity is that both are effectively modifying either the WHERE or HAVING clause of an underlying query. The main difference is that Conditions are applied within a widget itself, and do not effect any other widgets, while a Filter effects all widgets within a dashboard that have the specific data field in their underlying reporting table.

In general, dashboard filters are easier to maintain as they are generally visible at the dashboard level. Widget Conditions can be useful if a specific widget's scope needs to be narrow (i.e. showing only people who have spent more than $50, only people who have gotten past level 5, etc), but as the Condition is not visible unless a user is editing the Widget, it may not be known that there is a Condition.

How to use a Data Field with numeric values as a dimension?

By default, Omniata recognises numerical-value Data Fields as Measures in Reporting Tables. If you have a numerical Event Field, Custom User Field or Table Field and you want to use it as a Dimension, follow these instructions. The basic assumption is that the Data Field is question is created and ready to be used.

  1. Open the setup for the Reporting Table in question. It can be a new Reporting Table or an old one which you are editing.

  2. Search the Data Field and drag-and-drop it as a Dimension. In this example the Field is called "Tutorial Step Id". Save the Reporting Table. Remember to run a job so that the Reporting Table contains data for the new Data Field.

  3. Open the setup for the Widget in question. It can be a new Widget or an old one which you are editing. Select the Reporting Table in question to be used for the Widget. In this example the Reporting Table is called "Num Field Test".
  4. The Data Field is listed as Measure in the Design Mode. Click the options of the Data Field and select Make Dimension.

  5. Notice that the Data Field in question is now listed as Dimension in Data Fields section. Use the Data Field as Dimension in the Widget by dragging it to Setup section.

How do I numerically arrange the axes in a Widget?

Sometimes when using Custom SQL mode, it's desireable to have a numeric value for the x-axis. Examples include levels, tiers, the count of an event occurring, etc. In some cases the widget will not display the information in the desired order, such as displaying numeric levels zero through ten as 0, 1, 10, 2, 3, 4, 5, 6, 7, 8, 9. This is because the system is recognizing the level field as a string, and sorting the field in lexicographic order. To have the numeric field be sorted numerically, simply alter the query from this:

    SUM(revenue) AS revenue
FROM revenue_table

to this:

    CONCAT(level, '') AS level,
    SUM(revenue) AS revenue
FROM revenue_table

Concatenating the level field with an empty string will force it to be a string data type, which will ensure the level field is sorted numerically.

How do I automatically request the underlying data set of a Widget?

To automate data pull for a specific widget you need to generate a user token and request the CSV and JSON

Generating a User Token

  1. Go to Configurations > Account > Account Settings

  2. Click Generate New Token

Requesting the CSV Data of a Widget

  1. Go to your dashboard
  2. Locate your widget
  3. Right Click the Export to CSV option in the drop down
  4. Copy the URL address using the Copy Link Address
  5. Append the following parameters to the URL:
    • &auth_token=TOKEN
  6. The URL can now be used in requests that haven't authenticated with Omniata

Requesting the JSON Data of a Widget

  1. Go to your dashboard
  2. Locate your widget
  3. Right Click the Debug option in the drop down
  4. Copy the URL address from the Debug option
  5. Append the following parameters to the URL:
    • &auth_token=TOKEN
  6. The URL can now be used in requests that haven't authenticated with Omniata

This article was last updated on October 6, 2015 23:15. If you didn't find your answer here, search for another article or contact our support to get in touch.