suse_reddit_adlevel_final_v2

Full reference: paidteam-data-warehouse.customer_ads_suse.suse_reddit_adlevel_final_v2Type: Final | Rows: 38,952 | Size: 18.2 MB

Reddit Ads ad-level final data (V2). Contains the complete historical record of Reddit 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
CampaignID	STRING	Yes	Reddit campaign ID
AdgroupID	STRING	Yes	Reddit ad group ID
AdID	STRING	Yes	Reddit ad ID
Campaignname	STRING	Yes	Campaign name
Adgroupname	STRING	Yes	Ad group name
Adname	STRING	Yes	Ad name
Campaignobjective	STRING	Yes	Campaign objective
GoalType	STRING	Yes	Goal type
Impressions	INTEGER	Yes	Number of impressions
Clicks	INTEGER	Yes	Number of clicks
CTR	FLOAT	Yes	Click-through rate
CPC	FLOAT	Yes	Cost per click
ECPM	FLOAT	Yes	Effective CPM
CPV	FLOAT	Yes	Cost per video view
Spend	FLOAT	Yes	Total spend
Frequency	FLOAT	Yes	Average frequency per user
Reach	INTEGER	Yes	Unique users reached
RedditLeads	INTEGER	Yes	Reddit native lead form submissions
ConversionLeadClicks	INTEGER	Yes	Click-through lead conversions
ConversionLeadViews	INTEGER	Yes	View-through lead conversions
ConversionSignUpClicks	INTEGER	Yes	Click-through sign-up conversions
ConversionSignUpViews	INTEGER	Yes	View-through sign-up conversions
ConversionViewContentClicks	INTEGER	Yes	Click-through content view conversions
ConversionViewContentViews	INTEGER	Yes	View-through content view conversions
VideoviewRate	FLOAT	Yes	Video view rate
VideoCompletionRate	FLOAT	Yes	Video completion rate
Videowatched25percent	INTEGER	Yes	Video watched to 25% count
Videowatched50percent	INTEGER	Yes	Video watched to 50% count
Videowatched75percent	INTEGER	Yes	Video watched to 75% count
Videowatched100percent	INTEGER	Yes	Video watched to 100% count
_ingested_at	TIMESTAMP	Yes	Timestamp when the row was inserted by the refresh script

Partitioning & Clustering

• Partitioned by: Date (DAY)
• Partitions: 383
• Clustered by: Campaignname, Adgroupname

Refresh Logic

The scheduled refresh script performs the following for Reddit:

1. Read all rows from suse_reddit_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,
  Campaignobjective,
  SUM(Impressions) AS total_impressions,
  SUM(Clicks) AS total_clicks,
  SUM(Spend) AS total_spend,
  SUM(RedditLeads) AS total_leads
FROM `paidteam-data-warehouse.customer_ads_suse.suse_reddit_adlevel_final_v2`
WHERE Date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY Date, Campaignname, Campaignobjective
ORDER BY Date DESC
LIMIT 100;

Notes

• Column casing matches the staging table (Campaignname, Adgroupname, AdgroupID). This inconsistency with other platforms must be handled in cross-platform views using column aliasing.
• Clustering uses the lowercase-n name columns (Campaignname, Adgroupname) to match the actual column names.
• Reddit uses Spend for cost, unlike Google (CostUSD), Microsoft (Cost), and LinkedIn (Cost).