jaymay

{
  "responses":[
  {
    "id":1,
    "delivery_id":1,
    "user_id":154506367,
    "rating": null,
    "comment": null,
    "delivered_at":"2013-08-29T00:00:00-07:00",
    "rated_at":null

jaymay

set @run_at = '2014-12-31 00:00:00';
select a.account_id, a.subdomain, aa2.num_active_agents_90_days
from accounts a 
inner join 
(select aa.account_id, count(*) num_active_agents_90_days, @run_at as run_at, 'all' as timespan
from active_agents aa 
where aa.run_at between subdate(@run_at, interval 90 day) and @run_at
and timespan = '1d'
group by aa.account_id
) aa2

jaymay

use dw_prod_analytics;

SELECT 
    a.account_id,
    a.subdomain,
    co.num_tickets,
    co.num_users,
    r.num_agents + r.num_admins activated_agents,
    a.max_agents
FROM

jaymay

select plan_type,
count(*) num_accounts, sum(mrr*12) total_arr, avg(mrr*12) avg_arr, sum(max_agents) total_agents, avg(max_agents) avg_agents, std(max_agents) std_agents
from accounts a
inner join roles r using (account_id, run_at, timespan)
inner join zuora_mrr mrr using (account_id, run_at, timespan)
where run_at= 20150304 
and max_agents between 10 and 50 
and plan_type != 1
and mrr > 0
group by plan_type;

jaymay

$(document).ready(function() {

  // stuff here
 
  // identify a user's attributes
     var dimensionValue = HelpCenter.user.role;
     ga('set', 'dimension1', dimensionValue);
 
     var dimensionValue = HelpCenter.user.name;
     ga('set', 'dimension2', dimensionValue);

jaymay

use classic;

select
e.delta_date run_at,
e.account_id, 
e.author_id AS agent_id,
ui.value as email,
if(u.roles = 2, 1, 0) as is_admin,
count(*) as num_actions
from classic.events e 

jaymay

select i.full_name industry, widget_accounts, all_customers, round((widget_accounts / all_customers) * 100, 2) perc_accounts_w_widget
from 
(
select industry, count(*) all_customers, sum(if(w.widget_ticket_last_7 > 0, 1, 0)) widget_accounts
from accounts a
left outer join 
(select t.account_id, max(run_at) max_run_at, sum(t.new_channel_zendesk_widget) widget_ticket_last_7
from tickets t 
where run_at between subdate(date(now()), interval 7 day) and date(now())
and t.new_channel_zendesk_widget > 0

jaymay

select results.industry, 
percentile_approx(avg_duration/60, 0.5) avg_duration_minutes, 
percentile_approx(avg_wait_time/60, 0.5) avg_wait_time_minutes,
count(*) num_accounts,
sum(count) num_calls
  from
  (
    select 
    asr.industry, 
    v.account_id, 

jaymay

$(document).ready(function() {

  // stuff here
 
  // identify a user's attributes
     var dimensionValue = HelpCenter.user.role;
     ga('set', 'dimension1', dimensionValue);
 
     var dimensionValue = HelpCenter.account.subdomain;
     ga('set', 'dimension2', dimensionValue);

jaymay

select case when u.roles in (2,3) then 'agent'
       else 'user'
       end role, 
       asr.target_audience,
       count(*) num_views,
       count(distinct unique_token) unique_tokens
from default.stats_hc_events hc 
left outer join classic.users u on u.id = hc.user_id
inner join classic.account_survey_responses asr on asr.account_id = hc.account_id
where dt between 20150601 and 20150607 and hc.child_type = 'Article' and hc.type = 'child_view'