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


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 = Total 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 Datasets in Chartio. After defining the queries in each dataset, 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 (Dataset) 1 

Query (Dataset) 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:

Merge Datasets.png


Query 1: 

      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"
GROUP BY "Month of Created Date"
ORDER BY "Month of Created Date" ASC 
LIMIT 1000;

Query 2:

      TO_CHAR("su0"."payment_start_date", 'YYYY-MM') AS "Month of Payment Start Date"
    , COUNT(DISTINCT "su0"."subscription_id") AS "Count of Subscriptions"
FROM "public"."subscriptions" AS "su0"
WHERE ("su0"."payment_start_date" BETWEEN {CALENDAR.START} AND {CALENDAR.END})
GROUP BY "Month of Payment Start Date"
ORDER BY "Month of Payment Start Date" 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: 

    , Cost
    , Subscriptions

          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


          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