How to load Reddit Ads data into Snowflake with OWOX Data Marts
To get Reddit Ads data into Snowflake, create a Reddit app and a refresh token, then set up a connector Data Mart with those credentials, the Ad Performance endpoint and the fields you need. The connector writes one row per ad per day into your Snowflake account and refreshes it on a schedule. Spend arrives in microcurrency, so divide it by 1,000,000.
Reddit is the channel most marketing teams add last and report on least. The spend is modest, the Ads Manager export is good enough for a monthly look, and so the data never reaches the warehouse where every other channel already is.
That works until someone asks which communities actually convert, or wants Reddit in the same table as the rest. This guide loads Reddit Ads data into Snowflake with the Reddit Ads connector in OWOX Data Marts. The setup takes a little longer than for other ad platforms, because Reddit requires your own app credentials, so that part gets the most attention here.
What you end up with
A table in your own Snowflake account, reddit_ads_report, with one row per ad per day: impressions, clicks, spend and conversions.
The connector reads the Reddit Ads API, writes that table and repeats on a schedule. It does not model the data. The reports on top are SQL you write.
Before you start
In Reddit. A Reddit account that owns or can access the ad account, and the account ID from Reddit Ads Manager.
In Snowflake. A storage of type Snowflake connected to your project, as described in the storage guide: account identifier, warehouse, and a key pair or a username with a programmatic access token. The role must be able to create a database, a schema and tables. A small dedicated warehouse with auto-suspend is enough, and Snowflake pricing explained shows what a daily load costs.
OWOX Data Marts
See your first report built in real time. 15 minutes.
- Connect your data warehouse
- Pick your metrics
- Get a live Google Sheets report
In the time it takes to write a ticket. Then imagine never writing that ticket again.
Book a DemoWe'll use your actual use case
Step 1: create the Reddit credentials
Step 1: create the Reddit credentials
Unlike the connectors for Facebook, Google or LinkedIn, this one has no sign-in button. You create a small app in Reddit and generate a refresh token for it. It is a one-time job, and the credentials guide has the exact requests. In outline:
- Create the app. In Reddit Ads, open Developer Applications and click Create App. Set the redirect URI to
https://www.reddit.com/prefs/apps. Reddit shows you an App ID and a Secret. - Authorise it. Open Reddit’s authorisation URL with your App ID, the
adsreadscope and a permanent duration, while signed in as the account that owns the ad account, and click Allow. Reddit redirects you to a URL containing acode. - Exchange the code. Send that code to Reddit’s token endpoint, authenticated with the App ID and Secret. The response contains the refresh token.
The adsread scope is read-only. The connector does not change anything in the ad account.
Step 2: create the Data Mart and fill in the connector
Step 2: create the Data Mart and fill in the connector
Click New Data Mart, enter a title such as “Reddit Ads Performance”, select the Snowflake storage and click Create Data Mart. On the Data Setup tab, set Definition Type to Connector and choose Reddit Ads.
Fill in the form:
- App ID and Secret from the app you created.
- Redirect URI:
https://www.reddit.com/prefs/apps, the same value as in the app. - Refresh Token from the exchange.
- User Agent: Reddit requires one on every API request, in a set format:
googleapps:owox-data-marts.redditads:v1.0.0 (by /u/your_reddit_username), with your own Reddit username. - Account IDs: the ad account ID from Reddit Ads Manager.
Step 3: choose the endpoint and the fields
Step 3: choose the endpoint and the fields
Each Data Mart loads one endpoint. Reddit has more of them than most platforms, in three groups.
Performance. Ad Performance is the main report: daily metrics for each ad.
Performance with a breakdown. The same metrics split by one more dimension: community, keyword, interest, placement, country, DMA, campaign, ad group or account. The community breakdown is the one that is specific to Reddit: it shows which subreddits your ads ran in and what each returned.
Structure. Campaigns, Ad Groups and Ads hold names, objectives, statuses and bids. Ad Account, Custom Audiences, Funding Instruments and Lead Gen Forms cover the rest.
Start with Ad Performance. Add the community breakdown as a second Data Mart when you want to compare subreddits.
Then select fields.
Most of the list is conversions. Reddit reports each event type separately, add to cart, add to wishlist, lead, page visit, purchase, search and others, each with its own columns. Select the events you actually track and skip the rest.
The last screen asks for the database, schema and table. The setup proposes a database named after the source, reddit_ads_owox, the schema PUBLIC and a table named after the endpoint.
Step 4: publish, backfill and schedule
Step 4: publish, backfill and schedule
Click Publish & Run Data Mart. Publishing starts the first import. Afterwards the Data Setup tab shows the source, the Snowflake table and its columns.
History. Click Manual Run and choose Backfill (custom period). One backfill run covers at most 31 days. For a longer history, run consecutive periods, each after the previous one has finished.
Schedule. A Data Mart without a trigger does not run again. On the Triggers tab, add a trigger of type Connector Run: daily, weekly, monthly or at an interval.
Scheduled runs are incremental. Each one loads the current day plus the days set by the Reimport Lookback Window, two by default. Rows are merged on the ad ID and the date, so a re-imported day replaces the earlier version.
What the table looks like in Snowflake
What the table looks like in Snowflake
Spend is in microcurrency. The spend column holds the amount multiplied by one million, in the account’s currency. An ad that spent 12.50 shows 12500000. Divide by 1,000,000 in every query, and read the currency column if you run accounts in more than one.
Names are case-sensitive. The connector creates the schema, table and column names quoted. In Snowflake that makes them case-sensitive, and an unquoted name is read as upper case. reddit_ads_owox.public.reddit_ads_report fails with “does not exist or not authorized”. reddit_ads_owox."PUBLIC"."reddit_ads_report" works. The database is not quoted; the schema, the table and every column are.
date needs its quotes twice over. It is the column’s real name and also a keyword, so always write "date".
IDs, not names. The report carries ad_id. Ad, ad group and campaign names are in the structure endpoints. Load them as separate Data Marts and join on the ID.
Query the data
Both queries were validated on Snowflake against a table loaded by the connector.
Spend, clicks and cost per click by ad and day, for the last 30 days:
SELECT
"date",
"ad_id",
SUM("impressions") AS impressions,
SUM("clicks") AS clicks,
SUM("spend") / 1000000 AS spend,
SUM("spend") / 1000000
/ NULLIF(SUM("clicks"), 0) AS cpc
FROM reddit_ads_owox."PUBLIC"."reddit_ads_report"
WHERE "date" >=
DATEADD(day, -30, CURRENT_DATE())
GROUP BY "date", "ad_id"
ORDER BY "date", spend DESC;A weekly summary with cost per thousand impressions and key conversions:
SELECT
DATE_TRUNC('week', "date") AS week,
"currency",
SUM("impressions") AS impressions,
SUM("clicks") AS clicks,
SUM("spend") / 1000000 AS spend,
SUM("spend") / 1000000
/ NULLIF(SUM("impressions"), 0) * 1000 AS cpm,
SUM("key_conversion_total_count")
AS key_conversions
FROM reddit_ads_owox."PUBLIC"."reddit_ads_report"
GROUP BY 1, 2
ORDER BY 1;Compute cost per click and CPM from the sums, as above. The cpc, ecpm and ctr columns hold the value for one ad on one day and cannot be averaged across rows.
A view over this table is the place to do the division once and rename "date" to something friendlier. Snowflake as a reusable reporting layer shows how to structure that.
From table to report
A connector Data Mart can be reported from directly. On its Destinations tab you attach reports to Google Sheets, Data Studio, Slack or email, each with its own columns and schedule.
Because of the microcurrency, it is better to publish your own query, with spend already divided, as a second Data Mart and report from that one. Snowflake reports in Google Sheets covers the delivery, and self-service marketing analytics on Snowflake shows how to put Reddit next to the other channels in one table.
When a run fails
Run History shows the error Reddit returned. With this connector, check the credentials first.
- The redirect URI differs. It must be identical in the Reddit app, in the authorisation URL and in the connector form.
- The refresh token was created by another account. Authorise while signed in as the Reddit account that owns the ad account.
- The User Agent is missing or changed. Reddit rejects requests without a properly formatted one.
- The scope is wrong. The token must be issued with
adsread.
Where to go next
The same connector setup loads the other sources into the same Snowflake account, most of them with a sign-in button and no app to create: Facebook Ads, Google Ads, Microsoft Ads, LinkedIn Ads, TikTok Ads and Shopify.
For the platform itself, start with what Snowflake is and how it works.
Turn your data into decisions.
Governed data marts give you the clean foundation ML needs to actually work.
- No AI hallucinations
- Analyst-governed definitions
- Every number traces to SQL
Frequently Asked Questions
Does the Reddit Ads connector change anything in my ad account?
No. The refresh token is issued with the adsread scope, which is read-only. The connector reads reports and settings from the Reddit Ads API and writes them to tables in your own Snowflake account. It does not create or edit campaigns, ads or budgets.
Can I get AI summaries of Reddit Ads data in Snowflake?
Yes. AI Insights in OWOX Data Marts runs on a published Data Mart and delivers a written summary to email, Slack, Microsoft Teams or Google Chat on a schedule. Point it at a Data Mart where spend is already converted from microcurrency, so the summary reads the same numbers as your reports.
How can I combine Reddit Ads with other channels in Snowflake?
Load each platform with its own connector Data Mart into the same Snowflake account. Then write one query that selects date, channel, impressions, clicks and spend from each table and combines them with UNION ALL, dividing Reddit spend by 1,000,000 first. Publish that query as a Data Mart so every report uses the same definition.
Which Reddit Ads reports can I load into Snowflake?
Ad Performance gives daily metrics for each ad. The same metrics are available with a breakdown by community, keyword, interest, placement, country, DMA, campaign, ad group or account. Campaigns, Ad Groups and Ads hold names, statuses and bids. Each endpoint is loaded by its own Data Mart into its own table.
How do backfill and incremental loads work for Reddit Ads?
Publishing the Data Mart starts the first import. For history, use Manual Run with Backfill (custom period); one run covers at most 31 days, so load longer periods as consecutive runs. Scheduled runs are incremental: each loads the current day plus the days set by the Reimport Lookback Window, two by default, and rows are merged on the ad ID and date.
What credentials does the Reddit Ads connector need?
An App ID and Secret from an app created under Developer Applications in Reddit Ads, the redirect URI used for that app, a refresh token issued with the adsread scope, a User Agent string in the format Reddit requires, and the ad account ID. There is no sign-in button for Reddit, so the app and the token are created once by hand.
Why is Reddit Ads spend so large in the Snowflake table?
The spend column is stored in microcurrency: the amount multiplied by one million, in the account's currency. Divide it by 1,000,000 in your query. Compute cost per click and CPM from the divided spend and the summed clicks and impressions, and read the currency column if you run accounts in more than one currency.
How do I load Reddit Ads data into Snowflake automatically?
Create a Reddit app and a refresh token, then create a Data Mart in OWOX Data Marts on your Snowflake storage with the definition type Connector and the source Reddit Ads. Enter the app credentials and the ad account ID, pick the Ad Performance endpoint and the fields you need, and publish. A Connector Run trigger on the Triggers tab repeats the load on a schedule.
