7. Create a chart to demonstrate the relationship between the company's marketing spend and customer acquisition, by month

Overview

To answer this business question, we can break it down into sub-questions:

  1. How do I calculate the company's marketing spend by month? 
  2. How do I calculate customer acquisition by month? 
  3. How do I combine the results of these two queries? 
  4. What is the best way to demonstrate the relationship between these two metrics? 

Answers to questions: 

  1. marketing spend = sum of the cost field from the marketing table
  2. customer acquisition = count of ids from the subscription table
  3. The complexity of this question comes from the fact that there is no relationship between the Marketing table and the Subscriptions table in the schema, and that we need to measure both of these fields against different dates, marketing.created_date and subscriptions.payment_start_date, respectively. To blend the results of two independent queries into a single chart, utilize Layers in Chartio. After defining the queries in each layer, make sure to utilize the Merge Type of Outer Join, as this will ensure that no records are dropped out of the result set if a particular date value does not exist in one of the query results
  4. Given the independent nature of these inputs, select a visualization that allows for multiple Y axes. Namely, the line chart or the bar-line chart. 

Data Location:

Query (Layer) 1 

Query (Layer) 2

Analysis Technique:

SQL Concepts:

Chartio Concepts:

Additional Transformations:

  • None required, though the sort could be applied using the data pipeline

Visualization Approach:

Interactive Mode Configuration and Sample Answer:

SQL: 

Query 1: 

SELECT 
      TO_CHAR("ma0"."created_date", 'YYYY-MM') AS "Month of Created Date"
    , SUM("ma0"."cost") AS "Total sum of Cost"
FROM "public"."marketing" AS "ma0"
WHERE ("ma0"."created_date" BETWEEN {CALENDAR_INTERVAL.START} AND {CALENDAR_INTERVAL.END})
GROUP BY "Month of Created Date"
ORDER BY "Month of Created Date" ASC 
LIMIT 1000;

Query 2:

SELECT 
      TO_CHAR("su0"."payment_start_date", 'YYYY-MM') AS "Month Payment Start"
    , COUNT(DISTINCT "su0"."subscription_id") AS "Count of Subscriptions"
FROM "public"."subscriptions" AS "su0"
WHERE ("su0"."payment_start_date" BETWEEN {CALENDAR_INTERVAL.START} AND {CALENDAR_INTERVAL.END})
GROUP BY "Month Payment Start"
ORDER BY "Month Payment Start" ASC 
LIMIT 1000;

SQL Note: This query could also be performed within a single SQL query; however, it requires subqueries and manually writing the FULL OUTER JOIN. To reduce complexity, the WHERE clauses are removed from the queries. Answer shown below: 

SELECT 
      Created_Month
    , Cost
    , Subscriptions
FROM
(

    (SELECT 
          TO_CHAR(created_date, 'YYYY-MM') AS Created_Month
        , SUM(cost) AS Cost
    FROM public.marketing
    GROUP BY TO_CHAR(created_date, 'YYYY-MM')
    ORDER BY TO_CHAR(created_date, 'YYYY-MM') ASC) as Q1

    FULL OUTER JOIN

    (SELECT 
          TO_CHAR(payment_start_date, 'YYYY-MM') AS Payment_Start_Month
        , COUNT(DISTINCT subscription_id) AS Subscriptions
    FROM public.subscriptions
    GROUP BY TO_CHAR(payment_start_date, 'YYYY-MM')
    ORDER BY TO_CHAR(payment_start_date, 'YYYY-MM') ASC) as Q2

    ON Q1.Created_Month = Q2.Payment_start_Month

) as Q3
WHERE Created_Month is not null