We recently added the following high-level metrics to each report end model:
conversions
view_through_conversions
total_value
total_items
I wonder if users would also want to see these metrics (or at least just conversions and total_value?) for specific types of conversion events (the above are totals).
So if a user ran the package with the default reddit_ads__conversion_event_types values, they'd also have conversion metrics relating to lead, purchase, and custom events specifically.
How would you implement this feature?
For account_report, something like
{{ config(enabled=var('ad_reporting__reddit_ads_enabled', True)) }}
with report as (
select *
from {{ var('account_daily_report') }}
),
accounts as (
select *
from {{ var('account') }}
),
{# This includes data at the event type level that we'll need to roll up and pivot out #}
conversions_report as (
select *
from {{ var('account_conversions_report') }}
),
rollup_conversions_report as (
select
source_relation,
date_day,
account_id,
sum(conversions) as conversions,
sum(view_through_conversions) as view_through_conversions,
sum(total_value) as total_value,
sum(total_items) as total_items
{% if var('reddit_ads__conversion_event_types') %}
{% for event_type in var('reddit_ads__conversion_event_types') %}
, sum(case when event_name = '{{ event_type|lower }}' then conversions else 0 end) as {{ event_type|lower }}_conversions
, sum(case when event_name = '{{ event_type|lower }}' then total_value else 0 end) as {{ event_type|lower }}_conversions_value
{% endfor %}
{% endif %}
{{ fivetran_utils.persist_pass_through_columns(pass_through_variable='reddit_ads__account_conversions_passthrough_metrics', transform = 'sum') }}
from conversions_report
{% if var('reddit_ads__conversion_event_types') %}
where
{% for event_type in var('reddit_ads__conversion_event_types') %}
event_name = '{{ event_type|lower }}'
{% if not loop.last %} or {% endif %}
{% endfor %}
{% endif %}
group by 1,2,3
),
joined as (
select
report.source_relation,
report.date_day,
report.account_id,
accounts.currency,
accounts.attribution_type,
accounts.status,
accounts.time_zone_id,
sum(report.clicks) as clicks,
sum(report.impressions) as impressions,
sum(report.spend) as spend,
sum(rollup_conversions_report.conversions) as conversions,
sum(rollup_conversions_report.view_through_conversions) as view_through_conversions,
sum(rollup_conversions_report.total_value) as total_value,
sum(rollup_conversions_report.total_items) as total_items
{% if var('reddit_ads__conversion_event_types') %}
{% for event_type in var('reddit_ads__conversion_event_types') %}
, sum(rollup_conversions_report.{{ event_type|lower }}_conversions) as {{ event_type|lower }}_conversions
, sum(rollup_conversions_report.{{ event_type|lower }}_conversions_value) as {{ event_type|lower }}_conversions_value
{% endfor %}
{% endif %}
{{ fivetran_utils.persist_pass_through_columns(pass_through_variable='reddit_ads__account_passthrough_metrics', transform = 'sum') }}
{{ fivetran_utils.persist_pass_through_columns(pass_through_variable='reddit_ads__account_conversions_passthrough_metrics', transform = 'sum') }}
from report
left join accounts
on report.account_id = accounts.account_id
and report.source_relation = accounts.source_relation
left join rollup_conversions_report
on report.account_id = rollup_conversions_report.account_id
and report.source_relation = rollup_conversions_report.source_relation
and report.date_day = rollup_conversions_report.date_day
{{ dbt_utils.group_by(7) }}
)
select *
from joined
I do wonder if we'd want to prepend a total_ to the pre-existing conversions and view_through_conversions fields, though this would be breaking
Is there an existing feature request for this?
Describe the Feature
We recently added the following high-level metrics to each report end model:
I wonder if users would also want to see these metrics (or at least just
conversions
andtotal_value
?) for specific types of conversion events (the above are totals).So if a user ran the package with the default
reddit_ads__conversion_event_types
values, they'd also have conversion metrics relating tolead
,purchase
, andcustom
events specifically.How would you implement this feature?
For account_report, something like
I do wonder if we'd want to prepend a
total_
to the pre-existingconversions
andview_through_conversions
fields, though this would be breakingDescribe alternatives you've considered
Just include the total conversions fields
Are you interested in contributing this feature?
Anything else?
No response