如何在Google Sheets的Analytics插件中汇总指定目标完成数？

Hey there! I get it—trying to aggregate only certain goal completions can be tricky when the straightforward approaches don't work. Let's break down why your previous attempts didn't pan out, then walk through two reliable solutions.

Why Your Earlier Formulas Failed #

• ga:goalCompletionsAll is a single metric that returns the sum of all goal completions, and there's no way to filter out specific goals directly in the GA function call.
• ga:goal2-14Completions isn't a valid Google Analytics metric—each goal has its own unique metric (e.g., ga:goal2Completions, ga:goal3Completions), so range syntax like this won't work.
• Using & between metrics (like ga:goal2Completions & ga:goal3Completions) is for combining metrics in the same request, but it doesn't sum them—it just returns each value separately, and you can't use that syntax to add them up directly.

Solution 1: Sum Individual Goal Metrics #

The simplest way is to pull each target goal's completion count separately, then add them together. Here's how:

=GA("ga:YOUR_PROPERTY_ID", "ga:goal2Completions", "YYYY-MM-DD", "YYYY-MM-DD") + 
 GA("ga:YOUR_PROPERTY_ID", "ga:goal3Completions", "YYYY-MM-DD", "YYYY-MM-DD") + 
 GA("ga:YOUR_PROPERTY_ID", "ga:goal4Completions", "YYYY-MM-DD", "YYYY-MM-DD") + 
 // Add the remaining 5 goal metrics here (e.g., ga:goal7Completions, ga:goal8Completions, etc.)

Replace YOUR_PROPERTY_ID with your actual Google Analytics property ID (looks like ga:12345678), and adjust the start/end dates to match your reporting month.

Solution 2: Use QUERY to Filter and Sum Target Goals #

If you prefer a more scalable approach (especially if you might adjust the list of goals later), pull all goal completion data first, then use Google Sheets' QUERY function to filter and sum only the goals you care about:

1. First, pull the goal IDs and their corresponding completion counts:

=GA("ga:YOUR_PROPERTY_ID", "ga:goalID,ga:goalCompletions", "YYYY-MM-DD", "YYYY-MM-DD", "dimensions=ga:goalID")

This will return a 2-column table: Column 1 is the goal ID (e.g., 2, 3, 4), Column 2 is the completion count for that goal.

2. Wrap that in a QUERY function to sum only your target goals:

=QUERY(
  GA("ga:YOUR_PROPERTY_ID", "ga:goalID,ga:goalCompletions", "YYYY-MM-DD", "YYYY-MM-DD", "dimensions=ga:goalID"),
  "SELECT SUM(Col2) WHERE Col1 IN ('2','3','4','7','8','10','11','12') LABEL SUM(Col2) 'Total Target Completions'",
  1
)

Update the IN ('2','3',...) section with the 8 goal IDs you need to include. The 1 at the end tells QUERY that the first row is a header.

Quick Notes #

• Make sure your Google Analytics add-on is properly authorized and has access to the property you're querying.
• Double-check that the goal IDs match exactly (e.g., if you're tracking Goal 2, the metric is ga:goal2Completions and the goalID dimension will return 2).

内容的提问来源于stack exchange，提问作者Denneappeltje