Daily AI search vs organic traffic log in Google Sheets

Every morning at 6am, log yesterday's AI search traffic right next to my organic search traffic in a single Google Sheets workbook, so I can finally see whether AI answers are sending me visits or quietly replacing them. Use a cron trigger. This is a deterministic data sync: no summarizing, no commentary, no ranking, and no AI generated text anywhere in the output. Just a dependable daily append that a dashboard or a weekly report can sit on top of.

Target day. At the start of the run, compute the previous complete UTC day and use that one date for all three sources, so every tab lines up on the same date. Profound interprets a bare YYYY-MM-DD date as Eastern Time, so do not pass bare dates: pass explicit UTC instants, YYYY-MM-DDT00:00:00Z for the start and YYYY-MM-DDT23:59:59Z for the end of that day. Profound v2 report ranges are inclusive at both ends. The v2 referral and bot reports aggregate hourly in UTC, so scoping to a complete UTC day is exactly what keeps partial-day rows out of the sheet.

Step 1, referral traffic from AI answer engines. Call Profound's Get Referrals Report V2 for the target day to get visits arriving from AI answer engines. These reports use cursor pagination and default to a page size of 10, so send the maximum page size and keep passing info.next_cursor back as cursor until no cursor comes back. Collect every row before writing anything.

Step 2, AI crawler activity. Call Profound's Get Bots Report V2 over the same UTC window to get bot and crawler visits, citations, indexing, and training broken down per bot and bot type. Paginate the same way, collecting all rows.

Step 3, organic search. Call Google Search Console's Search Analytics Query for my verified property with the same target day as both startDate and endDate, requesting the date dimension so the returned row carries the day, and capture clicks, impressions, ctr, and position. Use the siteUrl string exactly as Search Console reports it, since an inexact match silently returns empty data instead of erroring. Search Console lags two to three days and keeps revising its most recent days, so set dataState to "all", read metadata.firstIncompleteDate, and record whether the target day was final or still incomplete in its own column rather than dropping the row. If Search Console returns no rows at all for that day, still append a row of zeros marked incomplete so the tab never develops gaps.

Step 4, write to Google Sheets. One workbook, three tabs, one per source, each with a fixed column layout and the date in the first column. Use these layouts and keep them identical on every run. AI Referrals: date, source, visits, one row per answer engine. AI Crawlers: date, bot, bot type, visits, citations, indexing, training, one row per bot. Organic Search: date, clicks, impressions, ctr, average position, data state, one row for the day. If a metric is missing from a response, write 0 rather than leaving the cell blank or letting the columns shift.

Write safely. On the first run, if a tab does not exist, create it and write its header row. Before appending to a tab, read that tab's date column and skip it if the target date is already present, so a retry or a manual re-run cannot double append. Write with Append Values using USER_ENTERED so the dates and numbers land as real dates and numbers that a chart can read, and INSERT_ROWS so nothing below the table gets overwritten.

Keep it boring on purpose. Do not add a summary step, do not interpret the movement, and do not reorder or editorialize the rows. If one source fails, append the rows that did succeed and let the run surface the failure rather than writing partial or invented values.