Hi,

I would like to get pro month and marketing campaign the values of about 12 KPIs. This dashboard will be used by different countries/marks.
My KPIS do not have the same entries. For example :
- the customer satisfaction is calculated on the number of customers "satisfied", "unsatisfied", "acceptable",
- the percentage of corrected and on-time reports is based on the number of reports "total", "late", "with failures", "late and with failures",
- the availability of the is based on the number of hours... etc.

Do you have an idea of the way to proceed?

I created the following tables :
-Tbcampaign (ID_Campaign, Campaign name)
-TbKPI (ID_KPI, KPI Name)
-TbRegion (ID_Region, Region Name)
-TbTime (ID_Time, Month, Year)
-TbFact (ID_KPI, ID_Campaign, ID_Region, ID_Time, Value)

... but how can I proceed with the KPIs?
Thanks a lot in advance!
Co.

Does anyone know what KPIs are and how countries/marks are related?

Does anyone know what KPIs are and how countries/marks are related?

Hi Moniker,

Sorry, KPI = Key Performance Indicators. For example, Customer satisfaction with the user help desk (%age), average delay for responding a complaint, etc.

In this case, countries = markts.

Thanks!

Are you just trying to calculate a number to give to management on a regular basis?

Are you just trying to calculate a number to give to management on a regular basis?

Yes, several numbers, to be given each month.

This is your original description of your tables:

-Tbcampaign (ID_Campaign, Campaign name)
-TbKPI (ID_KPI, KPI Name)
-TbRegion (ID_Region, Region Name)
-TbTime (ID_Time, Month, Year)
-TbFact (ID_KPI, ID_Campaign, ID_Region, ID_Time, Value)

Clearly, you're going to have to use TbFact as it's the only one with all the keys, but there's not enough information about what a "fact" is in this situation. You haven't really provided much info here, but in general terms, regardless of how different each market may be, you're going to have to make up some metrics with different variables that all equate to each other and add up to the same thing.

What is it you've already tried? Where are you stuck? I can come up with examples here, but not without more information. I'm not understanding how you plan on coming up with performance indicators when there's not an apples-to-apples comparison. I may be taller and thinner than the majority of people, but that doesn't mean squat if you're talking about hair length. (My GF wins that one, but she's short. ;) )

What you need to do here is get even-ish numbers for each attribute and then it's simple math from there. The problem is that, without the same attributes associated with each location, you're going to (intentionally or not) tick some people off. Your definition of timeliness in Canada may or may not measure up with what is considered a failure in India, and so on. What can you find in common with the various regions?

Hi Moniker,

Thanks again (and in advance!). It will be ok to define the same criteria for the same regions, so let's assume that they are all the same.
My problem is to get the indicators.
Here are some of them :
1. Customer satisfaction with the user help desk (employees will give, pro campaign and pro month, the number of customer "satisfied", "unsatisfied" and "without opinion"
2. The average time for complaints to be dealt with (in days)
3. The difference between planned budget and effective budget
etc.

My problem is that the different indicators depend on different type of values : how can I organise that in my tables?
Thanks a lot in advance!!
Co.

How are they different values? What am I not getting?

As best as I can tell from the description, you have a percentage, a number, and a number.

1) Satisfied, Not Satisfied, No Opinion -- a percentage of each out of the total.
2) Number of days
3) Difference between budget and actual dollars

Store each of these, take an average, and then scale it.

I don't think I'm getting this right though. I'm not sure what the problem is as the solution seems self-evident.

Sorry, I am just starting with Acces...
So, my problem is : in which tables do the employees enter their data?

For some KPI, there will be 3 felds (example 1 : number of satisfied, not satisfied, no opinion), and the data for the different KPI could be a number of people, a number of hours, etc.

And then, where and how do I calculate the KPIs? With requests?
Thanks!

There are not three fields. There are three values that are stored in one field. For example, you store the field "CustomerResponse" like this:

No Opinion = 0
Not Satisfied = 1
Satisfied = 2

Then, you end up with one field that looks like this:

CustomerResponse
1
2
2
0
1
2
0

That is attached to a table that has a "ticket number" or whatever you refer to them as. The ultimate goal is to get to this:

TicketID CustomerResponse
1 1
2 2
3 2
4 0
5 1
6 2
7 0

You then use the same ticket ID to associate budgets, times, etc.

This is a part of normalization. You do not want to store the words "Satisfied" and "Not Satisfied" multiple times as that's a waste of space. You want to store values that represent them. It's like a multiple choice quiz. You select A, B, C, or D, not the entire answer.

Thanks!
And what if the employees directly enter the final results, each month?
For example :
Customer satisfaction => x "satisfied", y "unsatisfied", z "no opinon"

Thanks in advance,
Co.

There are not three fields. There are three values that are stored in one field. For example, you store the field "CustomerResponse" like this:

No Opinion = 0
Not Satisfied = 1
Satisfied = 2

Then, you end up with one field that looks like this:

CustomerResponse
1
2
2
0
1
2
0

That is attached to a table that has a "ticket number" or whatever you refer to them as. The ultimate goal is to get to this:

TicketID CustomerResponse
1 1
2 2
3 2
4 0
5 1
6 2
7 0

You then use the same ticket ID to associate budgets, times, etc.

This is a part of normalization. You do not want to store the words "Satisfied" and "Not Satisfied" multiple times as that's a waste of space. You want to store values that represent them. It's like a multiple choice quiz. You select A, B, C, or D, not the entire answer.