How to load Microsoft Ads data into Snowflake with OWOX Data Marts
To get Microsoft Ads data into Snowflake, create a connector Data Mart, sign in with Microsoft, enter the numeric account and customer IDs, choose the Ad Performance Report and publish. The connector creates the table in your Snowflake account and refreshes it on a schedule. The report is segmented by device, network and match type, so every query starts with an aggregation.
Microsoft Ads is rarely the largest line in a paid search budget, which is exactly why it gets left out of reporting. The numbers stay in the Microsoft Advertising interface, somebody exports them once a month, and the cross-channel report shows Google with a footnote.
Loading Microsoft Ads into Snowflake fixes that for good. This guide sets it up with the Microsoft Ads connector in OWOX Data Marts: the IDs to use, the report to load, what the table looks like and the SQL to read it.
What you end up with
A table in your own Snowflake account, microsoft_ads_ad_performance_report, with daily performance for every ad: impressions, clicks, spend and conversions, alongside the campaign, ad group and ad they belong to.
The connector requests the report from the Microsoft Advertising API, writes it into the table and repeats on a schedule. It does not model the data. The reporting logic is SQL you write.
Before you start
In Microsoft Advertising. A user who can open the account, and two numbers: the account ID and the customer ID. Take both from the address bar when the account is open: the value of aid is the account ID and the value of cid is the customer ID. Microsoft also shows alphanumeric account numbers in some places. Those are not the ones the connector needs.
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 for a daily load, and Snowflake pricing explained shows what it 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 Data Mart
Click New Data Mart, enter a title such as “Microsoft Ads Performance”, select the Snowflake storage and click Create Data Mart. On the Data Setup tab, set Definition Type to Connector and choose Microsoft Ads.
Step 2: sign in and enter the IDs
Step 2: sign in and enter the IDs
Sign in with Microsoft is the short way. Use the Microsoft Advertising user who has access to the account. The connector can only import accounts that this user can reach.
Manually means bringing your own credentials: an app registered in Microsoft Azure, which gives a client ID and client secret, a refresh token obtained for that app, and a developer token requested in Microsoft Advertising under Settings, Developer Settings. The credentials guide covers each step. With the sign-in button you skip all of it.
Then enter Account ID(s) and Customer ID.
Two advanced settings are worth a look before you continue.
- Report Timezone. The default is Greenwich Mean Time. If your account runs in another time zone, set it here, or daily totals will not match the Microsoft Advertising interface.
- Aggregation. Daily by default. Weekly and monthly are available if you never need the day.
Step 3: choose the report and the fields
Step 3: choose the report and the fields
Each Data Mart loads one endpoint. Microsoft Ads has three.
- Ad Performance Report. Daily ad performance: impressions, clicks, spend and conversions, segmented by device, network and match type. This is the one for reporting.
- User Location Performance Report. The same metrics by the user’s physical location: country, state and city.
- Campaigns. Campaign and ad group structure: type, status, bid strategy and budget. No metrics.
Then select fields.
Notice how many fields are locked. The report’s keys are not only the ad and the day. They include currency, ad distribution, device type, device operating system, network, top versus other, both match types, language and campaign type. These columns are always loaded, because together they identify a row.
The last screen asks for the database, schema and table. The setup proposes a database named after the source, the schema PUBLIC and a table named after the endpoint.
Step 4: publish and run
Click Publish & Run Data Mart. Publishing starts the first import. When it finishes, the Data Setup tab shows the source, the Snowflake table it writes to and the columns.
Step 5: load history and set the schedule
Step 5: load history and set the schedule
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, so late conversions update rows that were already loaded. Rows are merged on the keys, so a re-imported day replaces the earlier version.
What the table looks like in Snowflake
What the table looks like in Snowflake
One ad has many rows per day. Because of the keys above, a single ad on a single day appears once for each combination of device, network, match type and the rest. A SELECT * looks like duplicates. It is not: it is the report’s grain. Every query on this table starts with GROUP BY.
Names are case-sensitive, and capitalised. The connector creates the schema, table and column names quoted, exactly as written, and Microsoft’s field names start with a capital letter: CampaignName, Spend, TimePeriod. In Snowflake a quoted name is case-sensitive and an unquoted one is read as upper case, so SELECT Spend looks for SPEND and fails. Write "Spend", and quote the schema and table too: ads_owox."PUBLIC"."microsoft_ads_ad_performance_report". The database name is not quoted.
The day is TimePeriod. There is no column called date. With daily aggregation, TimePeriod is the day.
Query the data
Both queries were validated on Snowflake against a table loaded by the connector. The examples use a shared database, ads_owox, for all ad platforms.
Spend, clicks and impressions by campaign and day, for the last 30 days:
SELECT
"TimePeriod" AS date,
"CampaignName" AS campaign,
SUM("Impressions") AS impressions,
SUM("Clicks") AS clicks,
SUM("Spend") AS spend
FROM ads_owox."PUBLIC"."microsoft_ads_ad_performance_report"
WHERE "TimePeriod" >=
DATEADD(day, -30, CURRENT_DATE())
GROUP BY "TimePeriod", "CampaignName"
ORDER BY date, spend DESC;The same period by device, with cost per click and click-through rate computed from the sums:
SELECT
"DeviceType" AS device,
SUM("Impressions") AS impressions,
SUM("Clicks") AS clicks,
SUM("Spend") AS spend,
SUM("Spend") / NULLIF(SUM("Clicks"), 0) AS cpc,
SUM("Clicks") / NULLIF(SUM("Impressions"), 0) AS ctr
FROM ads_owox."PUBLIC"."microsoft_ads_ad_performance_report"
WHERE "TimePeriod" >=
DATEADD(day, -30, CURRENT_DATE())
GROUP BY "DeviceType"
ORDER BY spend DESC;Do not sum or average the Ctr, AverageCpc and ConversionRate columns. They are ratios for one segment of one ad on one day. Recompute them from summed clicks, impressions and spend, as above.
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.
In practice this table is too granular to hand to anyone as it is. Publish the campaign-by-day query above as its own Data Mart and report from that. Snowflake reports in Google Sheets covers the delivery, and self-service marketing analytics on Snowflake shows how to put Microsoft Ads next to the other channels in one table.
When the numbers do not match the interface
When the numbers do not match the interface
Three causes account for most differences.
- Time zone. The report time zone in the connector is not the account’s time zone. Set it in the advanced settings and run a backfill for the affected period.
- Recent days. Conversions are still arriving. Compare a period that ended more than a few days ago, or raise the lookback window.
- Partial aggregation. A filter on device or network in the query, left over from an earlier analysis, removes rows the interface total includes.
If a run fails outright, Run History shows the error the API returned. The usual one is an ID problem: an alphanumeric account number where the numeric aid value belongs, or an account the signed-in user cannot open.
Where to go next
The same setup loads the other sources into the same Snowflake account: Google Ads, Facebook Ads, LinkedIn Ads, TikTok Ads, Reddit Ads and Shopify.
For the layer you build on top of these tables, see Snowflake as a reusable reporting layer. 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
Why do Microsoft Ads totals in Snowflake differ from the Microsoft Advertising interface?
The most common cause is the time zone. The connector's Report Timezone setting defaults to Greenwich Mean Time, so daily totals shift if the account runs in another zone. Set it in the connector's advanced settings and backfill the period. Recent days can also differ while conversions are still arriving.
How do I load Microsoft Ads data into Snowflake automatically?
Create a Data Mart in OWOX Data Marts on your Snowflake storage, set its definition type to Connector and choose Microsoft Ads. Sign in with Microsoft, enter the numeric account ID and customer ID, pick the Ad Performance Report and the fields you need, then publish. A Connector Run trigger on the Triggers tab repeats the load daily, weekly, monthly or at an interval.
How do I deliver Microsoft Ads data from Snowflake to stakeholders?
Publish a query that aggregates the report, for example spend by campaign and day, as its own Data Mart. On its Destinations tab, create reports to Google Sheets, Data Studio, Slack or email, each with its own columns and schedule. The data stays in your Snowflake account, and every report reads the same definition.
Why does the Microsoft Ads table have several rows for one ad on one day?
That is the grain of the Ad Performance Report. Its keys include device type, device operating system, network, ad distribution, match types, language and campaign type as well as the ad and the day, so one ad has a row for each combination. Always aggregate with GROUP BY, and recompute ratios such as CTR and CPC from summed clicks, impressions and spend.
How do I schedule Microsoft Ads loads and keep recent days correct?
Add a Connector Run trigger on the Data Mart's Triggers tab. Each scheduled run is incremental: it loads the current day plus the days set by the Reimport Lookback Window, two by default, so late conversions update rows that were already loaded. Rows are merged on the report's keys, so a re-imported day replaces the earlier version. Run History lists every run with its status.
Can Microsoft Ads be combined with Google Ads and other channels in Snowflake?
Yes. 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. For Microsoft Ads the day is the TimePeriod column and spend is Spend. Publish the query as a Data Mart so every report uses the same definition.
Why does my query fail on Microsoft Ads column names in Snowflake?
The connector creates schema, table and column names quoted, exactly as Shopify spells them in camel case, for example totalPrice and processedAt. In Snowflake a quoted name is case-sensitive and an unquoted name is read as upper case, so the column is not found. Write every name in double quotes exactly as created.
Which Microsoft Ads IDs does the connector need?
The numeric account ID and customer ID used by the Microsoft Advertising API. Open the account in Microsoft Advertising and read them from the address bar: the aid value is the account ID and the cid value is the customer ID. The alphanumeric account numbers shown elsewhere in the interface are not accepted. The signed-in user must have access to the account.
