select * from at_sales_persons where ROLE = 'RET';
select * from at_sales_person_tree;
select * from at_outlet_accum_data;
select * from at_fse_accum_data;
select * from at_ceo_accum_data;
SELECT * FROM at_sales_persons WHERE ID IN (SELECT ID FROM
at_sales_person_tree WHERE ROLE = 'FSE');
TO FIND OUT MANAGER ID:-
select b.MANAGER_ID, [Link] FROM at_sales_persons as a INNER join
at_sales_person_tree AS b on [Link] = [Link] where [Link]='550299';
Find manager id
select MANAGER_ID from at_sales_person_tree where ID IN (select ID from
at_sales_persons where ID = '550299');
To find circle_id:-
select b.circle_id, [Link] FROM at_sales_persons as a INNER join at_sales_person_tree
AS b on [Link] = [Link] where [Link]='550299';
select ID, MANAGER_ID from at_sales_person_tree where MANAGER_ID = '326870';
select * from at_ceo_accum_data where id=488670 and dt='2014-12-01' and
kpi='MNPGA';
select * from at_zbm_accum_data where kpi='VANPROD' and dt = '2014-09-01' and
id in (488673,
613151,647309,647885,648551)
select PERSON_ID from at_sales_person_tree where START_DATE<= '2014-12-01'
and END_DATE > '2014-12-01' and manager_id in (488685)
select distinct sum([Link]) TMR , tree2.manager_id TMID from
at_sales_person_tree tree2,at_sales_persons asp2 ,(select distinct sum([Link])
DISR,tree1.manager_id DISD from at_sales_person_tree tree1 ,at_sales_persons
asp1, ( select distinct tree.manager_id FSE ,count(distinct(tree.person_id)) RET from
at_outlet_accum_data data ,at_sales_person_tree tree where
[Link]=tree.person_id and [Link]='VTSGA' and [Link]='2014-09-01' and
data.D30 > 0 and tree.circle_id=21 and date_format(tree.start_date,'%Y-%m-%d')
<='2014-09-30' and tree.end_date >'2014-09-30' group by tree.manager_id)FSEE
where [Link]=tree1.manager_id and [Link]='DIST' and
tree1.person_id=[Link] and date_format(tree1.start_date,'%Y-%m-%d') <='2014-
09-30' and tree1.end_date >'2014-09-30' group by tree1.manager_id )DIST1 where
tree2.manager_id=[Link] and [Link]='TM' and tree2.person_id = [Link]
and date_format(tree2.start_date,'%Y-%m-%d') <='2014-09-30' and tree2.end_date
>'2014-09-30' group by tree2.manager_id;
select PERSON_ID from at_sales_person_tree where manager_id =488673 and
END_DATE>= '2014-12-01';
select PERSON_ID from at_sales_person_tree where manager_id in (488676,
488680,488678) and END_DATE>= '2014-12-01';
select PERSON_ID from at_sales_person_tree where manager_id in (663839,
663838,
50425,647320,647315,644777,638225,636532,621226,614975,605973,605972,60
5969,601514,
601512,598452,488736,488718,488705,488699,488697,488696,488688) and
END_DATE>= '2014-12-01';
select * from at_outlet_accum_data where kpi='VTCS' and dt='2014-09-01';
select * from at_sales_persons where id=488979;
select * from at_sales_persons where role='CEO';
select * from at_outlet_accum_data where id=659550 and kpi='VANPROD' and
dt='2014-09-01';
select * from at_ceo_accum_data where id=488670 and kpi='ACTVAN' and
dt='2014-09-01';
select * from at_fse_accum_data where id=601518 and kpi='VTSGA';
select * from at_outlet_accum_data WHERE kpi='VTSGA' AND ID=659355;-- 659355
select * from at_sales_person_tree where person_id=659355;
select PERSON_ID from at_sales_person_tree where manager_id=489242;
select * from at_sales_person_tree where manager_id=488676 and END_DATE LIKE
'%3999%';
select * from at_outlet_accum_data where id in (select PERSON_ID from
at_sales_person_tree where manager_id=489242) and kpi='VTSGA';
select * from at_service_retailer_number_tracker where circle_master_Id=108;
select * from at_ceo_accum_data where kpi='ACTVAN';
select * from at_sales_person_tree where person_id in(
select ID from at_sales_persons where mobile_no in(select VAN_RETAILER_No from
at_service_retailer_number_tracker where circle_master_Id=108));
select * from at_sales_person_active_dates where id='489181';
SELECT * FROM airtel_poc_auto_4.at_service_retailer_number_tracker where
circle_master_id=108;
select * from at_sales_persons where MOBILE_NO='8295587343';
select * from at_ceo_accum_data where kpi='ACTVAN';
select * from at_outlet_accum_data where id=659355 and kpi='VTSGA';
select * from at_sales_person_active_dates where id=659355;
select * from at_ceo_accum_data where id=488670 and kpi='VTSGA';
select * from at_sales_person_tree where person_id=488673;
SELECT * from at_sales_persons where id=488676;
select * from at_zbm_accum_data where id in (
Select PERSON_ID from at_sales_person_tree where manager_id=488670 And
End_Date>='2014-12-01' ) and kpi='VANPROD' and dt='2014-12-01';
Select person_id from at_sales_person_tree where manager_id=636532 AND
END_DATE LIKE('%3999%');
Select * from at_dis_accum_data where id=600745 and kpi='ACTVAN';
Select * from at_zsm_accum_data where id=488678 and kpi='VTSGA';
select * from at_zbm_accum_data where id in (Select PERSON_ID from
at_sales_person_tree where manager_id=488670 AND END_DATE LIKE('%3999%'))
and kpi='VTSGA' and dt='2014-11-01';
select * from at_sales_person_tree where person_id in ( select manager_id from
at_sales_person_tree where person_id in (489044,650440,655750,640375) and
end_date >= '2014-11-01') and end_date >= '2014-11-01';
select * from at_sales_persons where id=636532;
select * from at_sales_persons where ID=644774;
select * from at_sales_person_tree where person_id=488676;
select * from at_ceo_accum_data where KPI='VTSGA' and Id=488670;
select * from at_outlet_accum_data where KPI='VTSGA' and Id =488670;
select count(van.Circle_master_id) into vAV from at_service_retailer_number_tracker
van ,at_circle_mapping at
where at.circle_id = 21 and at.circle_master_id=van.Circle_master_id and
service='VAN' ;
select * from at_outlet_accum_data where kpi='VTSGA' AND DT='2014-12-01'
Select * from at_zbm_accum_data where id=647885 and kpi='VANPROD';
Select * from at_zbm_accum_data where id=647885 and kpi='Actvan';
Select * from at_zbm_accum_data where id=647885 and kpi='VTSGA';
Select * from at_sales_person_tree where person_id=647885;
select * from at_service_retailer_number_tracker where circle_master_id = 108 and
van_retailer_no in (
select mobile_no from at_sales_persons where id in (
select person_id from at_sales_person_tree where MANAGER_ID in (
select person_id from at_sales_person_tree where MANAGER_ID in (
select person_id from at_sales_person_tree where MANAGER_ID in (
select person_id from at_sales_person_tree where MANAGER_ID in (
select person_id from at_sales_person_tree where MANAGER_ID = 647885 and
end_date >= '2014-12-01') and end_date >= '2014-12-01') and end_date >=
'2014-12-01' ) and end_date >= '2014-12-01') and end_date >= '2014-12-01' ) );
select id from at_sales_persons where MOBILE_NO
in(8295587344,8295587346,8295587356);
Select * from at_outlet_accum_data where id in(659558,659555,659550) and
kpi='VTSGA' and dt='2014-12-01';
Select * from at_zbm_accum_data where id=647885 and kpi='VTSGA';
select * from at_fse_accum_data where id in (Select PERSON_ID from
at_sales_person_tree where manager_id=488670 AND END_DATE LIKE('%3999%'))
and kpi='VTSGA' and dt='2014-11-01';
select * from at_dist_accum_data where id=221286;
select PERSON_ID from at_sales_person_tree where MANAGER_ID='221030';
select * from at_active_retailers_dump where circle='MP' and RETAILER_MSISDN in
(9755609808,
9893745736,9752014069,9993558816,9893936477,9993739909,9630682791,998
1752999,7869286966,9993558759,9755609747,9993559293,7869266943,786926
6942,9993559265,9981085300,9993724683,9893504255,9981752744,999372457
3,7869300696,9752479253,9752479250,9752479255,9752852409,9179160137,99
93559452,9993732121,7869277382,9981145284,9981082815,9893116356,97524
08358,9981930688,9752408316,9752407570,9893168006,9981700828,98935127
37,7389190907,7869025660,7389137951,8827473864,8827461193,8827471126,9
630597357,9630596817,9630597385,9630593130,8827749153,8827749073,7024
982865,7024982831,9752680084,9752675127,9752679321,7024476920,7024477
168,7024475710,7024475726,7024475807,7024475796,7869878517,7869879383)
;
select * from at_airtel_prepaid_money_circle_mapping;
select * from at_sales_persons where mobile_no='9993559265';
select MOBILE_NO from at_sales_persons where id
in(281981,280982,279973,285576,289547,292887,
266104 ,278424,278160,312557,313107,809217,312035,314860,314452,3189
37,317498,319980,
323459,
322737,324199,326855,325875,299967,300500,301038,307052,333858,332033,3
37673, 765472, 242916,240559,238845,234094,258533
256195
257507
255111
835924
849122
852015
983734
767034
986967
988310
258935
988861
988740
281086
1006825
1012700
1012321
1012425
1013023
1038927
275650
1048904
1048964
1058234
1061015
1073747
282474
280564
286977
291791
291445
267120
267024
277145
312461
312967
808999
315000
317731
321248
320945
320189
326273
326572
811074
332404
333245
775939
244189
243455
231042
230848
260534
258837
257480
817509
828288
828286
831279
832022
832035
831760
859666
987883
988643
1012090
1014032
1014029
1017908
281295
1038951
1041309
1050181
1057366
285167
285487
289560
286730
294432
293048
292105
291338
291263
272063
312327
318295
320918
320317
320356
320215
323337
322877
325460
327253
295012
298635
300393
302227
303414
305762
329887
335908
336885
226421
782210
781944
244206
242433
239646
259781
256224
254447
254028
255604
252631
252720
817428
820482
826332
828442
841281
851484
851288
860148
984396
339401
1013627
1049087
1049569
1049561 ,
1049286 ,
1062085
1067510
284776
290813
290772
290732
293099
292516
291933
263452
270182
267545
277952
312039 ,
313844
317541 ,
320849 ,
815155 ,
325285 ,
295826 ,
300597 ,
303391 ,
801157 ,
308956 ,
331990 ,
339556 ,
226431 ,
227987 ,
241846 ,
240021 ,
234829 ,
234394 ,
233683 ,
261765 ,
256065 ,
254539 ,
255660 ,
255651 ,
253210 ,
246864 ,
826409 ,
828186 ,
831252 ,
242157 ,
840303 ,
852269 ,
983264 ,
983173 ,
986591 ,
1008504 ,
1013983 ,
295806 ,
1039008 ,
1038972 ,
1050136 ,
250229 ,
1058671 ,
1057912 ,
1060669 ,
1061640 ,
1067352 ,
1067502 ,
280620 ,
279532 ,
284078 ,
283463 ,
293376 ,
268894 ,
272560 ,
270458 ,
270793 ,
272037 ,
271620 ,
271674 ,
274542 ,
274674 ,
275748 ,
317071 ,
325207 ,
325547 ,
295278 ,
295378 ,
301400 ,
307101 ,
308285 ,
340100 ,
338555 ,
236747 ,
229723 ,
261985 ,
258130 ,
259116 ,
259842 ,
255097 ,
251607 ,
251510 ,
842417 ,
842251 ,
986587 ,
988500 ,
1001712 ,
1001969 ,
1001759 ,
1002561 ,
1012231 ,
1017132 ,
1030647 ,
1038318 ,
1038952 ,
1049098 ,
1050006 ,
1058032 ,
1074120 ,
1080777 ,
291402 ,
291301 ,
291186 ,
265555 ,
262343 ,
264024 ,
820368 ,
273007 ,
831597 ,
270934 ,
808591 ,
313635 ,
326140 ,
296340 ,
300616 ,
984054 ,
248578 ,
245817);
select * from at_zbm_accum_data where id in(281981,
280982,279973,285576,289547,292887,266104 ,
278424 ,
278160 ,
312557 ,
313107 ,
809217 ,
312035 ,
314860 ,
314452 ,
318937 ,
317498 ,
319980 ,
323459 ,
322737 ,
324199 ,
326855 ,
325875 ,
299967 ,
300500 ,
301038 ,
307052 ,
333858 ,
332033 ,
337673 ,
765472 ,
242916 ,
240559 ,
238845 ,
234094 ,
258533 ,
256195 ,
257507 ,
255111 ,
835924 ,
849122 ,
852015 ,
983734 ,
767034 ,
986967 ,
988310 ,
258935 ,
988861 ,
988740 ,
281086 ,
1006825 ,
1012700 ,
1012321 ,
1012425 ,
1013023 ,
1038927 ,
275650 ,
1048904 ,
1048964 ,
1058234 ,
1061015 ,
1073747 ,
282474 ,
280564 ,
286977 ,
291791 ,
291445 ,
267120 ,
267024 ,
277145 ,
312461 ,
312967 ,
808999 ,
315000 ,
317731 ,
321248 ,
320945 ,
320189 ,
326273 ,
326572 ,
811074 ,
332404 ,
333245 ,
775939 ,
244189 ,
243455 ,
231042 ,
230848 ,
260534 ,
258837 ,
257480 ,
817509 ,
828288 ,
828286 ,
831279 ,
832022 ,
832035 ,
831760 ,
859666 ,
987883 ,
988643 ,
1012090 ,
1014032 ,
1014029 ,
1017908 ,
281295 ,
1038951 ,
1041309 ,
1050181 ,
1057366 ,
285167 ,
285487 ,
289560 ,
286730 ,
294432 ,
293048 ,
292105 ,
291338 ,
291263 ,
272063 ,
312327 ,
318295 ,
320918 ,
320317 ,
320356 ,
320215 ,
323337 ,
322877 ,
325460 ,
327253 ,
295012 ,
298635 ,
300393 ,
302227 ,
303414 ,
305762 ,
329887 ,
335908 ,
336885 ,
226421 ,
782210 ,
781944 ,
244206 ,
242433 ,
239646 ,
259781 ,
256224 ,
254447 ,
254028 ,
255604 ,
252631 ,
252720 ,
817428 ,
820482 ,
826332 ,
828442 ,
841281 ,
851484 ,
851288 ,
860148 ,
984396 ,
339401 ,
1013627 ,
1049087 ,
1049569 ,
1049561 ,
1049286 ,
1062085 ,
1067510 ,
284776 ,
290813 ,
290772 ,
290732 ,
293099 ,
292516 ,
291933 ,
263452 ,
270182 ,
267545 ,
277952 ,
312039 ,
313844 ,
317541 ,
320849 ,
815155 ,
325285 ,
295826 ,
300597 ,
303391 ,
801157 ,
308956 ,
331990 ,
339556 ,
226431 ,
227987 ,
241846 ,
240021 ,
234829 ,
234394 ,
233683 ,
261765 ,
256065 ,
254539 ,
255660 ,
255651 ,
253210 ,
246864 ,
826409 ,
828186 ,
831252 ,
242157 ,
840303 ,
852269 ,
983264 ,
983173 ,
986591 ,
1008504 ,
1013983 ,
295806 ,
1039008 ,
1038972 ,
1050136 ,
250229 ,
1058671 ,
1057912 ,
1060669 ,
1061640 ,
1067352 ,
1067502 ,
280620 ,
279532 ,
284078 ,
283463 ,
293376 ,
268894 ,
272560 ,
270458 ,
270793 ,
272037 ,
271620 ,
271674 ,
274542 ,
274674 ,
275748 ,
317071 ,
325207 ,
325547 ,
295278 ,
295378 ,
301400 ,
307101 ,
308285 ,
340100 ,
338555 ,
236747 ,
229723 ,
261985 ,
258130 ,
259116 ,
259842 ,
255097 ,
251607 ,
251510 ,
842417 ,
842251 ,
986587 ,
988500 ,
1001712 ,
1001969 ,
1001759 ,
1002561 ,
1012231 ,
1017132 ,
1030647 ,
1038318 ,
1038952 ,
1049098 ,
1050006 ,
1058032 ,
1074120 ,
1080777 ,
291402 ,
291301 ,
291186 ,
265555 ,
262343 ,
264024 ,
820368 ,
273007 ,
831597 ,
270934 ,
808591 ,
313635 ,
326140 ,
296340 ,
300616 ,
984054 ,
248578 ,
245817 )and kpi='fsecash';
select distinct(PERSON_ID) from at_sales_person_tree where MANAGER_ID
in(221430,
221430,221366,859628,1008415,859637,859628,221286,221603,221498,221470,
1060521,1066743,
1066743,1079050,1008415,1060502) and date_format(start_date, '%Y%m%d') <=
DATE_format('2014-12-01','%Y%m%d') and end_date >= '2014-12-01' and
circle_id=16;
select PERSON_ID from at_sales_person_tree where MANAGER_ID in(select
distinct(PERSON_ID) from at_sales_person_tree where MANAGER_ID
in(221430,221430,221366,859628,1008415,859637,859628,221286,221603,22149
8,221470,1060521,1066743,1066743,1079050,1008415,1060502) and
date_format(start_date, '%Y%m%d') <= DATE_format('2014-12-01','%Y%m%d') and
end_date >= '2014-12-01' and circle_id=16) and
date_format(start_date, '%Y%m%d') <= DATE_format('2014-12-01','%Y%m%d') and
end_date >= '2014-12-01' and circle_id=16;
select person_id from at_sales_person_tree where MANAGER_ID
in(222628,222518,222732,222980,225238,1057149) and date_format(start_date,
'%Y%m%d') <= DATE_format('2014-12-01','%Y%m%d') and end_date >= '2014-12-
01' and circle_id=16;
select * from at_outlet_accum_data where id in(
select PERSON_ID from at_sales_person_tree where MANAGER_ID in(select
distinct(PERSON_ID) from at_sales_person_tree where MANAGER_ID
in( 221430,221366,859628,1008415,859637,859628,221286,221603,221498,2214
70,1060521,1066743,106743,1079050,1008415,1060502) and
date_format(start_date, '%Y%m%d') <= DATE_format('2014-12-01','%Y%m%d') and
end_date >= '2014-12-01' and circle_id=16 and
date_format(start_date, '%Y%m%d') <= DATE_format('2014-12-01','%Y%m%d') and
end_date >= '2014-12-01' and circle_id=16 ) ) and kpi='actret' and d1>0;
select * from at_tm_accum_data where id='221030' and kpi='ACTRET';
select * from at_outlet_accum_data where kpi='ACTRET';
select distinct aspt.MANAGER_ID as fse,count(disctt(aspt.person_id)) as retValue
from at_outlet_accum_data accumtable ,at_sales_person_tree aspt where
[Link]=aspt.person_id and aspt.circle_id=16 and
date_format(aspt.start_date, '%Y%m%d') <= DATE_format('2014-12-01','%Y%m%d')
AND date_format(aspt.end_date, '%Y%m%d') > DATE_format('2014-12-01','%Y%m
%d') and [Link]=DATE_format('2014-12-01','%Y-%m-01') group by
aspt.manager_id;
select * from at_fse_accum_data where id in (Select PERSON_ID from
at_sales_person_tree where manager_id=488670 AND END_DATE LIKE('%3999%'))
and kpi='VTSGA' and dt='2014-11-01';
select * from at_active_fse_dump where ACTOR_STATE='Chhattisgarh' and msisdn in
(9981948874,
9993033056,9752811123,9993879755,9993048958,9993048960,9993163873);
select * from at_airtel_prepaid_money_circle_mapping;
select * from at_sales_persons where mobile_no=9893749787;
select * from at_sales_persons where id in(222646);
select * from at_dist_accum_data where id in() and kpi='fsecash';
select * from at_sales_person where id='222646' ;
select person_id from at_sales_person_tree where MANAGER_ID
in(222646,222717,222562,223136,223142,223253,1032884) and end_date >=
'2014-12-01';
select person_id from at_sales_person_tree where MANAGER_ID
in(294518,265366,265254,265688,268019,272700,271649) and
date_format(start_date, '%Y%m%d') <= DATE_format('2014-12-01','%Y%m%d') and
end_date >= '2014-12-01' and circle_id=16
select * from at_sales_persons where id=221030;
select PERSON_ID from at_sales_person_tree where manager_id=221030 and
start_date<='2014-12-01' and end_date>'2014-12-01';
select person_id from at_sales_person_tree where manager_id in (select PERSON_ID
from at_sales_person_tree where manager_id=221001 and start_date<='2014-12-
01' and end_date>'2014-12-01') and START_DATE<='2014-12-01' and
END_DATE>'2014-12-01' and circle_id=16;
select person_id from at_sales_person_tree where manager_id in (select person_id
from at_sales_person_tree where manager_id in (select PERSON_ID from
at_sales_person_tree where manager_id=221030 and start_date<='2014-12-01'
and end_date>'2014-12-01') and START_DATE<='2014-12-01' and
END_DATE>'2014-12-01' and circle_id=16) and START_DATE<='2014-12-01' and
END_DATE>'2014-12-01' and circle_id=16;
select * from at_outlet_accum_data where id in (select person_id from
at_sales_person_tree where manager_id in (select person_id from
at_sales_person_tree where manager_id in (select PERSON_ID from
at_sales_person_tree where manager_id=221030 and start_date<='2014-12-01'
and end_date>'2014-12-01') and START_DATE<='2014-12-01' and
END_DATE>'2014-12-01' and circle_id=16)
and START_DATE<='2014-12-01' and END_DATE>'2014-12-01' and circle_id=16)
and kpi='ACTRET' and d1=1 and dt='2014-12-01';
Increase Customer base Increase Activation outlet - DSSO, Monitor Quality of acquisition, Speed of
activation, Control M2 Decay, Focus on High Value FR/ high rental plans, increase no of quality outlet and
extraction from quality outlet, control churn, keep a close watch on competition and their product offering.
Distribution management & expansion Increasing depth and width of distribution thru granular
approach. Increase spread of recharge, activation & data outlets. Increase retail footprint for airtel
(franchise+own retail+ASC). Market mapping using GIS tools (lat / long) and Tower wiser data to ensure
existing and new towers are profitable. Closely monitor ROI of channel partners and engagement level.
Increase Revenue -Focus on High ARPU products in Voice, VAS and Data, both on acquisition and
recharge business, Focus on increasing High Value base, Revenue extraction from various legs
migrant/STD, Daily shopper, High Value, Data 2G/3G. Keep a close watch on competition and feed the
information to marketing teams to evaluate the impact.
Mark to Market - Increase Customer Market share & Revenue Market Share thru intelligently analyzing
competitive data.
GTM increase Sales force effectiveness. Thru training, engagement and effective deployment of
national programs.
Increase Retail visibility, notice ability & share of recommendation.
Realized revenue upside and cost savings through improvement in quality of customer acquisitions
measured through M2 Decay
- Defined and drove lead parameters Gross Adds from Quality Outlets and High Value First Recharge to
improve M2 Decay
- Authored content and defined the Quality Way-of-Working to cascade agenda to the last mile
Sales Force Effectiveness
- Launched GOAL, a scorecard encapsulating organizational priorities, for the internal and external sales
team, receiving over 3 Mn hits per month across SMS and Mobile App
- Translated organizational priorities into metrics and encapsulated into a scorecard (iScore and 3iScore)
for each sales actor from regional sales managers to FSE
- Ranking basis scorecard amongst peers which are currently the backbone of engagement programs
with Distributors and FSEs
Reduced M2 decay in new acquisition by over 50% while increasing gross add for the period April 13 to
Dec 13.
Delivered the shortest TAT for prepaid activation across MPCG circle after new FTA process was
launched Nov 12.