0

Facebook Ads / Google Adwords + Zendesk + Segment - Ticket Load

Question

How are my ads affecting my support ticket load?

Sources

Facebook Ads

Google Adwords

Zendesk

Query

-- page view from traffic from Facebook Ads
with
page_hits as
(
      select  pages.received_at,
              pages.anonymous_id,
              ads.id as ad_id,
              ads.name as ad_name,
              i.spend
        from  segment.pages as pages
        join  facebook_ads.ads as ads
          on  strpos(pages.context_page_search, 'tbfbadsid=' + ads.id)
        join  facebook_ads.insights as i
          on  i.ad_id = ads.id
),

-- get signup data
signups as
(
    select  date_trunc('month', s.received_at) signup_month,
            s.user_id,
            s.anonymous_id
      from  segment.signup as s
)

    select  ph.ad_name,
            coalesce(count(distinct z.id), 0)
      from  page_hits as ph
 left join  signups as si
        on  ph.anonymous_id = si.anonymous_id
            -- join Zendesk ticket infomation
 left join  app.zendesk_created_ticket as z
        on  z.user_id = si.user_id
  group by  ph.ad_name
  order by  2 desc