Calculating average number of customers that were contacted before successful conversion

I three tables:

Table Name: Customer

Column Name:        Data Type:              Description:
id                   integer        Unique id for customer
company_id         integer(FK to company.id) Unique id for a company that the customer works for
contact_name           varchar           Full name of customer
contact_email          varchar           Email of customer
create_dt              date          Date when customer added to database

Table Name: Company

Column Name:          Data Type:              Description:
id                    integer        Unique id for company
company_name          varchar             Name of company
create_dt             date     Date when company added to database
converted             boolean        if customer was converted
converted_dt          date       Date company purchased the product Value is null if the company did not

Table Name: Contacted (actions/ how the customer was contacted)

Column Name:          Data Type:              Description:
customer_id       integer(FK to Customer.id)    Unique id for contact
rep_name          varchar      Name of the rep who made the contact with the customer
rep_email         varchar      Email of rep who made the contact
contact_channel   varchar      Channel used to contact the customer(email,phone, in-person...)
contact_dt         date        Date of contact

I’m trying to write a query that to show the average number of customers that were contacted before they were successfully converted as a customer, on a company level. I’m able to get the number of counts before customers were successfully converted but not sure how to get the avg count and factor in instances where they were eventually converted.

COUNT(CASE WHEN converted = FALSE AND converted_dt is NULL THEN 1 ELSE 0 END) as contacts_made_before_conversion,

CASE WHEN Company.converted_dt is NOT null THEN count(contact_dt) over(partition by company_name order by contact_dt DESC END) as contacted_counts

FROM Contacted

LEFT JOIN 
  (SELECT Customer.id as customer_id , company_name, 
   Company.converted, 
   FROM Customer LEFT JOIN Company
   ON Company.id = Customer.company_id) cc

ON cc.customer_id = Contacted.customer_id

GROUP BY company_name, customer_id

Answer

This is basically a left join with aggregation:

select c.company_id,
       count(distinct c.id) as num_customers,
       count(co.id) as num_contacts,
       count(co.id) * 1.0 / count(distinct c.id) as num_per_customer
from customer c left join
     contacted co
     on co.customer_id = c.id and
        co.converted_dt < c.created_dt
group by c.company_id

Advertisement

Answer