stg_google_play.yml

version: 2

models:

  - name: stg_google_play__stats_installs_app_version
    description: >
      Each line is a daily snapshot of the monthly installs report by app and app version.
      Records with `NULL` app version codes are aggregated together into daily batches.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - source_relation
            - package_name
            - date_day
            - app_version_code
    columns:
      - name: source_relation
        description: "{{ doc('source_relation') }}"
      - name: active_devices_last_30_days
        description: '{{ doc("active_device_installs") }}'
      - name: app_version_code
        description: '{{ doc("app_version_code") }}'
      - name: device_installs
        description: '{{ doc("daily_device_installs") }}'
      - name: device_uninstalls
        description: '{{ doc("daily_device_uninstalls") }}'
      - name: device_upgrades
        description: '{{ doc("daily_device_upgrades") }}'
      - name: user_installs
        description: '{{ doc("daily_user_installs") }}'
      - name: user_uninstalls
        description: '{{ doc("daily_user_uninstalls") }}'
      - name: date_day
        description: '{{ doc("date") }}'
      - name: install_events
        description: '{{ doc("install_events") }}'
      - name: package_name
        description: '{{ doc("package_name") }}'
      - name: uninstall_events
        description: '{{ doc("uninstall_events") }}'
      - name: update_events
        description: '{{ doc("update_events") }}'

  - name: stg_google_play__stats_crashes_app_version
    description: >
      Each line is a daily snapshot of the monthly crashes report by app version and app.
      Records with `NULL` app version codes are aggregated together into daily batches.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - source_relation
            - package_name
            - date_day
            - app_version_code
    columns:
      - name: source_relation
        description: "{{ doc('source_relation') }}"
      - name: app_version_code
        description: '{{ doc("app_version_code") }}'
      - name: anrs
        description: '{{ doc("daily_anrs") }}'
      - name: crashes
        description: '{{ doc("daily_crashes") }}'
      - name: date_day
        description: '{{ doc("date") }}'
      - name: package_name
        description: '{{ doc("package_name") }}'

  - name: stg_google_play__stats_ratings_app_version
    description: >
      Each line is a daily snapshot of the monthly ratings report by app and app version.
      Records with `NULL` app version codes are aggregated together into daily batches.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - source_relation
            - package_name
            - date_day
            - app_version_code
    columns:
      - name: source_relation
        description: "{{ doc('source_relation') }}"
      - name: app_version_code
        description: '{{ doc("app_version_code") }}'
      - name: average_rating
        description: '{{ doc("daily_average_rating") }}'
      - name: date_day
        description: '{{ doc("date") }}'
      - name: package_name
        description: '{{ doc("package_name") }}'
      - name: rolling_total_average_rating
        description: '{{ doc("total_average_rating") }}'

  - name: stg_google_play__stats_installs_device
    description: >
      Each line is a daily snapshot of the monthly installs report by app and type of device model.
      Records with `NULL` device types are aggregated together into daily batches.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - source_relation
            - package_name
            - date_day
            - device
    columns:
      - name: source_relation
        description: "{{ doc('source_relation') }}"
      - name: active_devices_last_30_days
        description: '{{ doc("active_device_installs") }}'
      - name: device_installs
        description: '{{ doc("daily_device_installs") }}'
      - name: device_uninstalls
        description: '{{ doc("daily_device_uninstalls") }}'
      - name: device_upgrades
        description: '{{ doc("daily_device_upgrades") }}'
      - name: user_installs
        description: '{{ doc("daily_user_installs") }}'
      - name: user_uninstalls
        description: '{{ doc("daily_user_uninstalls") }}'
      - name: date_day
        description: '{{ doc("date") }}'
      - name: device
        description: '{{ doc("device") }}'
      - name: install_events
        description: '{{ doc("install_events") }}'
      - name: package_name
        description: '{{ doc("package_name") }}'
      - name: uninstall_events
        description: '{{ doc("uninstall_events") }}'
      - name: update_events
        description: '{{ doc("update_events") }}'

  - name: stg_google_play__stats_ratings_device
    description: >
      Each line is a daily snapshot of the monthly ratings report by app and type of device model.
      Records with `NULL` device types are aggregated together into daily batches.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - source_relation
            - package_name
            - date_day
            - device
    columns:
      - name: source_relation
        description: "{{ doc('source_relation') }}"
      - name: average_rating
        description: '{{ doc("daily_average_rating") }}'
      - name: date_day
        description: '{{ doc("date") }}'
      - name: device
        description: '{{ doc("device") }}'
      - name: package_name
        description: '{{ doc("package_name") }}'
      - name: rolling_total_average_rating
        description: '{{ doc("total_average_rating") }}'

  - name: stg_google_play__stats_installs_os_version
    description: >
      Each line is a daily snapshot of the monthly installs report by app and user OS version.
      Records with `NULL` os versions are aggregated together into daily batches.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - source_relation
            - package_name
            - date_day
            - android_os_version
    columns:
      - name: source_relation
        description: "{{ doc('source_relation') }}"
      - name: active_devices_last_30_days
        description: '{{ doc("active_device_installs") }}'
      - name: android_os_version
        description: '{{ doc("android_os_version") }}'
      - name: device_installs
        description: '{{ doc("daily_device_installs") }}'
      - name: device_uninstalls
        description: '{{ doc("daily_device_uninstalls") }}'
      - name: device_upgrades
        description: '{{ doc("daily_device_upgrades") }}'
      - name: user_installs
        description: '{{ doc("daily_user_installs") }}'
      - name: user_uninstalls
        description: '{{ doc("daily_user_uninstalls") }}'
      - name: date_day
        description: '{{ doc("date") }}'
      - name: install_events
        description: '{{ doc("install_events") }}'
      - name: package_name
        description: '{{ doc("package_name") }}'
      - name: uninstall_events
        description: '{{ doc("uninstall_events") }}'
      - name: update_events
        description: '{{ doc("update_events") }}'

  - name: stg_google_play__stats_ratings_os_version
    description: >
      Each line is a daily snapshot of the monthly ratings report by app and user OS version.
      Records with `NULL` os versions are aggregated together into daily batches.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - source_relation
            - package_name
            - date_day
            - android_os_version
    columns:
      - name: source_relation
        description: "{{ doc('source_relation') }}"
      - name: average_rating
        description: '{{ doc("daily_average_rating") }}'
      - name: date_day
        description: '{{ doc("date") }}'
      - name: android_os_version
        description: '{{ doc("device") }}'
      - name: package_name
        description: '{{ doc("package_name") }}'
      - name: rolling_total_average_rating
        description: '{{ doc("total_average_rating") }}'

  - name: stg_google_play__stats_crashes_os_version
    description: >
      Each line is a daily snapshot of the monthly crashes report by app and user OS version.
      Records with `NULL` os versions are aggregated together into daily batches.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - source_relation
            - package_name
            - date_day
            - android_os_version
    columns:
      - name: source_relation
        description: "{{ doc('source_relation') }}"
      - name: android_os_version
        description: '{{ doc("android_os_version") }}'
      - name: anrs
        description: '{{ doc("daily_anrs") }}'
      - name: crashes
        description: '{{ doc("daily_crashes") }}'
      - name: date_day
        description: '{{ doc("date") }}'
      - name: package_name
        description: '{{ doc("package_name") }}'

  - name: stg_google_play__stats_installs_country
    description: >
      Each line is a daily snapshot of the monthly installs report by app and user country.
      Records with `NULL` countries are aggregated together into daily batches.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - source_relation
            - package_name
            - date_day
            - country
    columns:
      - name: source_relation
        description: "{{ doc('source_relation') }}"
      - name: active_devices_last_30_days
        description: '{{ doc("active_device_installs") }}'
      - name: country
        description: '{{ doc("country") }}'
      - name: device_installs
        description: '{{ doc("daily_device_installs") }}'
      - name: device_uninstalls
        description: '{{ doc("daily_device_uninstalls") }}'
      - name: device_upgrades
        description: '{{ doc("daily_device_upgrades") }}'
      - name: user_installs
        description: '{{ doc("daily_user_installs") }}'
      - name: user_uninstalls
        description: '{{ doc("daily_user_uninstalls") }}'
      - name: date_day
        description: '{{ doc("date") }}'
      - name: install_events
        description: '{{ doc("install_events") }}'
      - name: package_name
        description: '{{ doc("package_name") }}'
      - name: uninstall_events
        description: '{{ doc("uninstall_events") }}'
      - name: update_events
        description: '{{ doc("update_events") }}'

  - name: stg_google_play__stats_ratings_country
    description: >
      Each line is a daily snapshot of the monthly ratings report by app and country.
      Records with `NULL` countries are aggregated together into daily batches.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - source_relation
            - package_name
            - date_day
            - country
    columns:
      - name: source_relation
        description: "{{ doc('source_relation') }}"
      - name: country
        description: '{{ doc("country") }}'
      - name: average_rating
        description: '{{ doc("daily_average_rating") }}'
      - name: date_day
        description: '{{ doc("date") }}'
      - name: package_name
        description: '{{ doc("package_name") }}'
      - name: rolling_total_average_rating
        description: '{{ doc("total_average_rating") }}'

  - name: stg_google_play__store_performance_country
    description: >
      Each line is a daily snapshot of the monthly store performance report by app and country.
      Records with `NULL` countries are aggregated together into daily batches.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - source_relation
            - package_name
            - date_day
            - country_region
    columns:
      - name: source_relation
        description: "{{ doc('source_relation') }}"
      - name: country_region
        description: Two-letter abbreviation of the country or region where the user’s Google account is registered.
      - name: date_day
        description: '{{ doc("date") }}'
      - name: package_name
        description: '{{ doc("package_name") }}'
      - name: store_listing_acquisitions
        description: '{{ doc("store_listing_acquisitions") }}'
      - name: store_listing_conversion_rate
        description: '{{ doc("store_listing_conversion_rate") }}'
      - name: store_listing_visitors
        description: '{{ doc("store_listing_visitors") }}'

  - name: stg_google_play__store_performance_source
    description: Each line is a daily snapshot of the monthly store performance report by app and type of traffic source.
    columns:
      - name: source_relation
        description: "{{ doc('source_relation') }}"
      - name: traffic_source_unique_key
        description: Surrogate key hashed on `source_relation`, `date_day`, `package_name`, `traffic_source`, `search_term`, `utm_campaign`, and `utm_source`.
        tests:
          - unique
          - not null
      - name: date_day
        description: '{{ doc("date") }}'
      - name: package_name
        description: '{{ doc("package_name") }}'
      - name: search_term
        description: >
          The term the user searched for before navigating to your store listing. Only available when the traffic source is Google Play search.

          Note: Can be Other this value does not reach certain minimum thresholds
      - name: store_listing_acquisitions
        description: '{{ doc("store_listing_acquisitions") }}'
      - name: store_listing_conversion_rate
        description: '{{ doc("store_listing_conversion_rate") }}'
      - name: store_listing_visitors
        description: '{{ doc("store_listing_visitors") }}'
      - name: traffic_source
        description: '{{ doc("traffic_source") }}'
      - name: utm_campaign
        description: >
          The value of the utm_campaign URL parameter in deep links to your store listing. Only available when the traffic source is a third-party referral.
          
          Note: Can be Other this value does not reach certain minimum thresholds
      - name: utm_source
        description: >
          The value of the utm_source URL parameter in deep links to your store listing. Only available when the traffic source is a third-party referral.
          
          Note: Can be Other this value does not reach certain minimum thresholds

  - name: stg_google_play__stats_installs_overview
    description: Each line is a daily snapshot of the monthly overview installs report by app.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - source_relation
            - package_name
            - date_day
    columns:
      - name: source_relation
        description: "{{ doc('source_relation') }}"
      - name: active_devices_last_30_days
        description: '{{ doc("active_device_installs") }}'
      - name: device_installs
        description: '{{ doc("daily_device_installs") }}'
      - name: device_uninstalls
        description: '{{ doc("daily_device_uninstalls") }}'
      - name: device_upgrades
        description: '{{ doc("daily_device_upgrades") }}'
      - name: user_installs
        description: '{{ doc("daily_user_installs") }}'
      - name: user_uninstalls
        description: '{{ doc("daily_user_uninstalls") }}'
      - name: date_day
        description: '{{ doc("date") }}'
      - name: install_events
        description: '{{ doc("install_events") }}'
      - name: package_name
        description: '{{ doc("package_name") }}'
      - name: uninstall_events
        description: '{{ doc("uninstall_events") }}'
      - name: update_events
        description: '{{ doc("update_events") }}'

  - name: stg_google_play__stats_crashes_overview
    description: Each line is a daily snapshot of the monthly overview crashes report by app.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - source_relation
            - package_name
            - date_day
    columns:
      - name: source_relation
        description: "{{ doc('source_relation') }}"
      - name: anrs
        description: '{{ doc("daily_anrs") }}'
      - name: crashes
        description: '{{ doc("daily_crashes") }}'
      - name: date_day
        description: '{{ doc("date") }}'
      - name: package_name
        description: '{{ doc("package_name") }}'

  - name: stg_google_play__stats_ratings_overview
    description: Each line is a daily snapshot of the monthly overview ratings report by app.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - source_relation
            - package_name
            - date_day
    columns:
      - name: source_relation
        description: "{{ doc('source_relation') }}"
      - name: average_rating
        description: '{{ doc("daily_average_rating") }}'
      - name: date_day
        description: '{{ doc("date") }}'
      - name: package_name
        description: '{{ doc("package_name") }}'
      - name: rolling_total_average_rating
        description: '{{ doc("total_average_rating") }}'

  - name: stg_google_play__earnings
    description: >
      Each line in the report represents a type of transaction, like when you charge a customer money or pay Google a fee, along with the original and converted amounts.
      Does not include chargebacks. No transactions made in the European Economic Area will not be Google fees. 
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - source_relation
            - order_id
            - transaction_type
            - tax_type
            - amount_buyer_currency # this is because withholding taxes (https://support.google.com/googleplay/android-developer/answer/9384608?hl=en) can be split up into multiple records
    columns:
      - name: source_relation
        description: "{{ doc('source_relation') }}"
      - name: amount_buyer_currency
        description: Total amount for this invoice line before currency conversion.
      - name: amount_merchant_currency
        description: Total amount for this invoice line after currency conversion.
      - name: base_plan_id
        description: Base plan id of the subscription product.
      - name: buyer_country
        description: '{{ doc("country") }}'
      - name: buyer_currency
        description: Currency the order was converted from.
      - name: buyer_postal_code
        description: Postal code of the buyer.
      - name: buyer_state
        description: State in which the buyer resides.
      - name: currency_conversion_rate
        description: Exchange rate used when converting buyer amounts to payout amounts in merchant currency.
      - name: order_id
        description: Unique ID assigned to this order. Subscription order IDs include the renewal cycle number at the end. Renamed from `description`. One order will have a record for every kind of transaction associated with it.
      - name: buyer_hardware
        description: Android device model used to make the purchase. For subscription orders, this refers to the model used for the original purchase.
      - name: merchant_currency
        description: Currency to which the order was converted. This is the local currency you are paid in.
      - name: offer_id
        description: ID of the offer used to purchase the product.
      - name: package_name
        description: Package name of the app where the product was sold. Renamed from `product_id`.
      - name: product_title
        description: Developer-specified name of the product. Shown in the buyer's locale.
      - name: product_type
        description: Used to identify the type of product sold. '0' represents the sale of a paid app, '1' represents the sale of an in-app product or subscription.
      - name: refund_type
        description: Refunds where the buyer was reimbursed their full spend will be marked as 'Full'. Refunds for part of a user’s payment amount will be marked as 'Partial'. 
      - name: sku_id
        description: '{{ doc("sku_id") }}'
      - name: tax_type
        description: Kind of tax for tax lines; null for non-tax lines.
      - name: transaction_date
        description: Date of this order based on the PDT time zone (in 'MMM DD, YYYY' format).
      - name: transaction_type
        description: Type of transaction - Charge, Google fee, Tax, Charge refund, Google fee refund, Charge rebill, Tax rebill, Google fee rebill, etc. Nulls are coalesced into 'Other'
      - name: transaction_pt_timestamp
        description: >
          Timestamp (in Pacific Time) of  when the order was placed. This is cast as a `dbt.type_timestamp()` which compiles to a timestamp WITHOUT a timezone. 
          However, all times reported in Google Play are in Pacific Time.
      - name: transaction_timezone
        description: Timezone in which the order was placed (PDT or PST).
        
  - name: stg_google_play__subscriptions_country
    description: >
      Each line is a daily snapshot of the monthly subscriptions report by individual country, app, and subscription product.
      Records with `NULL` countries are aggregated together into daily batches.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - source_relation
            - package_name
            - date_day
            - country
            - product_id
    columns:
      - name: source_relation
        description: "{{ doc('source_relation') }}"
      - name: total_active_subscriptions
        description: '{{ doc("total_active_subscriptions") }}'
      - name: cancelled_subscriptions
        description: '{{ doc("cancelled_subscriptions") }}'
      - name: country
        description: '{{ doc("country") }}'
      - name: date_day
        description: '{{ doc("date") }}'
      - name: new_subscriptions
        description: '{{ doc("new_subscriptions") }}'
      - name: package_name
        description: '{{ doc("package_name") }}'
      - name: product_id
        description: '{{ doc("sku_id") }}'

seeds:
  - name: google_play__country_codes
    description: ISO-3166 country mapping table
    columns:
      - name: country_name
        description: The ISO-3166 English country name sent by Google.
      - name: alternative_country_name
        description: Due to differences in the official ISO country names used by Google and Apple's naming convention, we've added an alternative territory name that will allow us to join downstream reports.
      - name: country_code_numeric
        description: The 3 digit ISO-3166 country code. ([Original Source](https://github.com/lukes/ISO-3166-Countries-with-Regional-Codes/blob/master/all/all.csv))
      - name: country_code_alpha_2
        description: The 2 character ISO-3166 country code. ([Original Source](https://github.com/lukes/ISO-3166-Countries-with-Regional-Codes/blob/master/all/all.csv))
      - name: country_code_alpha_3
        description: The 3 character ISO-3166 country code. ([Original Source](https://github.com/lukes/ISO-3166-Countries-with-Regional-Codes/blob/master/all/all.csv))
      - name: region
        description: The UN Statistics region name assignment. ([Original Source](https://github.com/lukes/ISO-3166-Countries-with-Regional-Codes/blob/master/all/all.csv))
      - name: region_code
        description: The UN Statistics region numerical code assignment. ([Original Source](https://github.com/lukes/ISO-3166-Countries-with-Regional-Codes/blob/master/all/all.csv))
      - name: sub_region
        description: The UN Statistics sub-region name. ([Original Source](https://github.com/lukes/ISO-3166-Countries-with-Regional-Codes/blob/master/all/all.csv))
      - name: sub_region_code
        description: The UN Statistics sub-region numerical code. ([Original Source](https://github.com/lukes/ISO-3166-Countries-with-Regional-Codes/blob/master/all/all.csv))