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
.