Appearance
customer_ads_suse
The customer_ads_suse dataset is the primary dataset in the paidteam-data-warehouse GCP project. It contains all tables, views, and UDFs for the SUSE paid advertising data pipeline, ingesting data from 6 ad platforms: Google, Microsoft, LinkedIn, Reddit, 6sense, and StackAdapt.
Dataset Properties
| Property | Value |
|---|---|
| Full path | paidteam-data-warehouse.customer_ads_suse |
| Region | europe-west2 (London) |
| Total tables | 33 |
| Total views | 4 |
| Total UDFs | 10 |
| Estimated storage | ~830 MB |
| Estimated rows | ~2.15 million |
Table Inventory
V2 Adlevel Tables -- Staging
Staging tables receive daily data loads from Dataslayer. Their contents represent a rolling window that is fully replaced on each ingestion cycle.
| Table | Rows | Size (MB) | Partitioned By | Clustering |
|---|---|---|---|---|
suse_google_adlevel_staging_v2 | 22,911 | 10.27 | Date | -- |
suse_microsoft_adlevel_staging_v2 | 19,097 | 8.57 | Date | -- |
suse_linkedin_adlevel_staging_v2 | 8,543 | 4.84 | Date | -- |
suse_reddit_adlevel_staging_v2 | 4,934 | 2.31 | Date | -- |
suse_6sense_adlevel_staging_v2 | 192,880 | 53.34 | Date | -- |
suse_stackadapt_adlevel_staging_v2 | 3,765 | 1.76 | Date | -- |
V2 Adlevel Tables -- Final
Final tables contain the full historical record. The daily refresh script deletes overlapping rows by Date and ID columns, then inserts fresh data from staging, preserving any historical rows not present in the current staging window.
| Table | Rows | Size (MB) | Partitioned By | Clustering |
|---|---|---|---|---|
suse_google_adlevel_final_v2 | 213,175 | 94.12 | Date | Campaign, Adgroup |
suse_microsoft_adlevel_final_v2 | 145,169 | 65.71 | Date | CampaignName, AdGroupName |
suse_linkedin_adlevel_final_v2 | 243,168 | 140.16 | Date | CampaignName, CampaignGroupName |
suse_reddit_adlevel_final_v2 | 38,952 | 18.23 | Date | Campaignname, Adgroupname |
suse_6sense_adlevel_final_v2 | 192,880 | 54.81 | Date | Campaign, AdGroup |
suse_stackadapt_adlevel_final_v2 | 3,765 | 1.78 | Date | Campaign, AdGroup |
V2 Creative Tables
Creative tables store ad creative metadata (ad copy, image URLs, video URLs, thumbnails). They use a MERGE strategy: existing records are updated, new records are inserted, and removed creatives are preserved for historical reference.
| Table | Rows | Size (MB) | Partitioned By | Clustering |
|---|---|---|---|---|
suse_linkedin_creative_staging_v2 | 1,527 | 1.51 | -- | -- |
suse_linkedin_creative_final_v2 | 1,667 | 1.66 | -- | CampaignID, CreativeID |
suse_reddit_creative_staging_v2 | 202 | 0.09 | -- | -- |
suse_reddit_creative_final_v2 | 666 | 0.27 | -- | CampaignID, AdGroupID, AdID |
suse_microsoft_creative_search_staging_v2 | 978 | 0.39 | -- | -- |
Logging and Metadata
These tables track pipeline execution and UDF versioning.
| Table | Rows | Purpose |
|---|---|---|
refresh_log_v2 | 36 | Logs each adlevel refresh run (status, row counts, timestamps) |
creative_refresh_log_v2 | 48 | Logs each creative refresh run (status, rows merged, timestamps) |
udf_metadata | 10 | Version tracking and documentation for all 10 UDFs |
Views
| View | Description |
|---|---|
linkedin_creative_performance_v2 | Joins LinkedIn creative metadata with adlevel performance data, enriched with UDF fields |
reddit_creative_performance_v2 | Joins Reddit creative metadata with adlevel performance data, enriched with UDF fields |
unified_ads_base | V1 legacy cross-platform UNION ALL of all 6 platform final tables |
unified_ads_enriched | V1 legacy enriched view with UDF metadata parsing on top of unified_ads_base |
See the Views Overview for full details on each view.
User-Defined Functions (UDFs)
All 10 UDFs live in this dataset and parse campaign naming conventions at query time.
| UDF | Description |
|---|---|
get_channel | Extracts the advertising channel from campaign name |
get_campaign_code | Extracts the numeric campaign code |
get_product_line | Extracts the product line (e.g., Business Critical Linux) |
get_product | Extracts the specific product name |
get_subproduct | Extracts the sub-product or variant |
get_geo | Extracts the geographic targeting region |
get_tier | Extracts the tier classification |
get_language | Extracts the language targeting |
get_audience | Extracts the audience segment |
get_content_name | Extracts human-readable content name from creative/campaign name |
See the UDFs Overview for signatures, logic, and examples.
V1 Legacy Tables
The dataset also contains 12 legacy tables from the original V1 pipeline. These tables are deprecated and should not be used for new reporting. They remain in the dataset for backward compatibility with existing Looker Studio dashboards that have not yet been migrated.
Legacy tables follow the naming pattern suse_{platform}_adlevel_staging and suse_{platform}_adlevel_final (without the _v2 suffix). The V1 pipeline used different refresh logic and did not include creative tracking or UDF enrichment.
Deprecated
V1 legacy tables are no longer refreshed by the daily pipeline. Use the _v2 tables for all new work.
Querying the Dataset
sql
-- List all tables in the dataset
SELECT table_name, row_count, size_bytes / 1048576 AS size_mb
FROM `paidteam-data-warehouse.customer_ads_suse.INFORMATION_SCHEMA.TABLE_STORAGE`
ORDER BY size_bytes DESC;sql
-- Compare staging vs final schema for a platform
SELECT column_name, data_type
FROM `paidteam-data-warehouse.customer_ads_suse.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name IN (
'suse_google_adlevel_staging_v2',
'suse_google_adlevel_final_v2'
)
ORDER BY table_name, ordinal_position;