How to Integrate Retool with Facebook Ads

Standard Meta OAuth user tokens expire after 60 days and require re-authentication through a browser flow — impractical for a Retool server-side integration. System Users are service accounts in Meta Business Manager designed specifically for API integrations; their tokens do not expire unless manually revoked. In Meta Business Manager (business.facebook.com), go to Business Settings (gear icon). In the left sidebar, navigate to Users → System Users. Click Add to create a new System User. Give it a descriptive name (e.g., Retool API Integration) and set the role to Admin (for full read/write access) or Employee (for read-only access to most data). Click Create System User. The new user appears in the list. Click Generate New Token on the System User. Select your app from the dropdown (create a basic app in Meta Developers portal if you do not have one — a Business type app works). In the permissions section, enable ads_read for read-only reporting access, plus ads_management if you need to pause campaigns or adjust budgets from Retool. Click Generate Token. Meta displays the token — copy it immediately. Unlike standard tokens, System User tokens do not have a 60-day expiry unless you set one explicitly. After generating the token, assign the System User to your ad account: in Business Settings → Accounts → Ad Accounts, select your ad account, click Add People, and add the System User with Analyst access (read-only) or Advertiser access (read/write).

In Retool, navigate to the Resources tab and click Add Resource. Select REST API from the resource type list. Name the resource Meta Marketing API or Facebook Ads. In the Base URL field, enter https://graph.facebook.com/v19.0 — use a recent stable API version (Meta updates version numbers regularly; v19.0 was current as of early 2024, check the Meta developer changelog for the latest stable version). In the Headers section, add a default header that will be sent with every request: click Add Header, set the name to Authorization and the value to Bearer {{ config.META_ACCESS_TOKEN }}. This references a Retool configuration variable for the access token. Alternatively, you can add the token as a URL parameter named access_token — Meta's Graph API accepts authentication via either the Authorization header or an access_token URL parameter. To store the token as a configuration variable: go to Settings → Configuration Variables in Retool, click Add variable, name it META_ACCESS_TOKEN, paste the System User token as the value, and mark it as secret. Using a configuration variable rather than hardcoding the token in the resource allows credential rotation without editing the resource, and the secret flag prevents the token from being exposed in frontend context. Click Save on the Resource and test it by creating a quick GET query to /me/adaccounts to confirm the token has access to your ad accounts.

Create a new query targeting your Meta Marketing API resource. Set Method to GET. Set Path to /act_{{ retoolContext.configVars.META_AD_ACCOUNT_ID }}/insights — replace the ad account ID reference with your actual account ID in the format act_123456789 (the act_ prefix is required). In the URL Parameters section, add the following parameters to control what data the Insights API returns. Add fields with a comma-separated list of metrics: campaign_id, campaign_name, spend, impressions, clicks, ctr, cpm, cpc, reach, frequency, and actions (for conversions). The actions field returns all conversion events as an array. Add date_preset with value last_30d for the last 30 days, or use time_range with a JSON value like { since: {{ dateRangePicker.value[0] }}, until: {{ dateRangePicker.value[1] }} } for custom date ranges. Add level with value campaign to get campaign-level aggregation (options: account, campaign, adset, ad). Add limit with value 200 to retrieve up to 200 campaigns. Run the query. Meta returns a data array with one object per campaign containing the requested metrics. Note that the actions field returns an array of objects like [{ action_type: 'purchase', value: '15' }] — you need to extract the specific action types in your transformer.

1// JavaScript transformer for Meta Insights API response
2// Flatten the response and extract specific conversion action types
3const campaigns = data.data || [];
4
5return campaigns.map(campaign => {
6  // Extract specific action types from the actions array
7  const actions = campaign.actions || [];
8  const getAction = (type) => {
9    const action = actions.find(a => a.action_type === type);
10    return action ? parseFloat(action.value) : 0;
11  };
12
13  const purchases = getAction('purchase');
14  const addToCart = getAction('add_to_cart');
15  const leads = getAction('lead');
16  const spend = parseFloat(campaign.spend || 0);
17
18  return {
19    campaign_id: campaign.campaign_id || '',
20    campaign_name: campaign.campaign_name || '',
21    spend: spend.toFixed(2),
22    spend_display: `$${spend.toFixed(2)}`,
23    impressions: parseInt(campaign.impressions || 0),
24    clicks: parseInt(campaign.clicks || 0),
25    ctr: campaign.ctr ? (parseFloat(campaign.ctr) * 100).toFixed(2) + '%' : '0%',
26    cpm: campaign.cpm ? `$${parseFloat(campaign.cpm).toFixed(2)}` : '$0',
27    cpc: campaign.cpc ? `$${parseFloat(campaign.cpc).toFixed(2)}` : '$0',
28    reach: parseInt(campaign.reach || 0),
29    purchases: purchases,
30    add_to_cart: addToCart,
31    leads: leads,
32    cost_per_purchase: purchases > 0 ? `$${(spend / purchases).toFixed(2)}` : 'N/A',
33    roas: purchases > 0 && spend > 0 ? (purchases / spend * 100).toFixed(2) : '0'
34  };
35});

With the Insights data available, build the dashboard layout. Drag a Table component onto the canvas and bind it to {{ getInsights.data }}. Configure columns with appropriate formatting: spend with currency format, ctr and roas as numbers, purchases as integer. Enable sort on all metric columns so buyers can rank campaigns by any KPI. Add conditional formatting: highlight rows where cost_per_purchase is above a target value (reference a Number input labeled Target CPP to make this dynamic). Add a DateRangePicker component at the top of the dashboard. Create a second Insights query for daily data to power trend charts — use the same endpoint but add time_increment: 1 to the URL parameters and add date as a field. This returns one row per day. Drag a Chart component below the table and bind it to {{ getDailyInsights.data }}. Set Chart type to Line, X axis to date, and add two data series: spend and purchases. This creates a dual-axis view showing spend trend alongside conversion volume. Add a second Chart as a bar chart showing the same campaigns table data sorted by spend — this gives a visual performance ranking. Add Stat components at the top showing totals for the period: total spend, total purchases, and blended ROAS — calculate these with a JavaScript query that sums the campaigns data array.

1// JavaScript query to compute period totals for Stat components
2// Reference this query in Stat component value fields
3const campaigns = getInsights.data || [];
4
5const totalSpend = campaigns.reduce((sum, c) => sum + parseFloat(c.spend || 0), 0);
6const totalPurchases = campaigns.reduce((sum, c) => sum + (parseInt(c.purchases) || 0), 0);
7const totalImpressions = campaigns.reduce((sum, c) => sum + (parseInt(c.impressions) || 0), 0);
8const totalClicks = campaigns.reduce((sum, c) => sum + (parseInt(c.clicks) || 0), 0);
9const blendedRoas = totalSpend > 0 ? (totalPurchases / totalSpend * 100).toFixed(2) : 0;
10
11return {
12  total_spend: `$${totalSpend.toFixed(2)}`,
13  total_purchases: totalPurchases,
14  blended_roas: blendedRoas + 'x',
15  total_impressions: totalImpressions.toLocaleString(),
16  blended_ctr: totalImpressions > 0
17    ? ((totalClicks / totalImpressions) * 100).toFixed(2) + '%'
18    : '0%'
19};

Beyond reporting, Retool can control Facebook Ads campaigns if the System User has Advertiser permission. Create a query to pause or activate campaigns: set Method to POST, Path to /{{ campaignsTable.selectedRow.campaign_id }}, and Body to { status: {{ statusToggle.value ? 'ACTIVE' : 'PAUSED' }} }. Wire this to a status toggle button on each campaign row. For budget adjustments, create a POST query to /{{ campaignsTable.selectedRow.campaign_id }} with Body { daily_budget: {{ Math.round(budgetInput.value * 100) }} } — Meta stores budgets in cents, so multiply by 100. Add a Confirm dialog before any status change or budget modification. These operations require ads_management permission in your System User token. Create a campaign lookup panel where buyers paste a campaign ID into a text input and see all ad sets and ads under that campaign using /{{ campaignIdInput.value }}/adsets and /{{ adsetIdInput.value }}/ads endpoints. The full campaign hierarchy becomes navigable from Retool. For teams building full paid media operations platforms combining Meta, Google, TikTok, and LinkedIn data with revenue attribution, RapidDev's team can architect a comprehensive multi-platform Retool solution.

1// Query to fetch ad sets for a selected campaign
2// Path: /{{ campaignsTable.selectedRow.campaign_id }}/adsets
3// Method: GET
4// URL Parameters:
5{
6  "fields": "id,name,status,daily_budget,bid_amount,targeting,effective_status",
7  "limit": "200"
8}
9
10// Transformer for ad sets response
11const adsets = data.data || [];
12return adsets.map(adset => ({
13  id: adset.id || '',
14  name: adset.name || '',
15  status: adset.status || '',
16  effective_status: adset.effective_status || '',
17  daily_budget: adset.daily_budget
18    ? `$${(parseInt(adset.daily_budget) / 100).toFixed(2)}`
19    : 'Campaign Budget',
20  bid_amount: adset.bid_amount
21    ? `$${(parseInt(adset.bid_amount) / 100).toFixed(2)}`
22    : 'Automatic'
23}));