Simple time series metric

Here is an example of a fusion chart recently created using MySQL, Perl, PHP and Fusion charts with fudged data. X axis is date & hour for last 7 days.  The data is pulled from MySQL and generates a XML file for consumption by PHP and Fusion.

The time data is in UTC (unix timestamp and first second of each hour. For example, 1262307600 => 2010-01-01 01:00:01) summarized with other dimensions and metrics. The simplified fact table looks like

ts_begin_hr, dim_1, dim_2, metric_1, metric_2, etc.

The query used to pull required report data.

         SELECT 'dim1' dim, concat(D_H.d, ' ', D_H.hr) pst,

             CASE WHEN P.met1 IS NULL THEN  0.0  ELSE P.met1 end METRIC

         FROM

            (

             SELECT d.d, h.hr 

             FROM

              (SELECT '00' hr UNION SELECT '01' UNION SELECT '02' UNION SELECT '03' UNION SELECT '04'

               UNION SELECT '05' UNION SELECT '06' UNION SELECT '07' UNION SELECT '08' 

               UNION SELECT '09' UNION SELECT 10 UNION  SELECT 11 UNION SELECT 12 

               UNION SELECT 13  UNION SELECT 14 UNION  SELECT 15 UNION SELECT 16 

               UNION SELECT 17 UNION SELECT 18 UNION   SELECT 19 UNION SELECT 20 

               UNION SELECT 21 UNION SELECT 22 UNION SELECT 23

              ) h

              CROSS JOIN

              (SELECT full_date d FROM DW_DB.dim_date

               WHERE full_date BETWEEN date_add(current_date(), interval -6 day) AND current_date()

              ) d  -- 1

            ) D_H

            LEFT OUTER JOIN

            (

              SELECT 'dim1' dim,

                  , date(convert_tz(from_unixtime(ts_begin_hr), 'GMT', 'America/Los_Angeles')) d

                  , hour(convert_tz(from_unixtime(ts_being_hr), 'GMT', 'America/Los_Angeles')) hr

                  , sum(met1) met1

              FROM DW_DB.FACT_TABLE FT       

              JOIN DW_DB.DIM_TABLE DM

                ON FT.dim_id = DM.dim_id

              WHERE from_unixtime(ts_begin_hr) >= date_add(now(), interval -7 day)

              GROUP BY dim, d, hr

            ) P  -- 2

         ON D_H.d = P.d and D_H.hr = P.hr

         WHERE D_H.d < date(convert_tz(now(), 'GMT', 'America/Los_Angeles'))

            OR (D_H.d = date(convert_tz(now(), 'GMT', 'America/Los_Angeles'))

                AND D_H.d <= hour(convert_tz(now(), 'GMT', 'America/Los_Angeles'))

              )

         ORDER BY D_H.d, D_H.hr

        ;";

Subquery 1 (in blue) gets a cross-joined table of 24 hours (0..23) with last 6 dates resulting in output like YYYY-MM-DD HH.  Single digit hours are converted to two digits in string format (SELECT '0n').  The system and mysql were running in UTC timezone while business users needed them in PST tz.  Subquery 2 (in brown)  generates required metric in PST and joined with subquery 1.  With where clause any extra hours (later than current hour) is filtered out in left outer join.  

Subquery 1 is joined with left outer join for possibility that during a given hour there might not be metric (say, dollar amount) available because there might not be any transaction during that hour for a given dimension.

The result set is dumped to XML file for Fusion chart consumption resulting in above output through php wrapper. The chart is updated every hour.

User traversal digraphs

Visualizing the users' traversal on a website or path traversed in an application or nodes visited may need to be analyzed for better user experience or improving the efficiency the path traversed or to improve the conversion rate by getting the user to the end state. For example, it may be that users visiting one set of nodes (node meaning particular state of application like tutorial state machine) convert better than users going through different set of states. There are many cases where a graph or directed graph is a necessity.

You can use freeware tools like ‘dot’ to draw directed graphs. In the following example, I assume the state/node is represented by simple integer and path taken by the edge (directed line).

Here, I have shown a simplified version of a project I worked on in quickly generating a digraph that can be sent or uploaded to site for internal business users on regular intervals in real time. Few assumptions are made to help explain the main idea and they are - each visit to the node is timestamped and user can only traverse forward (meaning visit to the node number higher than the current one). To start with data is summarized in a table with each user or device or some unique key identifier, node visited, and time of visit.

Order the data in chronological order for each unique id so that by doing the self-join (see code snippet below) in SQL we can simply find out the next node the user visited.

SELECT T1.node_id current_node, T2.node_id next_node, count(1) ct
FROM table T1 JOIN table T2 ON T1.row_id+1 = T2.row_id
WHERE T1.unique_id = T2.unique_id # say, user_id
GROUP BY T1.node_id, T2.node_id

You can also provide weightage/cost to the edge by normalizing the count which will result in set of rows similar to
      Current_node, next_node, count, count/max_count

This is all the data we need to generate input file for ‘dot’ application. Write a program that takes the above input and dump it into a file with content –

Digraph G {
   # splines=false;
   rankdir=LR

   2 ->3 [penwidth=4.9, label="1190"]
   3 -> 4 [penwidth=4.9, label="1150"]
   4 -> 5 [penwidth=4.8]
   5 -> 6 [penwidth=4.8]
   6 -> 7 [penwidth=4.8]
   7 -> 8 [penwidth=4.8]
   …
   …
}

By providing this as input you can generate the output in multiple formats including pdf, ps, etc. See graph 1 below.  You can provide more input parameters in the file to fancy the graph or with more information like drop-off (% of users dropped) between states, see graph 2. In essence you are generating a digraph to visualize the data in a more meaningful way.

Digraph 1 - with sample weights between states 2 & 3, 3 & 4

With dot input file content like

  subgraph cluster1 {
    rankdir="LR"
    2 -> 3  [penwidth="23", label="23.48%",  taillabel="4450",  headlabel="3405"]
    3 -> 4  [penwidth="25", label="24.9%",  taillabel="3405",  headlabel="2557"]
    4 -> 5  [penwidth="18", label="18.34%",  taillabel="2557",  headlabel="2088"]
    5 -> 6  [penwidth="19", label="19.3%",  taillabel="2088",  headlabel="1685"]
    6 -> 7  [penwidth="20", label="20.18%",  taillabel="1685",  headlabel="1345"]
    7 -> 8  [penwidth="26", label="26.47%",  taillabel="1345",  headlabel="989"]
    8 -> 9  [penwidth="35", label="35.29%",  taillabel="989",  headlabel="640"]
    9 -> 10  [penwidth="39", label="38.59%",  taillabel="640",  headlabel="393"]
    10 -> 11 [penwidth="36", label="35.88%",  taillabel="393",  headlabel="252"]
  }

Digraph 2 - with users drop-off between states in %