getGaKeywordCount

Gets the number of keywords driving traffic to a profile between two dates.

* May be inconsistant with the website

getGaMetric

Gets analytics data from Google Analytics to create custom reports for multiple clients and profiles.

Function

=getGaKeywordCount(profileId, startDate, endDate)

Details

This function gets the number of keywords that are driving traffic to a specific profile between two dates. It works by collecting all the keywords that drove traffic between the start date and end date and returning the total number of keywords.

Inconsistancy: Google doesn't provide a keyword count in the API so this count may not match up to the website.
Limitation: Does not work on high traffic accounts.

Parameters

Name Type Description
profileId Number Profile ID from the Profile ID sheet
startDate Date Starting date
endDate Date Ending date

Cells:
  A1: 12345678
  A2: 11/01/2011
  A3: 11/02/2011
=getGaKeywordCount(A1, A2, A3)

Function

=getGaMetric(profileId, metric, startDate, endDate, optFilter, optDimensions, optSegment, optSort, optIncludeHeaders, optMax, optEnableSleep)

Details

Gets analytics data by metrics and dimensions within a date range.

It will return an error if the date is in the future or if the start date is after the begining date.


Tip: A complete list of metrics and dimensions is available on the Google Analytics API website.

Parameters

Name Type Description
profileId Number The ID number for the profile
metric String Google Analytics metric you want to get data for.
Note You must add ga: if you're using multiple metrics in on the same function. It's not required if you only use one metric.
Common metrics
ga:visits
Total number of visitors to your website for the requested time period.
ga:bounces
The total number of single-page (or event) sessions to your website.
ga:goal(n)completions
The total number of completions for the requested goal number.
ga:totalEvents
The total number of events for the profile, across all categories
ga:pageLoadTime
Total Page Load Time is the amount of time (in milliseconds).
ga:transactions
The total number of transactions.
startDate Date Starting date
endDate Date Ending date
Limitation Long date ranges may timeout with high traffic profiles. Try a shorter date range.
Warning Long date ranges may result in sampled numbers.
optFilter Filter (Optional) Filter data from your results.
Available operators
  • == Equals
  • != Does not equal
  • Greater than
  • Less than
  • >= Greater than or equal to
  • <= Less than or equal to
  • ; And
  • , Or
See at the examples below or go to the filters section on the Google Analytics' API website for more details.
optDimensions String (Optional) Dimentions.
Available operators
  • == Exact match
  • != Does not match
  • =@ Contains substring
  • !@ Does not contain substring
  • =~ Matches regular expression
  • !~ Does not match regular expression
Common dimensions
ga:visitorType
A boolean indicating if a visitor is new or returning. Possible values: New Visitor, Returning Visitor..
ga:referralPath
The path of the referring URL.
ga:source
The domain of the referring URL, or "google" with AdWords autotagging.
ga:medium
May be "organic", "referral" or "ppc" if used with AdWords autotagging.
ga:keyword
The keyword used to reach the website.
ga:city
The cities of website visitors.
ga:region
The state of website visitors.
See dimensions on the Google Analytics' API website for more details.
optSegment Number (Optional) Segment ID
optSort String (Optional) Sort
Known Bug Sorting doesn't work. You have to sort the data yourself.
optIncludeHeaders Boolean (Optional) Include headers (true or false)
optMax Number (Optional) Maximum rows
optEnableSleep Number (Optional) Slow down API calls (default is true)

The basics:
Number of visitors between Nov 11, 2011 and Nov 20, 2011

Cells:
  A1: 12345678
  A2: visits
  A3: 11/01/2011
  A4: 11/20/2011
=getGaMetric(A1, A2, A3, A4)

One filter:
Number of organic visitors between Nov 11, 2011 and Nov 20, 2011.

Cells:
  A1: 12345678
  A2: visits
  A3: 11/01/2011
  A4: 11/02/2011
  A5: ga:medium==organic
=getGaMetric(A1, A2, A3, A4, A5)

Filter A (and) Filter B:
Get the number of visitors between Nov 11, 2011 and Nov 20, 2011 that is both organic traffic and came in using the keyword "seo company"

Cells:
  A1: 12345678
  A2: visits
  A3: 11/01/2011
  A4: 11/02/2011
  A5: ga:medium==organic;ga:keyword==seo company
=getGaMetric(A1, A2, A3, A4, A5)

Filter A (or) Filter B:
Get the number of visitors between Nov 11, 2011 and Nov 20, 2011 that came in using the keyword "seo company" or "philadelphia seo"

Cells:
  A1: 12345678
  A2: visits
  A3: 11/01/2011
  A4: 11/02/2011
  A5: ga:keyword==seo company,ga:keyword==philadelphia seo
=getGaMetric(A1, A2, A3, A4, A5)

Visits by month (dimensions)
Get visits in the date range broken down by month.

Cells:
  A1: 12345678
  A2: visits
  A3: 01/01/2011
  A4: 06/30/2011
  A5: ga:month
=getGaMetric(A1, A2, A3, A4, , A5, , , true)
// Will return 6 rows, one per month, January to June.

Visits by week (dimensions)
Get visits in the date range broken down by week.

Cells:
  A1: 12345678
  A2: visits
  A3: 01/01/2011
  A4: 12/31/2011
  A5: ga:week
=getGaMetric(A1, A2, A3, A4, , A5, , , true)
// Will return 52 rows numbered 1 to 52 for each week in 2011

// Tip: If you want to convert the week number to a date, add this:
  D1: =getMonday(A3)
  D2: =D1+7
  D3: =D2+7
  D4: =D3+7
  D5: .... and so on.

Visits by city (dimensions)
Get visits in the date range broken down by city.

Cells:
  A1: 12345678
  A2: visits
  A3: 01/01/2011
  A4: 06/30/2011
  A5: ga:city
=getGaMetric(A1, A2, A3, A4, , A5, , , true)

Many metrics & many dimensions combo:
Creates two columns visits and transactions, broken down into rows by month and year

Cells:
  A1: 12345678
  A2: ga:visits,ga:transactions 
  A3: 01/01/2011
  A4: 12/30/2012
  A5: ga:month,ga:year
=getGaMetric(A1, A2, A3, A4, , A5, , , true)

Referring links:
Get referral URLs that came to the site between two dates.

Note: Visits is added because a metric is required. Google Docs will create three columns: the domain, the path, and the number of visits.

Cells:
  A1: 12345678
  A2: visits 
  A3: 01/01/2011
  A4: 03/30/2011
  A5: ga:source,ga:referralPath
=getGaMetric(A1, A2, A3, A4, , A5, , , true)

Crazy town:
Get columns for visits and bounces that came from organic traffic, using the keyword "seo company". Break it out by month, and the visitor's city, for the first quarter of 2011. Oh, and also include column headers. Zing!

Cells:
  A1: 12345678
  A2: ga:visits,ga:bounces 
  A3: 01/01/2011
  A4: 03/30/2011
  A5: ga:medium==organic;ga:keyword=seo company
  A6: ga:month,ga:city
=getGaMetric(A1, A2, A3, A4, A5, A6, , , true)