Billing

End of Month process:

1. Obtain ClientStats and ensure it is available for the previous month within:
   stg_finance__billing_client_stats

2. Obtain ManualStats and ensure it is available for the previous month within:
   stg_finance__delivered_manual_monthly
   
   The process for #1 and #2 is described here: https://github.com/ozone-project/dbt/blob/master/ozone/data/maintain_seed_built_models.md

3. Once both seed files are finalized and the related models have been updated, the billing stats must be re-published to include the above client & manual stats from the previous month.
   Republish fct_back_office__billing_monthly_managed & fct_back_office__billing_monthly
   ( fct_back_office__billing_monthly_programmatic does not reference any of the client or manual data )

Data structure:

1. fct_back_office__billing_monthly_programmatic : A collection of all 1st party data from the previous month, with all required levels of Revenue Calculations. The only addition is that Data Revenue calculations are made using the previous month’s data revenue share splits.

2. fct_back_office__billing_monthly_managed : Special consideration is given for Managed Lineitems and the special considerations made for any overdelivery past the booked revenue figure for that lineitem.

3. fct_back_office__billing_monthly : A final output which unions managed data with all other data from the programmatic table

Concepts

1. ClientStats are provided by external clients (advertisers) and collected (by AndyW) into a single file.
   The billing of Managed Lineitems is focused on the data provided by clients. If Ozone overdelivers a lineitem, the client will not count that revenue and Ozone can not bill the client for it.

2. Billable vs Delivered : Delivered_* fields are calculated by using Ozone’s own delivery data, while billable_* fields are calculated by:

   1. Take ClientStats which subtracts any overdelivery

   2. Review the ratio of delivery a publisher made for the lineitem in reality, and divide the client-provided stats by the ratio of delivery per publisher

   3. Final ‘billable' revenue fields will attribute the revenue per publisher based on their share, but without any overdelivery accounted for

3. ManualStats are provided for any Lineitems which are not actually run through Ozone’s own pipelines. For example with “Skins” and related takeovers, adtags are provided to the publisher and they book those tags directly into their own Adserver. None of this data will show up in any Ozone delivery* tables.
   The provided file allows for collecting this lineitem data from each publisher, and providing it in a summary file broken down by publisher, lineitem, and revenue code.

4. Data Revenue & Distr Revenue calculations
   Publishers within Ozone contribute data to the system which allows for targeting lineitems to Audiences/Segments. These publishers receive a kickback for the amount of traffic they have contributed to that segment for lineitems which delivered revenue against that segment.
   
   At the end of the month, a summary of publisher shares per segment is made within fct_publisher__monthly_billing_revenue
   
   This is then used to calculate Data Revenue and Distr Revenue values

5. CPCv
   For CPCv campaigns, the revenue is already calculated within delivery_detailed to be the Salesforce ‘gross_price’ (minus AMV, Agency Fees, and Barter rates) multiplied by the number of completed views.
   
   That value is stored in advertiser_delivered_net_revenue within delivery_detailed.
   This impacts any Lineitem where the salesforce ‘metric’ field is set to “CPCv”
   
   Important note: The other revenue columns (realtime + publisher revenues) are the actual bid prices that come in realtime during delivery, which are set based on CPM. These will not be changed for CPCv and can be referred to in order to see the actual bid prices versus the amount billed to advertisers.
   
   

6. Levels of Revenue, calculations based on Revenue Code:
   All levels of revenue are explained below, with reference taken from:
   https://docs.google.com/spreadsheets/d/1ZJdVwx64tSAHmCJKQ6jm2j8Y0A0KAM0O7dMU648v4a4/edit#gid=1240779212
   
   The main concept to be aware of is that traffic is being assigned a Revenue Code based on it’s seat_name, deal_id, and publisher.
   Based on that revenue_code, we then know the ozone_fee and data_contribution percent which is used to calculate the levels of publisher revenues.
   For Salesforce lineitems, we know the amv_percent which causes the difference for gross/net campaign prices.

Revenue Columns

Realtime Bid Revenues

Baseline Revenues

Advertiser Revenues (estimated from delivery)

Publisher Revenues