mixpanel-engage-query

Command-line tool to query the Mixpanel Engage API for People Data. With other words, export list of Mixpanel users with selected properties, optionally filtered by query on property values.

This script is especially powerful in combination with mixpanel-engage-post, that allow you to batch post additions/updates/deletion of people profiles, and jq (a command-line JSON processor).

Installation

Install Node.js.

Type npm install --global mixpanel-engage-query

That's it! Run it by typing engage in your terminal.

Setup

To run the script you must specify your Mixpanel API key and secret either as parameters, as environment variables MIXPANEL_API_KEY and MIXPANEL_API_SECRET, in a .env file located in the script's directory (typically useful if you check out the source from Github) or in a .engagerc file in your home directory.

Example of .env and ~/.engagerc file:

MIXPANEL_API_KEY=f49785f7a0yourkey2019c6ba15d71f5
MIXPANEL_API_SECRET=a69ca325ayoursecret4f5ed45cafb66

Example Usage

Get help

$ engage --help

Usage: engage -k [string] -s [string]

Options:
  -k, --key         Mixpanel API key                                    [string]
  -s, --secret      Mixpanel API secret                                 [string]
  -f, --format      Output format, json or csv        [string] [default: "json"]
  -t, --total       Only return total count of results
  -q, --query       A segmentation expression (see Mixpanel API doc)    [string]
  -p, --properties  Properties to output (e.g. '$email $first_name'). Outputs
                    all properties if none specified.
  -r, --required    Skip entries where the required properties are not set (e.g.
                    '$email $first_name').
  --na, --noarray   Output json as one object per row, instead of one array of
                    objects.
  -u, --url         Only return the URL of query without making the actual
                    request.
  -h, --help        Help                                               [boolean]

Examples:
  engage -q 'properties["$last_seen"] >     Query using expression
  "2015-04-24T23:00:00"'
  engage -p '$email $first_name'            Limit output to only given list of
                                            space delimited properties

Note that Mixpanel API key/secret may also be set using environment variables.
For more information, see https://github.com/stpe/mixpanel-engage-query

Get everything

$ engage

Example output:

[
  {
      "$browser": "Chrome",
      "$city": "Kathmandu",
      "$country_code": "NP",
      "$initial_referrer": "$direct",
      "$initial_referring_domain": "$direct",
      "$os": "Windows",
      "$timezone": "Asia/Katmandu",
      "id": "279267",
      "nickname": "bamigasectorone",
      "$last_seen": "2015-04-15T13:07:30",
      "$distinct_id": "15b9cba739b75-03c7e24a3-459c0418-101270-13d9bfa739ca6"
  }
]

Default behaviour is to output the result as an array of entries. Using the noarray flag will instead output one entry per row.

Note that $distinct_id is included as a property for convenience.

Get just the number of results

engage -t (assumes Mixpanel key/secret set as environment variables)

Example output:

1138

Only output specific fields

engage -p '$email $first_name'

Example output:

[
  { '$email': '[e-mail]', '$first_name': 'Joakim' },
  { '$email': '[e-mail]', '$first_name': 'Henrik' },
  { '$email': '[e-mail]', '$first_name': 'Jonas' }
]

Output as CSV instead of JSON

engage -f csv

Example output:

[e-mail];Joakim
[e-mail];Henrik
[e-mail];Jonas

Note: currently no special escaping or similar is implemented, so depending on values CSV may end up invalid.

Query using expression

This example returns people with $last_seen timestamp greater (later) than 24th of April (see the Mixpanel documentation for segmentation expressions).

engage -q 'properties["$last_seen"] > "2015-04-24T23:00:00"'

Relative date parsing

Often you need a query with a condition relative to today's date. In order to avoid having to generate the command-line parameters dynamically you can use a placeholder as [[DATE:<date string>]] which will be replaced by a correctly formatted date for the Mixpanel API. The <date string> may be formatted according to what Sugar Dates supports.

Examples:

engage -q 'properties["$last_seen"] > "[[DATE:yesterday]]"'

engage -q 'properties["$last_seen"] > "[[DATE:the beginning of last month]]"'