Export ClickFlare Data to Looker Studio, Tableau, Power BI and BigQuery

function pullClickFlare() {
  const apiKey = PropertiesService.getScriptProperties().getProperty('CLICKFLARE_API_KEY');
  const tz = 'UTC';
  const end = new Date();
  const start = new Date(end.getTime() - 30 * 24 * 3600 * 1000);
  const fmt = d => Utilities.formatDate(d, tz, 'yyyy-MM-dd');

  const metrics = ['date', 'campaignID', 'campaignName', 'visits', 'clicks',
                   'conversions', 'revenue', 'cost', 'profit'];
  const body = {
    startDate: fmt(start) + ' 00:00:00',
    endDate: fmt(end) + ' 23:59:59',
    timezone: tz,
    groupBy: ['date', 'campaignID'],
    metrics: metrics,
    sortBy: 'date',
    orderType: 'asc',
    pageSize: 10000,
    includeAll: false,
    conversionTimestamp: 'visit',
  };

  const rows = [];
  for (let page = 1; page <= 1000; page++) {
    body.page = page;
    const res = UrlFetchApp.fetch('https://public-api.clickflare.io/api/report', {
      method: 'post',
      contentType: 'application/json',
      headers: { 'api-key': apiKey },
      payload: JSON.stringify(body),
    });
    const items = JSON.parse(res.getContentText()).items || [];
    items.forEach(r => rows.push(metrics.map(m => r[m] ?? '')));
    if (items.length < body.pageSize) break;
  }

  const sheet = SpreadsheetApp.getActive().getSheetByName('ClickFlare');
  sheet.clearContents();
  sheet.getRange(1, 1, 1, metrics.length).setValues([metrics]);
  if (rows.length) sheet.getRange(2, 1, rows.length, metrics.length).setValues(rows);
}

let
    Body = [
        startDate = "2026-10-01 00:00:00",
        endDate = "2026-10-31 23:59:59",
        timezone = "UTC",
        groupBy = {"date", "campaignID"},
        metrics = {"date", "campaignID", "campaignName", "visits", "clicks",
                   "conversions", "revenue", "cost", "profit"},
        sortBy = "date",
        orderType = "asc",
        page = 1,
        pageSize = 50000,
        includeAll = false,
        conversionTimestamp = "visit"
    ],
    Response = Web.Contents(
        "https://public-api.clickflare.io/api/report",
        [
            Headers = [#"Content-Type" = "application/json", #"api-key" = ApiKey],
            Content = Json.FromValue(Body),
            Timeout = #duration(0, 0, 5, 0)
        ]
    ),
    Items = Json.Document(Response)[items],
    Result = Table.FromRecords(Items)
in
    Result

import csv, os, requests
from datetime import date, timedelta

API = "https://public-api.clickflare.io/api/report"
KEY = os.environ["CLICKFLARE_API_KEY"]
METRICS = ["date", "campaignID", "campaignName", "trafficSourceName",
           "visits", "clicks", "conversions", "revenue", "cost", "profit"]

def pull(start, end, page_size=50000):
    body = {
        "startDate": f"{start} 00:00:00", "endDate": f"{end} 23:59:59",
        "timezone": "UTC", "groupBy": ["date", "campaignID"],
        "metrics": METRICS, "sortBy": "date", "orderType": "asc",
        "pageSize": page_size, "includeAll": False,
        "conversionTimestamp": "visit",
    }
    for page in range(1, 1001):
        body["page"] = page
        r = requests.post(API, json=body, headers={"api-key": KEY}, timeout=300)
        r.raise_for_status()
        items = r.json().get("items", [])
        yield from items
        if len(items) < page_size:
            break

end = date.today()
start = end - timedelta(days=30)
with open("clickflare.csv", "w", newline="") as f:
    w = csv.DictWriter(f, fieldnames=METRICS, extrasaction="ignore")
    w.writeheader()
    w.writerows(pull(start, end))

import json
with open("clickflare.ndjson", "w") as f:
    for row in pull(start, end):
        f.write(json.dumps(row) + "\n")

bq load \
  --source_format=NEWLINE_DELIMITED_JSON \
  --autodetect \
  --replace \
  your_dataset.clickflare_daily \
  gs://your-bucket/clickflare/2026-10-31.ndjson