双Facebook广告账户每周营销洞察数据获取及入库技术咨询

Hey there! Since you've already got your Facebook App set up with the Marketing API and linked both ad accounts, let's break down the complete workflow and technical details to automate weekly extraction of your ad insights (impressions, clicks, spend, etc.) and load them into your data warehouse for reporting.

First, let's lock in your authentication setup for reliable, repeated access:

• Get a long-lived access token: Short-lived tokens expire in 1 hour, which won't work for weekly jobs. Extend your short-lived token with this curl command:

  curl -i -X GET "https://graph.facebook.com/v18.0/oauth/access_token?grant_type=fb_exchange_token&client_id={YOUR_APP_ID}&client_secret={YOUR_APP_SECRET}&fb_exchange_token={SHORT_LIVED_TOKEN}"
  

  Store this long-lived token securely—use environment variables or a secrets manager, never hardcode it!
• Verify required permissions: Ensure your token has the ads_read permission (mandatory for pulling insights). Check with:

  curl -i -X GET "https://graph.facebook.com/v18.0/me/permissions?access_token={YOUR_LONG_LIVED_TOKEN}"
  

• Confirm ad account access: Double-check your app can reach both accounts by running this for each account ID:

  curl -i -X GET "https://graph.facebook.com/v18.0/act_{AD_ACCOUNT_ID}/?fields=name&access_token={YOUR_LONG_LIVED_TOKEN}"
  

  You should get the account name back if access is configured correctly.

Next, let's pull the actual data using the Marketing API. Here's how to structure your requests:

• Use the Insights endpoint: The core endpoint is /act_{AD_ACCOUNT_ID}/insights. Key parameters to specify:
  • fields: List your target metrics—impressions,clicks,spend plus extras like reach,ctr if needed.
  • time_range: For weekly pulls, set {"since":"START_DATE","until":"END_DATE"} (e.g., last Monday to last Sunday). Or use time_increment=7 for automatic weekly rollups.
  • level: Choose granularity (e.g., account for total account stats, campaign if you need breakdowns by campaign).
• Handle pagination: The API returns max 5000 rows per request. If you have more data, use the after value from the response's paging object to fetch subsequent pages.
• Example Python code (with facebook-business-sdk):

  from facebook_business.api import FacebookAdsApi
  from facebook_business.adobjects.adaccount import AdAccount
  
  # Initialize API
  FacebookAdsApi.init(
      app_id='YOUR_APP_ID',
      app_secret='YOUR_APP_SECRET',
      access_token='YOUR_LONG_LIVED_TOKEN'
  )
  
  # Define weekly date range (adjust to your preferred window)
  start_date = "2024-05-20"
  end_date = "2024-05-26"
  
  # Pull insights for one ad account
  ad_account = AdAccount('act_{AD_ACCOUNT_ID}')
  insights = ad_account.get_insights(
      fields=['impressions', 'clicks', 'spend', 'date_start', 'date_stop'],
      params={
          'time_range': {'since': start_date, 'until': end_date},
          'level': 'account',
          'time_increment': 7
      }
  )
  
  # Convert to a processable format
  insights_data = [insight.export_all_data() for insight in insights]
  

  Repeat this for your second account, or loop through a list of account IDs to streamline the process.

To run this job on autopilot every week, set up a scheduled task:

• Linux/macOS: Use cron. Edit your crontab with crontab -e and add a line like:

  0 2 * * 1 /usr/bin/python3 /path/to/your/script.py >> /path/to/logfile.log 2>&1
  

  This runs the script every Monday at 2 AM (adjust the time—wait a few hours after the week ends to ensure Facebook's data is fully processed).
• Windows: Use Task Scheduler to create a weekly task that executes your Python script.
• Cloud-based: Opt for a serverless function (AWS Lambda, GCP Cloud Functions) with a weekly trigger if you don't want to manage a server.

Before loading, prep your data for consistency:

• Data cleaning:
  • Convert numeric fields (like spend) from strings to floats/decimals.
  • Standardize date formats (e.g., YYYY-MM-DD).
  • Add an ad_account_id field to distinguish data from your two accounts.
  • Remove duplicates (use date_start, ad_account_id, and level as a composite key to check existing rows in the warehouse).
• Load to warehouse:
  • BigQuery: Use the google-cloud-bigquery library to insert rows directly, or write to a Parquet file and load via the API.
  • Snowflake: Use snowflake-connector-python for INSERT statements or the COPY command.
  • Redshift: Use psycopg2 to connect and insert data, or leverage AWS Glue for ETL.

Add safeguards to keep your weekly job running smoothly:

• Logging: Write detailed logs (start time, records pulled, errors) to a file or cloud logging service (like CloudWatch).
• Rate limiting: The Marketing API has tiered rate limits. Handle 429 Too Many Requests errors with exponential backoff in your script.
• Failure alerts: Set up email/Slack notifications if the job fails (use SendGrid or Slack API to trigger alerts).
• Token renewal: Long-lived tokens expire after 60 days. Automate renewal by storing your app ID/secret and calling the token exchange endpoint periodically.

内容的提问来源于stack exchange，提问作者degmo