Skip to content
SEO与数字营销

Google Ads to Sheets or JSON: Fields to Check Before You Reconcile With CRM

By Dizital Media· ·Dizital Media

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

When a senior performance owner closes the month, the Ads export and the CRM rarely tell the same story on first glance. That gap is usually decided before download: rows without stable IDs, a range compared across two time rules, or a Conversions total built from different actions. Lock those three points first, then choose Sheets, CSV or JSON.

Direct answer: decide the reconcile question, fix IDs and row grain, write range plus account zone plus click-time versus conversion-time on the file, separate primary actions from secondary steps, and handle micros once. Use Sheets or XLSX for shared review and the API or warehouse path when a tool asks for JSON. Reconcile one action in one campaign group before any bid change.

Write the question before you click download

One sentence sets the whole file. For example: which campaign groups created sales-accepted demo requests in October, and at what spend? That sentence selects range, grain and the action to carry forward.

  • To answer spend by campaign, a campaign total is enough.
  • To answer when and where spend happened, add day and device splits.
  • To answer which intent spent without qualified follow-up, add search terms beside the keyword bought.

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. For a table that must combine day, device, geography or conversion splits, build it in the Reports area instead. For a repeated or large pull, query with GAQL through the Google Ads API rather than clicking.

Keep IDs and grain joinable

Display names are edited; IDs are not. Keep customer ID in the file, keep campaign ID next to the campaign name, and keep every split that makes a row unique, such as date, device, network slice, geographic slice and conversion action where used.

A campaign total and a campaign-by-day-by-device file are different grains. If the CRM key ignores that difference, valid rows overwrite each other and the shortfall looks like lost spend. Do not export one giant all-columns file. Export the grain you will actually analyse, name the file with account, report, range, basis and pull time, and keep the raw download unchanged while analysis happens in a copy.

Put range, zone and split on the file

Exports are dated in the account's time zone, which may not match your calendar or your CRM timestamps. A lead logged late on the last evening can sit in September on one side and October on the other, with no error on either side. Write the zone on the file itself.

A monthly view can hide a weak week, while a daily pull across a long range becomes hard to read. Use the segment control to split by day, week or month rather than exporting several ranges and joining them later. When two periods are compared, keep the same zone and the same conversion basis on both sides.

Fix which conversions count

Before you build a report on conversions, open the conversion settings and write down: the basis (conversion or click time), which actions are primary, the counting method and the attribution model. Conversion-time reporting places the event where it happened; click-time reporting places it where the click happened. A late-September click that becomes an early-October signup therefore sits in different months under the two rules. Neither rule is wrong, but they must not be mixed in one comparison.

Only primary actions feed the Conversions column used for bidding. Secondary steps such as a page view or click-to-contact stay separate and should be labelled that way in the 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 through the whole check.

Choose Sheets, CSV or JSON without breaking 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 CSV for scripts and stores, XLSX for spreadsheet review, and the Sheets route when several people must see the same live file.

Open CSV through 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. Add the cost column and work out your own ratios in the sheet, because an exported average can hide the segment that needs action. As a hypothetical teaching illustration with invented figures, not a client outcome: a file holds 47 ad-tracked contacts in a month; sales accepts 16 as qualified under a written definition, 11 dated inside that month and 5 in the next month, with 2 reaching order. Each figure describes a different stage.

Reporting downloads do not list JSON. When a downstream tool asks for JSON, use an owned route: call the REST form of the Google Ads API with a GAQL query and receive JSON rows, or land data in a warehouse and emit JSON there for a recurring match. For the field workflow behind this checklist, see Guangsuan's guide to exporting Google Ads data. Cost in an API row arrives in micros in the account currency, so convert once and note where that step happened.

Run one narrow pass before touching bids

  1. Select one primary action, one campaign group and one closed period.
  2. Align basis, range and zone on both extracts.
  3. Separate ad-sourced records from organic and referral entries.
  4. Check counting and repeated uploads before calling a remainder unexplained.
  5. Leave any remaining gap marked as unresolved rather than assigning unknown traffic to Ads.

When few contacts ever qualify under the written definition, review search-term intent and landing-page fit before moving bids. Structure decides whether the join is meaningful: clean naming, one intent per campaign, and tracking that matches how leads actually arrive.

When Guangsuan can help

If your reports are hard to read because the account structure is messy, or you want help turning exports into decisions, Guangsuan's Google Ads management service covers account structure, search terms, landing pages, conversion checks and reporting. Scope and fees sit in a contract, with no fixed enquiry promise. Details are on the Guangsuan Google Ads management service page. For a first look, bring the action list with primary flags plus one annotated extract that shows basis and zone.

FAQ

Click time or conversion time for month end? Use the rule that answers the question and keep it on both sides, with window and account zone filed with the extract.

Does an openable file mean a matchable file? No. Readable layout says nothing about grain, units, date rule or which actions were summed.

Can a missing term mean hidden rows? Yes. Very quiet rows can drop from a filtered view, so confirm filters and grain before saying the term never matched.