Retain Data API

api.ts

interface Request {
  queries: Array<Query>;
  filters?: Filter;
}

interface Response extends Array<Result> {}

interface Result {
  id: string;
  results: Array<number>;
  dimensions?: Array<Result>;
}

interface Query {
  id: string;
  calculate: Array<Aggregate>;
  across?: Array<Dimension>;
  filter?: Filter;
}

type Aggregate =
  | { aggregate: string }
  | { aggregate: 'average'; measure: string }
  | { aggregate: 'turnoverRate'; interval: string };

type Dimension =
  | { dimension: string }
  | { dimension: 'category'; name: string; sort?: 'ascending' | 'descending'; take?: number }
  | { dimension: 'bin'; measure: string; bins: Array<[number | null, number | null]> };

type Filter = FilterByMeasure | FilterByCategory | { and: Array<Filter> } | { or: Array<Filter> };
type MeasureOperators = 'eq' | 'gt' | 'gte' | 'lt' | 'lte' | 'ne';
type CategoryOperators = 'eq' | 'ne';
type FilterByMeasure = { by: 'measure'; measure: string; operator: MeasureOperators; value: number };
type FilterByCategory = { by: 'category'; category: string; operator: CategoryOperators; value: string };

/*
 * measure -- continuous variable of a record.
 *    measures aren't necessarily a particular field but they must be derived from one record.
 *    e.g. `years` can be a measure derived from some combination of the `anniversaryDate` and
 *    `separationDate`. the important thing is the cardinality of it. every measure is derived from
 *    one record 
 * 
 *    1 record ==> 1 measure
 * 
 * category -- categorical variable of a record.
 *    categories are also not necessarily a particular field, but they again must be derived from
 *    one record. 
 * 
 *    1 record ==> 1 category
 *
 * dimension -- some derived categorical variable used to split an aggregate into many bins/buckets
 * 
 * aggregate -- an operation that can be applied to a measure to squash it to a single number
 * 
 *    many records ==> 1 aggregate
 */

// SUMMARY ROW
// headcount
const headcountRequest: Query[] = [
  {
    id: 'HEADCOUNT',
    calculate: [{ aggregate: 'employment' }],
  },
];

const headcountResponse: Result[] = [{ id: 'HEADCOUNT', results: [21959] }];

// new profiles
const newProfilesRequest: Query[] = [
  {
    id: 'NEW_PROFILES',
    calculate: [{ aggregate: 'newProfiles' }],
  },
  {
    id: 'NEW_PROFILES_RATE',
    calculate: [{ aggregate: 'newProfilesRate' }],
  },
];
const newProfilesResponse: Result[] = [
  { id: 'NEW_PROFILES', results: [469] },
  { id: 'NEW_PROFILES_RATE', results: [-0.49] },
];

// growth rate
const growthRateRequest: Query[] = [
  {
    id: 'GROWTH_RATE',
    calculate: [{ aggregate: 'growthRate' }],
  },
  {
    id: 'GROWTH_RATE_RATE',
    calculate: [{ aggregate: 'growthRateRate' }],
  },
];
const growthRateResponse: Result[] = [
  { id: 'GROWTH_RATE', results: [0.02] },
  { id: 'GROWTH_RATE_RATE', results: [-0.02] },
];

// overall turnover
const overallTurnoverRequest: Query[] = [
  {
    id: 'OVERALL_TURNOVER',
    calculate: [{ aggregate: 'newTurnover' }],
  },
  {
    id: 'OVERALL_TURNOVER_RATE',
    calculate: [{ aggregate: 'newTurnoverRate' }],
  },
];
const overallTurnoverResponse: Result[] = [
  { id: 'OVERALL_TURNOVER', results: [441] },
  { id: 'OVERALL_TURNOVER_RATE', results: [-0.07] },
];

// turnover rate
const thirtyDays = 30 * 24 * 60 * 60 * 1000;
const turnoverRateRequest = [
  {
    id: 'TURNOVER_RATE',
    calculate: [{ aggregate: 'turnoverRate', interval: thirtyDays }],
  },
  {
    id: 'TURNOVER_RATE_RATE',
    calculate: [{ aggregate: 'turnoverRateRate', interval: thirtyDays }],
  },
];
const turnoverRateResponse: Result[] = [
  { id: 'TURNOVER_RATE', results: [0.02] },
  { id: 'TURNOVER_RATE_RATE', results: [0] },
];

const summaryRowRequest = [
  ...headcountRequest,
  ...newProfilesRequest,
  ...growthRateRequest,
  ...overallTurnoverRequest,
  ...turnoverRateRequest,
];

// TREND GRAPH
// headcount trend
const headcountBins: any[] = [
  // [null, january2017],
  // [null, february2017],
  // etc.
  /* ... */
];
const headcountTrendRequest = [
  {
    id: 'HEADCOUNT_TREND',
    calculate: [{ aggregate: 'employment' }],
    across: [
      {
        dimension: 'bin',
        measure: 'anniversaryDate',
        bins: headcountBins,
      },
    ],
  },
  {
    id: 'HEADCOUNT_TREND_PROFILES',
    calculate: [{ aggregate: 'profiles' }],
    across: [
      {
        dimension: 'bin',
        measure: 'createAt',
        bins: [
          /* ... */
        ],
      },
    ],
  },
  {
    id: 'HEADCOUNT_TREND_SEPARATIONS',
    calculate: [{ aggregate: 'turnover' }],
    across: [
      {
        dimension: 'bin',
        measure: 'separationDate',
        bins: [
          /* ... */
        ],
      },
    ],
  },
];
const headcountTrendResponse: Result[] = [
  {
    id: 'HEADCOUNT_TREND',
    results: [21959],
    dimensions: [
      { id: 'July 2017', results: [23000] },
      { id: 'August 2017', results: [22196] },
      { id: 'September 2017', results: [22242] },
      // etc
    ],
  },
  {
    id: 'HEADCOUNT_TREND_PROFILES',
    results: [469],
    dimensions: [
      { id: 'July 2017', results: [459] },
      { id: 'August 2017', results: [450] },
      { id: 'September 2017', results: [300] },
      // etc
    ],
  },
  {
    id: 'HEADCOUNT_TREND_SEPARATIONS',
    results: [441],
    dimensions: [
      { id: 'July 2017', results: [400] },
      { id: 'August 2017', results: [300] },
      { id: 'September 2017', results: [200] },
      // etc
    ],
  },
];

// turnover trend
const turnoverTrendRequest: Query[] = [
  {
    id: 'TURNOVER_TREND',
    calculate: [{ aggregate: 'turnover' }],
    across: [
      {
        dimension: 'bin',
        measure: 'separationDate',
        bins: [
          /* ... */
        ],
      },
      {
        dimension: 'category',
        name: 'terminationType',
      },
    ],
  },
];
const turnoverTrendResponse: Result[] = [
  {
    id: 'TURNOVER_TREND',
    results: [1220],
    dimensions: [
      {
        id: 'July 2017',
        results: [450],
        dimensions: [{ id: 'Voluntary', results: [200] }, { id: 'Involuntary', results: [300] }],
      },
      {
        id: 'August 2017',
        results: [450],
        dimensions: [{ id: 'Voluntary', results: [200] }, { id: 'Involuntary', results: [300] }],
      },
      // etc...
    ],
  },
];

// TENURE CHART
const averageTenureRequest: Query[] = [
  {
    id: 'TENURE_AVERAGE',
    calculate: [{ aggregate: 'average', measure: 'tenure' }],
  },
  {
    id: 'TENURE_BANDS',
    calculate: [{ aggregate: 'profiles' }],
    across: [
      {
        dimension: 'bin',
        measure: 'tenure',
        bins: [[null, 1.5], [1.5, 5], [5, 10], [10, null]],
      },
    ],
  },
];
const averageTenureResponse: Result[] = [
  { id: 'TENURE_AVERAGE', results: [4] },
  {
    id: 'TENURE_BANDS',
    results: [18795],
    dimensions: [
      {
        id: '0 - 1.5',
        results: [4861],
      },
      {
        id: '1.5 - 5',
        results: [7260],
      },
      {
        id: '5 - 10',
        results: [4838],
      },
      {
        id: '10+',
        results: [1836],
      },
    ],
  },
];

// ROLLING ANNUAL TURNOVER BY TENURE BAND %
const oneYear = 365 * 24 * 60 * 60 * 1000;
const lastYear = Date.now() - oneYear;
const annualTurnoverByTenureBand: Query[] = [
  {
    id: 'ANNUAL_TURNOVER_BY_TENURE_BAND',
    calculate: [{ aggregate: 'turnover' }],
    across: [
      {
        dimension: 'bin',
        measure: 'tenure',
        bins: [
          /* ... */
        ],
      },
    ],
    filter: {
      by: 'measure',
      measure: 'separationDate',
      operator: 'gt',
      value: lastYear,
    },
  },
];

// FIRST YEAR TURNOVER RATE BY BUSINESS UNIT %
const firstYearTurnoverRateByBusinessUnitRequest: Query[] = [
  {
    id: 'FIRST_YEAR_TURNOVER_RATE_BY_BUSINESS_UNIT',
    calculate: [{ aggregate: 'turnoverRate', interval: oneYear }],
    across: [
      {
        dimension: 'category',
        name: 'orgLevel1',
      },
    ],
  },
];
const firstYearTurnoverRateByBusinessUnitResponse: Result[] = [
  {
    id: 'FIRST_YEAR_TURNOVER_RATE_BY_BUSINESS_UNIT',
    results: [0.3],
    dimensions: [
      { id: 'Technology', results: [0.2] },
      { id: 'Marketing', results: [0.2] },
      { id: 'Sales', results: [0.2] },
      // etc
    ],
  },
];

// ORGANIZATION BREAKDOWN
const organizationBreakdownRequest: Query[] = [
  {
    id: 'ORGANIZATION_BREAKDOWN',
    calculate: [{ aggregate: 'employment' }],
    across: [
      {
        dimension: 'department',
      },
    ],
  },
];
const organizationBreakdownResponse: Result[] = [
  {
    id: 'ORGANIZATION_BREAKDOWN',
    results: [21954],
    dimensions: [
      { id: 'Quicken Loans', results: [18880] },
      { id: 'Amrock', results: [2457] },
      { id: 'Rock Connections', results: [2090] },
      // etc
    ],
  },
];

// SPAN OF CONTROL
const spanOfControlRequest: Query[] = [
  {
    id: 'SPAN_OF_CONTROL',
    calculate: [{ aggregate: 'spanOfControl' }],
    across: [{ dimension: 'category', name: 'orgLevel1', take: 6 }],
  },
];
const spanOfControlResponse: Result[] = [
  {
    id: 'SPAN_OF_CONTROL',
    results: [9],
    dimensions: [
      { id: 'Rock Ventures', results: [12] },
      { id: 'Amrock', results: [10] },
      { id: 'Rocket Fiber', results: [9] },
      // etc
    ],
  },
];

// HEADCOUNT BY LOCATION
const headcountByLocationRequest: Query[] = [
  {
    id: 'HEADCOUNT_BY_LOCATION',
    calculate: [{ aggregate: 'employment' }],
    across: [{ dimension: 'category', name: 'city', take: 7 }, { dimension: 'category', name: 'office', take: 7 }],
  },
];
const headcountByLocationResponse: Result[] = [
  {
    id: 'HEADCOUNT_BY_LOCATION',
    results: [21954],
    dimensions: [
      {
        id: 'Detroit',
        results: [17000],
        dimensions: [
          { id: 'Qube', results: [1200] },
          { id: 'One Campus Martius', results: [1300] },
          { id: 'First National Building', results: [1000] },
        ],
      },
      {
        id: 'Cleveland',
        results: [1000],
        dimensions: [],
      },
    ],
  },
];

// TEAM MEMBERS BY EDUCATION
const teamMembersByEducationRequest: Query[] = [
  {
    id: 'TEAM_MEMBERS_BY_EDUCATION',
    calculate: [{ aggregate: 'employment' }],
    across: [{ dimension: 'category', name: 'education', take: 7 }],
  },
];
const teamMembersByEducationResponse: Result[] = [
  {
    id: 'TEAM_MEMBERS_BY_EDUCATION',
    results: [21954],
    dimensions: [
      { id: 'Michigan State University', results: [608] },
      { id: 'University of Michigan', results: [608] },
      { id: 'Wayne State', results: [608] },
    ],
  },
];

// REMOTE VS ONSITE
const remoteVsOnsiteRequest: Query[] = [
  {
    id: 'REMOTE_VS_ONSITE',
    calculate: [{ aggregate: 'employment' }],
    across: [{ dimension: 'category', name: 'remote' }],
  },
];
const remoteVsOnsiteResponse: Result[] = [
  {
    id: 'REMOTE_VS_ONSITE',
    results: [21954],
    dimensions: [{ id: 'Remote', results: [10000] }, { id: 'Onsite', results: [10000] }],
  },
];