This article will provide an overview over the MMT bridge PIM (plan items metrics) data model and how to leverage it to gain actionable insights.

The data model

Bridge provides schemas and data tables that reflect the organizational hierarchy of campaign planning in MMT Mercury. The two main data sources in this data model are Mercury, where most dimensions and all projected metrics originate from, as well as third party data delivery systems (technical vendors) that provide ad level dimensions and realized metrics.

The main entities in the data model are categories, campaigns, placements, plan items and ads. These entities are reflected in database tables and have the following relationships:

categories
|
└──>(0,n) campaigns
          |
          └──>(1,n) placements
                    |
                    └──>(1,n) plan items
                              |
                              └──>(0,n) ads
Find a more in-depth description of the entities here.

Schemas and versioning

As Mercury is constantly improving, Bridge has to adapt to given changes in the data model dictated by this evolution. To accomplish this without impinging on the consumption of the Bridge data by clients, the changes in the data model are reflected in the schemas of the database.

The current working schema for Bridge PIM data is  mercury_v3 . The next iteration after the data model changes will be   mercury_v4 . This gives clients the opportunity to adapt to the improvements in the data model on their own time, without experiencing breaks in their ability to consume the data.

Naming

All database object names are in  snake_case . Dimension names are verbose and describe the field's contents. Metrics can be divided into two groups, each having their own set of relevant suffixes:

General metrics:

  • *_projected: forecast for the given metric entered into mercury by planning team. 
  • *_realized: realized value of the given metric, sourced from a data delivery system.

Data delivery system performance metrics:

  • *_projected: forecast for the given metric entered into mercury by planning team. 
  • *_measured: realized value of the metric as measured by the data delivery system.
  • *_measured_reconciled: optional realized value of the metric after reconciliation with the data delivery system if a renegotiation occurred.
  • *_publisher_invoiceable: realized value of the metric negotiated with the publisher. 
  • *_publisher_reconciled: realized value of the metric after renegotiation with the
  • publisher.

Discount levels for price and cost metrics are denoted by  gross|n{1,3}  in the naming, before the suffix described above.

  • *_gross_*: the gross metric without any discounts applied.
  • *_n_*: gross metrics minus standard discounts as well as a special discount, agency discounts 1 and 2, depending on the chosen agency fee model.
  •  *_nn_*: *_n_* minus agency commission.
  • *_nnn_*: *_nn_* minus cash discount.

Entities and system tables

The data for the main entities of the data model is reflected in at least one table within the given schema. Each entity also has at least one defining ID that can be found in the up- or downstream hierarchy tables. The following ERD only contains primary key and relevant foreign keys for each table. All other columns are omitted for readability.

categories

Categories are a way to organize client campaign planning. A category can be defined by a brand, a specific product, a region, a calendar year and more. Categories are organized in a hierarchy that results in a path. This pat is available in the  path_names  field and has the following structure:

("tenant","client",category,subcategory,subsubcategory,...).

The primary key for this table is  id  which is named  category_id  in foreign tables.

campaigns

The table contains all campaign level dimensions like the campaign name, author, or currency as well as campaign level metrics. Campaigns have  id  as the primary key, which is named  campaign_id  in foreign tables. The field  accounting_system_id  can be used as a primary key as well if an accounting system is connected to Mercury. Depending on the implementation, it is possible that the  accounting_system_id  contains NULL values.

In Mercury campaigns can have multiple scenarios, i.e. varied campaign plans to be communicated with the client. Only one of the scenarios per campaign is approved and becomes the main scenario. The main scenario is the only campaign scenario that is available in the data model. A scenario qualifies as approved as soon as on plan item making up the scenario has  status = 'approvedExternally' .

plan_items

Campaigns are a collection of placements and plan items. Plan items represent the lowest level entity that make up the planning side of an advertising campaign in Mercury. All projection metrics, be it costs or performance, are defined on the plan item level. The primary key for plan items is  id  or  plan_item_id  in foreign tables.

In the planning process of a campaign, a plan item can have four different states stored in field status :

  • inPlanning
  • approvedInternally
  • approvedExternally
  • booked

For reporting purposes, a plan item only runs for one consecutive month, but can run for multiple months that are non-consecutive. To add a consecutive month, another plan item is added. These plan items are then grouped as a placement which has the key  mercury_uuid . The mercury_uuid is the main key for joining Mercury plan data with third party data sources. To enable this, the  mercury_uuid  is stored in the third party system and then extracted together with the relevant system data.

runtimes

The plan item runtimes are store in this table. As a plan item can run for multiple non-consecutive months, this table can have multiple rows per  plan_item_id .

creative_performance_metrics

As the name suggests, this table contains the ad and date level performance data fetched from the data delivery system or adserver. The plan item runtimes are partitioned into days (row wise) and subdivided with the delivery system ads for the given day.

The primary key for a delivery system ad is the combination of the  adserver_ad_id  and  adserver_tag_id . The primary key for the table is the combination of  plan_item_id ,  adserver_ad_id ,  adserver_tag_id  and  date . The   plan_item_id  is necessary as delivery system ads are sometimes reused for different plan items.

For plan items that have their runtime starts in the future, the runtimes are also partitioned into days and have NULL values for all delivery system ads and realized metric fields. Hence, running  SELECT MAX("date")  will return a date in the future. The field  adserver_data_until  is the timestamp describing when the ad data was fetched from the delivery system.

As the plan item runtimes are partitioned into days, so are all projected metrics on the plan item level. They are evenly distributed on the days of the runtime. Hence, the  creative_performance_metrics  table can be used as a basis for time series based reporting.

Changes in the data

Every table contains the two timestamps  exported_at  and  deleted_at  that define when data for the given entity id has been changed (created/edited) or deleted.

Joining the data

For reporting purposes, it can be useful to have one data source containing all entity data from categories down to delivery system ads. To achieve this the ERD can be used as a roadmap. Doing this implies that all metrics that are not available in the  creative_performance_metrics  table are to be treated as dimensions, as long as they are not subdivided by the number of rows following a join of tables aggregated on a lower level.

Relative metrics

The data model does not contain relative metrics as providing them on a given aggregation level can easily lead to skewed or plain wrong values when aggregated in the wrong manner as they cannot be averaged. The data model does contain all metrics to be able to calculate relative metrics as needed. A few examples:

  • Viewability

    SELECT
  CASE WHEN SUM(impressions_measured) > 0 THEN
    SUM(impressions_viewable_measured) / SUM(impressions_measured)
  ELSE
NULL
  END AS viewability
[...]

  • CTR

    SELECT
  CASE WHEN SUM(impressions_measured) > 0 THEN
    SUM(clicks_measured) / SUM(impressions_measured)
  ELSE
NULL
  END AS ctr
[...]

  • CPL

    SELECT
  CASE WHEN SUM(leads_measured) > 0 THEN
    SUM(cost_media_nn_realized) / SUM(leads_measured)
  ELSE
NULL
  END AS cpl
[...]

  • CPC

    Depending on how conversions are defined, refer to CPL using the following metrics (or sums of) in replacement for  leads_measured :
    • actions_measured
    • downloads_measured
    • emails_measured
    • engagements_measured
    • installs_measured
    • orders_measured
    • registrations_measured

  • CVR

    Just as for CPC refer to the CTR using the appropriate conversion metrics.


Joining third party data

As third party data is very heterogeneous, a one solution fits all approach does not exist. A good starting point is to check whether the  mercury_uuid  can be stored in a field of the third party system on a placement (or more granular) level and then exported with the rest of the data. This sometimes requires the use of naming conventions and string extractions.

If help with this topic is required, we at MMT will gladly lend a helping hand. Reach out!