Skip to main content

Metabase Dashboard for Outbound Campaigns

Metabase is used to analyze data in a much simpler way without using SQL. We maintain the following data for the companies:

  1. Number of ABC/OC/OS sent per day
  2. Orders placed via ABC
  3. Revenue generated via ABC
  4. ABC conversion %

How to create company-specific dashboards​

  1. Login into metabase.limechat.ai.
  2. Go to Browse all items under our analytics section.
  3. Click on New collection.
  4. Give a Name to the collection — Company_name Outbound Numbers.
  5. Select the + sign on top of the page and click on New dashboard.
  6. Name the dashboard — Company_name dashboard.

How to create Metabase dashboards for outbound campaigns​

  1. Log into metabase.limechat.ai.
  2. Click on Limekit under Our Data tab.

Abandoned Checkout​

1. Number of ABC sent​

  1. Click on Notifications Sent Notifications.

  2. Start adding different Filters

    1. Select the Account ID filter and enter the account id
    2. Select the Flow Id filter = 0.
    3. Select the Last Notified At filter and put the date range.

  3. Go to Summarize option

    1. Summarize by Number of distinct value of checkout URL.
    2. Next, Group by last notified (make sure last notified is selected by day).

  4. Go to the Visualization tab (bottom left of the page).

    1. Choose the Line visualization.
    2. Click on display and move the toggle of Show value on data points.
    3. Go to the axes tab and set the X-axis scale to ordinal using drop-down option.

  5. Save the file with the relevant name.

  6. Add it to company-specific dashboards

    Image

2. Orders placed via ABC​

  1. Click on Store order model.

  2. Start adding different Filters.

    1. Select the Account id filter and enter the account id.
    2. Select the Created at filter and put the date range.
    3. Select the Tag filter = ABC (or anything).

  3. Go to Summarize option.

    1. Summarize by Number of distinct value of order ID.
    2. Next, Group by created at (make sure last notified is selected by day).

  4. Click on Show editor (next to the Summarize tab).

  5. Click on View the SQL (the last tab) and click on convert this question to SQL.

  6. Remove - "public"."store_ordermodel"."tags" = 'abc') and add the following as per requirement:

    1. ABC conversion - "public"."store_ordermodel"."tags" @> (ARRAY['LC_ABC_ORDER'::character varying]))
    2. Reorder conversion - "public"."store_ordermodel"."tags" @> (ARRAY['LC_REORDER_ORDER'::character varying]))
    3. Upsell conversion - "public"."store_ordermodel"."tags" @> (ARRAY['LC_UPSELL_ORDER'::character varying]))

  7. Go to the Visualization tab (bottom left of the page).

    1. Choose the Line visualization.
    2. Click on display and move the toggle of Show value on data points.
    3. Go to axes tab and set the X-axis scale to ordinal using drop-down option

  8. Save the file with the relevant name.

  9. Add it to company-specific dashboards.

    Image
    Image
    Image

3. Revenue generated through campaigns​

  1. Repeat all the steps as same as above (order placed).
  2. Just make one change while summarizing
    1. Summarize by Sum of subtotal price.

4. ABC conversion​

  1. Go to Ask a question on top of the page.

    Image

  2. Click on Custom question.

  3. Go to Saved Questions.

    Image

  4. Search the collection/dashboard where you have saved the Abandoned checkout sent graph.

    Image

  5. Next, we will join the Orders table.

    Image
    Image

  6. Click on the back icon. Go to saved questions. Search for the collection and select the orders graph.

    Image

  7. Click on View the SQL and click on Convert this question to SQL.

  8. Now we need to modify the query in the 1st line after “count_2”.

    Image

  9. Add the line as mentioned in the below pic.

    Image

  10. Run the query and save the file with the relevant name.

Number of OC/OS sent​

  1. Click on Notifications Sent Notifications.

  2. Start adding different Filters.

    1. Select the Account Id filter and enter the account id.
    2. Select the Flow id filter = 1 (OC) / 2 (OS).
    3. Select the Last notified filter and put the date range.

  3. Go to summarize option.

    1. Summarize by Number of distinct value of order ID.
    2. Next, GROUP BY last notified (make sure last notified is selected by day).

  4. Go to the Visualization tab (bottom left of the page).

    1. Choose the Line visualization.
    2. Click on display and move the toggle of Show value on data points.
    3. Go to axes tab and set the X-axis scale to ordinal using drop-down option.

  5. Save the file with the relevant name.

    Image