Applovin Models

Copy-paste SQL models for turning raw Applovin advertiser reports into business-ready campaign performance reporting.

Copy and Paste these Applovin Models into your Data Layer to quickly go from raw data sources to business ready Applovin Reporting. Please note:

Models must be added chronological order from top to bottom (so that they can reference each other).
When adding your own models you must update the reference to include your source names. For example, in the model templates below.

Base

Transforms Raw Data to Clean Tables focused on Core Concepts.

applovin_advertiser_report_base

select
    md5(
      concat_ws(
        '::'
        , day::text
        , campaign_id_external
        , campaign
        , campaign_type
        , campaign_ad_type
        , campaign_package_name
        , campaign_store_id
        , ad
        , ad_id::text
        , ad_type
        , ad_creative_type
        , creative_set
        , creative_set_id
        , size
        , placement_type
        , external_placement_id
        , custom_page_id
        , device_type
        , platform
        , country
        , traffic_source
        , optimization_day_target::text
      )
    ) as id --pk
  , day::date as report_date
  , TRIM(campaign_id_external) as campaign_id
  , TRIM(campaign) as campaign_name
  , TRIM(campaign_type) as campaign_type
  , TRIM(campaign_ad_type) as campaign_ad_type
  , TRIM(campaign_package_name) as campaign_package_name
  , TRIM(campaign_store_id) as campaign_store_id
  , campaign_bid_goal::float as campaign_bid_goal
  , campaign_roas_goal::float as campaign_roas_goal_pct
  , TRIM(ad) as ad_name
  , ad_id::varchar as ad_id
  , TRIM(ad_type) as ad_type
  , TRIM(ad_creative_type) as ad_creative_type
  , TRIM(creative_set) as creative_set
  , TRIM(creative_set_id) as creative_set_id
  , TRIM(size) as ad_size
  , TRIM(placement_type) as placement_type
  , TRIM(external_placement_id) as external_placement_id
  , TRIM(custom_page_id) as custom_page_id
  , TRIM(device_type) as device_type
  , TRIM(platform) as platform
  , TRIM(country) as country
  , TRIM(traffic_source) as traffic_source
  , optimization_day_target as optimization_day_target
  , impressions as n_impressions
  , clicks as n_clicks
  , REPLACE(ctr, '%', '')::float as ctr_pct
  , conversions as n_installs
  , REPLACE(conversion_rate, '%', '')::float as conversion_rate_pct
  , REPLACE(cost, '$', '')::float as spend
  , REPLACE(average_cpc, '$', '')::float as average_cpc
  , REPLACE(average_cpa, '$', '')::float as average_cpa
  , sales as n_sales_events

  -- EXAMPLE ONLY — replace/extend with the attribution-window columns
  -- your account actually syncs (0d/1d/2d/3d/7d/14d/28d/30d/90d/1y)
  , sales_0_d as n_purchases_0d
  , sales_14_d as n_purchases_14d
  , sales_28_d as n_purchases_28d
  , roas_0_d::float as roas_0d
  , roas_14_d::float as roas_14d
  , roas_28_d::float as roas_28d
  , cpp_0_d::float as cost_per_purchase_0d
  , cpp_7_d::float as cost_per_purchase_7d
  , cpp_14_d::float as cost_per_purchase_14d
  , target_event_count_0_d as n_target_events_0d
  , cost_per_target_event_0_d::float as cost_per_target_event_0d

  , _fivetran_synced as __updated_at
from {{sources.applovin.advertiser_report}}
where _fivetran_deleted is not true

Fact

Creates a “Fact” about a core concept. For example, creating a customer order fact that defines when a customer placed their first order. This then gets joined downstream in output.

applovin_campaign_performance_facts

select
    b.report_date::text || '-' || b.campaign_id as id --pk
  , b.report_date
  , b.campaign_id
  , max(b.campaign_name) as campaign_name
  , max(b.campaign_type) as campaign_type
  , max(b.campaign_ad_type) as campaign_ad_type
  , max(b.campaign_package_name) as campaign_package_name
  , max(b.campaign_store_id) as campaign_store_id
  , sum(b.n_impressions) as n_impressions
  , sum(b.n_clicks) as n_clicks
  , sum(b.n_installs) as n_installs
  , sum(b.spend) as spend
  , sum(b.n_sales_events) as n_sales_events
  , sum(b.n_purchases_0d) as n_purchases_0d
  , sum(b.n_purchases_14d) as n_purchases_14d
  , sum(b.n_purchases_28d) as n_purchases_28d
  , sum(b.n_target_events_0d) as n_target_events_0d
from {{models.applovin_advertiser_report_base}} b
group by 1, 2, 3

Output

Final tables ready for use in explores in Switchboard. Output tables typically represent core business concepts that get joined together.

applovin_campaign_performance_output

select
    f.id as id --pk
  , f.report_date
  , f.campaign_id
  , f.campaign_name
  , f.campaign_type
  , f.campaign_ad_type
  , f.campaign_package_name
  , f.campaign_store_id
  , f.n_impressions
  , f.n_clicks
  , f.n_installs
  , f.spend
  , f.n_sales_events
  , f.n_purchases_0d
  , f.n_purchases_14d
  , f.n_purchases_28d
  , f.n_target_events_0d
  , f.spend / NULLIF(f.n_installs, 0) as cost_per_install
  , f.spend / NULLIF(f.n_clicks, 0) as cost_per_click
  , f.n_clicks::float / NULLIF(f.n_impressions, 0) as ctr_pct
  , f.n_installs::float / NULLIF(f.n_impressions, 0) as install_rate_pct
from {{models.applovin_campaign_performance_facts}} f