/
int_klaviyo.yml
167 lines (159 loc) · 9.6 KB
/
int_klaviyo.yml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
version: 2
models:
- name: int_klaviyo__event_attribution
description: >
Table enriching events with an additional layer of last-touch attribution.
Though Klaviyo already performs attribution on events each day, this extra step is necessary,
as certain kinds of events/metrics never get attributed to flows or campaigns via Klaviyo's internal model.
By default, the package performs attribution [in line with Klaviyo](https://help.klaviyo.com/hc/en-us/articles/115005248128).
It considers email opens and clicks, and SMS opens as the events eligible to be credited with conversions.
This attribution-eligibility can be configured by the `klaviyo__eligible_attribution_events` variable.
Note that this refers to the events eligible to credit campaigns and flows with conversions, _not_ the events
eligible to receive attribution (all kinds of events are privy to this).
Similar to Klaviyo, the package by default considers the conversion period/lookback window for email events to
be 120 hours (5 days) and 24 hours for SMS events. These can be configured through the
`klaviyo__email_attribution_lookback` and `klaviyo__sms_attribution_lookback` variables, respectively (in integer-hours).
Note: this model has an incremental materialization. Custom event-columns specified by the `klaviyo__event_pass_through_columns`
variable will appear here as well.
tests:
- dbt_utils.unique_combination_of_columns:
combination_of_columns:
- event_id
- source_relation
columns:
- name: variation_id
description: >
Unique ID of the attributed flow or campaign variation group.
This does not map onto another table.
- name: campaign_id
description: Foreign key referencing the CAMPAIGN that the event is attributed to.
- name: occurred_at
description: Timestamp of when the event was triggered.
- name: flow_id
description: Foreign key referencing the FLOW that the event is attributed to.
- name: flow_message_id
description: >
Unique ID of the FLOW_MESSAGE that the event is attributed to.
This does not map onto another table.
- name: event_id
description: Unique ID of the event.
tests:
- not_null
- name: metric_id
description: Foreign key referencing the metric being captured.
- name: person_id
description: Foreign key referencing the PERSON who triggered the event.
- name: type
description: Type of event that was triggered. This is the same as the METRIC name.
- name: uuid
description: Universally Unique Identifier of the event.
- name: numeric_value
description: Numeric value associated with the event (ie the dollars associated with a purchase).
- name: touch_type
description: >
Type of touch/message that the event itself is already attributed to in Klaviyo. Either 'flow', 'campaign', or null.
Note that the package will refer to campaign and flow interactions as "touches".
- name: occurred_on
description: Calendar date (UTC) on which the event occurred.
- name: touch_id
description: Coalescing of the Klaviyo-attributed campaign_id and flow_id.
- name: touch_session
description: >
ID used to batch an individual person's events into attribution-groups. Each event that comes
attributed to a campaign or flow begins a new session/batch, in which the following events without
a flow/campaign_id have the same `touch_session` (and may be attributed to the same flow/campaign).
- name: session_start_at
description: >
Timestamp of when, relative to the current event, this person last interacted with a campaign or flow
according to Klaviyo. This is the beginning of the event's touch-session.
- name: session_event_type
description: >
The type of event through which, relative to the current event, the person last interacted with a campaign
or flow. This information is used to determine which lookback window to use (email vs sms).
- name: last_touch_id
description: The campaign or flow that the package attributed the event to.
- name: last_touch_type
description: What kind of touch the event was attributed to by the package -- 'campaign', 'flow', or null.
- name: _fivetran_synced
description: UTC Timestamp that indicates the start time of the Fivetran job that synced this event row.
- name: unique_event_id
description: The unique identifier for the combination of event_id and source_relation columns.
- name: source_relation
description: >
The source where this data was pulled from. If you are making use of the `union_schemas` variable, this will be the source schema.
If you are making use of the `union_databases` variable, this will be the source database. If you are not unioining together multiple
sources, this will be an empty string.
- name: int_klaviyo__campaign_flow_metrics
description: >
Table that draws from the `klaviyo__person_campaign_flow` model to aggregate event metrics
to the campaign or flow AND variation grain. A campaign with A/B versions will have 2 records.
**Counts** of the unique users and instances of the events, as well as **sums** of the numeric value associated
with events (i.e. revenue) will be pivoted out into columns, as configured by the `klaviyo__count_metrics` and `klaviyo__sum_revenue_metrics`
variables, respectively. See the dbt_project.yml file for the default metrics used.
These columns will be prefixed with `count_`, `unique_count_`, and `sum_revenue_`.
tests:
- dbt_utils.unique_combination_of_columns:
combination_of_columns:
- variation_id
- source_relation
- last_touch_campaign_id
- last_touch_flow_id
columns:
- name: last_touch_campaign_id
description: Foreign key referencing the CAMPAIGN attributed with these metrics (by the package's attribution model).
- name: last_touch_flow_id
description: Foreign key referencing the FLOW attributed with these metrics (by the package's attribution model).
- name: variation_id
description: >
Unique ID of the attributed flow or campaign variation group.
This does not map onto another table.
- name: total_count_unique_people
description: The count of the distinct people that have interacted with this campaign or flow.
- name: first_event_at
description: Timestamp of the first ever interaction between this campaign/flow and a person.
- name: last_event_at
description: Timestamp of the most recent interaction between this campaign/flow and a person.
- name: source_relation
description: >
The source where this data was pulled from. If you are making use of the `union_schemas` variable, this will be the source schema.
If you are making use of the `union_databases` variable, this will be the source database. If you are not unioining together multiple
sources, this will be an empty string.
- name: int_klaviyo__person_metrics
description: >
Table that draws from the `klaviyo__person_campaign_flow` model to aggregate event metrics
to the person grain.
**Counts** of instances of the events and **sums** of the numeric value (i.e. revenue) associated
with events (total vs organic/not attributed to flows or campaigns) will be pivoted out into columns, as configured by the
`klaviyo__count_metrics` and `klaviyo__sum_revenue_metrics`variables, respectively. See the dbt_project.yml file for
the default metrics used.
These columns will be prefixed with `total_count_`, `total_sum_revenue_` (organic + attributed), and `organic_sum_revenue_`
(not attributed to a campaign or flow).
tests:
- dbt_utils.unique_combination_of_columns:
combination_of_columns:
- person_id
- source_relation
columns:
- name: person_id
description: Unique ID of the person.
- name: count_total_campaigns
description: Count of the number of campaigns this person has interacted with.
- name: count_total_flows
description: Count of the number of flows this person has interacted with.
- name: first_event_at
description: Timestamp of when the user first triggered an event (not limited to campaign and flow interactions).
- name: last_event_at
description: Timestamp of when the user last triggered an event (not limited to campaign and flow interactions).
- name: first_campaign_touch_at
description: Timestamp of when the user first interacted with a campaign.
- name: last_campaign_touch_at
description: Timestamp of when the user last interacted with a campaign.
- name: first_flow_touch_at
description: Timestamp of when the user first interacted with a flow.
- name: last_flow_touch_at
description: Timestamp of when the user last interacted with a flow.
- name: source_relation
description: >
The source where this data was pulled from. If you are making use of the `union_schemas` variable, this will be the source schema.
If you are making use of the `union_databases` variable, this will be the source database. If you are not unioining together multiple
sources, this will be an empty string.