Gorgias Models

Copy-and-paste Gorgias model templates for users, customers, tickets, messages, and support performance reporting.

Copy and Paste these Gorgias Models into your Data Layer to quickly go from raw data sources -> business ready Gorgias Reporting. Please note:

Models must be added chronological order from top to bottom (so that they can reference each other).
When adding your own models you must update the reference to include your source names. For example, in the model templates below.

Base

Transforms Raw Data to Clean Tables focused on Core Concepts

gorgias_users_base

select
    id::varchar as user_id --pk
  , NULLIF(TRIM(name), '') as user_name
  , LOWER(TRIM(first_name)) as first_name
  , LOWER(TRIM(last_name)) as last_name
  , LOWER(TRIM(email)) as email
  , LOWER(TRIM(role_name)) as role_name
  , active as is_active
  , TRIM(timezone) as timezone
  , created_datetime as created_at
  , updated_datetime as updated_at
  , _fivetran_synced as __updated_at
from {{sources.gorgias.users}}
where _fivetran_deleted is not true

gorgias_customers_base

select
    id::varchar as customer_id --pk
  , NULLIF(TRIM(name), '') as customer_name
  , LOWER(TRIM(first_name)) as first_name
  , LOWER(TRIM(last_name)) as last_name
  , LOWER(TRIM(email)) as email
  , active as is_active
  , TRIM(timezone) as timezone
  , NULLIF(TRIM(external_id), '') as external_id
  , created_datetime as created_at
  , updated_datetime as updated_at
  , _fivetran_synced as __updated_at
from {{sources.gorgias.customer}}
where _fivetran_deleted is not true

gorgias_tickets_base

select
    id::varchar as ticket_id --pk
  , customer_id::varchar as customer_id
  , (assignee_user->>'id')::varchar as assignee_user_id
  , LOWER(TRIM(assignee_user->>'email')) as assignee_email
  , TRIM(assignee_user->>'name') as assignee_name
  , NULLIF(TRIM(subject), '') as subject
  , NULLIF(TRIM(excerpt), '') as excerpt
  , LOWER(TRIM(status)) as status
  , LOWER(TRIM(channel)) as channel
  , LOWER(TRIM(via)) as via
  , LOWER(TRIM(languages)) as language
  , messages_count as n_messages
  , from_agent as is_first_message_from_agent
  , is_unread
  , spam as is_spam
  , NULLIF(TRIM(external_id), '') as external_id
  , created_datetime as created_at
  , opened_datetime as opened_at
  , last_message_datetime as last_message_at
  , last_received_message_datetime as last_received_message_at
  , closed_datetime as closed_at
  , snooze_datetime as snoozed_at
  , trashed_datetime as trashed_at
  , updated_datetime as updated_at
  , _fivetran_synced as __updated_at
from {{sources.gorgias.ticket}}
where _fivetran_deleted is not true

gorgias_messages_base

select
    id::varchar as message_id --pk
  , ticket_id::varchar as ticket_id
  , sender_id::varchar as sender_id
  , LOWER(TRIM(sender_email)) as sender_email
  , TRIM(sender_name) as sender_name
  , receiver_id::varchar as receiver_id
  , LOWER(TRIM(receiver_email)) as receiver_email
  , TRIM(receiver_name) as receiver_name
  , from_agent as is_from_agent
  , public as is_public
  , LOWER(TRIM(channel)) as channel
  , LOWER(TRIM(via)) as via
  , LOWER(TRIM(source_type)) as source_type
  , NULLIF(TRIM(stripped_text), '') as message_text
  , created_datetime as created_at
  , sent_datetime as sent_at
  , opened_datetime as opened_at
  , _fivetran_synced as __updated_at
from {{sources.gorgias.message}}
where _fivetran_deleted is not true

Fact

Creates a "Fact" about a core concept. For example, creating a customer order fact that defines when a customer placed their first order. This then gets joined downstream in output.

gorgias_ticket_message_facts

select
    ticket_id as ticket_id --pk
  , count(*) as n_messages_total
  , count(*) filter (where is_from_agent) as n_agent_messages
  , count(*) filter (where not is_from_agent) as n_customer_messages
  , min(created_at) as first_message_at
  , min(created_at) filter (where is_from_agent) as first_agent_message_at
  , max(created_at) as last_message_at
from {{models.gorgias_messages_base}}
group by 1

gorgias_ticket_facts

select
    t.ticket_id as ticket_id --pk
  , t.customer_id
  , t.assignee_user_id
  , t.assignee_email
  , t.status
  , t.channel
  , t.via
  , t.language
  , t.is_spam
  , t.created_at
  , t.closed_at
  , m.first_message_at
  , m.first_agent_message_at
  , m.last_message_at
  , m.n_messages_total
  , m.n_agent_messages
  , m.n_customer_messages
  -- minutes from ticket creation to the first agent reply
  , extract(epoch from (m.first_agent_message_at - t.created_at)) / 60.0 as first_response_minutes
  -- minutes from ticket creation to close
  , extract(epoch from (t.closed_at - t.created_at)) / 60.0 as resolution_minutes
from {{models.gorgias_tickets_base}} t
left join {{models.gorgias_ticket_message_facts}} m on t.ticket_id = m.ticket_id

gorgias_customer_ticket_facts

select
    c.customer_id as customer_id --pk
  , count(t.ticket_id) as n_tickets
  , count(t.ticket_id) filter (where t.status = 'closed') as n_closed_tickets
  , min(t.created_at) as first_ticket_at
  , max(t.created_at) as last_ticket_at
  , avg(t.first_response_minutes) as avg_first_response_minutes
  , avg(t.resolution_minutes) as avg_resolution_minutes
from {{models.gorgias_customers_base}} c
left join {{models.gorgias_ticket_facts}} t on c.customer_id = t.customer_id
group by 1

Output

Final tables ready for use in explores in Switchboard. Output tables typically represent core business concepts that get joined together.

gorgias_tickets_output

select
    t.ticket_id as ticket_id --pk
  , t.created_at
  , t.closed_at
  , t.status
  , t.channel
  , t.via
  , t.language
  , t.is_spam
  , t.customer_id
  , c.customer_name
  , c.email as customer_email
  , t.assignee_user_id
  , u.user_name as assignee_name
  , u.email as assignee_email
  , u.role_name as assignee_role
  , t.n_messages_total
  , t.n_agent_messages
  , t.n_customer_messages
  , t.first_message_at
  , t.first_agent_message_at
  , t.last_message_at
  , t.first_response_minutes
  , t.resolution_minutes
from {{models.gorgias_ticket_facts}} t
left join {{models.gorgias_customers_base}} c on t.customer_id = c.customer_id
left join {{models.gorgias_users_base}} u on t.assignee_user_id = u.user_id

gorgias_customers_output

select
    c.customer_id as customer_id --pk
  , c.created_at
  , c.customer_name
  , c.first_name
  , c.last_name
  , c.email
  , c.is_active
  , c.external_id
  , cf.n_tickets
  , cf.n_closed_tickets
  , cf.first_ticket_at
  , cf.last_ticket_at
  , cf.avg_first_response_minutes
  , cf.avg_resolution_minutes
from {{models.gorgias_customers_base}} c
left join {{models.gorgias_customer_ticket_facts}} cf on c.customer_id = cf.customer_id

Gorgias Models

On this page