Merge LinkedIn Ads streams and export csv

merge_linkedin_streams.py

import glob

import pandas as pd


ad_campaign_analytics = pd.read_parquet([f for f in glob.glob("./linkedin_data/ad_campaign_analytics/*.parquet")])
ad_campaign_analytics = ad_campaign_analytics.loc[:, [
    'start_date',
    'costInLocalCurrency',
    'impressions',
    'clicks',
    'likes',
    'reactions',
    'comments',
    'pivotValues'
]]
ad_campaign_analytics["start_date"] = pd.json_normalize(ad_campaign_analytics["start_date"])["member0"]
ad_campaign_analytics["campaign_id"] = [str(r[0].split(":")[-1]) for r in ad_campaign_analytics["pivotValues"]]
ad_campaign_analytics.drop(columns=["pivotValues"], inplace=True)
ad_campaign_analytics = ad_campaign_analytics.loc[~ad_campaign_analytics[
    ["impressions", "clicks", "likes", "reactions", "comments"]
].isna().all(axis=1)]


campaigns = pd.read_parquet([f for f in glob.glob("./linkedin_data/campaigns/*.parquet")])
campaigns = campaigns.loc[:, ["id", "name"]]
campaigns["id"] = campaigns["id"].astype(str)


ad_campaign_analytics = ad_campaign_analytics.merge(
    campaigns, left_on="campaign_id", right_on="id", how="left").drop(
        columns=["id", "campaign_id"]
    ).rename(columns={"name": "campaign_name"}).sort_values(
        "start_date", ignore_index=True
    )

assert not ad_campaign_analytics["campaign_name"].isna().any()


ad_campaign_analytics.to_csv(
    "./linkedin_data/output/ad_campaign_analytics.csv", index=False
)