suse_microsoft_adlevel_staging_v2

Full reference: paidteam-data-warehouse.customer_ads_suse.suse_microsoft_adlevel_staging_v2Type: Staging | Rows: 19,097 | Size: 8.6 MB

Microsoft Ads ad-level staging data (V2). Dataslayer writes a rolling ~60-day window of ad-level performance data into this table daily. The scheduled refresh script reads from here to update the final table.

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)

Partitioning & Clustering

• Partitioned by: Date (DAY)
• Partitions: 60
• Clustering: None

Refresh Logic

This is a staging table. Dataslayer overwrites data here on a daily schedule. The refresh script reads from this table and upserts into suse_microsoft_adlevel_final_v2.

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

Sample Query

SELECT
  Date,
  AccountName,
  CampaignName,
  AdGroupName,
  AdTitle,
  Impressions,
  Clicks,
  Cost
FROM `paidteam-data-warehouse.customer_ads_suse.suse_microsoft_adlevel_staging_v2`
WHERE Date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
ORDER BY Date DESC, Impressions DESC
LIMIT 100;

Notes

• Microsoft uses CampaignName / AdGroupName / AdTitle naming (contrast with Google's Campaign / Adgroup / Adname).
• Microsoft uses AdGroupID (capital G) whereas Google uses AdgroupID (lowercase g). These casing differences must be accounted for in any cross-platform views.
• VideoViewsRate is currently INTEGER in this staging table. This may need to be changed to FLOAT64 if Microsoft starts returning decimal values.
• The AccountName column is unique to Microsoft; other platforms do not include account-level identifiers.