Attribution by answer

1-- core.zigpoll.attribution_by_answer
2-- Orders and revenue per answer option, for post-purchase questions such as "How
3-- did you hear about us?".
4-- Grain: month x zigpoll_account x poll x slide x answer x currency. Months are UTC
5-- calendar months of the response. Only responses carrying a Shopify order id,
6-- synthetic polls excluded.
7-- Depends on: core.zigpoll.answer_detail
8--
9-- Revenue is `order_value` as Zigpoll captured it on the response, in the order's
10-- own currency. Currency is part of the grain because summing EUR and USD orders
11-- into one number is meaningless; convert downstream if you need one figure.
12--
13-- MULTI-SELECT: an order whose buyer ticked "Instagram" and "A friend" counts in
14-- full under both. Each row is right on its own, but the rows of one slide add up
15-- to more than total revenue. For a single-choice slide they add up exactly.
16--
17-- Each order is counted once per answer even if the buyer somehow answered twice:
18-- orders and revenue are taken per distinct order id.
19
20WITH order_answers AS (
21    SELECT DISTINCT
22        zigpoll_account,
23        poll_id,
24        poll_title,
25        slide_id,
26        slide_title,
27        DATE_TRUNC(response_date, MONTH) AS month,
28        answer_value,
29        currency_code,
30        shopify_order_id,
31        order_value
32    FROM {{core.zigpoll.answer_detail}}
33    WHERE shopify_order_id IS NOT NULL
34      AND answer_value IS NOT NULL
35      AND NOT COALESCE(is_synthetic, FALSE)
36      -- Open text has no options to group on; matrix and rank answers need their
37      -- row or rank to mean anything, which this grain does not carry.
38      AND COALESCE(slide_type, '') NOT IN ('short-answer', 'long-answer', 'email-capture', 'phone-capture', 'matrix', 'rank')
39)
40