---
title: Customize NRQL data
source: https://docs.newrelic.com/docs/new-relic-solutions/build-nr-ui/build-ab-app/customize-nrql
---

> #### 💡 TIP
>
> This lesson is part of a course that teaches you how to build a New Relic application from the ground up. If you haven't already, check out the Overview.
>
> Each lesson in the course builds upon the last, so make sure you've completed the last lesson, Add NrqlQuery components to your nerdlet, before starting this one.

In this series, you're creating a New Relic application, or NR1 app for short, that shows A/B test data in a variety of charts. Most of the charts need to show data that comes from a demo service you spun up at the beginning of this series.

In the last lesson, you supplied real data, which comes from New Relic's database, to the following charts:

-   **Newsletter subscriptions per version**
-   **Version A - Page views**
-   **Version B - Page views**
-   **Total subscriptions per version**

Because you have experience querying subscription and page view data, you may feel ready to supply data to even more charts:

-   **Version A - Page views vs. subscriptions**
-   **Version B - Page views vs. subscriptions**

However, these charts are different than the charts you've supplied data to because they compare data from two different sources: `subscription` events and `pageView` events.

In this lesson, you learn how to format data from multiple sources to exist in the same chart.

## Experiment with data in the Data explorer [#experiment]

1.  From your [New Relic](https://one.newrelic.com) homepage's top navigation menu, go to to the **Data explorer**.
    Here, you can experiment with `subscription` event and `pageView` event queries to make sure you fetch the right data in your app.
2.  Select **subscription** from the **Custom events** menu.
    This queries NRDB for subscription event totals per minute over the last 30 minutes and shows that data in a chart.
3.  Click **Dimensions** to see a list of the attributes associated with these subscription events.
    You can filter and group subscription events using these dimensions. For **Version A - Page views vs. subscriptions** and **Version B - Page views vs. subscriptions**, you want to filter your subscription totals by `page_version`.
4.  Click the **NRQL** query to navigate to the Query builder.
    Here, you can view and manually edit the query to fetch the data you need.
5.  Add a `WHERE` clause to filter subscription totals by `page_version`:
    ````sql
    SELECT count(*) FROM subscription WHERE page_version = 'a' SINCE 30 MINUTES AGO TIMESERIES
    ```

    Remove the `TIMESERIES` clause to fetch a count:

    ```sql
    SELECT count(*) FROM subscription WHERE page_version = 'a' SINCE 30 MINUTES AGO
    ```

    Modify the `SINCE` clause to see totals over the past week:

    ```sql
    SELECT count(*) FROM subscription WHERE page_version = 'a' SINCE 7 DAYS AGO
    ```

    Click **Run** and see the data visualized in a billboard chart instead of a line chart.

    ````
6.  **Version A - Page views vs. subscriptions** and **Version B - Page views vs. subscriptions** need a combined total of four data values:
    -   All-time subscription totals for version A
    -   All-time page view totals for version A
    -   All-time subscription totals for version B
    -   All-time page view totals for version B

        Experiment with the Query builder to discover the four queries which pull the right data. In the end, you'll come up with the following four queries:

        ```sql
        SELECT count(*) FROM subscription WHERE page_version = 'a' SINCE 7 DAYS AGO
        SELECT count(*) FROM pageView WHERE page_version = 'a' SINCE 7 DAYS AGO
        SELECT count(*) FROM subscription WHERE page_version = 'b' SINCE 7 DAYS AGO
        SELECT count(*) FROM pageView WHERE page_version = 'b' SINCE 7 DAYS AGO
        ```

        > #### 💡 TECHNICAL DETAIL
        >
        > Unlike other structured query languages, NRQL doesn't provide a mechanism for joining data across sources. This is why you have to perform two queries to get `subscription` event totals and `pageView` event totals.

        Until now, you've provided every chart with a single query. Here, you have to provide two queries per chart. In the remaining steps, you'll learn how to customize `NrqlQuery` results to merge data from multiple sources.

## Merge NRQL from multiple sources [#merge]

1.  Change to the `customize-nrql-data/ab-test` directory of the [coursework repository](https://github.com/newrelic-experimental/nru-programmability-course):
    ````sh
    cd nru-programmability-course/customize-nrql-data/ab-test
    ```

    This directory contains the code that we expect your application to have at this point in the course. By navigating to the correct directory at the start of each lesson, you leave your custom code behind, thereby protecting yourself from carrying incorrect code from one lesson to the next.

    ````
2.  In your Nerdlet's `totals.js` file, implement `constructor()` and the React lifecycle method `componentDidMount()` in `VersionTotals`:
    ````jsx
    import React from "react";
    import { HeadingText, NrqlQuery, TableChart } from "nr1";

    export default class VersionTotals extends React.Component {
      constructor() {
        super(...arguments);

        this.state = {
          tableData: {
            metadata: {
              id: `totals-${this.props.version}`,
              name: `Version ${this.props.version}`,
              columns: ["name", "count"],
            },
            data: [
              {
                name: "Subscriptions",
                count: 0,
              },
              {
                name: "Page views",
                count: 0,
              },
            ],
          },
        };
      }

      componentDidMount() {
        NrqlQuery.query({
          accountIds: this.props.accountIds,
          query: `SELECT count(*) FROM subscription WHERE page_version = '${this.props.version}' SINCE 7 DAYS AGO`,
          formatType: NrqlQuery.FORMAT_TYPE.RAW,
        }).then(({ data }) => {
          if (data.raw) {
            let tableData = { ...this.state.tableData };
            tableData.data[0].count = data.raw.results[0].count;
            this.setState({ tableData });
          }
        });

        NrqlQuery.query({
          accountIds: this.props.accountIds,
          query: `SELECT count(*) FROM pageView WHERE page_version = '${this.props.version}' SINCE 7 DAYS AGO`,
          formatType: NrqlQuery.FORMAT_TYPE.RAW,
        }).then(({ data }) => {
          if (data.raw) {
            let tableData = { ...this.state.tableData };
            tableData.data[1].count = data.raw.results[0].count;
            this.setState({ tableData });
          }
        });
      }

      render() {
        return (
          <div>
            <HeadingText className="chartHeader">
              Version {this.props.version.toUpperCase()} - Page views vs.
              subscriptions
            </HeadingText>
            <TableChart data={[this.state.tableData]} fullWidth />
          </div>
        );
      }
    }
    ```

    <Callout variant="important">
      Make sure you replace `YOUR_ACCOUNT_ID` with your actual New Relic [account ID](/docs/accounts/accounts-billing/account-setup/account-id).
    </Callout>

    Here, you initialize your state in the component's constructor. The state has a single value, `tableData`, which holds series data and metadata for the `TableChart`. In [`componentDidMount()`](https://reactjs.org/docs/react-component.html#componentdidmount), you query the data that populates the data in this state.

    `componentDidMount()` is a React lifecycle method that is called when a component is mounted in the component tree. You use this method, instead of the constructor, to query data because your logic, which requests data from New Relic, introduces side effects and sets state values, neither of which you should do in the constructor.

    In `componentDidMount()`, you use `NrqlQuery` in a new way. First, you specified the `formatType` to `NrqlQuery.FORMAT_TYPE.RAW` because you're going to modify the data instead of supplying it directly to a chart Before, you used its JSX form in the `render()` method of a chart. Second, you called its `query()` method and handled the results in a `then()` callback, which logs them to the console.

    ````
3.  In your Nerdlet's `index.js` file, create a constant called `ACCOUNT_ID` and pass it to `VersionTotals`:
    ````jsx
    import React from "react";
    import { ChartGroup, Grid, GridItem } from "nr1";
    import EndTestSection from "./end-test";
    import NewsletterSignups from "./newsletter-signups";
    import PastTests from "./past-tests";
    import TotalCancellations from "./total-cancellations";
    import TotalSubscriptions from "./total-subscriptions";
    import VersionDescription from "./description";
    import VersionPageViews from "./page-views";
    import VersionTotals from "./totals";

    const ACCOUNT_ID = YOUR_ACCOUNT_ID
    const VERSION_A_DESCRIPTION = 'The newsletter signup message says, "Sign up for our newsletter"';
    const VERSION_B_DESCRIPTION = 'The newsletter signup message says, "Sign up for our newsletter and get a free shirt!"';

    export default class AbTestNerdletNerdlet extends React.Component {
      render() {
        return (
          <div>
            <Grid className="wrapper">
              <GridItem columnSpan={6}>
                <VersionDescription
                  description={VERSION_A_DESCRIPTION}
                  version="A"
                />
              </GridItem>
              <GridItem columnSpan={6}>
                <VersionDescription
                  description={VERSION_B_DESCRIPTION}
                  version="B"
                />
              </GridItem>
              <GridItem columnSpan={12}>
                <hr />
              </GridItem>
              <GridItem columnSpan={12}>
                <NewsletterSignups />
              </GridItem>
              <GridItem columnSpan={6}>
                <TotalSubscriptions />
              </GridItem>
              <GridItem columnSpan={6}>
                <TotalCancellations />
              </GridItem>
              <GridItem columnSpan={6}>
                <VersionTotals version="a" accountId={ACCOUNT_ID} />
              </GridItem>
              <GridItem columnSpan={6}>
                <VersionTotals version="b" accountId={ACCOUNT_ID} />
              </GridItem>
              <ChartGroup>
                <GridItem columnSpan={6}>
                  <VersionPageViews version="a" />
                </GridItem>
                <GridItem columnSpan={6}>
                  <VersionPageViews version="b" />
                </GridItem>
              </ChartGroup>
              <GridItem columnSpan={12}>
                <EndTestSection />
              </GridItem>
              <GridItem columnSpan={12}>
                <PastTests />
              </GridItem>
            </Grid>
          </div>
        );
      }
    }
    ```

    <Callout variant="important">
      Make sure you replace `YOUR_ACCOUNT_ID` with your actual New Relic [account ID](/docs/accounts/accounts-billing/account-setup/account-id).
    </Callout>

    ````
4.  Navigate to the root of your Nerdpack at `nru-programmability-course/customize-nrql-data/ab-test`.
5.  Generate a new UUID for your Nerdpack:
    ````sh
    nr1 nerdpack:uuid -gf
    ```

    Because you cloned the coursework repository that contained an existing Nerdpack, you need to generate your own unique identifier. This UUID maps your Nerdpack to your New Relic account.

    ````
6.  Serve your application locally:
    ````sh
    nr1 nerdpack:serve
    ```

    Navigate to your app in [New Relic](https://one.newrelic.com?nerdpacks=local).

    ````
7.  To view your logs, which are useful for uncovering errors in your application, turn on developer tools in your favorite browser and navigate to the **Console**:
    -   [FireFox instructions](https://developer.mozilla.org/en-US/docs/Tools/Web_Console)
    -   [Safari instructions](https://developer.apple.com/library/archive/documentation/NetworkingInternetWeb/Conceptual/Web_Inspector_Tutorial/DebuggingyourWebpage/DebuggingyourWebpage.html#//apple_ref/doc/uid/TP40017576-CH5-DontLinkElementID_24)
    -   [Chrome instructions](https://developers.google.com/web/tools/chrome-devtools#console)

        With your app on-screen, notice the NRQL data in the console.

        This console log tells you that you can find the data you're looking for, namely the `subscription` or `pageView` count, at `data.raw.results[0].count`. The next step is to store that count in `state.tableData`.

        > #### 💡 TIP
        >
        > If something doesn't work, use these debug tools to try to identify the problem.
        >
        > **Make sure you:**
        >
        > -   Copied the code correctly from the lesson
        > -   Generated a new UUID
        > -   Replaced all instances of `YOUR_ACCOUNT_ID` in your project with your actual New Relic [account ID](https://docs.newrelic.com/docs/accounts/accounts-billing/account-setup/account-id)
8.  In `totals.js`, store the query data in state:
    ````js
    import React from "react";
    import { HeadingText, NrqlQuery, TableChart } from "nr1";

    export default class VersionTotals extends React.Component {
      constructor() {
        super(...arguments);

        this.state = {
          tableData: {
            metadata: {
              id: `totals-${this.props.version}`,
              name: `Version ${this.props.version}`,
              columns: ["name", "count"],
            },
            data: [
              {
                name: "Subscriptions",
                count: 0,
              },
              {
                name: "Page views",
                count: 0,
              },
            ],
          },
        };
      }

      componentDidMount() {
        NrqlQuery.query({
          accountIds: this.props.accountIds,
          query: `SELECT count(*) FROM subscription WHERE page_version = '${this.props.version}' SINCE 7 DAYS AGO`,
          formatType: NrqlQuery.FORMAT_TYPE.RAW,
        }).then(({ data }) => {
          if (data.raw) {
            let tableData = { ...this.state.tableData };
            tableData.data[0].count = data.raw.results[0].count;
            this.setState({ tableData });
          }
        });

        NrqlQuery.query({
          accountIds: this.props.accountIds,
          query: `SELECT count(*) FROM pageView WHERE page_version = '${this.props.version}' SINCE 7 DAYS AGO`,
          formatType: NrqlQuery.FORMAT_TYPE.RAW,
        }).then(({ data }) => {
          if (data.raw) {
            let tableData = { ...this.state.tableData };
            tableData.data[1].count = data.raw.results[0].count;
            this.setState({ tableData });
          }
        });
      }

      render() {
        return (
          <div>
            <HeadingText className="chartHeader">
              Version {this.props.version.toUpperCase()} - Page views vs.
              subscriptions
            </HeadingText>
            <TableChart data={[this.state.tableData]} fullWidth />
          </div>
        );
      }
    }
    ```

    Pay close attention to the data you're accessing. The array indices are particularly important to get right. The subscription data should go to `state.tableData.data[0].count` because in `tableData`, it is the first element in the data array:

    ```js
    data: [
      {
        name: 'Subscriptions',
        count: 0
      },
      {
        name: 'Page views',
        count: 0
      },
    ],
    ```

    By similar logic, page view data should go to `state.tableData.data[1].count`.

    ````
9.  With your Nerdpack served locally, [view your application](https://one.newrelic.com?nerdpacks=local) to see your charts serving real data.
    > #### 💡 TIP
    >
    > If something doesn't work, use your browser's debug tools to try to identify the problem.
    >
    > **Make sure you:**
    >
    > -   Copied the code correctly from the lesson
    > -   Generated a new UUID
    > -   Replaced all instances of `YOUR_ACCOUNT_ID` in your project with your actual New Relic [account ID](https://docs.newrelic.com/docs/accounts/accounts-billing/account-structure/account-id/)

In this lesson, you learned how to use `NrqlQuery.query()` to fetch data from multiple sources and stitch them together into data your chart can use. Notice that there are still two charts in your NR1 application that use mock data:

-   **Total unsubscriptions per version**
-   **Past tests**

Unfortunately, your demo application doesn't create custom New Relic events when a user unsubscribes from your newsletter or you end an A/B test. In the next lesson, you'll learn how to use NerdGraph and NerdStorage to populate **Past tests**.

> #### 💡 COURSE
>
> This lesson is part of a course that teaches you how to build a New Relic application from the ground up. Continue on to the next lesson: Access NerdStorage from your nerdlet.