ideas.txt

pensarios em que precisamo fazer para chegar no plateau e quanto tempo ele levaria na media
 dai poderiamos direcionar produto e cosultorias nisso

Time to first N sales?
Time to first N opportunities?

ips.txt

blacklist: 167.89.10.242, Shared
blacklist: 167.89.10.246, Shared  
blacklist: 167.89.14.115, Shared 
blacklist: 167.89.19.23, Shared
blacklist: 50.31.54.241 , Shared  
blacklist: 167.89.47.188, Shared
blacklist: 167.89.39.10, Shared

leadgen2.sql

select * into temp all_dates from (
  select date_trunc('month', current_date - (a.a + (10*b.a)+ (100*c.a)) * INTERVAL '1' MONTH) as mes
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) i;

select
  a.id id_conta,
  abs(((extract( year FROM l.created_at ) - extract( year FROM a.created_at )) *12) + extract(MONTH FROM l.created_at ) - extract(MONTH FROM a.created_at )) age,
  count(l.id) total
from accounts a
inner join all_dates
inner join leads_managers lm on lm.account_id = a.id
left join leads l on l.leads_manager_id = lm.id
group by 1
limit 100;

leadgen_activation.sql

select * into temp all_dates from (
  select date_trunc('month', current_date - (a.a + (10*b.a)+ (100*c.a)) * INTERVAL '1' MONTH) as mes
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) i;

select * into temp leads_per_month from
(select a.id id_conta, date_trunc('month', l.created_at) mes_lead, count(*) total
from accounts a
inner join leads_managers lm on lm.account_id = a.id
inner join leads l on l.leads_manager_id = lm.id
group by 1,2) l;
create index leads_per_month_id_mes on leads_per_month(id_conta, mes_lead);
create index leads_per_month_id on leads_per_month(id_conta);

select * into temp leads_by_month_by_account from (select a.id id_conta,
  a.name cliente,
  date_trunc('month', a.created_at) criacao,
  sum(case when date_trunc('month', a.created_at) = date_trunc('month', totals.mes) then totals.total end) mes_1,
  sum(case when date_trunc('month', a.created_at + interval '1 month') >= date_trunc('month', totals.mes) then totals.total end) mes_2,
  sum(case when date_trunc('month', a.created_at + interval '2 month') >= date_trunc('month', totals.mes) then totals.total end) mes_3,
  sum(case when date_trunc('month', a.created_at + interval '3 month') >= date_trunc('month', totals.mes) then totals.total end) mes_4,
  sum(case when date_trunc('month', a.created_at + interval '4 month') >= date_trunc('month', totals.mes) then totals.total end) mes_5,
  sum(case when date_trunc('month', a.created_at + interval '5 month') >= date_trunc('month', totals.mes) then totals.total end) mes_6,
  sum(case when date_trunc('month', a.created_at + interval '6 month') >= date_trunc('month', totals.mes) then totals.total end) mes_7,
  sum(case when date_trunc('month', a.created_at + interval '7 month') >= date_trunc('month', totals.mes) then totals.total end) mes_8,
  sum(case when date_trunc('month', a.created_at + interval '8 month') >= date_trunc('month', totals.mes) then totals.total end) mes_9,
  sum(case when date_trunc('month', a.created_at + interval '9 month') >= date_trunc('month', totals.mes) then totals.total end) mes_10,
  sum(case when date_trunc('month', a.created_at + interval '10 month') >= date_trunc('month', totals.mes) then totals.total end) mes_11,
  sum(case when date_trunc('month', a.created_at + interval '11 month') >= date_trunc('month', totals.mes) then totals.total end) mes_12,
  sum(case when date_trunc('month', a.created_at + interval '12 month') >= date_trunc('month', totals.mes) then totals.total end) mes_13,
  sum(case when date_trunc('month', a.created_at + interval '13 month') >= date_trunc('month', totals.mes) then totals.total end) mes_14,
  sum(case when date_trunc('month', a.created_at + interval '14 month') >= date_trunc('month', totals.mes) then totals.total end) mes_15,
  sum(case when date_trunc('month', a.created_at + interval '15 month') >= date_trunc('month', totals.mes) then totals.total end) mes_16,
  sum(case when date_trunc('month', a.created_at + interval '16 month') >= date_trunc('month', totals.mes) then totals.total end) mes_17,
  sum(case when date_trunc('month', a.created_at + interval '17 month') >= date_trunc('month', totals.mes) then totals.total end) mes_18,
  sum(case when date_trunc('month', a.created_at + interval '18 month') >= date_trunc('month', totals.mes) then totals.total end) mes_19,
  sum(case when date_trunc('month', a.created_at + interval '19 month') >= date_trunc('month', totals.mes) then totals.total end) mes_20,
  sum(case when date_trunc('month', a.created_at + interval '20 month') >= date_trunc('month', totals.mes) then totals.total end) mes_21,
  sum(case when date_trunc('month', a.created_at + interval '21 month') >= date_trunc('month', totals.mes) then totals.total end) mes_22,
  sum(case when date_trunc('month', a.created_at + interval '22 month') >= date_trunc('month', totals.mes) then totals.total end) mes_23,
  sum(case when date_trunc('month', a.created_at + interval '23 month') >= date_trunc('month', totals.mes) then totals.total end) mes_24,
  sum(case when date_trunc('month', a.created_at + interval '24 month') >= date_trunc('month', totals.mes) then totals.total end) mes_25,
  sum(case when date_trunc('month', a.created_at + interval '25 month') >= date_trunc('month', totals.mes) then totals.total end) mes_26,
  sum(case when date_trunc('month', a.created_at + interval '26 month') >= date_trunc('month', totals.mes) then totals.total end) mes_27,
  sum(case when date_trunc('month', a.created_at + interval '27 month') >= date_trunc('month', totals.mes) then totals.total end) mes_28,
  sum(case when date_trunc('month', a.created_at + interval '28 month') >= date_trunc('month', totals.mes) then totals.total end) mes_29,
  sum(case when date_trunc('month', a.created_at + interval '29 month') >= date_trunc('month', totals.mes) then totals.total end) mes_30,
  sum(case when date_trunc('month', a.created_at + interval '30 month') >= date_trunc('month', totals.mes) then totals.total end) mes_31,
  sum(case when date_trunc('month', a.created_at + interval '31 month') >= date_trunc('month', totals.mes) then totals.total end) mes_32,
  sum(case when date_trunc('month', a.created_at + interval '32 month') >= date_trunc('month', totals.mes) then totals.total end) mes_33,
  sum(case when date_trunc('month', a.created_at + interval '33 month') >= date_trunc('month', totals.mes) then totals.total end) mes_34,
  sum(case when date_trunc('month', a.created_at + interval '34 month') >= date_trunc('month', totals.mes) then totals.total end) mes_35,
  sum(case when date_trunc('month', a.created_at + interval '35 month') >= date_trunc('month', totals.mes) then totals.total end) mes_36

  from accounts a
  inner join (
    select lm.id_conta, ad.mes, lm.total
    from all_dates ad
    left join leads_per_month lm on lm.mes_lead = ad.mes
  ) as totals on totals.id_conta = a.id
  group by 1,2,3
  order by 1,3) aaa;


-- select criacao,
--   count(distinct id_conta) total,
--   sum(case when mes_1 > 1 then 1 end) mes1,
--   sum(case when mes_3 > 1 then 1 end) mes3,
--   sum(case when mes_4 > 1 then 1 end) mes4,
--   sum(case when mes_5 > 1 then 1 end) mes5,
--   sum(case when mes_6 > 1 then 1 end) mes6,
--   sum(case when mes_7 > 1 then 1 end) mes7,
--   sum(case when mes_8 > 1 then 1 end) mes8,
--   sum(case when mes_9 > 1 then 1 end) mes9,
--   sum(case when mes_10 > 1 then 1 end) mes10,
--   sum(case when mes_12 > 1 then 1 end) mes12,
--   sum(case when mes_13 > 1 then 1 end) mes13,
--   sum(case when mes_14 > 1 then 1 end) mes14,
--   sum(case when mes_15 > 1 then 1 end) mes15,
--   sum(case when mes_16 > 1 then 1 end) mes16,
--   sum(case when mes_17 > 1 then 1 end) mes17,
--   sum(case when mes_18 > 1 then 1 end) mes18,
--   sum(case when mes_19 > 1 then 1 end) mes19,
--   sum(case when mes_20 > 1 then 1 end) mes20,
--   sum(case when mes_22 > 1 then 1 end) mes22,
--   sum(case when mes_23 > 1 then 1 end) mes23,
--   sum(case when mes_24 > 1 then 1 end) mes24
--   from leads_by_month_by_account
--   group by 1
--   order by 1;

-- select criacao,
--   count(distinct id_conta) total,
--   sum(case when mes_1 > 1 then 1 end) mes1,
--   sum(case when mes_2 > 1 and mes_1 > 1 then 1 end) mes2,
--   sum(case when mes_3 > 1 and mes_2 > 1 then 1 end) mes3
--   from leads_by_month_by_account
--   group by 1
--   order by 1;

\copy (select criacao, count(distinct id_conta) total, sum(case when mes_1 > 1 then 1 end) mes1, sum(case when mes_3 > 1 then 1 end) mes3, sum(case when mes_4 > 1 then 1 end) mes4, sum(case when mes_5 > 1 then 1 end) mes5, sum(case when mes_6 > 1 then 1 end) mes6, sum(case when mes_7 > 1 then 1 end) mes7, sum(case when mes_8 > 1 then 1 end) mes8, sum(case when mes_9 > 1 then 1 end) mes9, sum(case when mes_10 > 1 then 1 end) mes10, sum(case when mes_12 > 1 then 1 end) mes12, sum(case when mes_13 > 1 then 1 end) mes13, sum(case when mes_14 > 1 then 1 end) mes14, sum(case when mes_15 > 1 then 1 end) mes15, sum(case when mes_16 > 1 then 1 end) mes16, sum(case when mes_17 > 1 then 1 end) mes17, sum(case when mes_18 > 1 then 1 end) mes18, sum(case when mes_19 > 1 then 1 end) mes19, sum(case when mes_20 > 1 then 1 end) mes20, sum(case when mes_22 > 1 then 1 end) mes22, sum(case when mes_23 > 1 then 1 end) mes23, sum(case when mes_24 > 1 then 1 end) mes24 from leads_by_month_by_account group by 1 order by 1) to '/tmp/lead_gen_activation.csv' with csv;

leads.sql

select * into temp all_dates from (
  select date_trunc('month', current_date - (a.a + (10*b.a)+ (100*c.a)) * INTERVAL '1' MONTH) as mes
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) i;

select * into temp leads_per_month from
(select a.id id_conta, date_trunc('month', l.created_at) mes_lead, count(*) total
from accounts a
inner join leads_managers lm on lm.account_id = a.id
left join leads l on l.leads_manager_id = lm.id
group by 1,2) l;
create index leads_per_month_id_mes on leads_per_month(id_conta, mes_lead);
create index leads_per_month_id on leads_per_month(id_conta);

select * into temp leads_by_month_by_account from (select a.id id_conta,
  a.name cliente,
  date_trunc('month', a.created_at) criacao,
  sum(case when date_trunc('month', a.created_at) = date_trunc('month', totals.mes) then totals.total end) mes_1,
  sum(case when date_trunc('month', a.created_at + interval '1 month') = date_trunc('month', totals.mes) then totals.total end) mes_2,
  sum(case when date_trunc('month', a.created_at + interval '2 month') = date_trunc('month', totals.mes) then totals.total end) mes_3,
  sum(case when date_trunc('month', a.created_at + interval '3 month') = date_trunc('month', totals.mes) then totals.total end) mes_4,
  sum(case when date_trunc('month', a.created_at + interval '4 month') = date_trunc('month', totals.mes) then totals.total end) mes_5,
  sum(case when date_trunc('month', a.created_at + interval '5 month') = date_trunc('month', totals.mes) then totals.total end) mes_6,
  sum(case when date_trunc('month', a.created_at + interval '6 month') = date_trunc('month', totals.mes) then totals.total end) mes_7,
  sum(case when date_trunc('month', a.created_at + interval '7 month') = date_trunc('month', totals.mes) then totals.total end) mes_8,
  sum(case when date_trunc('month', a.created_at + interval '8 month') = date_trunc('month', totals.mes) then totals.total end) mes_9,
  sum(case when date_trunc('month', a.created_at + interval '9 month') = date_trunc('month', totals.mes) then totals.total end) mes_10,
  sum(case when date_trunc('month', a.created_at + interval '10 month') = date_trunc('month', totals.mes) then totals.total end) mes_11,
  sum(case when date_trunc('month', a.created_at + interval '11 month') = date_trunc('month', totals.mes) then totals.total end) mes_12,
  sum(case when date_trunc('month', a.created_at + interval '12 month') = date_trunc('month', totals.mes) then totals.total end) mes_13,
  sum(case when date_trunc('month', a.created_at + interval '13 month') = date_trunc('month', totals.mes) then totals.total end) mes_14,
  sum(case when date_trunc('month', a.created_at + interval '14 month') = date_trunc('month', totals.mes) then totals.total end) mes_15,
  sum(case when date_trunc('month', a.created_at + interval '15 month') = date_trunc('month', totals.mes) then totals.total end) mes_16,
  sum(case when date_trunc('month', a.created_at + interval '16 month') = date_trunc('month', totals.mes) then totals.total end) mes_17,
  sum(case when date_trunc('month', a.created_at + interval '17 month') = date_trunc('month', totals.mes) then totals.total end) mes_18,
  sum(case when date_trunc('month', a.created_at + interval '18 month') = date_trunc('month', totals.mes) then totals.total end) mes_19,
  sum(case when date_trunc('month', a.created_at + interval '19 month') = date_trunc('month', totals.mes) then totals.total end) mes_20,
  sum(case when date_trunc('month', a.created_at + interval '20 month') = date_trunc('month', totals.mes) then totals.total end) mes_21,
  sum(case when date_trunc('month', a.created_at + interval '21 month') = date_trunc('month', totals.mes) then totals.total end) mes_22,
  sum(case when date_trunc('month', a.created_at + interval '22 month') = date_trunc('month', totals.mes) then totals.total end) mes_23,
  sum(case when date_trunc('month', a.created_at + interval '23 month') = date_trunc('month', totals.mes) then totals.total end) mes_24,
  sum(case when date_trunc('month', a.created_at + interval '24 month') = date_trunc('month', totals.mes) then totals.total end) mes_25,
  sum(case when date_trunc('month', a.created_at + interval '25 month') = date_trunc('month', totals.mes) then totals.total end) mes_26,
  sum(case when date_trunc('month', a.created_at + interval '26 month') = date_trunc('month', totals.mes) then totals.total end) mes_27,
  sum(case when date_trunc('month', a.created_at + interval '27 month') = date_trunc('month', totals.mes) then totals.total end) mes_28,
  sum(case when date_trunc('month', a.created_at + interval '28 month') = date_trunc('month', totals.mes) then totals.total end) mes_29,
  sum(case when date_trunc('month', a.created_at + interval '29 month') = date_trunc('month', totals.mes) then totals.total end) mes_30,
  sum(case when date_trunc('month', a.created_at + interval '30 month') = date_trunc('month', totals.mes) then totals.total end) mes_31,
  sum(case when date_trunc('month', a.created_at + interval '31 month') = date_trunc('month', totals.mes) then totals.total end) mes_32,
  sum(case when date_trunc('month', a.created_at + interval '32 month') = date_trunc('month', totals.mes) then totals.total end) mes_33,
  sum(case when date_trunc('month', a.created_at + interval '33 month') = date_trunc('month', totals.mes) then totals.total end) mes_34,
  sum(case when date_trunc('month', a.created_at + interval '34 month') = date_trunc('month', totals.mes) then totals.total end) mes_35,
  sum(case when date_trunc('month', a.created_at + interval '35 month') = date_trunc('month', totals.mes) then totals.total end) mes_36

  from accounts a
  left join (
    select lm.id_conta, ad.mes, lm.total
    from all_dates ad
    left join leads_per_month lm on lm.mes_lead = ad.mes
  ) as totals on totals.id_conta = a.id and totals.mes between date_trunc('month', a.created_at) and date_trunc('month', a.created_at + 36 * interval '1' month)
  group by 1,2,3
  order by 1,3) aaa;

\copy (select * from leads_by_month_by_account) to '~/Desktop/leadgen.csv' with csv header;

/*select criacao,
  count(distinct id_conta) total,
  sum(case when mes_1 > 1 then 1 end) mes1,
  sum(case when mes_3 > 1 then 1 end) mes3,
  sum(case when mes_4 > 1 then 1 end) mes4,
  sum(case when mes_5 > 1 then 1 end) mes5,
  sum(case when mes_6 > 1 then 1 end) mes6,
  sum(case when mes_7 > 1 then 1 end) mes7,
  sum(case when mes_8 > 1 then 1 end) mes8,
  sum(case when mes_9 > 1 then 1 end) mes9,
  sum(case when mes_10 > 1 then 1 end) mes10,
  sum(case when mes_12 > 1 then 1 end) mes12,
  sum(case when mes_13 > 1 then 1 end) mes13,
  sum(case when mes_14 > 1 then 1 end) mes14,
  sum(case when mes_15 > 1 then 1 end) mes15,
  sum(case when mes_16 > 1 then 1 end) mes16,
  sum(case when mes_17 > 1 then 1 end) mes17,
  sum(case when mes_18 > 1 then 1 end) mes18,
  sum(case when mes_19 > 1 then 1 end) mes19,
  sum(case when mes_20 > 1 then 1 end) mes20,
  sum(case when mes_22 > 1 then 1 end) mes22,
  sum(case when mes_23 > 1 then 1 end) mes23,
  sum(case when mes_24 > 1 then 1 end) mes24
  from leads_by_month_by_account
  group by 1
  order by 1;*/

-- select criacao,
--   count(distinct id_conta) total,
--   sum(case when mes_1 > 1 then 1 end) mes1,
--   sum(case when mes_2 > 1 and mes_1 > 1 then 1 end) mes2,
--   sum(case when mes_3 > 1 and mes_2 > 1 then 1 end) mes3
--   from leads_by_month_by_account
--   group by 1
--   order by 1;

sales_lead_time.sql

create temp table first_sales as
select lm.id lm_id, min(lh.id) first_sale, min(lh.created_at) created_at from lead_lifecycle_histories lh
inner join leads_managers lm on lm.id = lh.leads_manager_id
where lh.type = 'Lead::LifecycleHistory::MarkedSaleHistory'
group by 1;


select acc.id,
(extract(year from fs.created_at) - extract(year from acc.created_at))*12 +
(extract(month from fs.created_at) - extract(month from acc.created_at)) sales_lead_time
from accounts acc
inner join leads_managers lm on lm.account_id = acc.id
inner join first_sales fs on fs.lm_id = lm.id;

vendas.sql

create temp table results as
select a.id, count(lh.id) from accounts a
inner join leads_managers lm on lm.account_id = a.id
left join lead_lifecycle_histories lh on lh.leads_manager_id = lm.id
  AND lh.type = 'Lead::LifecycleHistory::MarkedSaleHistory'
group by 1
order by 1;