Migrating Explore Data to Redshift and Looker

3 Comments

  • Chandra Robrock
    Community Moderator

    While I can't speak to Redshift specifically, we do pipe in Zendesk data into Looker. 

    For queries such as reporting on overall ticket volume, it's pretty straight-forward. For instance, I have a few dashboards to help non-Zendesk users get insight into the # of support tickets, broken out by specific categories that we're tracking via ticket tags. Looker users can also use these dashboards to see the very first comment on the ticket and click out to view the actual ticket in Zendesk. On this Looker dashboard specifically, they can also drill into the specific tickets they'd like to see (i.e. specific features, organizations, etc).

    Aside from that though, I've found it to be a bit more challenging to manipulate the data in the exact way I want to. It's also a bit of upkeep since I have to code certain things in LookML that Explore just allows me to filter for out-of-the-box. I also haven't found a way to report on tickets that do not contain a specific ticket tag (similar to the NOT_INCLUDES_ANY Explore logic) which is a real bummer for us.  

    To be honest though, once Zendesk Explore launched the ability to share Explore dashboards outside of Zendesk, this really changed the game for us. It's only available on Explore Enterprise right now, but mentioning it in case that's you primary use case for getting this data into Looker: https://support.zendesk.com/hc/en-us/articles/360051444694.

    We'll still use Zendesk reporting in Looker for some things, but have recently started relying on it a bit less for those reasons. Hope that helps as you continue exploring Looker more!

    0
  • Matt

    Thanks Chandra!

    0
  • David Young

    I've been working on aging ticket reporting in Looker.  We use FiveTran to transport the data into BigQuery and then use views to expose the data in Looker for modeling using dbt.

     The big thing I noticed during my analysis of the Zendesk database is that the ticket table holds the latest information for the ticket and any revision/edit to any field in the ticket is held in the ticket_field_history table.  Understanding how the ticket_field_history table is key to getting data out of the DB and using it for reporting purposes.  

    For the issue of aging tickets, you need to understand that a ticket can be updated without changing the status of a ticket and it will change the updated_at field in the ticket.  If you key off this field to determine the ticket age, you can have false reporting of ticket age.  To get around this issue, I created two additional fields in the view I use to model data on for Looker; status_date and ticket_age.

     1. status_date is the last time the 'status' field was updated.  This is important to know because when determining ticket age, you only want to know when the ticket field_name of 'status' was moved to 'solved' or 'closed' (depending on your process) to 'stop the aging clock'.  If it is in 'open', 'hold', etc. you'll want to keep the clock going.

      2. Next, I created the ticket_age using the status_date from above. If the status of a ticket was 'closed', 'solved' or 'deleted' then I needed to use the status_date using a timestamp_diff(status_date, create_at, day) statement.  Otherwise, use timestamp_diff(curent_timestamp(),created_at, day) statement.

    These two new fields in the view allowed me to create the LookML needed to determine age of a ticket in my reporting.
    LookML:

    dimension: age {
    type: number
    sql: ${TABLE}.ticket_age ;;
    }

    dimension: age_range {
    type: string
    sql: case
              when ${age} between 0 and 1 then '0 - 1'
              when ${age} between 2 and 4 then '2 - 4'
              when ${age} between 5 and 7 then '5 - 7'
              when ${age} > 7 then 'Seven +'
           end ;;
    }

    dimension_group: status {
    type: time
    timeframes: [time,date,week,month,quarter,year]
    sql: ${TABLE}.status_date ;;
    }

    The view (v_zendesk_tickets.sql) looks like this:

    WITH assigned__name AS
    (
    SELECT
           t.id,
           u.name AS assigned_name
    FROM
           staging_zendesk.`user` u
    INNER JOIN staging_zendesk.ticket t ON u.id = t.assignee_id
    ), last_status_update AS
    (
    select
         ticket_id,
         max(updated) as status_date
    from
         staging_zendesk.ticket_field_history
    where
         field_name = 'status'
    group by 1
    )

    select
         t.id,
         t._fivetran_synced,
         t.allow_channelback,
         t.assignee_id,
        a.assigned_name,
        t.brand_id,
        t.custom_category,
        t.custom_support_team,
        t.description,
        t.due_at,
        t.external_id,
        t.forum_topic_id,
        t.group_id,
        t.has_incidents,
        t.is_public,
        t.merged_ticket_ids,
        t.organization_id,
        t.priority,
        t.problem_id,
        t.recipient,
        t.requester_id,
        t.status,
        t.subject,
        t.submitter_id,
        u.name AS user_name,
        t.system_client,
        t.system_ip_address,
        t.system_json_email_identifier,
        t.system_latitude,
        t.system_location,
        t.system_longitude,
        t.system_message_id,
        t.system_raw_email_identifier,
        t.ticket_form_id,
        t.`type`,
        t.url,
        t.via_channel,
        t.via_source_from_id,
        t.via_source_from_title,
        t.via_source_rel,
        t.via_source_to_address,
        t.via_source_to_name,
        t.custom_branch,
        t.custom_trello_card_id,
        t.system_ccs,
        t.custom_device_replacement_reason,
        t.via_source_from_address,
        t.followup_ids,
        t.via_followup_source_id,
        t.created_at,
        t.updated_at,
        su.status_date,
        CASE WHEN t.status in ('closed','solved','deleted') THEN TIMESTAMP_DIFF(status_date,t.created_at,DAY)
                   ELSE TIMESTAMP_DIFF(CURRENT_TIMESTAMP() ,t.created_at,DAY)
        END as ticket_age
    FROM
        staging_zendesk.ticket AS t
        LEFT OUTER JOIN staging_zendesk.`user` AS u ON t.submitter_id = u.id
        LEFT OUTER JOIN assigned__name AS a ON t.id = a.id
        LEFT OUTER JOIN last_status_update as su on t.id = su.ticket_id
    ORDER BY t.id

    0

Please sign in to leave a comment.

Powered by Zendesk