[Feature] Pivot out conversion metrics for each type of conversion event in reddit_ads__conversion_event_types

[fivetran-jamie]

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

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

Describe alternatives you've considered

Just include the total conversions fields

Are you interested in contributing this feature?

• Yes.
• Yes, but I will need assistance.
• No.

Anything else?

No response

[fivetran-jamie]

If anyone is interested in seeing this, please chime in!