Skip to content

Instantly share code, notes, and snippets.

View jaymay's full-sized avatar

Jason Maynard jaymay

  • Zendesk
  • San Francisco
View GitHub Profile
@jaymay
jaymay / body.json
Last active May 24, 2018 21:24
https://<subdomain>.zendesk.com/api/v2/batch_analytics/benchmark/
{
"industry": {
"manufacturing": {
"full_resolution_time_in_minutes_mean": 8707.52279122116,
"first_reply_time_in_minutes_median": 285,
"first_reply_time_in_minutes_mean": 1491.2547945205479,
"satisfaction_rating": 0.9559394409937889,
"one_touch_percentage": 0.3623462630085147,
"nps": null,
"active_agent_comments_average": 7.0513157894736835,
@jaymay
jaymay / gist:89567916c877a264b7d6ff566b7f1a10
Last active September 12, 2016 12:57
Medidata request
select
e.author_id,
u.name,
ui.value as email,
u.roles,
count(distinct e.parent_id) num_distinct_updates_wo_reassigment
from classic.events e
inner join users u on e.author_id = u.id
inner join user_identities ui on e.author_id = ui.user_id
where e.account_id = 361618
@jaymay
jaymay / gist:247d41ef9d3ba48ef59b
Created February 4, 2016 14:11
ML eligibility
select a.plan_type,
count(*) total_accounts,
sum(if(p.satisfaction_ratings_last_90_days > 1000, mrr.mrr_amt, 0)) / sum(mrr_amt) perc_eligible_satisfaction_prediction,
sum(if(r.num_shared_macros > 10, mrr.mrr_amt, 0)) / sum(mrr_amt) perc_eligible_macro_suggestion,
sum(if(co.num_hc_articles > 10, mrr.mrr_amt, 0)) / sum(mrr_amt) perc_eligible_content_suggestion
from accounts_settings s
inner join accounts a using (account_id, run_at)
inner join fdw_mrr mrr using (account_id, run_at)
left join count_objects co using (account_id, run_at)
left join ticket_rules r using (account_id, run_at)
SELECT
agent_id,
agent_type,
is_owner,
agent_name,
agent_email,
subquery.*
FROM
(SELECT
account_id,
@jaymay
jaymay / .sql
Last active October 29, 2015 17:33
subscriptions patagonia testing
SELECT a.subdomain, s.*
FROM accounts a
INNER JOIN subscriptions s USING (account_id , run_at)
WHERE account_id IN (846669 , 846684, 846676)
AND run_at = 20151029
@jaymay
jaymay / pathfinder_query.sql
Created August 13, 2015 19:05
pathfinder research query
select result.account_id as account_id, plan_type, ticket_create_session_w_article_view, total_tickets_created, total_articles_viewed
from (
select account_id, sum(if(tickets_created > 0 and articles_viewed > 0, 1, 0)) ticket_create_session_w_article_view, sum(tickets_created) total_tickets_created, sum(articles_viewed) total_articles_viewed
from (
select
account_id, session, sum(if(type = 'create', 1, 0)) tickets_created, sum(if(type = 'child_view', 1, 0)) articles_viewed
from (
select
Sessionize(unix_time, user_id, 1800) as session, account_id, user_id, type
from (
@jaymay
jaymay / .sql
Created July 30, 2015 18:31
Talkdesk app installs
select a.plan_type, sum(api.num_installs) installs
from accounts a
left outer join app_installs api on a.account_id = api.account_id and a.run_at = api.run_at and a.timespan = api.timespan
inner join apps ap on api.app_id = ap.id
where a.run_at = 20150601
and ap.name like ('talkdesk')
and a.account_type = 'customer'
group by a.plan_type
@jaymay
jaymay / gist:ea1f40b417122223fc7f
Created July 14, 2015 22:54
Mongo collections example
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'
$(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
jaymay / gist:c1faf404157022efd1a1
Created May 6, 2015 22:32
Voice duration and wait time benchmarks
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,