Skip to content
SEO与数字营销

Google Ads to Excel, Sheets or JSON: Join Keys, Dates and Conversions to Lock Before CRM Reconcile

A AdSun• • By AdSun Editorial

Brand promotion: this article introduces Guangsuan's Google Ads management services.

A growth lead at a paid media team like AdSun often prepares one handoff file for finance: ad spend by campaign and day, matched to qualified pipeline in the CRM. When the totals differ, the cause is usually set before download — the file lacks a stable key, mixes two date bases, or counts different conversion actions.

Direct answer: Build the export around a join, not around a screenshot. Record customer and campaign IDs with every segment that defines a row, write range plus account time zone plus conversion-time versus click-time on the file, mark which actions are primary, and convert micros once. Check Excel and Sheets formatting on open, reserve JSON for the API or warehouse path, and reconcile one action and one campaign group before touching bids.

Define the join before you choose a format

On the Campaigns, Ad groups, Keywords, Search terms or Ads page, set the date range and columns, then use the download icon at the top right of the table (or the three-dot menu) to download a file.

That download is only joinable if each row has a stable identity. Keep customer ID in the file, keep campaign ID next to campaign name because names are edited, and keep every segment that splits a row, such as date, device, network, geographic slice and conversion action. For analysis beyond spend and clicks, export by: Search terms and keywords, to separate intent from the keyword you bid on.

A campaign total and a campaign-by-day-by-device export are different grains. If the CRM join ignores grain, valid rows overwrite each other and the gap looks like missing spend.

Lock range, time zone and conversion basis in writing

Exports are dated in the account's time zone, which may not match your calendar or your CRM timestamps.

A monthly file can hide a weak week, while a daily file over a long range becomes hard to review. Export the grain you will actually analyse. In a table or report, use the segment control to split data by day, week or month rather than exporting several ranges and joining them later.

Conversions need the same discipline. Conversions: click time vs conversion time is the part that most often makes an export look wrong. A click on 30 September that converts on 7 October belongs to different months under the two bases. Neither basis is wrong, but a comparison that mixes them measures the setting, not performance. Before you build the report, note basis, window, counting method and attribution model beside the file, because changing any of them changes historical comparisons.

Open Excel and Sheets without corrupting the file

Google Ads supports CSV, CSV for Excel, TSV and XLSX, and can send a file to Google Sheets or by email on a schedule.

Use XLSX or Sheets for human review and CSV for scripts and databases. Open CSV with Data > From Text/CSV, force UTF-8, confirm the delimiter, convert cost and clicks from text to numbers, and check one date row before trusting day-month order. Secondary actions — often micro-conversions such as a page view or a click-to-contact — are reported separately and should be labelled as such in your export.

If you rely on offline or CRM-confirmed leads, remember the export only shows what Google was able to record; sales-confirmed numbers live in your own system. Keep clicks, platform conversions, qualified enquiries and closed revenue as four separate counts.

What the JSON path actually requires

For recurring, large or automated exports, query with GAQL instead of clicking through the interface.

The API returns rows carrying the fields the query selected, for example campaign ID and name with clicks, conversions and cost in micros. Cost therefore needs one documented divide-by-one-million step in account currency, never a second conversion downstream. Name files with account, report, range, basis and extraction time, keep the raw file unchanged, and replace the same slice on retry rather than appending a duplicate. For the field workflow behind this checklist, see Guangsuan's guide to exporting Google Ads data.

Narrow reconcile pass before any bid decision

Pick one primary conversion action, one campaign group and one period. Align basis, range and time zone on both sides, separate ad-sourced records from organic and referral, then check counting and repeated uploads before calling a gap unexplained.

Hypothetical teaching example with invented numbers, not a client result: an export logs 73 ad-recorded leads in a month. Sales qualifies 22 under a written definition, 16 dated inside that month and 6 dated in the next month, and 5 become orders. The three totals describe different stages, so none contradicts another. If only a small share ever qualifies, review search terms and landing-page fit before adjusting bids.

Guangsuan's Google Ads management service covers account structure, search terms, landing pages, conversion checks and reporting. Scope, fees and handoff are on the Guangsuan Google Ads management service. Bring the conversion action list with primary status marked and one annotated export with basis and time zone written on it.

FAQ

Which IDs must be in the file to join to CRM?

Customer ID, campaign ID with campaign name, plus date and any device, network or conversion-action segment that defines a row.

Click-time or conversion-time for month-end?

Use the basis that answers the question and keep it on both sides, with window and account time zone recorded.

Does an opened file mean a reconcilable file?

No. Readable layout says nothing about grain, units, date basis or which actions were counted.

Ready to turn this into pipeline?

Book a 30-minute paid strategy audit with a senior strategist. We'll map your current spend, find the leaks, and model the 90-day lift.

Get My Free Strategy Audit