Skip to content

Marketing Cost

How MarketingCost is implemented per service category.

Calling

Welcome Offer cost

Marketing Cost = sum of termination cost in the "free" trial week. Margin = Revenue - Direct Cost + Marketing Cost, so it is almost never 0 because of minute limits. Only added for subscription (limited and unlimited) welcome offers.

Taken from SystemAccountTransaction with SystemAccountId = 4022, ReferenceTypeId = 1, answered calls only (ResultId = 1), then joined to calls on DirectParticipantId:

marketing_cost as (
    select
        "CdrDetailId",
        sum(abs("USDAmount")) as "MarketingCostUSD",
        sum(abs("SEKAmount")) as "MarketingCostSEK"
    from get_costs
    where "SystemAccountId" = 4022
    group by 1
)

Marketing Cost can appear outside the free trial week because of other campaigns and discounts:

select *
from dw4_prod.dbt."FactUserBucketTransaction"
where "EventTime" >= '2025-01-01' and "MarketingCostUSD" > 0 and "Trial" = false;

Missing from Accounting

Welcome Offer cost for World Credits. Identify it for Calling BucketId between 31000 and 32000 (bucket config): if Direct Cost > Product Revenue, then Product Revenue - Direct Cost = Marketing Cost. World Credits in these buckets expire after 30 days and all revenue and direct cost in that period count towards Marketing Cost.

Tell A Friend cost

  • Taken from SystemAccountTransaction with SystemAccountId = 4022, ReferenceTypeId = 1. It is not exclusively TAF
  • Transaction only has PayBucketId = 10003 (TAF bucket): Marketing Cost is TAF credits
  • Transaction has 10003 and other buckets or charges: take the share of TAF credits in the total transaction amount and multiply by Marketing Cost

MTU

Welcome Offer cost

If Direct Cost > Product Revenue, then Product Revenue - Direct Cost = Marketing Cost, applied as follows:

  • Welcome Offer plan (0 dollar plan), credit or bundle: Marketing Cost is added to margin, so margin is always 0
  • Regular MTU product: no Marketing Cost, margin not affected
  • Regular MTU product on a pricelist or pricelist tag: Price Paid minus Original Product Price = Marketing Cost, margin not affected

Tell A Friend cost

Same condition as Welcome Offer. If the transaction has PayBucketId = 10003, Marketing Cost is added to margin, so margin is always 0.

Code implementation

From SystemAccountTransaction, per marketplace Reference:

marketplace_account_transactions as (
    select
        acc."Reference",
        acc."ClientAccountId",
        min(acc."TransactionTime") as "FirstTransactionTime",
        max(acc."TransactionTime") as "LatestTransactionTime",
        min(acc."CurrencyCode") as "RevenueCurrencyCode",
        sum(iff(acc."SystemAccountId" = 3010, to_number(acc."Amount" / fx_rate."ExchangeRate", 20, 4), null)) as "RevenueUSD",
        sum(iff(acc."SystemAccountId" = 2440, to_number(acc."Amount" / fx_rate."ExchangeRate", 20, 4), 0)) as "ProviderCostUSD",
        sum(iff(acc."SystemAccountId" = 2429 and acc."SubAccountId" = 10003, to_number(acc."Amount" / fx_rate."ExchangeRate", 20, 4), 0)) as "TAFCreditAmountUSD",
        abs(iff(coalesce("RevenueUSD", 0) < "ProviderCostUSD", to_number(coalesce("RevenueUSD", 0) - "ProviderCostUSD", 20, 4), null)) as "MarketingCostUSD"
    from system_account_transaction as acc
    left join fact_exchange_rate as fx_rate
        on acc."CurrencyCode" = fx_rate."CurrencyId"
        and to_varchar(to_date(acc."TransactionTime"), 'YYYYMMDD') = fx_rate."DateKey"
    where acc."SystemAccountId" in (3010, 2440, 2429)
        and acc."ReferenceTypeId" in (65, 66, 67, 68, 73)
    group by 1, 2
)

This is joined to Marketplace Orders on OrderId. Marketing Cost is only kept for welcome offers, TAF credits and fully discounted products, excluding International Number.

Splitting into Welcome Offer and TAF

Marketing Cost can blend both. Two columns split it, and TAFCostUSD + WelcomeOfferCostUSD = MarketingCostUSD must always hold:

  • WelcomeOfferCostUSD: welcome offer without credits used = full Marketing Cost. With credits used = DeductionAmount / (DeductionAmount + TAFCreditAmount) * MarketingCost
  • TAFCostUSD: credits used without welcome offer = full Marketing Cost. With a welcome offer = TAFCreditAmount / (DeductionAmount + TAFCreditAmount) * MarketingCost

DeductionAmount comes from Backend Order events, TAFCreditAmount from Backend Accounting data.

Other services

  • Money Transfer: Marketing Cost is 0
  • International Number: Marketing Cost is 0. Numbers are bought from providers regardless of usage and may be attributed to multiple users, so splitting cost per order would be inconsistent
  • Wireless: Marketing Cost is 0