writing
Field Notes

Automation for Social Advertising Data Management with Python and SQL

Sep 2024 7 min Python · SQL

Efficiently managing and analyzing social advertising data requires robust and automated solutions. This post explores Python scripts specifically developed to streamline data retrieval, processing, and reporting from various social advertising sources, utilizing advanced SQL queries for precise data handling. All examples are anonymized to maintain strict confidentiality.

Establishing the Python Automation Environment

Each Python script begins by importing essential libraries to facilitate efficient data manipulation, interaction with databases, and integration with Google Sheets:

import pandas as pd
import numpy as np
from clickhouse_driver import Client
import gspread_pandas as gp
import custom_analytics_functions as caf
import os

These libraries are critical for the seamless automation of data analytics processes.

SQL Queries for Accurate Data Retrieval

At the core of these scripts are sophisticated SQL queries, precisely crafted to extract and calculate key performance metrics (KPIs) for social campaigns. A representative anonymized SQL query used for detailed campaign analytics looks like this:

SELECT 
    event_date AS day, 
    SUM(ae.revenue) AS spend, 
    SUM(fb_purchases) AS purchases,
    CASE WHEN ROUND(spend/purchases,2) IN (Inf, -Inf) OR isNaN(ROUND(spend/purchases,2)) THEN 0 ELSE ROUND(spend/purchases,2) END AS cpa,
    SUM(fb_checkouts) AS add_to_carts,
    CASE WHEN (spend/add_to_carts) = Inf THEN 0 ELSE ROUND(spend/add_to_carts,2) END AS cost_per_atc
FROM 
    {table_name} ae
LEFT JOIN (
    SELECT event_date, SUM(mb_website_purchases) AS fb_purchases, SUM(mb_checkouts_initiated) AS fb_checkouts
    FROM {facebook_table}
    WHERE event_date BETWEEN '2022-11-01' AND '{airflow_date}'
    AND mb_campaign_id LIKE '238XXXXXXXXXXXXXXX'
    GROUP BY event_date
) fs ON ae.event_date = fs.event_date
GROUP BY day
ORDER BY day;

This query structure allows accurate aggregation of spend, conversions, and performance metrics essential for informed decision-making.

Data Integration and Cleaning in Python

Post data extraction, scripts apply detailed transformations and cleaning steps, enhancing data accuracy and usability:

df['event_date'] = pd.to_datetime(df['event_date'])
df['spend'] = df['spend'].replace('[\$,]', '', regex=True).astype(float)

This meticulous data cleaning process ensures consistency and prepares the data for further analysis.

Advanced Data Merging and Conditional Logic

Python's powerful pandas library facilitates complex data merging, critical for historical data integration:

merged_df = pd.merge(current_df, historical_df, on='event_date', how='left')

merged_df['spend'] = merged_df.apply(
    lambda row: row['historical_spend'] if row['spend'] != row['historical_spend'] and row['event_date'] < pd.Timestamp('2024-06-01') else row['spend'], axis=1
)

Such conditional logic ensures accurate and reliable historical data reconciliation.

Google Sheets Integration for Real-Time Data Reporting

To enhance accessibility and transparency, the processed data is seamlessly integrated into Google Sheets:

caf.create_or_update_google_sheet(
    share_with='analytics_team@example.com',
    df=processed_df,
    workbook_name='Anonymized Campaign Report',
    sheet_name='Daily Analytics'
)
caf.auto_resize_columns(
    spreadsheet_id='AnonymizedSpreadsheetID',
    sheet_name='Daily Analytics'
)

This real-time integration ensures stakeholders have immediate access to the latest analytics.

Robust Error Handling and Data Validation

Scripts incorporate rigorous error checking and data validation measures, protecting against inaccuracies:

if df.empty:
    sys.exit("Error: Data retrieval resulted in an empty dataset. Exiting.", 1)
else:
    df.to_csv('/secure/path/report.csv', index=False)

This robust handling ensures high reliability and immediate issue detection.

Formatting and Enhancing Readability

Clear formatting enhances readability and comprehension of reported metrics:

def format_currency(x):
    return f'${x:,.2f}'

def format_percentage(x):
    return f'{x:,.2f}%'

processed_df['spend'] = processed_df['spend'].map(format_currency)
processed_df['ctr'] = processed_df['ctr'].map(format_percentage)

Readable formatting significantly improves stakeholder understanding and engagement.

Strategic Benefits and Applications

The implementation of these advanced Python scripts delivers several strategic benefits, including:

  • Efficiency Gains: Automating complex data retrieval and processing saves considerable time.
  • Enhanced Accuracy: Minimized manual handling reduces errors and improves data reliability.
  • Immediate Data Availability: Real-time updates facilitate rapid decision-making.
  • Comprehensive Error Management: Proactive error handling maintains data integrity.
  • Scalability: Adaptable scripts can easily scale to accommodate evolving business needs.

Conclusion

Through Python automation combined with advanced SQL queries, businesses can dramatically enhance the effectiveness and accuracy of their social advertising analytics. These robust and confidential solutions enable quick, data-driven decisions, positioning organizations for sustained strategic success in dynamic digital marketing environments.