Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Combining multiple transactions into a single event table

  • 12 February 2015
  • 2 replies
  • 332 views
L
Rank
Userlevel 5
Badge

Event tables are useful for deep analysis on user retention and for identifying how to improve the product or service. Not all datasets have raw event tables, but there are plenty of other transactional data tables we can utilize to generate an event table. With this event table in hand, we can then answer questions like:





  • How long does a user spend on site?


  • What is the conversion funnel between all the steps of all of our defined workflows?


  • What effects do attributes about the experience, such as click source or quoted prices, have on the retention or purchase frequency?






###Pre-transformation








###Post-tranformation




###Derived Table for Transformation



- view: trial_funnel


    derived_table:


      sql: |


        SELECT


        a.click_id AS click_id


        , 'click' AS event


        , a.username AS username


        , a.created_at AS created_at


        FROM click a


        UNION ALL


        SELECT


        b.purchase_id AS purchase_id


        , 'purchase' AS event


        , b.username AS username


        , b.created_at AS created_at


        FROM purchase b


        UNION ALL


        SELECT


        c.quote_id AS quote_id


        , 'quote' AS event


        , c.username AS username


        , c.created_at AS created_at


        FROM quote c





  fields:





    - dimension: click_id


      type: int


      sql: ${TABLE}.click_id





    - dimension: purchase_id


      type: int


      sql: ${TABLE}.purchase_id





    - dimension: quote_id


      type: int


      sql: ${TABLE}.quote_id





    - dimension: event





    - dimension: username





    - dimension_group: created


      type: time


      timeframes: [time, date, dow, dow_num, week, month_num, month, year]


      sql: ${TABLE}.created_at

2 replies

A

This is awesome thank you! But quick question, where/how do you create the seperate event_id in this query?

lloydtabb
Rank
Userlevel 6
Badge

event_id isn’t in the query, but here is how you might do it. A ROW_NUMBER() window function will number each row in most dialects of SQL.


   SELECT

     ROW_NUMBER() OVER () as event_id

    , *

   FROM (

   SELECT

        a.click_id AS click_id

        , 'click' AS event

        , a.username AS username

        , a.created_at AS created_at

        FROM click a

        UNION ALL

        SELECT

        b.purchase_id AS purchase_id

        , 'purchase' AS event

        , b.username AS username

        , b.created_at AS created_at

        FROM purchase b

        UNION ALL

        SELECT

        c.quote_id AS quote_id

        , 'quote' AS event

        , c.username AS username

        , c.created_at AS created_at

        FROM quote c)

If you are looking to generate a primary key, you could CONCAT(<event_type>,event_id) and that would also give you a unique event_id that would work as a primary key.

Reply


Terms of Service