How Can I Find the Daily Spend for Campaigns or Line Items?

Question

Where can I find the daily spend for campaigns or line items?

Environment

Connector: Twitter Ads

Answer

To find the daily spend for campaigns or line items, use the billed_charge_local_micro column in the CAMPAIGN_REPORT and LINE_ITEM_REPORT tables.

This column records spend in micros, where 1,000,000 micros equals one unit of the billing currency defined for the funding instrument associated with the campaign or line item. For example, if the billing currency is USD, a value of 5,500,000 micros equals $5.50. To convert this value to standard currency units, divide by 1,000,000. For more information, see the Twitter Ads API Currency documentation.

The following query returns daily spend grouped by campaign from the CAMPAIGN_REPORT table:

SELECT
  DATE(date) AS date_only,
  campaign_id,
  SUM(billed_charge_local_micro) / 1000000 AS daily_spend
FROM
  twitter_ads.CAMPAIGN_REPORT
GROUP BY
  DATE(date), campaign_id
ORDER BY
  date_only;

To calculate daily spend for line items, use the same query structure with the LINE_ITEM_REPORT table and group by line_item_id instead of campaign_id.