suse_microsoft_adlevel_final_v2

Full reference: paidteam-data-warehouse.customer_ads_suse.suse_microsoft_adlevel_final_v2Type: Final | Rows: 145,169 | Size: 65.7 MB

Microsoft Ads ad-level final data (V2). Contains the complete historical record of Microsoft Ads performance, refreshed daily from staging via a rolling 60-day DELETE+INSERT process.

Schema

Column	Type	Nullable	Description
Date	DATE	No	Date of ad performance
AccountName	STRING	Yes	Microsoft Ads account name
CampaignID	STRING	Yes	Microsoft Ads campaign ID
AdGroupID	STRING	Yes	Microsoft Ads ad group ID
AdID	STRING	Yes	Microsoft Ads ad ID
CampaignName	STRING	Yes	Campaign name
AdGroupName	STRING	Yes	Ad group name
AdTitle	STRING	Yes	Ad headline / title
CampaignType	STRING	Yes	Campaign type (Search, Audience, etc.)
FinalUrl	STRING	Yes	Final destination URL
Impressions	INTEGER	Yes	Number of impressions
Clicks	INTEGER	Yes	Number of clicks
CTR	FLOAT	Yes	Click-through rate
CPC	FLOAT	Yes	Cost per click
Cost	FLOAT	Yes	Total cost
Conversions	INTEGER	Yes	Number of conversions
ViewThroughConversions	INTEGER	Yes	View-through conversions
ConversionsRate	FLOAT	Yes	Conversion rate
VideoViews	INTEGER	Yes	Number of video views
VideoViewsRate	INTEGER	Yes	Video views rate
VideoViews25Percent	INTEGER	Yes	Video watched to 25%
VideoViews50Percent	INTEGER	Yes	Video watched to 50%
VideoViews75Percent	INTEGER	Yes	Video watched to 75%
VideoViews100Percent	INTEGER	Yes	Video watched to 100%
AverageWatchTimeVideo	INTEGER	Yes	Average video watch time (seconds)
_ingested_at	TIMESTAMP	Yes	Timestamp when the row was inserted by the refresh script

Partitioning & Clustering

• Partitioned by: Date (DAY)
• Partitions: 457
• Clustered by: CampaignName, AdGroupName

Refresh Logic

The scheduled refresh script performs the following for Microsoft:

1. Read all rows from suse_microsoft_adlevel_staging_v2
2. Identify the MIN(Date) and MAX(Date) range in staging
3. DELETE rows in this final table matching that date range AND the same CampaignID, AdGroupID, AdID combinations
4. INSERT all staging rows with _ingested_at = CURRENT_TIMESTAMP()
5. Log the operation to refresh_log_v2

Primary keys used for refresh: Date, CampaignID, AdGroupID, AdID

Sample Query

SELECT
  Date,
  CampaignName,
  CampaignType,
  SUM(Impressions) AS total_impressions,
  SUM(Clicks) AS total_clicks,
  SUM(Cost) AS total_cost,
  SUM(Conversions) AS total_conversions
FROM `paidteam-data-warehouse.customer_ads_suse.suse_microsoft_adlevel_final_v2`
WHERE Date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY Date, CampaignName, CampaignType
ORDER BY Date DESC
LIMIT 100;

Notes

• VideoViewsRate is currently INTEGER. If Microsoft begins returning decimal values, this column should be altered to FLOAT64 in both staging and final tables simultaneously.
• The AccountName column is unique to Microsoft among all platforms in this warehouse.
• Clustering is on CampaignName and AdGroupName (human-readable names, not IDs) to optimize dashboard queries that typically filter by campaign name.