How to Integrate Retool with Bing Ads (Microsoft Advertising)

Microsoft Advertising requires two separate credentials: a developer token (identifies your API application) and an OAuth access token (authenticates the specific user account). First, obtain a developer token: sign in to the Microsoft Advertising UI at ads.microsoft.com. Go to Tools → API Center. If you don't have a developer token yet, click Apply for a Developer Token. For testing, you can immediately use the 'BBBBBBBBBBBB' sandbox developer token. For production, you'll receive your assigned developer token after approval — it appears in the API Center under your account settings. Next, set up the Azure AD app registration. Navigate to the Azure Portal (portal.azure.com) → Azure Active Directory → App registrations → New registration: - Name: 'Retool Microsoft Advertising' - Supported account types: Accounts in any organizational directory (for business accounts) or Personal Microsoft accounts (for personal MSA accounts) - Redirect URI: https://login.microsoftonline.com/common/oauth2/nativeclient (for desktop/server flows) After registration, go to API permissions → Add a permission → APIs my organization uses → search for 'Microsoft Advertising' → add the required scope (typically https://ads.microsoft.com/msads.manage). For a server-side Retool integration, use the client credentials flow if your Microsoft Advertising account supports it, or obtain a long-lived refresh token through the authorization code flow. Store the resulting access token and your developer token as Retool configuration variables: - BING_ADS_DEVELOPER_TOKEN: your developer token - BING_ADS_ACCESS_TOKEN: your OAuth access token (refresh periodically) - BING_ADS_CUSTOMER_ID: your Microsoft Advertising customer ID - BING_ADS_ACCOUNT_ID: your advertising account ID

Microsoft Advertising's REST API requires two authentication elements on every request: an Authorization Bearer token (OAuth) and a DeveloperToken header. Open Retool → Resources tab → Add Resource → REST API. Configure the resource: - Name: 'Microsoft Advertising (Bing Ads)' - Base URL: https://reporting.api.bingads.microsoft.com (for reporting operations) — you may also need https://campaign.api.bingads.microsoft.com for campaign management operations - Authentication: None (we add both auth elements as custom headers) Add the following default headers: - Authorization: Bearer {{ retoolContext.configVars.BING_ADS_ACCESS_TOKEN }} - DeveloperToken: {{ retoolContext.configVars.BING_ADS_DEVELOPER_TOKEN }} - Content-Type: application/json - CustomerAccountId: {{ retoolContext.configVars.BING_ADS_ACCOUNT_ID }} - CustomerId: {{ retoolContext.configVars.BING_ADS_CUSTOMER_ID }} The CustomerAccountId and CustomerId headers tell the API which account and customer context to use for the request — required for all Microsoft Advertising API calls. Create a second REST API Resource for campaign management if needed: - Name: 'Microsoft Advertising Campaign API' - Base URL: https://campaign.api.bingads.microsoft.com - Same headers as above Click Save Changes and Test Connection for both resources.

Microsoft Advertising's Reporting API is asynchronous: you submit a report request, receive a report request ID, poll until the status is Success, then download the report from the provided download URL. Create a 'Submit Report Request' query: - Method: POST - Path: /v13/Reporting/SubmitGenerateReport - Body: ```json { "ReportRequest": { "Format": "Csv", "ReportName": "RetoolCampaignReport", "ReturnOnlyCompleteData": false, "Type": "CampaignPerformanceReport", "Aggregation": "Daily", "Columns": ["TimePeriod", "CampaignName", "CampaignId", "Impressions", "Clicks", "Ctr", "AverageCpc", "Spend", "Conversions", "ConversionRate"], "Scope": { "AccountIds": [{{ retoolContext.configVars.BING_ADS_ACCOUNT_ID }}] }, "Time": { "PredefinedTime": "Last30Days" } } } ``` This returns a ReportRequestId. Create a polling query: - Method: GET - Path: /v13/Reporting/PollGenerateReport - URL parameter: ReportRequestId → {{ submitReportQuery.data.ReportRequestId }} Set the polling query to run when submitReportQuery succeeds. Add an event handler that re-triggers the poll query if the status is 'Pending' or 'InProgress'. When status is 'Success', the response contains a ReportDownloadUrl — trigger a download query to fetch the CSV data from that URL. Create the download query: - Method: GET (no resource — use 'Run JS Code' to fetch the URL) - Or configure a third resource with Base URL set dynamically to the download URL Display a loading state while the report generates using a Retool Loading component bound to the query status.

1// Poll and download handler — JavaScript query to manage the async report flow
2// This runs after pollReportQuery returns a Success status
3const pollResult = pollReportQuery.data;
4if (!pollResult) return { status: 'not_started', data: [] };
5if (pollResult.Status === 'Success' && pollResult.ReportDownloadUrl) {
6  // Return the download URL for the next query to fetch
7  return {
8    status: 'ready',
9    download_url: pollResult.ReportDownloadUrl,
10    report_id: pollResult.ReportRequestId
11  };
12} else if (pollResult.Status === 'Error') {
13  return {
14    status: 'error',
15    error_message: pollResult.TrackingId || 'Report generation failed'
16  };
17} else {
18  return {
19    status: 'pending',
20    tracking_id: pollResult.TrackingId
21  };
22}

Microsoft Advertising reports are returned as ZIP-compressed CSV files. The CSV download URL from the poll response points to a ZIP archive containing the report CSV. Since Retool queries can't natively decompress ZIP files, implement a lightweight approach: use the report download URL directly in a fetch request within a JavaScript query, or configure the report to return uncompressed CSV by setting ReturnOnlyCompleteData and a smaller date range. For a practical approach, use a JavaScript query to fetch and parse the CSV: - Create a 'Run JS Code' query type (no resource) - Use Retool's built-in fetch with the download URL from the poll response - Parse the CSV rows using a manual split approach Alternatively, set up a Retool Workflow that runs on a schedule: the Workflow submits the report request, waits for completion, downloads the CSV, parses it, and stores the cleaned data in an internal Retool Database or PostgreSQL table. Your Retool app then queries this internal table instead of the Microsoft Advertising API directly — much simpler and faster for the end user. Create a transformer to parse CSV text into table rows. Bind the data to: - A Table component showing campaign performance metrics - A Chart component (Line chart for daily spend trend, Bar chart for clicks by campaign) - Statistics components in the header for totals (total impressions, total clicks, total spend, average CTR)

1// CSV parser transformer for Microsoft Advertising report data
2// Input: data is the raw CSV text from the report download URL
3// Microsoft Advertising CSVs have a metadata header row before column names
4const lines = (typeof data === 'string' ? data : '').split('\n');
5// Skip report metadata rows (usually first few lines before the column header)
6const headerLineIndex = lines.findIndex(line => line.includes('CampaignName') || line.includes('Impressions'));
7if (headerLineIndex === -1) return [];
8const headers = lines[headerLineIndex].split(',').map(h => h.replace(/"/g, '').trim());
9const rows = [];
10for (let i = headerLineIndex + 1; i < lines.length; i++) {
11  const line = lines[i].trim();
12  if (!line || line.startsWith('"Total"') || line.startsWith('"Grand Total"')) continue;
13  const values = line.split(',').map(v => v.replace(/"/g, '').trim());
14  if (values.length < headers.length) continue;
15  const row = {};
16  headers.forEach((header, idx) => {
17    row[header.toLowerCase().replace(/\s+/g, '_')] = values[idx] || '';
18  });
19  row.spend_numeric = parseFloat(row.spend || '0');
20  row.clicks_numeric = parseInt(row.clicks || '0');
21  row.impressions_numeric = parseInt(row.impressions || '0');
22  rows.push(row);
23}
24return rows;

Create the Bing vs Google comparison view by adding a second resource targeting your Google Ads API and combining results in a transformer. Structure the comparison as a Table with two rows per campaign: one showing Bing data and one showing Google data, or side-by-side columns (BingClicks, GoogleClicks, BingCPC, GoogleCPC) for campaigns mapped across both platforms. The campaign mapping is stored in an internal database table (campaign_mapping with columns: bing_campaign_id, google_campaign_id, campaign_label). Query this table via your database resource to join Bing and Google campaign data by label. Create a JavaScript transformer that joins the two datasets: 1. Get Bing campaign data from bingReportQuery.data 2. Get Google campaign data from googleAdsQuery.data 3. Get the mapping from campaignMappingQuery.data 4. Merge by campaign label, create side-by-side comparison rows For automated reporting, set up a Retool Workflow: - Schedule trigger: Monday 8 AM - Step 1: Submit Microsoft Advertising report request (Resource Query block) - Step 2: Wait 2 minutes (add a delay or loop with poll) - Step 3: Download and parse report CSV (JavaScript block) - Step 4: Store results in internal database (Resource Query block targeting PostgreSQL) - Step 5: Send Slack notification with key metrics summary (Slack Resource Query block) This ensures the team starts each week with fresh Bing performance data already in the dashboard without anyone having to trigger a report manually.

1// Cross-engine comparison transformer
2// Joins Bing and Google campaign data using the campaign_mapping table
3const bingData = bingReportQuery.data || [];
4const googleData = googleAdsQuery.data || [];
5const mappings = campaignMappingQuery.data || [];
6return mappings.map(map => {
7  const bing = bingData.find(b => String(b.campaignid) === String(map.bing_campaign_id)) || {};
8  const google = googleData.find(g => String(g.campaign_id) === String(map.google_campaign_id)) || {};
9  return {
10    campaign_label: map.campaign_label,
11    bing_impressions: parseInt(bing.impressions_numeric || 0).toLocaleString(),
12    google_impressions: parseInt(google.impressions || 0).toLocaleString(),
13    bing_clicks: parseInt(bing.clicks_numeric || 0).toLocaleString(),
14    google_clicks: parseInt(google.clicks || 0).toLocaleString(),
15    bing_spend: '$' + parseFloat(bing.spend_numeric || 0).toFixed(2),
16    google_spend: '$' + parseFloat(google.cost || 0).toFixed(2),
17    bing_cpc: '$' + (bing.clicks_numeric > 0 ? (bing.spend_numeric / bing.clicks_numeric).toFixed(2) : '0.00'),
18    google_cpc: '$' + (parseFloat(google.average_cpc || '0')).toFixed(2)
19  };
20});