To answer this business question, we can break down the approach into sub-questions:
- How do I define marketing spend?
- How do I segment by Month and Description?
- Do I need to filter the results of this query to only include certain information?
- What transformations need to happen to present this data in the form of a stacked bar chart?
Answers to questions:
- Marketing spend is defined as the sum of cost from the marketing table
- Segment the results of this measure by dragging the created_date and description fields from the Marketing table into the Dimensions box of the chart creator. Adding fields to the Dimensions box in the chart creator will effectively add those fields to the SELECT and GROUP BY clauses of the underlying SQL. Update the date bucket for the created_date to month.
- Add the created_date field to the Filters box in the chart creator.
- The format will not work with a stacked bar chart as column one has repeated values. To resolve, we need to apply a pivot step in the data pipeline. This will transform the data such that the unique values populated into column 2 become columns in the resulting table and the values in column 3 populate the intersection of each row and column combination.
- Aggregation Function: Sum(Cost)
- WHERE (used with Calendar Interval)
- GROUP BY month and description
- ORDER BY month and description
Interactive Mode Configuration and Sample Answer:
SELECT TO_CHAR("ma0"."created_date", 'YYYY-MM') AS "Month of Created Date" , "ma0"."description" AS "Description" , SUM("ma0"."cost") AS "Total sum of Cost" FROM "public"."marketing" AS "ma0" GROUP BY "Month of Created Date", "Description" ORDER BY "Month of Created Date" ASC, "Description" ASC LIMIT 1000;