Tracking Sales Against Goals

[mykael (Michael Clarke)]

I'm looking for suggestions on how to handle this in Kibana.

I've got one index that contains sales goals. Each record has a state, a month and a slaes goal for that month. Say, In WA, in August, we want to sell 600 items.

Then I've got another aggregate index that contains actual sales by state and month. Say, In WA, in August, we sold 450 items.

What I want to do is to produce a couple of visualizations in Kiabana (don't care which technology), that clearly show how we're progressing against our sales goals.

Things like:

• Percentage of goal sales in WA that we achieved in August (75% in the above example)
• Shown as a metric or gauge?
• Percentage of goal sales achieved in year to date? (totalled by state)
• A heat map showing sales short fall or over achievement (goal - actual)
• A graph showing all states, with actual sales as a stacked bar and goal sales as stacked lines?

Can I use a transform on a complex index_pattern (sales_goals*,sales_actual*) to combine data from different indexes into a single one? (group by state and month, aggregate goal and actual?) To say produce a tracking index that has both the actual and goal sales for each state and month in it? That would at least let me used scripted fields to calculate the delta and percentage values.

Any other ideas on how to do this - that are, perhaps, a little more performance friendly.

Mik

[Hendrik_Muhs]

@mykael

Can I use a transform on a complex index_pattern (sales_goals*,sales_actual*) to combine data from different indexes into a single one?

Yes, you can combine indexes like this, but note that the fields your are grouping on must have the same name and be compatible for all indexes. Aggregations are more forgiving if your data is sparse, e.g. your goals index will not have the actual field and vice versa.

The transform should make your visualization queries quicker, because you basically pre-aggregate your data. The transform isn't for free, but assuming that you don't query the output only once, it should be faster than doing the same at runtime over and over.

An alternative to the scripted field: You can calculate the delta in an ingest pipeline attached to the transform destination (however I do not think this matters w.r.t. performance).

[mykael (Michael Clarke)]

Cool, I'll give it a go. I've already got the group by fields lined up - I've got a dashboard that shows actuals and goals in the same visualization, using the same filters. Just need to hang a scripted transformation off the back of it...

[mykael (Michael Clarke)]

Ah, ha. That's a bit sneaky.

I've got two indecies I want to go into this transfrom - sales_actual-yyyy.mm and sales_plan-yyyy.

Now, before I did this I create a dashboard that showed data from both of them, and to do this I created an index pattern called "sales_actual*,sales_plan*" - which let me build visualizations in kibana drawing data from both of them.

So when I tried to set up the transform I used that index pattern as well:

  "source" : {
    "index" : [
      "sales_actual*,sales_plan*"
    ]
  },

...and it didn't work. Everything loaded fine, but the transform got the message about it's indexes having been deleted after a few minutes.

A bit of messing around revealed that you have to use a different syntax to get it to work:

  "source" : {
    "index" : [
      "sales_actual-*","sales_plan-*"
    ]
  },

The combined index pattern Kibana needed for visualization just didn't want to work and you have to specify two 'single index' index patterns.

Now to work out why the planned value is coming up as zero...