Spotfire and Teradata Vantage Analytic Functions

Introduction

TIBCO Spotfire X is certified on Teradata Vantage, the data platform announced in October 2018, that can manage and analyze any type of data with high availability and concurrency while supporting all the deployment options and tools you expect.

Teradata Vantage is a great fit for Spotfire analytics. Now our smart, secure, governed, enterprise analytics platform with built-in data wrangling is enhanced with Teradata Vantage analytic functions powered by the Vantage NewSQL engine. With these new functions available from within Spotfire, business users and analytics experts alike can combine these powerful analytic tools with greater ease of use than ever.

The certification enhances Spotfire?s ability to provide self-service, AI-driven, visual, geo, and streaming analytics, and strengthens the long-term technology partnership between Teradata and TIBCO.

Below are three examples of analytical functions essential for any company to understand customer behavior on the web. Insights gained with functions like these allow them to become insight-driven organizations and provide more value to customers.

nPath

The nPath function scans a set of rows, looking for patterns that you specify. For each set of input rows that matches the pattern, nPath produces a single output row. The function provides a flexible pattern-matching capability that lets you specify complex patterns in the input data and define the values that are output for each matched input set. nPath is useful when your goal is to identify the paths that lead to an outcome. For example, you can use nPath to analyze website click data to identify paths that lead to sales over a specified amount.

Sessionize

The Sessionize function maps each click in a session to a unique session identifier. A session is a sequence of clicks by one user that are separated by at most n seconds. The function is useful for both sessionization and detecting web crawler ("bot") activity. Typical use is to understand user browsing behavior on a website.

Attribution

The Attribution function is used in web page analysis, where it lets companies assign weights to pages before certain events, such as buying a product. The function takes data and parameters from multiple tables and outputs attributions.

Other new functions within the NewSQL engine include time series functions, 4D analytics functions, and scoring functions for decision trees..

Below are five examples of some of these functions used in Spotfire custom queries.

Query 1

SELECT /* npath 1 */

 path, count(*) as cnt from nPath (on

(

SELECT id, TRANSLATE(tvshow USING UNICODE_TO_LATIN) AS tvshow, ts, attribution, time_to_conversion

   from attribution (

ON ap_tables.tv_shows_1 AS "INPUT" partition by id  order by ts

ON ap_tables.conversion_event_table_1 AS conversion DIMENSION

ON ap_tables.model1_table_1 AS model1 DIMENSION

USING

EventColumn('tvshow')

Windowsize('seconds:1200')

TimestampColumn('ts')

) AS dt

WHERE attribution > 0.00 or attribution is NULL)

partition by id order by ts

USING

mode(nonoverlapping)

pattern('a*.bb')

symbols(tvshow <> 'BreakingBad' as a,

              tvshow = 'BreakingBad' as bb)

result(accumulate(tvshow of any (a,bb)) as path))

GROUP BY path

Query 2

SELECT  /* npath 2 */

  * FROM npath (

  ON ap_tables.clickstream PARTITION BY userid ORDER BY clicktime

  USING

    Symbols (

        pagetype='home' AS home,

        pagetype <> 'home' AND pagetype <> 'checkout' AS clickview,

        pagetype='checkout' AS checkout)

        Pattern ('home.clickview*.checkout')



    Result (

        FIRST(userid of ANY(home, checkout, clickview)) AS userid,

        FIRST (sessionid of ANY(home, checkout, clickview)) AS sessioinid,

        COUNT (* of any(home, checkout, clickview)) AS cnt,

        FIRST (clicktime of ANY(home)) AS firsthome,

        LAST (clicktime of ANY(checkout)) AS lastcheckout)

    Filter (FIRST (EXTRACT(MINUTE FROM clicktime) + 10 OF ANY (home))

           >

           FIRST (EXTRACT(MINUTE FROM clicktime) of any(checkout)))

    Mode (OVERLAPPING)

)

Query 3

select  /* npath 3 */

 ct_a, ct_c, cast(any_ab as varchar(100)) a, cast(any_ac as varchar(100)) b from npath

(on (select * from ap_tables.nptest) partition by c2 order by c1

USING

mode (nonoverlapping)

pattern ('(A.(B|C))')

symbols(c3='A' as A,c3='B' as B,c3='C' as C)

result(count(* of A) as Ct_A,

         count(* of C) as Ct_C,

         accumulate(c3 of any(A,B)) as Any_AB,

         accumulate(c3 of any(A,C)) as Any_AC)) as dt

Query 4

SELECT   /* Sessionize  1 */

  *

FROM Sessionize (  

ON ap_tables.sessionize_table

PARTITION BY partition_id

USING  TimeColumn ('clicktime')  TimeOut (60) ClickLag (0.2) )
 

Query 5

SELECT /* Attribution  1 */

 * FROM Attribution (

  ON ap_tables.attribution_sample_table1 AS input1

    PARTITION BY user_id ORDER BY time_stamp

  ON ap_tables.attribution_sample_table2 AS input2

    PARTITION BY user_id ORDER BY time_stamp

  ON ap_tables.conversion_event_table AS conversion DIMENSION

  ON ap_tables.excluding_event_table AS excluding DIMENSION

  ON ap_tables.optional_event_table AS optional DIMENSION

  ON ap_tables.model1_table AS model1 DIMENSION

  ON ap_tables.model2_table AS model2 DIMENSION

 USING

  EventColumn ('event')

  TimestampColumn ('time_stamp')

  Windowsize ('rows:10&seconds:20')

 ) AS dt

Below is a screenshot of the data tables returned to Spotfire for each query:

For additional information about the Teradata Vantage platform please see:

https://www.teradata.com/Products/Software/Vantage

https://www.teradata.com/Press-Releases/2018/Teradata-Vantage-the-Platfo...